Recovering Standby database From Priamry Using Service Name in 19c
If due to some reason Archivelog got deleted from Primary Site and Not shipped yet on Standby Site,
then there will be Error in Alert log of Primary and Standby Site, And hence Standby Site will not be in Sync.
Pre-12c to recover Standby database there was many Manual Steps invloved ,But from 12cR2 this is automated and simplyfied.
Demonstrations
Let we create a Scenario and DEFER log_Archive_Dest_2 to Disable archive transfer on Standby Site and Delete few archives from Primary Side - DONOT TRY ON PROD ENVIRONMENT.
On Primary Side. [oracle@racnoden1 ~]$ sqlplus "/as sysdba" SQL> show parameter log_archive_Dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_2 string SERVICE=TNSTAR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DBTAR SQL> alter system set log_archive_dest_state_2='DEFER' ; System altered. SQL> @sync Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ---------- 1 247 247 0 2 184 184 0 Let we Login to grid user and Delete few archives from Primary Site (ONLY FOR TESTING PURPOSE) [grid@racnoden1 ~]$ asmcmd ASMCMD> cd 2022_07_25/ ASMCMD> rm thread_2_seq_184.683.1110988779 thread_2_seq_183.682.1110988773 ASMCMD> rm thread_1_seq_247.681.1110988767 ASMCMD> rm thread_1_seq_246.676.1110987021 Now let we enable log_archive_Dest_state_2 as below from Primary. SQL> alter system set log_archive_dest_state_2='ENABLE'; System altered. Primary Side alert log reported the same as below Current log# 3 seq# 249 mem# 1: +FRA/DBSET/ONLINELOG/online_a3.log 2022-07-25T16:04:36.045150+05:30 Errors in file /ora_home/app/oracle/diag/rdbms/dbset/DBSET1/trace/DBSET1_tt00_19229.trc: ORA-00308: cannot open archived log '+FRA/DBSET/ARCHIVELOG/2022_07_25/thread_1_seq_247.681.1110988767' ORA-17503: ksfdopn:2 Failed to open file +FRA/DBSET/ARCHIVELOG/2022_07_25/thread_1_seq_247.681.1110988767 ORA-15012: ASM file '+FRA/DBSET/ARCHIVELOG/2022_07_25/thread_1_seq_247.681.1110988767' does not exist 2022-07-25T16:04:36.047946+05:30 Errors in file /ora_home/app/oracle/diag/rdbms/dbset/DBSET1/trace/DBSET1_tt00_19229.trc: ORA-00308: cannot open archived log '+FRA/DBSET/ARCHIVELOG/2022_07_25/thread_1_seq_246.676.1110987021' ORA-17503: ksfdopn:2 Failed to open file +FRA/DBSET/ARCHIVELOG/2022_07_25/thread_1_seq_246.676.1110987021 ORA-15012: ASM file '+FRA/DBSET/ARCHIVELOG/2022_07_25/thread_1_seq_246.676.1110987021' does not exist 2022-07-25T16:04:40.609066+05:30 On Standby Side. Standby Side alert log reported error while fetching for missing archives as below. PR00 (PID:23174): Media Recovery Waiting for T-1.S-246 PR00 (PID:23174): Fetching gap from T-1.S-246 to T-1.S-247 PR00 (PID:23174): Error 12154 received logging on to the standby PR00 (PID:23174): FAL: Error 12154 connecting to DBSET for fetching gap sequence 2022-07-25T16:06:19.561439+05:30 Errors in file /ora_home/app/oracle/diag/rdbms/dbtar/DBTAR1/trace/DBTAR1_pr00_23174.trc: ORA-12154: TNS:could not resolve the connect identifier specified 2022-07-25T16:06:34.210339+05:30 PR00 (PID:23174): Error 12154 received logging on to the standby PR00 (PID:23174): FAL: Error 12154 connecting to DBSET for fetching gap sequence 2022-07-25T16:06:34.210484+05:30 Errors in file /ora_home/app/oracle/diag/rdbms/dbtar/DBTAR1/trace/DBTAR1_pr00_23174.trc: ORA-12154: TNS:could not resolve the connect identifier specified 2022-07-25T16:06:44.906986+05:30 PR00 (PID:23174): Error 12154 received logging on to the standby PR00 (PID:23174): FAL: Error 12154 connecting to DBSET for fetching gap sequence 2022-07-25T16:06:44.907148+05:30 Errors in file /ora_home/app/oracle/diag/rdbms/dbtar/DBTAR1/trace/DBTAR1_pr00_23174.trc: ORA-12154: TNS:could not resolve the connect identifier specified Check GAP on standby Site as below [oracle@racsetn1 ~]$ sqlplus "/as sysdba" SQL> @sync Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ---------- 1 249 245 4 2 186 179 7
Steps to Recover standby database using Service Name
As our setup is completed now let we proceed to Recover Standby database . => Stop Standby database on All Node except First Node. [oracle@racsetn1 ~]$ srvctl stop database -d DBTAR [oracle@racsetn1 ~]$ srvctl start instance -db DBTAR -i DBTAR1 => Connect to RMAN and execute below command. In this commant TNSSET is PRIMARY DATABASE TNSNAME recover standby database from service TNSSET; [oracle@racsetn1 ~]$ rman target / Recovery Manager: Release 19.0.0.0.0 - Production on Mon Jul 25 16:21:39 2022 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: DBSET (DBID=707766965) RMAN> recover standby database from service TNSSET; Starting recover at 25-JUL-22 using target database control file instead of recovery catalog Executing: alter database flashback off Oracle instance started Total System Global Area 2751463160 bytes Fixed Size 8900344 bytes Variable Size 637534208 bytes Database Buffers 2097152000 bytes Redo Buffers 7876608 bytes contents of Memory Script: { restore standby controlfile from service 'TNSSET'; alter database mount standby database; } executing Memory Script Starting restore at 25-JUL-22 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=254 instance=DBTAR1 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service TNSSET channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:12 output file name=+DATA/DBTAR/CONTROLFILE/control_01.ctl output file name=+FRA/DBTAR/CONTROLFILE/control_02.ctl Finished restore at 25-JUL-22 released channel: ORA_DISK_1 Statement processed Executing: alter system set standby_file_management=manual contents of Memory Script: { set newname for tempfile 1 to "+DATA/DBTAR/TEMPFILE/tmpfile_01.dat"; switch tempfile all; set newname for datafile 1 to "+DATA/DBTAR/DATAFILE/system.261.1108233369"; set newname for datafile 2 to "+DATA/DBTAR/DATAFILE/test_ts.276.1109780343"; set newname for datafile 3 to "+DATA/DBTAR/DATAFILE/sysaux.260.1108233369"; set newname for datafile 4 to "+DATA/DBTAR/DATAFILE/undotbs1.259.1108233369"; set newname for datafile 5 to "+DATA/DBTAR/DATAFILE/undotbs2.258.1108233369"; set newname for datafile 7 to "+DATA/DBTAR/DATAFILE/users.257.1108233369"; set newname for datafile 8 to "+DATA/DBTAR/DATAFILE/rename_file.ora"; catalog datafilecopy "+DATA/DBTAR/DATAFILE/system.261.1108233369", "+DATA/DBTAR/DATAFILE/test_ts.276.1109780343", "+DATA/DBTAR/DATAFILE/sysaux.260.1108233369", "+DATA/DBTAR/DATAFILE/undotbs1.259.1108233369", "+DATA/DBTAR/DATAFILE/undotbs2.258.1108233369", "+DATA/DBTAR/DATAFILE/users.257.1108233369", "+DATA/DBTAR/DATAFILE/rename_file.ora"; switch datafile all; } executing Memory Script executing command: SET NEWNAME Starting implicit crosscheck backup at 25-JUL-22 allocated channel: ORA_DISK_1 Crosschecked 21 objects Finished implicit crosscheck backup at 25-JUL-22 Starting implicit crosscheck copy at 25-JUL-22 using channel ORA_DISK_1 Finished implicit crosscheck copy at 25-JUL-22 searching for all files in the recovery area cataloging files... cataloging done List of Cataloged Files ======================= File Name: +FRA/DBTAR/AUTOBACKUP/2022_07_11/s_1109790315.680.1109790443 File Name: +FRA/DBTAR/ARCHIVELOG/2022_07_25/thread_2_seq_177.683.1110983945 ... ... File Name: +FRA/DBTAR/ARCHIVELOG/2022_06_24/thread_2_seq_9.277.1108237969 renamed tempfile 1 to +DATA/DBTAR/TEMPFILE/tmpfile_01.dat in control file executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME cataloged datafile copy datafile copy file name=+DATA/DBTAR/DATAFILE/system.261.1108233369 RECID=21 STAMP=1110991062 cataloged datafile copy datafile copy file name=+DATA/DBTAR/DATAFILE/test_ts.276.1109780343 RECID=22 STAMP=1110991063 cataloged datafile copy datafile copy file name=+DATA/DBTAR/DATAFILE/sysaux.260.1108233369 RECID=23 STAMP=1110991063 cataloged datafile copy datafile copy file name=+DATA/DBTAR/DATAFILE/undotbs1.259.1108233369 RECID=24 STAMP=1110991064 cataloged datafile copy datafile copy file name=+DATA/DBTAR/DATAFILE/undotbs2.258.1108233369 RECID=25 STAMP=1110991065 cataloged datafile copy datafile copy file name=+DATA/DBTAR/DATAFILE/users.257.1108233369 RECID=26 STAMP=1110991066 cataloged datafile copy datafile copy file name=+DATA/DBTAR/DATAFILE/rename_file.ora RECID=27 STAMP=1110991066 datafile 1 switched to datafile copy input datafile copy RECID=21 STAMP=1110991062 file name=+DATA/DBTAR/DATAFILE/system.261.1108233369 datafile 2 switched to datafile copy input datafile copy RECID=22 STAMP=1110991063 file name=+DATA/DBTAR/DATAFILE/test_ts.276.1109780343 datafile 3 switched to datafile copy input datafile copy RECID=23 STAMP=1110991063 file name=+DATA/DBTAR/DATAFILE/sysaux.260.1108233369 datafile 4 switched to datafile copy input datafile copy RECID=24 STAMP=1110991064 file name=+DATA/DBTAR/DATAFILE/undotbs1.259.1108233369 datafile 5 switched to datafile copy input datafile copy RECID=25 STAMP=1110991065 file name=+DATA/DBTAR/DATAFILE/undotbs2.258.1108233369 datafile 7 switched to datafile copy input datafile copy RECID=26 STAMP=1110991066 file name=+DATA/DBTAR/DATAFILE/users.257.1108233369 datafile 8 switched to datafile copy input datafile copy RECID=27 STAMP=1110991066 file name=+DATA/DBTAR/DATAFILE/rename_file.ora Executing: alter database rename file '+DATA/MUST_RENAME_THIS_LOGFILE_1.4294967295.4294967295' to '+DATA/DBTAR/ONLINELOG/online_01.log' Executing: alter database rename file '+FRA/MUST_RENAME_THIS_LOGFILE_1.4294967295.4294967295' to '+FRA/DBTAR/ONLINELOG/online_11.log' Executing: alter database rename file '+DATA/MUST_RENAME_THIS_LOGFILE_2.4294967295.4294967295' to '+DATA/DBTAR/ONLINELOG/online_02.log' Executing: alter database rename file '+FRA/MUST_RENAME_THIS_LOGFILE_2.4294967295.4294967295' to '+FRA/DBTAR/ONLINELOG/online_22.log' Executing: alter database rename file '+DATA/DBTAR/onlinelog/online_b3.log' to '+DATA/DBTAR/ONLINELOG/online_03.log' Executing: alter database rename file '+FRA/DBTAR/onlinelog/online_a3.log' to '+FRA/DBTAR/ONLINELOG/online_33.log' Executing: alter database rename file '+DATA/MUST_RENAME_THIS_LOGFILE_4.4294967295.4294967295' to '+DATA/DBTAR/ONLINELOG/online_04.log' Executing: alter database rename file '+FRA/MUST_RENAME_THIS_LOGFILE_4.4294967295.4294967295' to '+FRA/DBTAR/ONLINELOG/online_44.log' Executing: alter database rename file '+DATA/MUST_RENAME_THIS_LOGFILE_11.4294967295.4294967295' to '+DATA/DBTAR/ONLINELOG/group_11.279.1109789815' Executing: alter database rename file '+FRA/MUST_RENAME_THIS_LOGFILE_11.4294967295.4294967295' to '+FRA/DBTAR/ONLINELOG/group_11.669.1109789815' Executing: alter database rename file '+DATA/DBTAR/onlinelog/online_2_12.ora' to '+DATA/DBTAR/ONLINELOG/online_2_12.ora' Executing: alter database rename file '+FRA/DBTAR/onlinelog/online_2_12.ora' to '+FRA/DBTAR/ONLINELOG/online_2_12.ora' contents of Memory Script: { recover database from service 'TNSSET'; } executing Memory Script Starting recover at 25-JUL-22 using channel ORA_DISK_1 channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: using network backup set from service TNSSET destination for restore of datafile 00001: +DATA/DBTAR/DATAFILE/system.261.1108233369 channel ORA_DISK_1: restore complete, elapsed time: 00:00:04 channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: using network backup set from service TNSSET destination for restore of datafile 00002: +DATA/DBTAR/DATAFILE/test_ts.276.1109780343 channel ORA_DISK_1: restore complete, elapsed time: 00:00:04 channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: using network backup set from service TNSSET destination for restore of datafile 00003: +DATA/DBTAR/DATAFILE/sysaux.260.1108233369 channel ORA_DISK_1: restore complete, elapsed time: 00:00:04 channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: using network backup set from service TNSSET destination for restore of datafile 00004: +DATA/DBTAR/DATAFILE/undotbs1.259.1108233369 channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: using network backup set from service TNSSET destination for restore of datafile 00005: +DATA/DBTAR/DATAFILE/undotbs2.258.1108233369 channel ORA_DISK_1: restore complete, elapsed time: 00:00:02 channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: using network backup set from service TNSSET destination for restore of datafile 00007: +DATA/DBTAR/DATAFILE/users.257.1108233369 channel ORA_DISK_1: restore complete, elapsed time: 00:00:05 channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: using network backup set from service TNSSET destination for restore of datafile 00008: +DATA/DBTAR/DATAFILE/rename_file.ora channel ORA_DISK_1: restore complete, elapsed time: 00:00:08 starting media recovery media recovery complete, elapsed time: 00:00:00 Finished recover at 25-JUL-22 flashback needs to be reenabled on standby open Executing: alter system set standby_file_management=auto Finished recover at 25-JUL-22 RMAN> RMAN> exit => (For Active Data Guard only) Perform the following steps to recover redo data and open the physical standby database in read-only mode: [oracle@racsetn1 ~]$ sqlplus "/as sysdba" SQL> select name,open_Mode from v$database; NAME OPEN_MODE --------- -------------------- DBSET MOUNTED SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE UNTIL CONSISTENT; Database altered. SQL> SQL> ALTER DATABASE OPEN READ ONLY; Database altered. => Start MRP process as below on Standby Sites. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; Database altered. => Check for Any GAP SQL> @sync Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ---------- 1 249 249 0 2 186 186 0 => Verify LOGFILES, DATAFILES,TEMPLFILES as below. SQL> select member,type from v$logfile; MEMBER TYPE -------------------------------------------------- ------- +DATA/DBTAR/ONLINELOG/online_02.log ONLINE +FRA/DBTAR/ONLINELOG/online_22.log ONLINE +DATA/DBTAR/ONLINELOG/online_01.log ONLINE +FRA/DBTAR/ONLINELOG/online_11.log ONLINE +DATA/DBTAR/ONLINELOG/online_03.log ONLINE +FRA/DBTAR/ONLINELOG/online_33.log ONLINE +DATA/DBTAR/ONLINELOG/online_04.log ONLINE +FRA/DBTAR/ONLINELOG/online_44.log ONLINE +DATA/DBTAR/standbylogs/stdby_01.log STANDBY +FRA/DBTAR/standbylogs/stdby_11.log STANDBY +DATA/DBTAR/standbylogs/stdby_02.log STANDBY +FRA/DBTAR/standbylogs/stdby_22.log STANDBY +DATA/DBTAR/standbylogs/stdby_03.log STANDBY +FRA/DBTAR/standbylogs/stdby_33.log STANDBY +DATA/DBTAR/standbylogs/stdby_04.log STANDBY +FRA/DBTAR/standbylogs/stdby_44.log STANDBY +DATA/DBTAR/standbylogs/stdby_05.log STANDBY +FRA/DBTAR/standbylogs/stdby_55.log STANDBY +DATA/DBTAR/standbylogs/stdby_06.log STANDBY +FRA/DBTAR/standbylogs/stdby_66.log STANDBY +DATA/DBTAR/ONLINELOG/group_11.279.1109789815 ONLINE +FRA/DBTAR/ONLINELOG/group_11.669.1109789815 ONLINE +DATA/DBTAR/ONLINELOG/online_2_12.ora ONLINE +FRA/DBTAR/ONLINELOG/online_2_12.ora ONLINE 24 rows selected. SQL> select file_name from dba_Data_Files; FILE_NAME ---------------------- +DATA/DBTAR/DATAFILE/system.261.1108233369 +DATA/DBTAR/DATAFILE/sysaux.260.1108233369 +DATA/DBTAR/DATAFILE/undotbs2.258.1108233369 +DATA/DBTAR/DATAFILE/users.257.1108233369 +DATA/DBTAR/DATAFILE/test_ts.276.1109780343 +DATA/DBTAR/DATAFILE/undotbs1.259.1108233369 +DATA/DBTAR/DATAFILE/rename_file.ora 7 rows selected. SQL> select file_name from dba_Temp_files; FILE_NAME -------------------- +DATA/DBTAR/TEMPFILE/tmpfile_01.dat SQL> => Recycle Database on Standby Site as below and start Recoveyr again. [oracle@racsetn1 ~]$ srvctl stop database -d DBTAR [oracle@racsetn1 ~]$ srvctl start database -d DBTAR SQL> @name NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE --------- -------------------- ---------------- -------------------- DBSET READ ONLY PHYSICAL STANDBY MAXIMUM PERFORMANCE DBSET READ ONLY PHYSICAL STANDBY MAXIMUM PERFORMANCE SQL> alter database recover managed standby database disconnect from session; Database altered. SQL> @name NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE --------- -------------------- ---------------- -------------------- DBSET READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE DBSET READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE SQL>
© 2021 Ace2Oracle. All Rights Reserved | Developed By IBOX444