Uncategorized

Rebuild Physical Standby using RMAN full backup

Overview:
At times standby database can go out of Sync for many reasons like, accidentally deleting archive log on primary or RMAN backup deleted archive log before shipping to standby database server. When this happens you must rebuild the Standby database using primary database backup.

In this article we outline the process of rebuild the Physical Standby which is out of sync from Primary.

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

  •     Take full RMAN backup of primary. Create location if does not exists.

run {
allocate channel c1 device  type disk format ‘/backup/nsmdb/NSMDB_%U’;
backup database plus archivelog tag=”FORSTDBY”;
}

  •       Take a control file backup from primary.
rman target /
backup current controlfile for standby format ‘/backup/nsmdb/NSMDB_stby_ctl’;


  •       Copy backup pieces to standby server. Create location if does not exists on standby
  •       Stop and clean up standby Database files from locations
srvctl stop database –d nsmdr




  • Start the standby database instance in nomount and restore the controlfile.
rman target /                            
startup nomount
restore standby controlfile from ‘/backup/nsmdb/NSMDB_stby_ctl’


  •      Mount the standby database, stop MRP if it is up, catalog the backup pieces.
SQL> alter database mount standby database;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
rman target /
catalog start with ‘/backup/nsmdb/NSMDB_%U’;


  •      Now restore database.
run {
allocate channel c1 device  type disk format ‘/backup/nsmdb/NSMDB_%U’;
restore database;
}
Exit;


  •         Need to shutdown the database.
SQL>  shutdown immediate;           


  •        Start the database in read only mode.
$ srvctl start database –d nsmdr      


  •        Restart MRP and ensure that recovery is progressing.
DGMGRL> edit database nsmdr set state=APPLY-ON;


Monitoring RMAN Progress

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;

Conclusion
In this article we have learnt the steps to rebuild a physical standby database which was out of sync from primary database.