Roll Forward Standby Database using Primary Database RMAN Incremental Backup

The Physical Standby can be rolled forward using incremental backups of the Primary database.

This technique can be particularly useful in following cases:

               

-          The Standby is too far behind the Primary and it would take longer for MRP to apply all the redo than it would take the RMAN to create incremental backup of primary and roll forward the standby

-          The Standby is behind the primary and the archivelogs to roll forward the standby have been lost

-          Physical standby has NOLOGGING changes

In any of the above cases, the first step would always be to check the SCN at which the divergence has occurred.

The next steps would be to take an incremental backup of the primary database from that SCN and to recover the standby database using that backup.

Such an incremental backup is not a part of the RMAN retention policy of the primary database.

Following exercise tries to create divergence of Primary and standby by inhibiting the transport of redo from the primary to standby by stopping the listeners. Then a lot of transactions are done on the primary database, thus creating a situation where standby is far behind the primary.

1.       Mount primary and take full backup.

As a best practice, take full backup of the primary.

    RMAN> startup mount;

  RMAN> backup database plus archivelog;

2.       Open primary and create table with huge amount of data

    SQL> alter database open;

  SQL> create table table_dummy as select * from dba_tables;

  SQL> -- repeat  the following insert statement multiple times

  SQL> insert into table_dummy select * from table_dummy; 

3.       Commit the transactions made above  

The Log files will get switched automatically as the table gets large creating a huge backlog trail.

4.        Now mount the standby  and check the  current SCN of the standby

    SQL> select current_scn from v$database;

  1288770     

5.       Take incremental backup on primary for rolling forward the standby database

Connect to Primary database as target and take incremental backup from the SCN at which the divergence occurred.

   

RMAN> backup incremental from scn 1288770 database

      format '/tmp/forStandby_%U' tag 'forStandby';

6.       Send over the backup files from the Primary server to the Standby server

scp /tmp/forStandby_%U oracle@dg2:/tmp/

7.       Catalog the files at standby site using RMAN with standby as target

RMAN> catalog start with '/tmp/forStand';

8.       Recover the standby database with cataloged incremental backup

RMAN> recover database noredo

9.       Backup the Primary DB Controlfile

This step is required in case changes to the database structure have been made.

                Though no structural changes have been done in our case, this step is present for illustration.

With RMAN target as primary, backup current controlfile for standby

 RMAN> backup current controlfile for standby format '/tmp/forStandbyCtrl.bkp'

10.   Send over the controlfile backup to standby

scp /tmp/forStandbyCtrl.bkp oracle@dg2:/tmp/

11.   Shutdown standby and start in nomount mode

RMAN> shutdown immediate;

RMAN> startup nomount;

12.   Restore Standby controlfile at standby site

Connect RMAN with target as standby and restore the controlfile from the standby controlfile just generated

RMAN> restore standby controlfile from '/tmp/forStandbyCtrl.bkp';

13.   Ensure that the db_file_name_convert and the log_file_name_convert parameters are set appropriately

14.   Clear the Standby Redo Log groups on standby

alter database clear logfile group 4;

alter database clear logfile group 5;

alter database clear logfile group 6;

       alter database clear logfile group 7;

15.   Toggle the flashback

alter database flashback off;

alter database flashback on;

16.   Start redo apply at standby site

alter database recover managed standby database using current logfile;