Wednesday, 4 January 2017

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.

No comments:

Post a Comment

Oracle Transparent Data Encryption (TDE) On Exadata

Oracle Transparent Data Encryption (TDE) enables the organizations to encrypt sensitive application data on storage media completely transp...