Resync Physical standby using RMAN Incremental backup

Just another day at the office, our production database has 2 physical standby and it lagging for more that 1000 archives.
the reason is the password was changed and the password file was not copied to standby.

below are the steps i did to resync the standby. for the first standby i tried to restore archive but its taking time .

NOTE:
– Primay and Standby filesystem is the same.
– Shared NFS is mounted on both server.


1. [Standby] Stop the managed standby apply process:

 
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
  744389405

SQL> alter database recover managed standby database cancel;

Database altered.

SQL>

2. [Standby] Shutdown the standby database

 
SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL>


3. [Primary] On the primary, take an incremental backup from the SCN number where the standby has been stuck:

 
RMAN> run {
allocate channel c1 type disk format '/backup/TEST01/%U.bkp';
backup incremental from scn 744389405 database;
}

2> 3> 4>
allocated channel: c1
channel c1: SID=455 device type=DISK

Starting backup at 21-AUG-13

backup will be obsolete on date 28-AUG-13
archived logs will not be kept or backed up
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00002 name=/db/db04/oradata/TEST01/sysaux01.dbf
input datafile file number=00001 name=/db/db04/oradata/TEST01/system01.dbf
input datafile file number=00003 name=/db/db04/oradata/TEST01/undotbs01.dbf
input datafile file number=00004 name=/db/db04/oradata/TEST01/users01.dbf
channel c1: starting piece 1 at 21-AUG-13
channel c1: finished piece 1 at 21-AUG-13
piece handle=/backup/TEST01/b0ohqcd3_1_1.bkp tag=TAG20130821T000035 comment=NONE
channel c1: backup set complete, elapsed time: 00:02:25

backup will be obsolete on date 28-AUG-13
archived logs will not be kept or backed up
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 21-AUG-13
channel c1: finished piece 1 at 21-AUG-13
piece handle=/backup/TEST01/b1ohqchk_1_1.bkp tag=TAG20130821T000035 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:15
Finished backup at 21-AUG-13
RMAN-08591: WARNING: invalid archived log deletion policy
released channel: c1

RMAN>
RMAN>

4. [Primary] On the primary, create a new standby controlfile:

 
SQL> alter database create standby controlfile as '/backup/TEST01/TEST01_standby.ctl';
Database altered.
SQL>


5. [Standby] Bring up the instance in nomount mode:

 
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 6714322944 bytes
Fixed Size 2172392 bytes
Variable Size 3472886296 bytes
Database Buffers 3221225472 bytes
Redo Buffers 18038784 bytes
SQL>

6. [Standby] Check the location of the controlfile:

 
SQL> show parameter control;

NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
control_file_record_keep_time integer 7
control_files string /db/db02/oradata/TEST01/contr
ol01.ctl, /db/db03/oradata/TES
T01/control02.ctl, /db/db04/o
radata/TEST01/control03.ctl
control_management_pack_access string DIAGNOSTIC+TUNING
SQL>

7. [Standby] Replace the controlfile with the one you just created in primary.

 
lnx01lhr: TEST01>cp /backup/TEST01/TEST01_standby.ctl /db/db02/oradata/TEST01/control01.ctl
lnx01lhr: TEST01>cp /backup/TEST01/TEST01_standby.ctl /db/db03/oradata/TEST01/control02.ctl
lnx01lhr: TEST01>cp /backup/TEST01/TEST01_standby.ctl /db/db04/oradata/TEST01/control03.ctl
lnx01lhr: TEST01>

8.[Standby] Mount the standby database:

 
SQL> alter database mount standby database;

Database altered.

SQL>

NOTE : since my backup resides on the filesystem on primary. no need for me to catalog the backup.

9.Recover these files:

 
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/21/2013 00:40:24
ORA-19870: error while restoring backup piece /backup/TEST01/b0ohqcd3_1_1.bkp
ORA-19573: cannot obtain exclusive enqueue for datafile 2

RMAN> exit

Recovery Manager complete.

If you encountered above error, it means recovery was started when we put the DB on mount state.
we need to cancel it.

Cancel the recovery.

 
lnx01lhr: TEST01>sqlplus '/ as sysdba'

SQL*Plus: Release 11.1.0.7.0 - Production on Wed Aug 21 00:40:46 2013

Copyright (c) 1982, 2008, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Now try to do the recover command.

 
lnx01lhr: TEST01>rman target /

Recovery Manager: Release 11.1.0.7.0 - Production on Wed Aug 21 00:41:21 2013

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: TEST01 (DBID=1197757016, not open)

RMAN> recover database noredo;

Starting recover at 21-AUG-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=525 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /db/db04/oradata/TEST01/system01.dbf
destination for restore of datafile 00002: /db/db04/oradata/TEST01/sysaux01.dbf
destination for restore of datafile 00003: /db/db04/oradata/TEST01/undotbs01.dbf
destination for restore of datafile 00004: /db/db04/oradata/TEST01/users01.dbf
channel ORA_DISK_1: reading from backup piece /backup/TEST01/b0ohqcd3_1_1.bkp
channel ORA_DISK_1: piece handle=/backup/TEST01/b0ohqcd3_1_1.bkp tag=TAG20130821T000035
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:35
Finished recover at 21-AUG-13

RMAN>

9.Exit RMAN and start managed recovery process:

 
SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL>

10.Check the SCN’s in primary and standby:

 
#####################################################################################################
Database Information

PROTECTION_MODE       PROTECTION_LEVEL    ROLE                 SWITCHOVER_STATUS
-------------------- -------------------- -------------------- --------------------
MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY PHYSICAL STANDBY     SESSIONS ACTIVE

DBName                      Last_Seq_On_Primary Last_Seq_Applied_on_Standby GAP
--------------------------- ------------------- --------------------------- ----------
TEST01                      30742               30742                       0

#####################################################################################################
lnx01lhr: TEST01>

Thanks!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s