You have a standby DB out of sync and all logs on primary are gone.
DGMGRL> show database teststb
Database - teststb
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 1 day(s) 19 hours 16 minutes 16 seconds (computed 18 seconds ago)
Apply Lag: 4 days 4 hours 41 minutes 22 seconds (computed 18 seconds ago)
Average Apply Rate: 20.00 KByte/s
Real Time Query: OFF
Instance(s):
TESTSTB
Database Error(s):
ORA-16766: Redo Apply is stopped
Database Warning(s):
ORA-16853: apply lag has exceeded specified threshold
ORA-16855: transport lag has exceeded specified threshold
Database Status:
ERROR
DGMGRL>
At this stage you have 2 options:
1:- rebuild standby
2:- Rollforward standby
What about a “one line” command that does automatically all the steps you usually do on a rollforward?
Here is the magic:
[oracle@standbyracnode ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Fri Jun 9 15:22:38 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: TEST19C (DBID=2463024481, not open)
RMAN> recover standby database from service TEST19C;
Starting recover at 09-JUN-23
using target database control file instead of recovery catalog
Oracle instance started
Total System Global Area 3690986544 bytes
Fixed Size 9141296 bytes
Variable Size 721420288 bytes
Database Buffers 2952790016 bytes
Redo Buffers 7634944 bytes
contents of Memory Script:
{
restore standby controlfile from service 'TEST19C';
alter database mount standby database;
}
executing Memory Script
Starting restore at 09-JUN-23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=246 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service TEST19C
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:26
output file name=+DATA/TESTSTB/CONTROLFILE/control01.ctl
output file name=+RECO/TESTSTB/CONTROLFILE/control02.ctl
Finished restore at 09-JUN-23
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";
set newname for tempfile 2 to
"+DATA";
set newname for tempfile 3 to
"+DATA";
switch tempfile all;
set newname for datafile 1 to
"+DATA/TESTSTB/DATAFILE/system.298.1138381155";
set newname for datafile 3 to
"+DATA/TESTSTB/DATAFILE/sysaux.297.1138381265";
set newname for datafile 4 to
"+DATA/TESTSTB/DATAFILE/undotbs1.296.1138381401";
set newname for datafile 5 to
"+DATA/TESTSTB/CF698878BCD8663BE0530118A8C0FD00/DATAFILE/system.295.1138381429";
set newname for datafile 6 to
"+DATA/TESTSTB/CF698878BCD8663BE0530118A8C0FD00/DATAFILE/sysaux.294.1138381459";
set newname for datafile 7 to
"+DATA/TESTSTB/DATAFILE/users.292.1138381497";
set newname for datafile 8 to
"+DATA/TESTSTB/CF698878BCD8663BE0530118A8C0FD00/DATAFILE/undotbs1.290.1138381503";
set newname for datafile 9 to
"+DATA/TESTSTB/DATAFILE/undotbs2.289.1138381523";
set newname for datafile 10 to
"+DATA/TESTSTB/CF6A704554271263E0530218A8C04D71/DATAFILE/system.293.1138381563";
set newname for datafile 11 to
"+DATA/TESTSTB/CF6A704554271263E0530218A8C04D71/DATAFILE/sysaux.291.1138381597";
set newname for datafile 12 to
"+DATA/TESTSTB/CF6A704554271263E0530218A8C04D71/DATAFILE/undotbs1.288.1138381625";
set newname for datafile 13 to
"+DATA/TESTSTB/CF6A704554271263E0530218A8C04D71/DATAFILE/undo_2.287.1138381643";
set newname for datafile 14 to
"+DATA/TESTSTB/CF6A704554271263E0530218A8C04D71/DATAFILE/users.286.1138381661";
catalog datafilecopy "+DATA/TESTSTB/DATAFILE/system.298.1138381155",
"+DATA/TESTSTB/DATAFILE/sysaux.297.1138381265",
"+DATA/TESTSTB/DATAFILE/undotbs1.296.1138381401",
"+DATA/TESTSTB/CF698878BCD8663BE0530118A8C0FD00/DATAFILE/system.295.1138381429",
"+DATA/TESTSTB/CF698878BCD8663BE0530118A8C0FD00/DATAFILE/sysaux.294.1138381459",
"+DATA/TESTSTB/DATAFILE/users.292.1138381497",
"+DATA/TESTSTB/CF698878BCD8663BE0530118A8C0FD00/DATAFILE/undotbs1.290.1138381503",
"+DATA/TESTSTB/DATAFILE/undotbs2.289.1138381523",
"+DATA/TESTSTB/CF6A704554271263E0530218A8C04D71/DATAFILE/system.293.1138381563",
"+DATA/TESTSTB/CF6A704554271263E0530218A8C04D71/DATAFILE/sysaux.291.1138381597",
"+DATA/TESTSTB/CF6A704554271263E0530218A8C04D71/DATAFILE/undotbs1.288.1138381625",
"+DATA/TESTSTB/CF6A704554271263E0530218A8C04D71/DATAFILE/undo_2.287.1138381643",
"+DATA/TESTSTB/CF6A704554271263E0530218A8C04D71/DATAFILE/users.286.1138381661";
switch datafile all;
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting implicit crosscheck backup at 09-JUN-23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 09-JUN-23
Starting implicit crosscheck copy at 09-JUN-23
using channel ORA_DISK_1
Finished implicit crosscheck copy at 09-JUN-23
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: +RECO/TESTSTB/ARCHIVELOG/2023_06_09/thread_1_seq_153.353.1139066787
File Name: +RECO/TESTSTB/ARCHIVELOG/2023_06_09/thread_2_seq_108.352.1139066789
File Name: +RECO/TESTSTB/ARCHIVELOG/2023_06_09/thread_1_seq_154.347.1139066823
File Name: +RECO/TESTSTB/ARCHIVELOG/2023_06_09/thread_2_seq_109.346.1139066825
File Name: +RECO/TESTSTB/ARCHIVELOG/2023_06_09/thread_2_seq_110.349.1139066859
File Name: +RECO/TESTSTB/ARCHIVELOG/2023_06_09/thread_1_seq_155.351.1139066861
renamed tempfile 1 to +DATA in control file
renamed tempfile 2 to +DATA in control file
renamed tempfile 3 to +DATA 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
executing command: SET NEWNAME
executing command: SET NEWNAME
cataloged datafile copy
datafile copy file name=+DATA/TESTSTB/DATAFILE/system.298.1138381155 RECID=4 STAMP=1139067526
cataloged datafile copy
datafile copy file name=+DATA/TESTSTB/DATAFILE/sysaux.297.1138381265 RECID=5 STAMP=1139067576
cataloged datafile copy
datafile copy file name=+DATA/TESTSTB/DATAFILE/undotbs1.296.1138381401 RECID=6 STAMP=1139067679
cataloged datafile copy
datafile copy file name=+DATA/TESTSTB/CF698878BCD8663BE0530118A8C0FD00/DATAFILE/system.295.1138381429 RECID=7 STAMP=1139067736
cataloged datafile copy
datafile copy file name=+DATA/TESTSTB/CF698878BCD8663BE0530118A8C0FD00/DATAFILE/sysaux.294.1138381459 RECID=8 STAMP=1139067762
cataloged datafile copy
datafile copy file name=+DATA/TESTSTB/DATAFILE/users.292.1138381497 RECID=9 STAMP=1139067779
cataloged datafile copy
datafile copy file name=+DATA/TESTSTB/CF698878BCD8663BE0530118A8C0FD00/DATAFILE/undotbs1.290.1138381503 RECID=10 STAMP=1139067792
cataloged datafile copy
datafile copy file name=+DATA/TESTSTB/DATAFILE/undotbs2.289.1138381523 RECID=11 STAMP=1139067809
cataloged datafile copy
datafile copy file name=+DATA/TESTSTB/CF6A704554271263E0530218A8C04D71/DATAFILE/system.293.1138381563 RECID=12 STAMP=1139067816
cataloged datafile copy
datafile copy file name=+DATA/TESTSTB/CF6A704554271263E0530218A8C04D71/DATAFILE/sysaux.291.1138381597 RECID=13 STAMP=1139067820
cataloged datafile copy
datafile copy file name=+DATA/TESTSTB/CF6A704554271263E0530218A8C04D71/DATAFILE/undotbs1.288.1138381625 RECID=14 STAMP=1139067824
cataloged datafile copy
datafile copy file name=+DATA/TESTSTB/CF6A704554271263E0530218A8C04D71/DATAFILE/undo_2.287.1138381643 RECID=15 STAMP=1139067829
cataloged datafile copy
datafile copy file name=+DATA/TESTSTB/CF6A704554271263E0530218A8C04D71/DATAFILE/users.286.1138381661 RECID=16 STAMP=1139067834
datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=1139067526 file name=+DATA/TESTSTB/DATAFILE/system.298.1138381155
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=1139067576 file name=+DATA/TESTSTB/DATAFILE/sysaux.297.1138381265
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=1139067679 file name=+DATA/TESTSTB/DATAFILE/undotbs1.296.1138381401
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=1139067736 file name=+DATA/TESTSTB/CF698878BCD8663BE0530118A8C0FD00/DATAFILE/system.295.1138381429
datafile 6 switched to datafile copy
input datafile copy RECID=8 STAMP=1139067762 file name=+DATA/TESTSTB/CF698878BCD8663BE0530118A8C0FD00/DATAFILE/sysaux.294.1138381459
datafile 7 switched to datafile copy
input datafile copy RECID=9 STAMP=1139067779 file name=+DATA/TESTSTB/DATAFILE/users.292.1138381497
datafile 8 switched to datafile copy
input datafile copy RECID=10 STAMP=1139067792 file name=+DATA/TESTSTB/CF698878BCD8663BE0530118A8C0FD00/DATAFILE/undotbs1.290.1138381503
datafile 9 switched to datafile copy
input datafile copy RECID=11 STAMP=1139067809 file name=+DATA/TESTSTB/DATAFILE/undotbs2.289.1138381523
datafile 10 switched to datafile copy
input datafile copy RECID=12 STAMP=1139067816 file name=+DATA/TESTSTB/CF6A704554271263E0530218A8C04D71/DATAFILE/system.293.1138381563
datafile 11 switched to datafile copy
input datafile copy RECID=13 STAMP=1139067820 file name=+DATA/TESTSTB/CF6A704554271263E0530218A8C04D71/DATAFILE/sysaux.291.1138381597
datafile 12 switched to datafile copy
input datafile copy RECID=14 STAMP=1139067824 file name=+DATA/TESTSTB/CF6A704554271263E0530218A8C04D71/DATAFILE/undotbs1.288.1138381625
datafile 13 switched to datafile copy
input datafile copy RECID=15 STAMP=1139067829 file name=+DATA/TESTSTB/CF6A704554271263E0530218A8C04D71/DATAFILE/undo_2.287.1138381643
datafile 14 switched to datafile copy
input datafile copy RECID=16 STAMP=1139067834 file name=+DATA/TESTSTB/CF6A704554271263E0530218A8C04D71/DATAFILE/users.286.1138381661
Executing: alter database rename file '+DATA/MUST_RENAME_THIS_LOGFILE_1.4294967295.4294967295' to '+DATA/TESTSTB/ONLINELOG/group_1.299.1138381917'
Executing: alter database rename file '+RECO/TEST19C/ONLINELOG/group_1.314.1087130049' to '+RECO/TESTSTB/ONLINELOG/group_1.275.1138381945'
Executing: alter database rename file '+DATA/MUST_RENAME_THIS_LOGFILE_2.4294967295.4294967295' to '+DATA/TESTSTB/ONLINELOG/group_2.300.1138381977'
Executing: alter database rename file '+RECO/TEST19C/ONLINELOG/group_2.312.1087130049' to '+RECO/TESTSTB/ONLINELOG/group_2.278.1138382007'
Executing: alter database rename file '+DATA/MUST_RENAME_THIS_LOGFILE_3.4294967295.4294967295' to '+DATA/TESTSTB/ONLINELOG/group_3.301.1138382041'
Executing: alter database rename file '+RECO/TEST19C/ONLINELOG/group_3.289.1087134643' to '+RECO/TESTSTB/ONLINELOG/group_3.281.1138382075'
Executing: alter database rename file '+DATA/MUST_RENAME_THIS_LOGFILE_4.4294967295.4294967295' to '+DATA/TESTSTB/ONLINELOG/group_4.302.1138382107'
Executing: alter database rename file '+RECO/TEST19C/ONLINELOG/group_4.321.1087134667' to '+RECO/TESTSTB/ONLINELOG/group_4.277.1138382141'
contents of Memory Script:
{
recover database from service 'TEST19C';
}
executing Memory Script
Starting recover at 09-JUN-23
using channel ORA_DISK_1
skipping datafile 5; already restored to SCN 2130808
skipping datafile 6; already restored to SCN 2130808
skipping datafile 8; already restored to SCN 2130808
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service TEST19C
destination for restore of datafile 00001: +DATA/TESTSTB/DATAFILE/system.298.1138381155
channel ORA_DISK_1: restore complete, elapsed time: 00:00:37
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service TEST19C
destination for restore of datafile 00003: +DATA/TESTSTB/DATAFILE/sysaux.297.1138381265
channel ORA_DISK_1: restore complete, elapsed time: 00:08:17
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service TEST19C
destination for restore of datafile 00004: +DATA/TESTSTB/DATAFILE/undotbs1.296.1138381401
channel ORA_DISK_1: restore complete, elapsed time: 00:06:40
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service TEST19C
destination for restore of datafile 00007: +DATA/TESTSTB/DATAFILE/users.292.1138381497
channel ORA_DISK_1: restore complete, elapsed time: 00:00:10
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service TEST19C
destination for restore of datafile 00009: +DATA/TESTSTB/DATAFILE/undotbs2.289.1138381523
channel ORA_DISK_1: restore complete, elapsed time: 00:06:49
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service TEST19C
destination for restore of datafile 00010: +DATA/TESTSTB/CF6A704554271263E0530218A8C04D71/DATAFILE/system.293.1138381563
channel ORA_DISK_1: restore complete, elapsed time: 00:00:20
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service TEST19C
destination for restore of datafile 00011: +DATA/TESTSTB/CF6A704554271263E0530218A8C04D71/DATAFILE/sysaux.291.1138381597
channel ORA_DISK_1: restore complete, elapsed time: 00:02:09
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service TEST19C
destination for restore of datafile 00012: +DATA/TESTSTB/CF6A704554271263E0530218A8C04D71/DATAFILE/undotbs1.288.1138381625
channel ORA_DISK_1: restore complete, elapsed time: 00:02:10
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service TEST19C
destination for restore of datafile 00013: +DATA/TESTSTB/CF6A704554271263E0530218A8C04D71/DATAFILE/undo_2.287.1138381643
channel ORA_DISK_1: restore complete, elapsed time: 00:00:11
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service TEST19C
destination for restore of datafile 00014: +DATA/TESTSTB/CF6A704554271263E0530218A8C04D71/DATAFILE/users.286.1138381661
starting media recovery
archived log for thread 1 with sequence 161 is already on disk as file +RECO/TESTSTB/ARCHIVELOG/2023_06_09/thread_1_seq_161.364.1139067757
archived log for thread 1 with sequence 162 is already on disk as file +RECO/TESTSTB/ARCHIVELOG/2023_06_09/thread_1_seq_162.359.1139067741
archived log for thread 2 with sequence 116 is already on disk as file +RECO/TESTSTB/ARCHIVELOG/2023_06_09/thread_2_seq_116.357.1139067653
archived log for thread 2 with sequence 117 is already on disk as file +RECO/TESTSTB/ARCHIVELOG/2023_06_09/thread_2_seq_117.361.1139067731
archived log file name=+RECO/TESTSTB/ARCHIVELOG/2023_06_09/thread_1_seq_161.364.1139067757 thread=1 sequence=161
archived log file name=+RECO/TESTSTB/ARCHIVELOG/2023_06_09/thread_2_seq_116.357.1139067653 thread=2 sequence=116
archived log file name=+RECO/TESTSTB/ARCHIVELOG/2023_06_09/thread_2_seq_117.361.1139067731 thread=2 sequence=117
archived log file name=+RECO/TESTSTB/ARCHIVELOG/2023_06_09/thread_1_seq_162.359.1139067741 thread=1 sequence=162
media recovery complete, elapsed time: 00:00:29
Finished recover at 09-JUN-23
Executing: alter system set standby_file_management=auto
Finished recover at 09-JUN-23
RMAN>After this, you just need to enable apply and done!
DGMGRL> EDIT DATABASE 'teststb' SET STATE='APPLY-ON';
Succeeded.
DGMGRL>
DGMGRL> show database teststb
Database - teststb
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 698.00 KByte/s
Real Time Query: OFF
Instance(s):
TESTSTB
Database Status:
SUCCESS
DGMGRL>
Comments