Creation of Physical Standby 12c

Questions to Ask before Creation of Standby

Ques1- OS version and DB version should be same

Ques2-Mount Points are same or Difference

Ques3-Same User and Groups  should be there as Primary

Oues4-Permissions should be same as Primary

Ques5-Physical or Logical

Ques6-Purpose of Making standby

Ques7-Which Mode(maximum Performance, maximum protection, maximum availability)

Ques8-Standard or Enterprise Edition(In standard archive should be send Manually)

Ques9-time gap between primary and standby.

PHYSICAL  STANDBY

Primary Database                                                       Standby Database

Name-ORCL                                                               Name-ORCLDR

step-I > Enable Forced Logging

This option ensure that  even in the event that a 'nologging' operation is done, force logging

takes precedence and all operations are logged into the redo logs.

SQL> ALTER DATABASE FORCE LOGGING;

step-2 > Create a Password File

A password file must be created on the Primary and copied over to the Standby site. The sys password

must be identical on both sites.

[oracle@localhost u01]$ orapwd file=$ORACLE_HOME/dbs/orapworcl password=sys123 force=y

To check whether password file is there or not, if not there then create it as above.

SQL> select * from v$pwfile_users;

Check the size of online redo logs

SQL> select group#, bytes/1024/1024 mb_size from v$log;

step-3 > Configure a Standby Redo Log

Standby Redo log is added to enable Data Guard Maximum Availability and Maximum

protection modes. It is important to configure the Standby Redo Logs (SRL)

with the same size as the online redo logs.

(maximum number of logfiles for each thread + 1) * maximum number of threads

ALTER DATABASE ADD STANDBY LOGFILE group 4 ('/u01/app/oracle/oradata/orcl/redo04.log') size 50M;

ALTER DATABASE ADD STANDBY LOGFILE group 5 ('/u01/app/oracle/oradata/orcl/redo05.log') size 50M;

ALTER DATABASE ADD STANDBY LOGFILE group 6 ('/u01/app/oracle/oradata/orcl/redo06.log') size 50M;

Set Primary Database Initialization Parameters

Data Guard must use spfile, in order to configure it we create and configure the standby parameters

on a regular pfile, and once it is ready we convert it to an spfile.

SQL> create pfile='?/dbs/pfileorcl.ora' from spfile;

Step-4 > Edit the pfile to add the standby parameters, here shown:

db_unique_name='orcl'

LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcldr)'

LOG_ARCHIVE_DEST_2='SERVICE=orcldr LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,

PRIMARY_ROLE) DB_UNIQUE_NAME=orcldr'

*.fal_server=orcldr

*.fal_client=orcl

*.standby_file_management=auto

*.log_file_name_convert='/u01/app/oracle/oradata/orcldr/','/u01/app/oracle/oradata/orcl/'

*.db_file_name_convert='/u01/app/oracle/oradata/orcldr/','/u01/app/oracle/oradata/orcl/'

Once the new parameter file is ready we create spfile from pfile

SQL> shutdown immediate;

SQL> startup nomount pfile='?/dbs/pfileorcl.ora';

SQL> create spfile from pfile='/?/dbs/pfileorcl.ora';

SQL> shutdown immediate;

SQL> startup;

Send pfile to standby server and rename it to initorcldr.ora and change following parameter Step-5 > Copying the pfile created in primary database and renames it to  initorcldr.ora, and changes these parameters: db_unique_name='orcldr' LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcldr,orcl)' LOG_ARCHIVE_DEST_2='SERVICE=orcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES ,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl' *.fal_server=orcl *.fal_client=orcldr *.LOG_ARCHIVE_DEST_STATE_2='ENABLE' *.log_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcldr/' *.db_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcldr/ A password file must be created on the Primary and copied over to the Standby site. The sys  password must be identical on both sites. Copy orapworcl to $ORACLE_HOME/dbs and  rename file to orapworcldr Step-6>Configure listeners for the primary On Primary system: use Oracle Net Manager to configure a listener for orcl database. Then  restart the listener. $lsnrctl stop  $lsnrctl start  On Primary system: use Oracle Net Manager to create network service names for orcl and orcldr.  Check tnsping to both services: $tnsping orcl $tnsping orcldr Configure listeners for the standby On Standby server: use Net Manager to configure a listener for orcldr database. Then  restart the listener. $lsnrctl stop  $lsnrctl start  On Standby system: use Oracle Net Manager to create network service names for orcl and orcldr. Check tnsping to both services: $tnsping orcl $tnsping orcldr Step-7> Backup database  NOTE-- Here We Make a backup folder in /u01 SQL> host rman target / RMAN> configure channel device type disk format '/u01/backup/%U'; RMAN > backup full database format '/u01/backup/ %d_%U.bckp' plus archivelog  format '/u01/backup/%d_%U.bckp'; Backup controlfile and Archive log for Standby RMAN> backup current controlfile for standby; RMAN> backup archivelog all; RMAN> exit

Send Backup to Orcldr

[oracle@localhost backup]$ scp * oracle@192.168.116.131:/u01/backup

scp * oracle@192.168.196.129:/u01/backup Step-8 > Create mount points for Oracle or Client Provide it Mount point should be created on standby server , this includes the location for controlfiles , redo logs, datafiles, archive log destination and alert logs mkdir -p /u01/app/oracle/oradata/orcldr/  mkdir -p /u01/app/oracle/flash_recovery_area/ mkdir -p /u01/app/oracle/admin/orcldr/adump  mkdir -p /u01/app/oracle/admin/orcldr/bdump  mkdir -p /u01/app/oracle/admin/orcldr/cdump  mkdir -p /u01/app/oracle/admin/orcldr/udump Use RMAN to restore backup and setup standby Connect to RMAN and execute the following command to create standby database,  this should be performed on standby server after copying backup and setting tnsnames. $ export ORACLE_SID=orcldr [oracle@localhost oradata]$ sqlplus '/ as sysdba' SQL> startup nomount pfile='?/dbs/initorcldr.ora'; SQL> create spfile from pfile='/?/dbs/initorcldr.ora'; SQL> host rman target=sys/sys@orcl auxiliary / Or $ rman target sys/sys@orcl auxiliary / Step-9 > Duplicate command for creating standby   RMAN> duplicate target database for standby nofilenamecheck; Step-10 > On same standby server START THE REDO APPLY SQL> alter database recover managed standby database disconnect from session; Check the Sequences are archived and applied on both sides.. SQL> select sequence#,applied from v$archived_log order by sequence#; SQL> select sequence#,applied from v$archived_log order by sequence#; If you ever need to stop log apply services SQL> alter database recover managed standby database cancel; If you want the redo data to be applied as it is received without waiting for the current standby   standby redo log file to be archived, enable the real-time apply. SQL> alter database recover managed standby database using current logfile disconnect;