Convert a Failed Primary Into a Physical Standby Database Using RMAN backups

When a failover occurs in Data Guard configuration and we have activated our standby database as our new primary database then our old primary database cannot participate again in data guard configuration unless it is recoverd as a new standby database in our new configuration.

We can recover our old primary database using FLASHBACK as well as RMAN backups

PFB steps to recover a failed primary database using RMAN backup :-

1. Mount the Old Primary Database :-

bash-3.2$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jul 26 09:48:02 2013

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

Enter user-name: /as sysdba
Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1043886080 bytes
Fixed Size                  2213384 bytes
Variable Size             817891832 bytes
Database Buffers          213909504 bytes
Redo Buffers                9871360 bytes
Database mounted.

2. Find out the SCN at which old standby database becomes new primary :-

login to Old standby which is now primary in new configuration

 SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;

TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)
----------------------------------------
1120211

3. Login to OLD primary database(failover primary) and connect with RMAN and restore and recover database using backup available of failover primary before failover SCN . Remember its a incomplete recovery as we are doing a point in time recovery :-

bash-3.2$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jul 26 10:25:28 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TEST (DBID=2119844559, not open)

RMAN>

RMAN> run
2> {
3> set until scn 1055317;
4> restore database;
5> recover database;
6> }

executing command: SET until clause

Starting restore at 26-JUL-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
flashing back control file to SCN 1055317
ORACLE error from target database:
ORA-38729: Not enough flashback database log data to do FLASHBACK.

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /disk1/STBY/STBY/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /disk1/STBY/STBY/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /disk1/STBY/STBY/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /disk1/STBY/STBY/users01.dbf
channel ORA_DISK_1: reading from backup piece /disk1/STBY/flash_recovery_area/STBY/2013_07_25/o1_mf_nnndf_TAG20130725T111211_8z1gomb4_.bkp
channel ORA_DISK_1: piece handle=/disk1/STBY/flash_recovery_area/STBY/2013_07_25/o1_mf_nnndf_TAG20130725T111211_8z1gomb4_.bkp tag=TAG20130725T111211
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 26-JUL-13

Starting recover at 26-JUL-13
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 11 is already on disk as file /disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_25/o1_mf_1_11_8z2vbqs5_.arc
archived log for thread 1 with sequence 12 is already on disk as file /disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_25/o1_mf_1_12_8z2vbqj3_.arc
archived log for thread 1 with sequence 13 is already on disk as file /disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_25/o1_mf_1_13_8z2vbqht_.arc
archived log for thread 1 with sequence 14 is already on disk as file /disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_25/o1_mf_1_14_8z2vbqh2_.arc
archived log for thread 1 with sequence 15 is already on disk as file /disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_25/o1_mf_1_15_8z2vbqhg_.arc
archived log for thread 1 with sequence 16 is already on disk as file /disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_25/o1_mf_1_16_8z2vbqgt_.arc
archived log for thread 1 with sequence 17 is already on disk as file /disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_25/o1_mf_1_17_8z2vbqhx_.arc
archived log for thread 1 with sequence 18 is already on disk as file /disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_25/o1_mf_1_18_8z2vbqfq_.arc
archived log for thread 1 with sequence 19 is already on disk as file /disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_25/o1_mf_1_19_8z2vbqho_.arc
archived log for thread 1 with sequence 20 is already on disk as file /disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_25/o1_mf_1_20_8z2vbmoj_.arc
archived log for thread 1 with sequence 21 is already on disk as file /disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_25/o1_mf_1_21_8z2vbn4z_.arc
archived log for thread 1 with sequence 22 is already on disk as file /disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_25/o1_mf_1_22_8z2vh2r6_.arc
archived log for thread 1 with sequence 23 is already on disk as file /disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_25/o1_mf_1_23_8z2wro33_.arc
archived log for thread 1 with sequence 24 is already on disk as file /disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_25/o1_mf_1_24_8z2x4qjx_.arc
archived log for thread 1 with sequence 25 is already on disk as file /disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_25/o1_mf_1_25_8z2x4vv4_.arc
archived log for thread 1 with sequence 26 is already on disk as file /disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_25/o1_mf_1_26_8z2xcf4x_.arc
archived log for thread 1 with sequence 27 is already on disk as file /disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_25/o1_mf_1_27_8z2xcj92_.arc
archived log for thread 1 with sequence 28 is already on disk as file /disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_25/o1_mf_1_28_8z2xhjl0_.arc
archived log for thread 1 with sequence 29 is already on disk as file /disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_25/o1_mf_1_29_8z2xv5gp_.arc
archived log for thread 1 with sequence 30 is already on disk as file /disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_25/o1_mf_1_30_8z3685n2_.arc
archived log for thread 1 with sequence 31 is already on disk as file /disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_25/o1_mf_1_31_8z383o8v_.arc
archived log for thread 1 with sequence 32 is already on disk as file /disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_25/o1_mf_1_32_8z38gryx_.arc
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=10
channel ORA_DISK_1: reading from backup piece /disk1/STBY/flash_recovery_area/STBY/2013_07_25/o1_mf_annnn_TAG20130725T111259_8z1gq36b_.bkp
channel ORA_DISK_1: piece handle=/disk1/STBY/flash_recovery_area/STBY/2013_07_25/o1_mf_annnn_TAG20130725T111259_8z1gq36b_.bkp tag=TAG20130725T111259
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_26/o1_mf_1_10_8z55bzxz_.arc thread=1 sequence=10
channel default: deleting archived log(s)
archived log file name=/disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_26/o1_mf_1_10_8z55bzxz_.arc RECID=36 STAMP=821788047
archived log file name=/disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_25/o1_mf_1_11_8z2vbqs5_.arc thread=1 sequence=11
archived log file name=/disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_25/o1_mf_1_12_8z2vbqj3_.arc thread=1 sequence=12
archived log file name=/disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_25/o1_mf_1_13_8z2vbqht_.arc thread=1 sequence=13
archived log file name=/disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_25/o1_mf_1_14_8z2vbqh2_.arc thread=1 sequence=14
archived log file name=/disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_25/o1_mf_1_15_8z2vbqhg_.arc thread=1 sequence=15
archived log file name=/disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_25/o1_mf_1_16_8z2vbqgt_.arc thread=1 sequence=16
archived log file name=/disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_25/o1_mf_1_17_8z2vbqhx_.arc thread=1 sequence=17
archived log file name=/disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_25/o1_mf_1_18_8z2vbqfq_.arc thread=1 sequence=18
archived log file name=/disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_25/o1_mf_1_19_8z2vbqho_.arc thread=1 sequence=19
archived log file name=/disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_25/o1_mf_1_20_8z2vbmoj_.arc thread=1 sequence=20
archived log file name=/disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_25/o1_mf_1_21_8z2vbn4z_.arc thread=1 sequence=21
archived log file name=/disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_25/o1_mf_1_22_8z2vh2r6_.arc thread=1 sequence=22
archived log file name=/disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_25/o1_mf_1_23_8z2wro33_.arc thread=1 sequence=23
archived log file name=/disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_25/o1_mf_1_24_8z2x4qjx_.arc thread=1 sequence=24
archived log file name=/disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_25/o1_mf_1_25_8z2x4vv4_.arc thread=1 sequence=25
archived log file name=/disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_25/o1_mf_1_26_8z2xcf4x_.arc thread=1 sequence=26
archived log file name=/disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_25/o1_mf_1_27_8z2xcj92_.arc thread=1 sequence=27
media recovery complete, elapsed time: 00:00:05
Finished recover at 26-JUL-13

NOTE:- If backup of failed primary is not avaible recreate the physical standby using backup of newly created primary database after failover.

PFB output of alert logfile once RMAN restore and recovery is applied :-

alter database recover logfile '/disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_25/o1_mf_1_27_8z2xcj92_.arc'

Media Recovery Log /disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_25/o1_mf_1_27_8z2xcj92_.arc

Recovery of Online Redo Log: Thread 1 Group 3 Seq 28 Reading mem 0

  Mem# 0: /disk1/STBY/STBY/redo03.log

Recovery of Online Redo Log: Thread 1 Group 4 Seq 29 Reading mem 0

  Mem# 0: /disk1/STBY/STBY/online_redo01.log

Recovery of Online Redo Log: Thread 1 Group 5 Seq 30 Reading mem 0

  Mem# 0: /disk1/STBY/STBY/online_redo02.log

Recovery of Online Redo Log: Thread 1 Group 6 Seq 31 Reading mem 0

  Mem# 0: /disk1/STBY/STBY/online_redo03.log

Recovery of Online Redo Log: Thread 1 Group 1 Seq 32 Reading mem 0

  Mem# 0: /disk1/STBY/STBY/redo01.log

Incomplete Recovery applied until change 1055317 time 07/25/2013 17:08:24

Media Recovery Complete (TEST)

Completed: alter database recover logfile '/disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_25/o1_mf_1_27_8z2xcj92_.arc'

4. Once old primary database has been recovered start the process to change it into new physical standby database :-

bash-3.2$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jul 26 10:28:05 2013

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

Enter user-name: /as sysdba

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

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Database altered.

This command will dismount the database and convert the control file to standby controlfile;

6. Shutdown the newly physical standby database and open it in mount mode.

SQL> SHUTDOWN IMMEDIATE;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1043886080 bytes
Fixed Size                  2213384 bytes
Variable Size             817891832 bytes
Database Buffers          213909504 bytes
Redo Buffers                9871360 bytes
Database mounted.

7. Start redo transport to the new physical standby database:-

Login to new Primary database and check current state of archive destinations :-

SQL> SELECT DEST_ID, DEST_NAME, STATUS, PROTECTION_MODE, DESTINATION,ERROR,SRL FROM V$ARCHIVE_DEST_STATUS;

   DEST_ID DEST_NAME                      STATUS    PROTECTION_MODE      DESTINATION                    ERROR                          SRL
---------- ------------------------------ --------- -------------------- ------------------------------ ------------------------------ ---
         1 LOG_ARCHIVE_DEST_1             VALID     MAXIMUM PERFORMANCE  /oracle/oracle/ora11g/product/                                NO
                                                                         dbs/arch

         2 LOG_ARCHIVE_DEST_2             VALID     MAXIMUM PERFORMANCE  TEST                                                          YES

if LOG_ARCHIVE_DEST_2 is set to new standby database then check redo transport and if not then PFB steps to configure it :-

SQL > ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=TEST NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TEST';

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

SQL> Alter system  switch logfile;

system altered 

SQL> SELECT DEST_ID, DEST_NAME, STATUS, PROTECTION_MODE, DESTINATION,ERROR,SRL FROM V$ARCHIVE_DEST_STATUS;

Error column has to be blank for LOG_ARCHIVE_DEST_2.

Now login to new standby database and start redo apply :-

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

Database altered.

PFB output in alert log once redo process starts :-

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION

Attempt to start background Managed Standby Recovery process (TEST)

Fri Jul 26 10:42:14 2013

MRP0 started with pid=54, OS id=8716550

MRP0: Background Managed Standby Recovery process started (TEST)

 started logmerger process

Fri Jul 26 10:42:19 2013

Managed Standby Recovery not using Real Time Apply

Parallel Media Recovery started with 24 slaves

Waiting for all non-current ORLs to be archived...

All non-current ORLs have been archived.

Media Recovery Log /disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_26/o1_mf_1_1_8z55gw2x_.arc

Media Recovery Log /disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_26/o1_mf_1_2_8z55gw2v_.arc

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION

Media Recovery Log /disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_26/o1_mf_1_3_8z55gw2r_.arc

Media Recovery Log /disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_26/o1_mf_1_4_8z55ndh1_.arc

Media Recovery Log /disk1/STBY/flash_recovery_area/STBY/archivelog/2013_07_26/o1_mf_1_5_8z55ndl6_.arc

Media Recovery Waiting for thread 1 sequence 6

Failed Primary successfully converted to new Physical standby database. If we want we can again switchover the roles of newly created primary and physical standby databases to their original pre-failure roles.

PFB link to Convert a Failed Primary Into a Physical Standby Database Using FLASHBACK database.

I hope this article helped you.

Regards,

Amit Rath