Oracle Standby - Roll Forwarding Standby Database - Over the Service

Oracle Standby - Roll Forwarding Standby Database - Over the Service

In cases where a physical standby database is far behind the primary database, an RMAN incremental backup can be used to roll the standby database forward faster than redo log apply. In this procedure, the RMAN BACKUP INCREMENTAL FROM SCN command is used to create an incremental backup on the primary database that starts at the current SCN of the standby and is used to roll forward the standbydatabase.

With Oracle 12c database , We can roll forward or restore standby database over the service. So primarily , there are two method to roll forward database 1) Using Conventional Incremental RMAN Backup and 2) Using RMAN Recovery of the standby database over the Service(of primary Database). 

In this blog , We will discuss about Second method of Roll forwarding i.e RMAN recovery of the standby database over the service.

Follow the below steps to Roll Forward your Standby database:

1) Stop the MRP process on the standby database.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

2) Find the SCN number on primary and Standby database for comparison purpose later. 

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

SQL> select min(fhscn) from x$kcvfh;

SQL> select HXFIL, FHSCN from X$KCVFH;

SQL> select min(f.fhscn) from x$kcvfh f, v$datafile d where f.hxfil =d.file# and d.enabled != 'READ ONLY'     ;

3) Roll forward standby database over the service.

RMAN> recover database from service PRIMARY noredo using compressed backupset; 
Starting recover at 12-JAN-22 
Starting implicit crosscheck backup at 12-JAN-22 
using target database control file instead of recovery catalog 
allocated channel: ORA_DISK_1 
channel ORA_DISK_1: SID=29 device type=DISK 
Crosschecked 2 objects 
Finished implicit crosscheck backup at 12-JAN-22 
 
Starting implicit crosscheck copy at 12-JAN-22 
using channel ORA_DISK_1 
Finished implicit crosscheck copy at 12-JAN-22 
 
searching for all files in the recovery area 
cataloging files... 
no files cataloged 
 
using channel ORA_DISK_1 
skipping datafile 51; already restored to SCN 1467778778 
.......
........
channel ORA_DISK_1: starting incremental datafile backup set restore 
channel ORA_DISK_1: using compressed network backup set from service PRIMARY 
destination for restore of datafile 00001: +DATA/STANDBY/DATAFILE/system.260.1043528437 
...................
.......................
.
 
channel ORA_DISK_1: starting incremental datafile backup set restore 
channel ORA_DISK_1: using compressed network backup set from service PRIMARY 
destination for restore of datafile 000117: +DATA/STANDBY/DATAFILE/apptabespace.265.1043528467748 
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 
  

Finished recover at 12-JAN-22 

4) Compare both primary and standby’s datafile SCN’s using the SQLs in step 1.

Make Sure SCN in in sync if not follow the processes in step 3 again. 

5) Restore the control file again from primary Database.

RMAN> shutdown immediate 
RMAN> startup nomount 
RMAN> restore standby controlfile from service PRIMARY;  

Mount database
RMAN> alter database mount 

6) Because controlfile is restored from PRIMARY, the datafile locations in STANDBY controlfile will be same as PRIMARY database, so catalog datafiles in STANDBY will do the necessary rename operations. 

RMAN> CATALOG START WITH '<Standby Datafile Location>';

7) Switch the datafiles to its correct names at the standby site 

RMAN> SWITCH DATABASE TO COPY; 

8)Use the current SCN returned in step 4 to determine if new data files were added to the primary database since the standby database was last refreshed.If yes, these datafiles need to be restored on the standby from the primary database.

SELECT FILE#, NAME FROM V$DATAFILE WHERE CREATION_CHANGE# > <checkpoint_change# from 2>;

run

{

Restore datafile <Datafile from above command>,<> from service PRIMARY;

}

Follow step 6 & 7 again.

9)If the standby database needs to be configured for FLASHBACK use the below step to enable.

SQL> ALTER DATABASE FLASHBACK OFF;
SQL> ALTER DATABASE FLASHBACK ON;

10) On standby database, clear all online & standby redo log groups:

select GROUP# from V$LOG;

 select GROUP# from V$LOGFILE where TYPE='STANDBY' group by GROUP#; 

SQL> ALTER DATABASE CLEAR LOGFILE GROUP [group number];

11) Start the MRP process on standby
SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

You can learn more about oracle database administration: 

Pro Oracle GoldenGate for the DBA

Oracle Security Tools Installation and Configuration

Expert Oracle Exadata 

Oracle Database 12c Release 2 Real Application Clusters Handbook: Concepts, Administration, Tuning & Troubleshooting

Expert Oracle RAC 12c

==========================================================

Please check our other blogs for  Dataguard ,Standby  & DR.