Recover Large Archive Gap In Standby Database using single Command.
If the archive gap is substantial between the primary and physical standby databases, and it cannot be recovered through the MRP process or Archivelog deleted before applying on standby db then there is no need to refresh the standby database with incremental backup. Instead, the standby database can be recovered using primary services
RMAN> RECOVER STANDBY DATABASE FROM SERVICE testDB;
Starting recover at 10-JAN-24
Oracle instance started
Total System Global Area 53687090008 bytes
Fixed Size 30145368 bytes
Variable Size 7247757312 bytes
Database Buffers 46305116160 bytes
Redo Buffers 104071168 bytes
contents of Memory Script:
{
restore standby controlfile from service ‘testDB’;
alter database mount standby database;
}
executing Memory Script
Starting restore at 10-JAN-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1334 instance=testDB device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service testDB
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=+DATA/testDB/control01.ctl
output file name=+FRA/testDB/control02.ctl
Finished restore at 10-JAN-24
released channel: ORA_DISK_1
Statement processed
Executing: alter system set standby_file_management=manual
contents of Memory Script:
{
set newname for tempfile 2 to
“+DATA/testDB/TEMPFILE/temp.295.1068761213”;
switch tempfile all;
set newname for datafile 1 to
“+DATA/testDB/DATAFILE/system01.dbf”;
set newname for datafile 2 to
“+DATA/testDB/DATAFILE/sysaux01.dbf”;
set newname for datafile 3 to
“+DATA/testDB/DATAFILE/undotbs01.dbf”;
set newname for datafile 4 to
“+DATA/testDB/DATAFILE/users01.dbf”;
set newname for datafile 5 to
“+DATA/testDB/DATAFILE/undotbs1.290.1068759067”;
set newname for datafile 6 to
“+DATA/testDB/DATAFILE/dwe.259.1068759067”;
set newname for datafile 7 to
“+DATA/testDB/DATAFILE/dwe_ndx.258.1068759067”;
.
“+DATA/testDB/DATAFILE/dwe.304.1081717387”;
catalog datafilecopy “+DATA/testDB/DATAFILE/system01.dbf”,
“+DATA/testDB/DATAFILE/sysaux01.dbf”,
“+DATA/testDB/DATAFILE/undotbs01.dbf”,
“+DATA/testDB/DATAFILE/users01.dbf”,
“+DATA/testDB/DATAFILE/undotbs1.290.1068759067”,
“+DATA/testDB/DATAFILE/dwe.259.1068759067”,
“+DATA/testDB/DATAFILE/dwe_ndx.258.1068759067”,
“+DATA/testDB/DATAFILE/dwh.264.1068759067”,
“+DATA/testDB/DATAFILE/dwh_ndx.263.1068759067”,
“+DATA/testDB/DATAFILE/dww.266.1068759067”,
“+DATA/testDB/DATAFILE/dww_ndx.262.1068759067”,
..
..
executing Memory Script
executing command: SET NEWNAME
Starting implicit crosscheck backup at 10-JAN-24
allocated channel: ORA_DISK_1
Crosschecked 6 objects
Finished implicit crosscheck backup at 10-JAN-24
Starting implicit crosscheck copy at 10-JAN-24
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 10-JAN-24
searching for all files in the recovery area
cataloging files…
cataloging done
List of Cataloged Files
=======================
File Name: +FRA/testDB/AUTOBACKUP/2024_01_06/s_1081649857.272.1081649889
File Name: +FRA/testDB/AUTOBACKUP/2024_01_06/s_1081671425.333.1081671459
File Name: +FRA/testDB/ARCHIVELOG/2024_01_08/thread_1_seq_1764.323.1082613623
File Name: +FRA/testDB/ARCHIVELOG/2024_01_08/thread_1_seq_1765.288.1082634551
File Name: +FRA/testDB/ARCHIVELOG/2024_01_08/thread_1_seq_1766.283.1082646769
File Name: +FRA/testDB/ARCHIVELOG/2024_01_08/thread_1_seq_1767.315.1082656825
File Name: +FRA/testDB/ARCHIVELOG/2024_01_08/thread_1_seq_1768.376.1082663419
File Name: +FRA/testDB/ARCHIVELOG/2024_01_08/thread_1_seq_1769.348.1082671631
..
renamed tempfile 2 to +DATA/testDB/TEMPFILE/temp.295.1068761213 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
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
..
cataloged datafile copy
datafile copy file name=+DATA/testDB/DATAFILE/system01.dbf RECID=82 STAMP=1082821215
cataloged datafile copy
datafile copy file name=+DATA/testDB/DATAFILE/sysaux01.dbf RECID=83 STAMP=1082821215
cataloged datafile copy
datafile copy file name=+DATA/testDB/DATAFILE/undotbs01.dbf RECID=84 STAMP=1082821215
cataloged datafile copy
datafile copy file name=+DATA/testDB/DATAFILE/users01.dbf RECID=85 STAMP=1082821215
cataloged datafile copy
datafile copy file name=+DATA/testDB/DATAFILE/undotbs1.290.1068759067 RECID=86 STAMP=1082821215
cataloged datafile copy
datafile copy file name=+DATA/testDB/DATAFILE/dwe.259.1068759067 RECID=87 STAMP=1082821215
cataloged datafile copy
datafile copy file name=+DATA/testDB/DATAFILE/dwe_ndx.258.1068759067 RECID=88 STAMP=1082821215
cataloged datafile copy
datafile copy file name=+DATA/testDB/DATAFILE/dwh.264.1068759067 RECID=89 STAMP=1082821215
cataloged datafile copy
datafile copy file name=+DATA/testDB/DATAFILE/dwh_ndx.263.1068759067 RECID=90 STAMP=1082821215
cataloged datafile copy
datafile copy file name=+DATA/testDB/DATAFILE/dww.266.1068759067 RECID=91 STAMP=1082821215
cataloged datafile copy
datafile copy file name=+DATA/testDB/DATAFILE/dww_ndx.262.1068759067 RECID=92 STAMP=1082821215
cataloged datafile copy
datafile copy file name=+DATA/testDB/DATAFILE/dwe.267.1068759067 RECID=93 STAMP=1082821215
…
..
datafile 1 switched to datafile copy
input datafile copy RECID=82 STAMP=1082821215 file name=+DATA/testDB/DATAFILE/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=83 STAMP=1082821215 file name=+DATA/testDB/DATAFILE/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=84 STAMP=1082821215 file name=+DATA/testDB/DATAFILE/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=85 STAMP=1082821215 file name=+DATA/testDB/DATAFILE/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=86 STAMP=1082821215 file name=+DATA/testDB/DATAFILE/undotbs1.290.1068759067
datafile 6 switched to datafile copy
input datafile copy RECID=87 STAMP=1082821215 file name=+DATA/testDB/DATAFILE/dwe.259.1068759067
datafile 7 switched to datafile copy
input datafile copy RECID=88 STAMP=1082821215 file name=+DATA/testDB/DATAFILE/dwe_ndx.258.1068759067
datafile 8 switched to datafile copy
input datafile copy RECID=89 STAMP=1082821215 file name=+DATA/testDB/DATAFILE/dwh.264.1068759067
datafile 9 switched to datafile copy
input datafile copy RECID=90 STAMP=1082821215 file name=+DATA/testDB/DATAFILE/dwh_ndx.263.1068759067
datafile 10 switched to datafile copy
input datafile copy RECID=91 STAMP=1082821215 file name=+DATA/testDB/DATAFILE/dww.266.1068759067
datafile 11 switched to datafile copy
input datafile copy RECID=92 STAMP=1082821215 file name=+DATA/testDB/DATAFILE/dww_ndx.262.1068759067
datafile 12 switched to datafile copy
input datafile copy RECID=93 STAMP=1082821215 file name=+DATA/testDB/DATAFILE/dwe.267.1068759067
datafile 13 switched to datafile copy
input datafile copy RECID=94 STAMP=1082821216 file name=+DATA/testDB/DATAFILE/dwe.269.1068759067
datafile 14 switched to datafile copy
..
contents of Memory Script:
{
recover database from service ‘testDB’;
}
executing Memory Script
Starting recover at 10-JAN-24
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 testDB
destination for restore of datafile 00001: +DATA/testDB/DATAFILE/system01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service testDB
destination for restore of datafile 00002: +DATA/testDB/DATAFILE/sysaux01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service testDB
destination for restore of datafile 00003: +DATA/testDB/DATAFILE/undotbs01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service testDB
destination for restore of datafile 00004: +DATA/testDB/DATAFILE/users01.dbf
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 testDB
destination for restore of datafile 00005: +DATA/testDB/DATAFILE/undotbs1.290.1068759067
channel ORA_DISK_1: restore complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service testDB
destination for restore of datafile 00006: +DATA/testDB/DATAFILE/dwe.259.1068759067
channel ORA_DISK_1: restore complete, elapsed time: 00:02:46
channel ORA_
…
..
destination for restore of datafile 00038: +DATA/testDB/DATAFILE/dww.305.1081717339
channel ORA_DISK_1: restore complete, elapsed time: 00:00:36
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service testDB
destination for restore of datafile 00039: +DATA/testDB/DATAFILE/dwe.304.1081717387
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
starting media recovery
archived log for thread 1 with sequence 1782 is already on disk as file +FRA/testDB/ARCHIVELOG/2024_01_08/thread_1_seq_1782.438.1082821213
archived log file name=+FRA/testDB/ARCHIVELOG/2024_01_08/thread_1_seq_1782.438.1082821213 thread=1 sequence=1782
media recovery complete, elapsed time: 00:00:02
Finished recover at 10-JAN-24
Executing: alter system set standby_file_management=auto
Finished recover at 10-JAN-24
4. Recover the standby database upto consistent mode .
SQL> select name,open_Mode from v$database;
NAME OPEN_MODE
——— ——————–
testDB MOUNTED
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE UNTIL CONSISTENT;
Database altered.
SQL>select name,open_Mode from v$database;
NAME OPEN_MODE
——— ——————–
testDB MOUNTED
SQL> alter database open read only;
Database altered.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
——— ——————–
testDB READ ONLY
SQL> alter database recover managed standby database disconnect from session;
Database altered.
Recovery Completed.