Rebuild Physical Standby Using RMAN Incremental Backup

Overview:
In my previous article I have shown you how to rebuild a physical standby database using RMAN full database backup. Please refer below link for same.

Rebuild Physical Standby using RMAN full backup  

Rolling forward a standby using RMAN full database backup can be time consuming and take several hours based on the size of the database. RMAN offers a flexibility to take the SCN based backup that can be used to incrementally roll forward a standby database. Using RMAN incremental backup to roll forward a standby database is the easiest and quickest way to sync a standby database.

Environment Details

Hostname	Primary server :– lnx-nsm-prod01/ lnx-nsm-prod02 Standby Server :–  lnx-nsm-dr01/ lnx-nsm-dr02
Operating system	Linux
Database Environment	NSMDB and NSMDR

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

SQL> select current_scn from v$database;

• Take a control file backup from NSMDB primary.

$ rman target /

RMAN> backup current controlfile for standby format '/backup/nsmdb/nsmdb_stby_ctl';

• Create backup directory and take an incremental backup from primary, use the SCN from standby in step 1.

$ mkdir -p /backup/nsmdb/

$ rman target /

RMAN> run {

allocate channel c1 device  type disk format '/backup/nsmdb/nsmdb_%U';

allocate channel c2 device  type disk format '/backup/nsmdb/nsmdb_%U';

allocate channel c3 device  type disk format '/backup/nsmdb/nsmdb_%U';

allocate channel c4 device  type disk format '/backup/nsmdb/nsmdb_%U';

allocate channel c5 device  type disk format '/backup/nsmdb/nsmdb_%U';

allocate channel c6 device  type disk format '/backup/nsmdb/nsmdb_%U';

allocate channel c7 device  type disk format '/backup/nsmdb/nsmdb_%U';

allocate channel c8 device  type disk format '/backup/nsmdb/nsmdb_%U';

backup incremental from scn scn_from_step_1 database;

}

• Get datafile file# and name from primary database, and spool output to text file f1.txt for reference.

SQL> select file#, name from v$datafile order by 1;

• Copy the backup pieces to standby hosts in the same location.

$ mkdir -p  /backup/nsmdb

• Stop mrp at the standby.

DGMGRL> edit database nsmdr set state=APPLY-OFF;

• Get datafile file# and name from standby  database, and spool output to text file f2.txt for reference.

SQL> select file#, name from v$datafile order by 1;

• Bring down standby database, all instances.

$ Srvctl stop database –d nsmdr

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

$ rman target /                            

RMAN> startup nomount

RMAN> restore standby controlfile from '/backup/nsmdb/nsmdr_stby_ctl';

• Mount the standby database, stop MRP, catalog the backup pieces.

SQL> alter database mount standby database

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

$ rman target /

RMAN> sql 'alter database mount';

RMAN> catalog start with '/backup/nsmdb';

• Catalog data files and switch the files to copy.

RMAN> catalog start with '+DATA/nsmdr/datafile';

RMAN> SWITCH DATABASE TO COPY;

• Recover the standby database.

$ rman target /

RMAN> run {

allocate channel c1 device  type disk format '/backup/nsmdb/nsmdb_%U';

allocate channel c2 device  type disk format '/backup/nsmdb/nsmdb_%U';

allocate channel c3 device  type disk format '/backup/nsmdb/nsmdb_%U';

allocate channel c4 device  type disk format '/backup/nsmdb/nsmdb_%U';

allocate channel c5 device  type disk format '/backup/nsmdb/nsmdb_%U';

allocate channel c6 device  type disk format '/backup/nsmdb/nsmdb_%U';

allocate channel c7 device  type disk format '/backup/nsmdb/nsmdb_%U';

allocate channel c8 device  type disk format '/backup/nsmdb/nsmdb_%U';

recover database noredo;

}

• Restart MRP and ensure that recovery is progressing.

DGMGRL> edit database nsmdr set state=APPLY-ON;

• Monitoring

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

SQL> set lines 150

SQL> col MESSAGE for a100

SQL> col sid for 999999

SQL> col ELAPSED_SECONDS for 999999

SQL> col TIME_REMAINING for 999999

SQL> col comp for 999 heading "%Comp"

SQL> 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;

Conclusion

In this article we have learnt the steps to roll forward a physical standby database using RMAN incremental backup.