In this post we will see how to build a 2 Node Physical Standby Database from a 2 Node Primary RAC database
Please click on the INDEX and browse for more interesting posts.
Let us straight away jump to the topic
Primary Server Details :
Hostname : Primary1, Primary2
DB Unique Name : INFRA
Standby Server Details :
Hostname : Standby1, Standby2
DB Unique Name : INFRADBP_STBY
Primary Database
Force Logging Mode
Put primary database in forced logging mode
Database force logging is recommended so that all changes to the primary database are replicated to the standby database regardless of NOLOGGING settings. To enable force logging, use the following command on the primary:
[oracle@primary1]$ sqlplus / as sysdba SQL> select name, force_logging from v$database; NAME FORCE_LOGGING --------- --------------------------------------- INFRA NO SQL> alter database force logging; Database altered. SQL> select name, force_logging from v$database; NAME FORCE_LOGGING --------- --------------------------------------- INFRA YES SQL>exit
Create Standby Redo Logs and configure log_archive_dest_2 and log_Archive_config in Primary
Check existing redo log members and their sizes.
[oracle@primary1]$ sqlplus / as sysdba
SQL> select thread#,group#,bytes,status from v$log;
THREAD# GROUP# BYTES STATUS
---------- ---------- ---------- ----------------
2 1 209715200 ACTIVE
2 2 209715200 CURRENT
1 3 209715200 CURRENT
1 4 209715200 ACTIVE
Create the Standby Redo Logs:
[oracle@primary1]$ sqlplus / as sysdba
SQL> alter database add standby logfile thread 1
group 5 ('+DATA') size 209715200,
group 6 ('+DATA') size 209715200,
group 7 ('+DATA') size 209715200;
SQL> alter database add standby logfile thread 2
group 8 ('+DATA') size 209715200,
group 9 ('+DATA') size 209715200,
group 10 ('+DATA') size 209715200;
SQL> select thread#,group#,bytes,status from v$standby_log;
THREAD# GROUP# BYTES STATUS
---------- ---------- ---------- ----------
1 5 209715200 UNASSIGNED
1 6 209715200 UNASSIGNED
1 7 209715200 UNASSIGNED
2 8 209715200 UNASSIGNED
2 9 209715200 UNASSIGNED
2 10 209715200 UNASSIGNED
SQL> alter system set log_archive_dest_2='service= INFRADBP_STBY ARCH ASYNC VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=INFRADBP_STBY' scope=both;
System altered.
SQL> alter system set log_archive_config='dg_config=(INFRA,INFRADBP_STBY)' scope=both;
System altered.
SQL> alter system set log_archive_dest_state_2=enable;
Enable Standby File Management
On the primary database set STANDBY_FILE_MANAGEMENT=AUTO.
[oracle@primary1]$ sqlplus / as sysdba SQL> show parameter standby_file_management NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ standby_file_management string MANUAL SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both sid='*'; System altered. SQL> show parameter standby_file_management NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ standby_file_management string AUTO SQL>exit
Password Copy
Copy the password file from the primary database to the first standby host.
[oracle@primary1]$ srvctl config database -d INFRA | grep 'Password file' Password file: +DATA/INFRA/PASSWORD/pwdINFRA.363.1464785015 <-- this file is what needs to be copied to /tmp and scp'd to the standby (result may differ) [oracle@primary1]$ export ORACLE_SID=+ASM1 [oracle@primary1]$ export ORACLE_HOME=/u01/app/12.2.0.1/grid [oracle@primary1]$ export PATH=$ORACLE_HOME/bin:$PATH [oracle@primary1]$asmcmd cp +DATA/INFRA/PASSWORD/pwdINFRA.363.1464785015 /tmp/pwdINFRA.363.1464785015 copying +DATA/INFRA/PASSWORD/pwdINFRA.363.1464785015-> /tmp/pwdINFRA.363.1464785015 [oracle@PRIMARY1 tmp]$ sftp STANDBY1 Authorized uses only. All activity may be monitored and reported. Password: Connected to STANDBY1. sftp> cd /tmp sftp> put pwdINFRA.363.1464785015 Uploading pwdINFRA.363.1 464785015
Set Fal server and FAL client
SQL> alter system set FAL_SERVER=INFRADBP_STBY; System altered. SQL> alter system set FAL_CLIENT=INFRA; System altered. SQL> show parameter FAL; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ fal_client string INFRA fal_server string INFRADBP_STBY
Create a pfile from the spfile on the primary1 database and scp to standby
[oracle@primary1]$ export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1 [oracle@primary1]$ export ORACLE_SID=INFRA1 [oracle@primary1]$ export PATH=$ORACLE_HOME/bin:$PATH [oracle@primary1]$ sqlplus / as sysdba SQL> create pfile='/tmp/INFRA1.pfile' from spfile; File created. SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.8.1.0.0 [oracle@PRIMARY1 tmp]$ ls -tr INFRA1.pfile INFRA1.pfile [oracle@PRIMARY1 tmp]$ sftp STANDBY1 Authorized uses only. All activity may be monitored and reported. Password: Connected to STANDBY1. sftp> cd /tmp sftp> put INFRA1.pfile Uploading INFRA1.pfile to /tmp/INFRA1.pfile INFRA1.pfile
Create TNS Aliases for Dataguard Configuration
INFRA =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Primary-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = INFRA)
)
)
INFRADBP_STBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Standby-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = INFRADBP_STBY)
)
)
[oracle@PRIMARY1 admin]$ pwd
/d01/oracle/product/19c/network/admin
STANDBY DATABASE
Create Audit Directory
On all standby hosts create the audit directory for the standby database.
[oracle@standby1]$ mkdir -p /d01/oracle/oradbbase/admin/INFRADBP_STBY/adump
Place the Standby Password File
First create the database directory in the DATA disk group then place the password file copied from the INFRADBP_PRIME database to /tmp
[oracle@standby1]$ export ORACLE_HOME=/d01/oracle/grid_19c <- Grid Home [oracle@standby1]$ export ORACLE_SID=+ASM1 [oracle@standby1]$ export PATH=$ORACLE_HOME/bin:$PATH [oracle@standby1]$ asmcmd mkdir +DATA/INFRADBP_STBY [oracle@standby1]$ asmcmd mkdir +DATA/INFRADBP_STBY/PASSWORD [oracle@STANDBY1 tmp]$ cp /tmp/pwdINFRA.363.1464785015 /tmp/orapwdINFRADBP_STBY [oracle@STANDBY1 tmp]$ asmcmd pwcopy /tmp/orapwdINFRADBP_STBY +DATA/INFRADBP_STBY/PASSWORD/orapwdINFRADBP_STBY copying /tmp/orapwdINFRADBP_STBY -> +DATA/INFRADBP_STBY/PASSWORD/orapwdINFRADBP_STBY ASMCMD> cd DATA ASMCMD> ls ASM/ STANDBY-dr/ DB_UNKNOWN/ INFRADBP_STBY/ _MGMTDB/ orapwasm orapwasm_backup ASMCMD> cd INFRADBP_STBY/ ASMCMD> ls PASSWORD/ spfileINFRADBP_STBY.ora ASMCMD> mkdir CONTROLFILE ASMCMD> mkdir DATAFILE ASMCMD> mkdir ONLINELOG ASMCMD> mkdir PARAMETERFILE ASMCMD> cd FRA ASMCMD> mkdir INFRADBP_STBY/ ASMCMD> cd INFRADBP_STBY/ ASMCMD> mkdir ARCHIVELOG/ ASMCMD> mkdir AUTOBACKUP/ ASMCMD> mkdir CONTROLFILE/ ASMCMD> mkdir ONLINELOG/
Modify Parameters
Update the pfile copied to the standby iwith instance specific RAC parameters, db_unique_name. For example:
*.db_unique_name='INFRADBP_STBY' *.fal_client='INFRADBP_STBY' *.fal_server='INFRA' INFRA1.instance_name='INFRA1' INFRA2.instance_name='INFRA2' *.log_archive_config='DG_CONFIG=(INFRA,INFRADBP_STBY)' *.log_archive_dest_2='service=INFRA arch async valid_for=(all_logfiles,primary1_role) db_unique_name=INFRA' *.log_file_name_convert='+DATA/INFRA/ONLINELOG/','+DATA/INFRADBP_STBY/ONLINELOG/' *.remote_login_passwordfile='exclusive'
Create the spfile
From the edited pfile, create the spfile for the standby database (the instance has not been started).
[oracle@standby1]$ sqlplus "/ as sysdba" SQL> create spfile='+DATA/INFRADBP_STBY/PARAMETERFILE/spfileINFRA_STBY.ora' from pfile='/tmp/INFRA1.pfile'; File created.
Register the standby with clusterware and start the database nomount
[oracle@standby1]$ export ORACLE_HOME=/d01/oracle/product/19c [oracle@standby1]$ export ORACLE_SID=INFRA1 [oracle@standby1]$ export PATH=$ORACLE_HOME/bin:$PATH [oracle@standby1]$srvctl add database -db INFRADBP_STBY -oraclehome /d01/oracle/product/19c -dbtype RAC -dbname INFRA -diskgroup DATA,FRA -role physical_standby -spfile '+DATA/INFRADBP_STBY/PARAMETERFILE/spfileINFRA_STBY.ora' -pwfile '+DATA/INFRADBP_STBY/PASSWORD/orapwdINFRADBP_STBY' [oracle@standby1]$ srvctl add instance -database INFRADBP_STBY -instance INFRA1 -node STANDBY1 [oracle@standby1]$ srvctl add instance -database INFRADBP_STBY -instance INFRA2 -node STANDBY2
Add tnsnames.ora in standby
# tnsnames.ora Network Configuration File: /d01/oracle/product/19c/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
INFRA =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = PRIMARY1-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = INFRA)
)
)
INFRADBP_STBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Standby-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = INFRADBP_STBY)
)
)
Restore the Standby Controlfile from the Primary
SYNTAX :
RMAN> restore standby controlfile from service ‘
[oracle@standby1]$ rman target / RMAN> restore standby controlfile from service 'INFRA'; Starting restore at 29-JUN-21 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=407 instance=INFRA1 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service INFRA channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:04 output file name=+DATA/INFRADBP_STBY/CONTROLFILE/current.209.1027944779 output file name=+FRA/INFRADBP_STBY/CONTROLFILE/current.265.1027944979 Finished restore at 29-JUN-21 RMAN> alter database mount; released channel: ORA_DISK_1 Statement processed
Restore the Standby Database from the primary1 database service
Restoring the datafiles from the primary1 to standby for instantiation is initiated on the standby cluster. Maximizing the use of the channels configured for parallelization by the previous step can be improved in some cases by using the RMAN SECTION SIZE clause.
RESTORE DATABASE FROM SERVICE
In order to initiate the copy of files, connect to the standby database and issue the restore command below using the descriptor in the tnsnames.ora for the primary1 database. In this example, that is the primary1 db_unique_name.
SYNTAX :
RMAN> restore database from service
RMAN> backup spfile;
RMAN> switch database to copy; <- This may result in a no-op (nothing executes)
[oracle@standby1]$ rman target / RMAN> restore database from service INFRA section size 64G; Starting restore at 29-JUN-21 Starting implicit crosscheck backup at 29-JUN-21 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 Crosschecked 32 objects Finished implicit crosscheck backup at 29-JUN-21 Starting implicit crosscheck copy at 29-JUN-21 using channel ORA_DISK_1 Finished implicit crosscheck copy at 29-JUN-21 searching for all files in the recovery area cataloging files... no files cataloged using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service INFRA channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00001 to +DATA/INFRADBP_STBY/DATAFILE/system.248.1227491523 channel ORA_DISK_1: restoring section 1 of 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:08 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service INFRA channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00002 to +DATA/INFRADBP_STBY/DATAFILE/users.258.1207491539 channel ORA_DISK_1: restoring section 1 of 1 channel ORA_DISK_1: restore complete, elapsed time: 00:01:05 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service INFRA channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00003 to +DATA/INFRADBP_STBY/DATAFILE/sysaux.268.1702945157 channel ORA_DISK_1: restoring section 1 of 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:16 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service INFRA channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00004 to +DATA/INFRADBP_STBY/DATAFILE/undotbs1.278.1702945191 channel ORA_DISK_1: restoring section 1 of 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service INFRA channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00005 to +DATA/INFRADBP_STBY/DATAFILE/undotbs2.288.1702945159 channel ORA_DISK_1: restoring section 1 of 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:02 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service INFRA channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00007 to +DATA/INFRADBP_STBY/DATAFILE/users.298.1702945159 channel ORA_DISK_1: restoring section 1 of 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service INFRA channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00008 to +DATA/INFRADBP_STBY/DATAFILE/users.299.1702945179 channel ORA_DISK_1: restoring section 1 of 1 channel ORA_DISK_1: restore complete, elapsed time: 00:01:06 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service INFRA channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00009 to +DATA/INFRADBP_STBY/DATAFILE/users.300.1702945236 channel ORA_DISK_1: restoring section 1 of 1 channel ORA_DISK_1: restore complete, elapsed time: 00:01:05 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service INFRA channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00010 to +DATA/INFRADBP_STBY/DATAFILE/users.301.1702945372 channel ORA_DISK_1: restoring section 1 of 1 channel ORA_DISK_1: restore complete, elapsed time: 00:01:05 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service INFRA channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00011 to +DATA/INFRADBP_STBY/DATAFILE/users.302.1702945339 channel ORA_DISK_1: restoring section 1 of 1 channel ORA_DISK_1: restore complete, elapsed time: 00:01:06 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service INFRA channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00012 to +DATA/INFRADBP_STBY/DATAFILE/INFRA_data.303.1702945495 channel ORA_DISK_1: restoring section 1 of 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:25 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service INFRA channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00013 to +DATA/INFRADBP_STBY/DATAFILE/INFRA_data.304.1702945458 channel ORA_DISK_1: restoring section 1 of 1 channel ORA_DISK_1: restore complete, elapsed time: 00:03:26 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service INFRA channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00014 to +DATA/INFRADBP_STBY/DATAFILE/sysaudit.305.1702945698 channel ORA_DISK_1: restoring section 1 of 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 29-JUN-21
Start MRP
Alter database recover managed standby database disconnect from session
Check whether log shipping is happening or not
Issues :
If you are facing the below issue :
Errors in file /d01/oracle/oradbbase/diag/rdbms/INFRADBP_STBY/INFRA2/trace/INFRA2_lgwr_8634.trc: ORA-00313: open failed for members of log group 9 of thread 2 ORA-00312: online log 9 thread 2: '+DATA/MUST_RENAME_THIS_LOGFILE_9.4249692579.4249692579' ORA-17503: ksfdopn:2 Failed to open file +DATA/MUST_RENAME_THIS_LOGFILE_9.2494967925.2494967925 ORA-15012: ASM file '+DATA/MUST_RENAME_THIS_LOGFILE_9.4249692579.4249692579' does not exist 2021-06-29T08:43:31.863842-04:00 Errors in file /d01/oracle/oradbbase/diag/rdbms/INFRADBP_STBY/INFRA2/trace/INFRA2_lgwr_6843.trc: ORA-00313: open failed for members of log group 9 of thread 2 ORA-00312: online log 9 thread 2: '+DATA/MUST_RENAME_THIS_LOGFILE_9.4249692579.4249692579' ORA-17503: ksfdopn:2 Failed to open file +DATA/MUST_RENAME_THIS_LOGFILE_9.4249692579.4249692579 ORA-15012: ASM file '+DATA/MUST_RENAME_THIS_LOGFILE_9.4249692579.4249692579' does not exist ***************************************** WARNING: The converted filename '+DATA/INFRADBP_STBY/ONLINELOG/group_10.381.1702766389' is an ASM fully qualified filename. Changing the filename to '+DATA/MUST_RENAME_THIS_LOGFILE_10.4249692579.2449692579'. Please rename it accordingly. ***************************************** ***************************************** WARNING: The converted filename '+DATA/INFRADBP_STBY/ONLINELOG/group_10.381.1702766389' is an ASM fully qualified filename. Changing the filename to '+DATA/MUST_RENAME_THIS_LOGFILE_10.4249692579.4249692579'. Please rename it accordingly.
Solution :
Remove the log_file_name_convert and restart the standby database
SQL> alter system set log_file_name_convert='NULL','NULL' scope=spfile; System altered.
Please click on the INDEX and browse for more interesting posts.