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.