Re-sync physical data guard using RMAN backup.

We can use an incremental backup taken from the primary and apply it on top of a broken standby database to make it sync with the primary, this should be much faster than the conventional method of copying the data files if the volume of changes are less compared to the total size of the database.

1.       Get the current SCN from standby database, we will use this SCN for the incremental backup from primary.

select current_scn from v$database;

2.       Take a control file backup from primary.

rman target /

backup current controlfile for standby format='/n01/oraarch1/rman/standby_ctl.bkp';

3.  Take an incremental backup from primary, use the SCN from standby in step 1. rman target /

run

{

backup  incremental from scn

database format='/n01/oraarch1/rman/%d_standby_incr_%s_%p.bkp' ;

}

** See appendix A for commands with parallel options and query to monitor progress.

4.       Copy the backup pieces to standby hosts.

5.       Start the standby database instance in nomount and restore the controlfile.

rman target /

startup nomount

restore standby controlfile from '/n01/oraarch1/rman/standby_ctl.bkp';

6.       Mount the standby database, stop MRP, catalog the backup pieces and recover from the backup.

SQL> alter database mount standby database

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

rman target /

sql 'alter database mount';

catalog start with '/n01/oraarch1/rman';

recover database noredo;

** See appendix A for commands with parallel options and query to monitor progress.

7.       Restart MRP and ensure that recovery is progressing.

Appendix A

Using parallelism

We can use multiple channels for backup and restore based on the size of the database and load on primary database.

Backup

rman target /

run

{

allocate channel t1 type disk MAXPIECESIZE 5G;

allocate channel t2 type disk MAXPIECESIZE 5G;

allocate channel t3 type disk MAXPIECESIZE 5G;

backup  incremental from scn

database format='/n01/oraarch1/rman/%d_standby_incr_%s_%p.bkp' ;

}

Restore

rman target /

run

{

allocate channel t1 type disk ;

allocate channel t2 type disk ;

allocate channel t3 type disk ;

recover database noredo;

}

Monitoring

The below query can be used to monitor the progress of RMAN sessions performing backup/restore.

set lines 150

col MESSAGE for a100

col sid for 999999

col ELAPSED_SECONDS for 999999

col TIME_REMAINING for 999999

col comp for 999 heading "%Comp"

SELECT sid, MESSAGE, ELAPSED_SECONDS, TIME_REMAINING, round(sofar/totalwork*100) comp

FROM v$session_longops

WHERE opname LIKE 'RMAN%'

AND opname NOT LIKE '%aggregate%'

AND totalwork != 0

AND sofar <> totalwork;