Oracle Standby - Roll Forwarding Standby Database - Conventional RMAN Incremental

Oracle Standby - Roll Forwarding Standby Database - Conventional RMAN Incremental

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 standby database.

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 first method of Roll forwarding i.e Conventional Incremental RMAN Backup.

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 from where incremental backup is needed to be triggered.

On the STANDBY DATABASE, find the SCN which will be used for the incremental backup at the primary database. Run below queries and use the lowest value for the SCN number. 

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

SQL> select min(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'     ;

SQL> select min(checkpoint_change#) from v$datafile_header;

3) On Primary database , Run below query to find out if any datafile created after the SCN (Lowest SNC in Step2).

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

4) Trigger an incremental backup using the scn number from the step2 and backup of datafiles created after SNC in step2.

run

{

backup of datafile <Datafile number from Step3>,<> format '/backup/standby/stnd_u%' tag 'FORSTANBY';

BACKUP INCREMENTAL FROM SCN <SNC from Step 2>DATABASE FORMAT '/backup/standby/Standby_%U' tag 'FORSTANDBY';
BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/backup/standby/Standby/StandbyCTRL.bck';

}

5) Transfer backup to Standby Server .

6) Restore new control file and Catalog backup to the control file.

RMAN> shutdown;

RMAN> startup nomount;

RMAN> restore standby controlfile from '/backup/standby/Standby/StandbyCTRL.bck';

RMAN> alter database mount;

RMAN> CATALOG START WITH '/backup/standby/;

7)Check DB file name convert and use set new name accordingly.

run

{

set newname for datafile <Datafile from step 3 > to '+DISKGROUP';

restore datafile <Datafile from step 3 >;

}

8) 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>';

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

RMAN> SWITCH DATABASE TO COPY; 

10)Recover the standby database with the cataloged incremental backup:

RECOVER DATABASE NOREDO;

11)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;

12). On standby database, clear all standby redo log groups:
SQL> ALTER DATABASE CLEAR LOGFILE GROUP [group number];
Verify :
Run the queries from step 2 again to confirm that the incremental apply has moved the datafiles forward. The SCN should now be bigger than its initial values.

13). 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.