Rebuilding Standby Database Manually (11g and up)

One of my remote customers has been facing severe network outages between Production and DR sites to reasons unknown and challenges with certain geographic limitations, the bandwidth between sites is limited to 2MBPS and the replication between sites is aching slow. So, Customer has had tentative requirements to rebuild Standby more often between sites until the network issue is fully resolved.

This paper outlines the procedure to rebuild the STANDBY manually provided we have following things in place already between/on sites. And this procedure has a special case for One node RAC, Oracle dynamically applies SID naming convention in case of an online relocation. Due to this fact, I have to mention which SID to export before logging to the Database via SQLPLUS/RMAN/ASMCMD
(PR refers to - Production/Primary and PR1/PR2 are two nodes in RAC,  DR refers to - Disaster Recovery/Standby and DR1/DR2 are two nodes in RAC at DR Site)

• A working Dataguard Configuration between Sites (All Parameters on both PR and DR)
• A working Oracle Networking Configuration between sites (including Listener  and TNS)
• A working RMAN backup
• A RAC/One Node RAC/Single Node Oracle setup in place
• ASM
• Level 1 Skillset Oracle DBA Resource

In case if you would like to know how to configure Dataguard, Please refer to my post

Data Guard Configuration with Oracle DGMGRL.

On Production/Primary/PR1/PR2 (oracle os user prompt)

Find which SID is running

$ps -ef|grep pmon

Get the SID name and export it

$export ORACLE_SID=PRDB_1/PRDB_2

Create a directory (create this on DR too and create this on all nodes PR2/DR1/DR2)

$mkdir -p /backup/rman/4standby

Login to RMAN and take the copy of controlfile for STANDBY

$rman target /

Do log switches

RMAN>sql 'alter system switch logfile';

RMAN> sql 'alter system switch logfile';

Take the backup of controlfile for standby and full Database

RMAN>configure device type disk parallelism 10 backup type to backupset;

RMAN>configure channel device type disk format '/backup/rman/4standby/%d_% s_%p_%t';

RMAN>copy current controlfile for standby to '/backup/rman/4standby/standby_PR.ctl';

the above command dumps the controlfile to /backup/rman/4standby, SCP it to DR site to the same location '/backup/rman/4standby/'

RMAN>backup section size 500m as compressed backupset database;

RMAN>exit;

Disable the destination if it's running

$sqlplus / as sysdba

SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER SID='*';

Now take a copy of this backup to DR and keep it in the same location '/backup/rman/4standby'

Now we are on DR (DR/Standby/DR1/DR2)(oracle os user prompt)

Start the DR instance (on DR1/DR2)

$srvctl start database -d PRDB -o nomount

Find which SID is running

$ps -ef|grep pmon

Get the SID name and export it

$export ORACLE_SID=PRDBDR_1/PRDBDR_2

Login to RMAN and restore the controlfile and backup

$rman target /

RMAN>restore controlfile from '/backup/rman/4standby/standby_PR.ctl';

RMAN>sql 'alter database mount standby database';
RMAN>catalog start with '/backup/rman/4standby' noprompt';

RMAN>restore database;

RMAN>exit;

On PR at this time(oracle os user prompt)

Enable the destination to ship archivelogs

$sqlplus / as sysdba

SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SID='*';

On DR at this time (grid os user prompt)

Remove existing STANDBY Logfiles

Login as grid user

Find which ASM instance is running

$ps -ef|grep pmon

Get the ASM instance name and export it

export ORACLE_SID=+ASM1/+ASM2

Login to asmcmd” prompt

asmcmd>cd +PRREDO1/PRDB

asmcmd>rm sr* (Choose “y” on prompt)

asmcmd>cd +PRREDO2/PRDB

asmcmd>rm sr* (Choose “y” on prompt)

asmcmd>exit;

Back to oracle user prompt(oracle os user prompt)

Login to SQLPLUS prompt as SYSDBA

$sqlplus / as sysdba

Recreate Standby Logfiles

SQL>ALTER DATABASE ADD STANDBY LOGFILE '+PRREDO1/PRDB/srl1.f' SIZE 536870912;
SQL>ALTER DATABASE ADD STANDBY LOGFILE '+PRREDO2/PRDB/srl2.f' SIZE 536870912;
SQL>ALTER DATABASE ADD STANDBY LOGFILE '+PRREDO1/PRDB/srl3.f' SIZE 536870912;
SQL>ALTER DATABASE ADD STANDBY LOGFILE '+PRREDO2/PRDB/srl4.f' SIZE 536870912;
SQL>ALTER DATABASE ADD STANDBY LOGFILE '+PRREDO1/PRDB/srl5.f' SIZE 536870912;
SQL>ALTER DATABASE ADD STANDBY LOGFILE '+PRREDO2/PRDB/srl6.f' SIZE 536870912;
SQL>ALTER DATABASE ADD STANDBY LOGFILE '+PRREDO1/PRDB/srl7.f' SIZE 536870912;
SQL>ALTER DATABASE ADD STANDBY LOGFILE '+PRREDO2/PRDB/srl8.f' SIZE 536870912;

Open the Database in Managed Recovery mode

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

 Monitor the shipping and logs apply status

SQL>SELECT SEQUENCE#,BLOCK#,PROCESS,STATUS FROM V$MANAGED_STANDBY;

On PR at this time(oracle os user prompt)

Keep monitoring the logs apply status until the the previous sequence to current sequence updated with status "YES" under applied column from the below query

$sqlplus / as sysdba

SQL>SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG WHERE SEQUENCE#=(SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG);

The updated status "YES" for the current log sequence at PR confirms the synchronisation with its DR peer.

On DR at the time(oracle os user prompt)

Enable Active Dataguard mode

$sqlplus / as sysdba

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

SQL>ALTER DATABASE OPEN READ ONLY;

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

SQL><Query any application table to monitor the Active transactional SYNC which should match with PR transaction output>

If this has helped you, please do not forget share it and help others.

Good Luck!