If your standby database is out of sync from primary or some archive logs got deleted before it is being shipped or applied to standby ,then you can follow below method to sync standby database with primary. We can call this process as rolling forward of standby database.
In oracle 11g, this process is purely manual , which involves lot of steps. And this process saw a major improvement in oracle 12c.
And again in oracle 18c it is just one command to refresh the standby database from primary.
See below articles for 11g and 12c version:
For Oracle 11g database : How To Recover Standby Database When Archive Logs Are Missing In Primary
For Oracle 12c database : Rolling Forward Standby Database When Archives Missing In Primary In 12c
EXAMPLE:
primary database: CLSPROD
standby database: CLSTDBY
database_type: 2 node RAC
1. Cancel recovery of standby database:
SQL> recover managed standby database cancel;
Media recovery complete.
2. Keep the standby database in mount state from one node only:
As our standby database is a RAC, so we will shutdown the database from all nodes and mount the database from only one node.
[oracle@stdby-host]$srvctl stop database -d CLSTDBY
sqlplus / as sysdba
SQL> startup mount;
3. Connect with rman and run recovery command: [ ON STANDBY ]
SYNTAX – > RECOVER STANDBY DATABASE FROM SERVICE
Here CLSPROD – is the primary database service_name(TNS):
[oracle@stdby-host admin]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Thu Sep 9 15:39:06 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: CLSPROD (DBID=2290300697, not open)
RMAN> RECOVER STANDBY DATABASE FROM SERVICE CLSPROD;
Starting recover at 09-SEP-21
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 'CLSPROD';
alter database mount standby database;
}
executing Memory Script
Starting restore at 09-SEP-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1334 instance=CLSDR1 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service CLSPROD
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=+DATA/CLSDR/control01.ctl
output file name=+FRA/CLSDR/control02.ctl
Finished restore at 09-SEP-21
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/CLSDR/TEMPFILE/temp.295.1068761213";
switch tempfile all;
set newname for datafile 1 to
"+DATA/CLSDR/DATAFILE/system01.dbf";
set newname for datafile 2 to
"+DATA/CLSDR/DATAFILE/sysaux01.dbf";
set newname for datafile 3 to
"+DATA/CLSDR/DATAFILE/undotbs01.dbf";
set newname for datafile 4 to
"+DATA/CLSDR/DATAFILE/users01.dbf";
set newname for datafile 5 to
"+DATA/CLSDR/DATAFILE/undotbs1.290.1068759067";
set newname for datafile 6 to
"+DATA/CLSDR/DATAFILE/dwe.259.1068759067";
set newname for datafile 7 to
"+DATA/CLSDR/DATAFILE/dwe_ndx.258.1068759067";
.
"+DATA/CLSDR/DATAFILE/dwe.304.1081717387";
catalog datafilecopy "+DATA/CLSDR/DATAFILE/system01.dbf",
"+DATA/CLSDR/DATAFILE/sysaux01.dbf",
"+DATA/CLSDR/DATAFILE/undotbs01.dbf",
"+DATA/CLSDR/DATAFILE/users01.dbf",
"+DATA/CLSDR/DATAFILE/undotbs1.290.1068759067",
"+DATA/CLSDR/DATAFILE/dwe.259.1068759067",
"+DATA/CLSDR/DATAFILE/dwe_ndx.258.1068759067",
"+DATA/CLSDR/DATAFILE/dwh.264.1068759067",
"+DATA/CLSDR/DATAFILE/dwh_ndx.263.1068759067",
"+DATA/CLSDR/DATAFILE/dww.266.1068759067",
"+DATA/CLSDR/DATAFILE/dww_ndx.262.1068759067",
..
..
executing Memory Script
executing command: SET NEWNAME
Starting implicit crosscheck backup at 09-SEP-21
allocated channel: ORA_DISK_1
Crosschecked 6 objects
Finished implicit crosscheck backup at 09-SEP-21
Starting implicit crosscheck copy at 09-SEP-21
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 09-SEP-21
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: +FRA/CLSDR/AUTOBACKUP/2021_08_27/s_1081649857.272.1081649889
File Name: +FRA/CLSDR/AUTOBACKUP/2021_08_27/s_1081671425.333.1081671459
File Name: +FRA/CLSDR/ARCHIVELOG/2021_09_07/thread_1_seq_1764.323.1082613623
File Name: +FRA/CLSDR/ARCHIVELOG/2021_09_07/thread_1_seq_1765.288.1082634551
File Name: +FRA/CLSDR/ARCHIVELOG/2021_09_07/thread_1_seq_1766.283.1082646769
File Name: +FRA/CLSDR/ARCHIVELOG/2021_09_07/thread_1_seq_1767.315.1082656825
File Name: +FRA/CLSDR/ARCHIVELOG/2021_09_07/thread_1_seq_1768.376.1082663419
File Name: +FRA/CLSDR/ARCHIVELOG/2021_09_07/thread_1_seq_1769.348.1082671631
..
renamed tempfile 2 to +DATA/CLSDR/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/CLSDR/DATAFILE/system01.dbf RECID=82 STAMP=1082821215
cataloged datafile copy
datafile copy file name=+DATA/CLSDR/DATAFILE/sysaux01.dbf RECID=83 STAMP=1082821215
cataloged datafile copy
datafile copy file name=+DATA/CLSDR/DATAFILE/undotbs01.dbf RECID=84 STAMP=1082821215
cataloged datafile copy
datafile copy file name=+DATA/CLSDR/DATAFILE/users01.dbf RECID=85 STAMP=1082821215
cataloged datafile copy
datafile copy file name=+DATA/CLSDR/DATAFILE/undotbs1.290.1068759067 RECID=86 STAMP=1082821215
cataloged datafile copy
datafile copy file name=+DATA/CLSDR/DATAFILE/dwe.259.1068759067 RECID=87 STAMP=1082821215
cataloged datafile copy
datafile copy file name=+DATA/CLSDR/DATAFILE/dwe_ndx.258.1068759067 RECID=88 STAMP=1082821215
cataloged datafile copy
datafile copy file name=+DATA/CLSDR/DATAFILE/dwh.264.1068759067 RECID=89 STAMP=1082821215
cataloged datafile copy
datafile copy file name=+DATA/CLSDR/DATAFILE/dwh_ndx.263.1068759067 RECID=90 STAMP=1082821215
cataloged datafile copy
datafile copy file name=+DATA/CLSDR/DATAFILE/dww.266.1068759067 RECID=91 STAMP=1082821215
cataloged datafile copy
datafile copy file name=+DATA/CLSDR/DATAFILE/dww_ndx.262.1068759067 RECID=92 STAMP=1082821215
cataloged datafile copy
datafile copy file name=+DATA/CLSDR/DATAFILE/dwe.267.1068759067 RECID=93 STAMP=1082821215
...
..
datafile 1 switched to datafile copy
input datafile copy RECID=82 STAMP=1082821215 file name=+DATA/CLSDR/DATAFILE/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=83 STAMP=1082821215 file name=+DATA/CLSDR/DATAFILE/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=84 STAMP=1082821215 file name=+DATA/CLSDR/DATAFILE/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=85 STAMP=1082821215 file name=+DATA/CLSDR/DATAFILE/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=86 STAMP=1082821215 file name=+DATA/CLSDR/DATAFILE/undotbs1.290.1068759067
datafile 6 switched to datafile copy
input datafile copy RECID=87 STAMP=1082821215 file name=+DATA/CLSDR/DATAFILE/dwe.259.1068759067
datafile 7 switched to datafile copy
input datafile copy RECID=88 STAMP=1082821215 file name=+DATA/CLSDR/DATAFILE/dwe_ndx.258.1068759067
datafile 8 switched to datafile copy
input datafile copy RECID=89 STAMP=1082821215 file name=+DATA/CLSDR/DATAFILE/dwh.264.1068759067
datafile 9 switched to datafile copy
input datafile copy RECID=90 STAMP=1082821215 file name=+DATA/CLSDR/DATAFILE/dwh_ndx.263.1068759067
datafile 10 switched to datafile copy
input datafile copy RECID=91 STAMP=1082821215 file name=+DATA/CLSDR/DATAFILE/dww.266.1068759067
datafile 11 switched to datafile copy
input datafile copy RECID=92 STAMP=1082821215 file name=+DATA/CLSDR/DATAFILE/dww_ndx.262.1068759067
datafile 12 switched to datafile copy
input datafile copy RECID=93 STAMP=1082821215 file name=+DATA/CLSDR/DATAFILE/dwe.267.1068759067
datafile 13 switched to datafile copy
input datafile copy RECID=94 STAMP=1082821216 file name=+DATA/CLSDR/DATAFILE/dwe.269.1068759067
datafile 14 switched to datafile copy
..
contents of Memory Script:
{
recover database from service 'CLSPROD';
}
executing Memory Script
Starting recover at 09-SEP-21
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 CLSPROD
destination for restore of datafile 00001: +DATA/CLSDR/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 CLSPROD
destination for restore of datafile 00002: +DATA/CLSDR/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 CLSPROD
destination for restore of datafile 00003: +DATA/CLSDR/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 CLSPROD
destination for restore of datafile 00004: +DATA/CLSDR/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 CLSPROD
destination for restore of datafile 00005: +DATA/CLSDR/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 CLSPROD
destination for restore of datafile 00006: +DATA/CLSDR/DATAFILE/dwe.259.1068759067
channel ORA_DISK_1: restore complete, elapsed time: 00:02:46
channel ORA_
...
..
destination for restore of datafile 00038: +DATA/CLSDR/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 CLSPROD
destination for restore of datafile 00039: +DATA/CLSDR/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/CLSDR/ARCHIVELOG/2021_09_09/thread_1_seq_1782.438.1082821213
archived log file name=+FRA/CLSDR/ARCHIVELOG/2021_09_09/thread_1_seq_1782.438.1082821213 thread=1 sequence=1782
media recovery complete, elapsed time: 00:00:02
Finished recover at 09-SEP-21
Executing: alter system set standby_file_management=auto
Finished recover at 09-SEP-21
4. Recover the standby database upto consistent mode .
SQL> select name,open_Mode from v$database;
NAME OPEN_MODE
--------- --------------------
CLSPROD MOUNTED
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE UNTIL CONSISTENT;
Database altered.
Note - > If the above command is hung and taking long time to complete, then do alter system switch logfile ; from primary database.
SQL>select name,open_Mode from v$database;
NAME OPEN_MODE
--------- --------------------
CLSPROD MOUNTED
SQL> alter database open read only;
Database altered.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
CLSPROD READ ONLY
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
CLSPROD READ ONLY WITH APPLY
But at this stage, real time changes on the primary will not be reflected on standby database. So we need to recreate the standby redologs on standby database.
5. Recreate standby redologs: [ ON STANDBY ]
As our standby database is a 2 node RAC, we are 2 thread, ( one for each node). In case of standby database, no need to worry about thread concept.
--- First cancel the recovery:
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> select inst_id,GROUP#,TYPE,MEMBER from gv$logfile where TYPE='STANDBY' WHERE INST_ID=1;
INST_ID GROUP# TYPE MEMBER
---------- ---------- ------- --------------------------------------------------
1 15 STANDBY +DATA/CLSPROD/ONLINELOG/group_15.326.1081670395
1 15 STANDBY +FRA/CLSPROD/ONLINELOG/group_15.355.1081670397
1 16 STANDBY +DATA/CLSPROD/ONLINELOG/group_16.325.1081670425
1 16 STANDBY +FRA/CLSPROD/ONLINELOG/group_16.346.1081670429
1 17 STANDBY +DATA/CLSPROD/ONLINELOG/group_17.324.1081670447
1 17 STANDBY +FRA/CLSPROD/ONLINELOG/group_17.379.1081670451
1 18 STANDBY +DATA/CLSPROD/ONLINELOG/group_18.334.1081670457
1 18 STANDBY +FRA/CLSPROD/ONLINELOG/group_18.385.1081670459
SQL> select inst_id,thread#,group# from gv$standby_log;
INST_ID THREAD# GROUP#
---------- ---------- ----------
1 1 15
1 1 16
1 1 17
1 1 18
2 1 15
2 1 16
2 1 17
2 1 18
-- Drop all standby redologs:
alter database drop standby logfile group 15;
alter database drop standby logfile group 16;
alter database drop standby logfile group 17;
alter database drop standby logfile group 18;
-- Create standby redolog for both threads:
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 15 ('+DATA','+FRA') SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 16 ('+DATA','+FRA') SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 17 ('+DATA','+FRA') SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 18 ('+DATA','+FRA') SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 15 ('+DATA','+FRA') SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 16 ('+DATA','+FRA') SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 17 ('+DATA','+FRA') SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 18 ('+DATA','+FRA') SIZE 1G;
--- Once standby redologs are created start recovery:
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
Now all real time changes on primary will be reflected on standby database.
TROUBLESHOOTING:
1. RMAN-05150: Managed Recovery Process must be disabled before running RECOVER STANDBY DATABASE.
PROBLEM: below error ,while running recover standby database command,
RMAN> RECOVER STANDBY DATABASE FROM SERVICE CLSPROD;
Starting recover at 09-SEP-21
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 09/09/2021 15:38:47
RMAN-05150: Managed Recovery Process must be disabled before running RECOVER STANDBY DATABASE.
SOLUTION:
First cancel recovery , and then start recover standby database.
SQL> recover managed standby database cancel;
Media recovery complete.
RMAN> RECOVER STANDBY DATABASE FROM SERVICE CLSPROD;
Hello,
Thanks for the standby roll forward document. can you please let me know do not required to recreate the standby controlfilefile?
Regards,
Prashant
The recover command will recreate the standby controlfile, So we dont need to do it manually.
Thanks for the great article , just used on a 19c (19.11) test standby database and works like a treat. Our site is using data guard so didn’t need to execute statement “alter database recover managed standby database” , just had to execute statement “alter system set dg_broker_start=TRUE scope=both sid=’*’ ;” on both primary and standby databases. Thank you for taking the time to share your experience with other Oracle DBAs .
Karl
I think, this is an enhancement in 19c. Thanks for sharing this.
Hi,
Thanks for the article. It helped to recover the Standby database.
However, as soon as the standby server OS is restarted, the sequence logs stop applying again. Standby alert log shows there’s gap in sequence, which are old sequence numbers, prior to performing the recovery.
Any ideas about that.
Thanks
Inder