Category - DATAGUARD

Recovering Standby database Using Service Name - 19c

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>