Skip to main content

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


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!

Popular posts from this blog

Setting NLS_LANG on Windows for Oracle Database

Sometimes, it is difficult to deal with Windows Platform as it drains the hell out of us!! Recently, I have come across a situation where one of my client's requirement was to input Arabic language into Oracle Database [or] read/retrieve the output into Various client Applications such as PL/SQL Developer/SQL Developer/TOAD etc.. Inputting the non-english language into Database  has never been difficult as we are given plenty of language options within our beloved Oracle Database, but, the problem lies within the complex Windows OS when user wanted to view the data in his/her beloved language, in Applications such as PL/SQL Developer or SQL Developer etc.. This post is all about it.

Character Set, Character encoding, & Code point Yes, it is a group of characters that is recognised by the Hardware through the OS Interface. Every character is allocated a number, called a code point, these code points will be represented in the computer by one or more bytes. So, every character h…

Data Guard Configuration with Oracle DGMGRL

About Data GuardEfficient business operations, quality customer service, compliance with government regulations, and safeguarding corporate information assets all require high levels of data protection and data availability. Thus it is no surprise that data protection and data availability are among the top priorities for enterprises of all sizes and industries.
A set of questions one needs to ask him/herself is what If a disaster impacts ongoing business transactions, How soon can those systems be back to business again, How much Data is affordable to loose, is it acceptable? The only answer for all these questions to position a proper "Business Continuity Plan (BCP)" in place so that it helps business to grow inline with its consumer expectations.  Technically, this is where Oracle's Data Guard plays a vital role that ensures superior reliability, and rock solid performance. DataGuard is Oracle's complete Disaster Recovery Solution which can reliably deliver aggressi…

IPC Send timeout detected - The Story of a Database

Some errors make us panic, nervous, and beyond belief when we positively attempt to resolve a panic situation. Specific to case of Clusterware where it attempts to resolve a conflict by removing dead resources from the cluster but it suffers because of lack of additional resources to complete the task, and makes the whole situation even worst so everything that has in contact with the key resource also suffers. The key resource what we are talking is a RAC Database and the Clusterware under a panic situation is Oracle Clusterware.

Here's the environment
Oracle RDBMS 11.2.0.4.4Oracle GI 11.2.0.4.43 Node RAC Database with vault function enabledNetApp NFS Filer based Storage
What was the issue?
On Instance 3 I have noticed "IPC Send timeout". Fri Aug 14 04:46:05 2015 IPC Send timeout detected. Receiver ospid 20545 [ Fri Aug 14 04:49:55 2015 And then, Oracle has started suspending MMON actions one by one as we noticed below messages in the log Fri Aug 14 10:49:07 2015 Suspending M…