Failover with DG Broker (No-Flashback)
Overview:
Failover is an irreversible transition of a standby database to the primary role.
This is only done in the event of a catastrophic failure of the primary database
0. Enviroment
1. Primary Database Crashed
2. Steps to perform Failover with Physical Standby database
3. Reinstate database old primary (w5005pr)
4. Rebuild Standby for new primary
5. Enable old primary database (w5005pr)
Note: Flashback database is not enabled on both primary and standby (in our case)
Do you need to recreate all the Data Guard infrastructure once the failover has been performed?
Ans:
1. If flashback enabled on both primary and standby and flashback logs retention time has not exceeded, then it is not necessary to recreate standby from scratch.
2. If flashback not enabled on both primary and standby then we need to recreate standby from scratch.
Primary: Platform: Linuxx86_64 Server Name: RAC1.RAJASEKHAR.COM, IP: 192.168.2.101 DB Version: Oracle 11.2.0.3, File system: ASM DB Name: w5005pr, DB_UNIQUE_NAME: w5005pr; Non-RAC Flashback: Disabled Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1 Standby: Platform: Linuxx86_64 Server Name: RAC2.RAJASEKHAR.COM, IP: 192.168.2.102 DB Version: Oracle 11.2.0.3, , File system: ASM DB Name: w5005pr, DB_UNIQUE_NAME: w5005prg; Non-RAC Flashback: Disabled Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1
DGMGRL> show configuration; Configuration - w5005pr Protection Mode: MaxPerformance Databases: w5005pr - Primary database w5005prg - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS DGMGRL> [oracle@rac1 ~]$ ps -ef | grep pmon | grep -v grep oracle 3540 1 0 Jan11 ? 00:00:02 asm_pmon_+ASM [oracle@rac1 ~]$ Tue Jan 12 00:00:22 2016 CKPT (ospid: 2499): terminating the instance due to error 472 Tue Jan 12 00:00:23 2016 License high water mark = 5 Instance terminated by CKPT, pid = 2499 USER (ospid: 3029): terminating the instance Instance terminated by USER, pid = 3029 <---- Instance terminated
2. Steps to perform Failover with Physical Standby database
[oracle@rac2 ~]$ dgmgrl DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production Copyright (c) 2000, 2009, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. DGMGRL> connect sys/sys@w5005prg Connected. DGMGRL> show configuration; Configuration - w5005pr Protection Mode: MaxPerformance Databases: w5005pr - Primary database w5005prg - Physical standby database Fast-Start Failover: DISABLED Configuration Status: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor ORA-16625: cannot reach database "w5005pr" DGM-17017: unable to determine configuration status DGMGRL> DGMGRL> failover to w5005prg; Performing failover NOW, please wait... Failover succeeded, new primary is "w5005prg" <-- Succeeded DGMGRL> DGMGRL> show configuration Configuration - w5005pr Protection Mode: MaxPerformance Databases: w5005prg - Primary database w5005pr - Physical standby database (disabled) ORA-16661: the standby database needs to be reinstated Fast-Start Failover: DISABLED Configuration Status: SUCCESS DGMGRL>
3. Reinstate database old primary
DGMGRL> reinstate database w5005pr; Reinstating database "w5005pr", please wait... Error: ORA-16653: failed to reinstate database Failed. Reinstatement of database "w5005pr" failed DGMGRL> /* Failing it would have needed to configure flashback database to be able to do so automatically… If you try to start w5005pr database in MOUNT state and issue again a request to reinstate you will now get clear error message */ [oracle@rac1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 12 00:05:33 2016 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to an idle instance. SQL> startup mount; ORACLE instance started. Total System Global Area 1269366784 bytes Fixed Size 2227984 bytes Variable Size 1090519280 bytes Database Buffers 167772160 bytes Redo Buffers 8847360 bytes Database mounted. SQL> DGMGRL> reinstate database w5005pr; Reinstating database "w5005pr", please wait... Error: ORA-16827: Flashback Database is disabled <-- Actual error Failed. Reinstatement of database "w5005pr" failed DGMGRL> SQL> shut immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup nomount; ORACLE instance started. Total System Global Area 1269366784 bytes Fixed Size 2227984 bytes Variable Size 1090519280 bytes Database Buffers 167772160 bytes Redo Buffers 8847360 bytes SQL>
4. Rebuild Standby database for new primary (w5005prg)
[oracle@rac2 ~]$ rman target sys/sys@w5005prg auxiliary sys/sys@w5005pr Recovery Manager: Release 11.2.0.3.0 - Production on Tue Jan 12 00:22:53 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: W5005PR (DBID=4158252169) connected to auxiliary database: W5005PR (not mounted) RMAN> duplicate target database for standby from active database nofilenamecheck; Starting Duplicate Db at 12-JAN-16 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=24 device type=DISK contents of Memory Script: { backup as copy reuse targetfile '/u01/app/oracle/product/11.2.0/db_1/dbs/orapww5005prg' auxiliary format '/u01/app/oracle/product/11.2.0/db_1/dbs/orapww5005pr' ; } executing Memory Script Starting backup at 12-JAN-16 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=108 device type=DISK Finished backup at 12-JAN-16 contents of Memory Script: { sql clone "alter system set control_files = ''+DATA/w5005pr/controlfile/current.268.900456457'' comment= ''Set by RMAN'' scope=spfile"; backup as copy current controlfile for standby auxiliary format '+DATA/w5005pr/controlfile/current.268.900456457'; sql clone "alter system set control_files = ''+DATA/w5005pr/controlfile/current.268.900456457'' comment= ''Set by RMAN'' scope=spfile"; shutdown clone immediate; startup clone nomount; } executing Memory Script sql statement: alter system set control_files = ''+DATA/w5005pr/controlfile/current.268.900456457'' comment= ''Set by RMAN'' scope=spfile Starting backup at 12-JAN-16 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy copying standby control file output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_w5005prg.f tag=TAG20160112T002322 RECID=5 STAMP=900894203 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 Finished backup at 12-JAN-16 sql statement: alter system set control_files = ''+DATA/w5005pr/controlfile/current.268.900456457'' comment= ''Set by RMAN'' scope=spfile Oracle instance shut down connected to auxiliary database (not started) Oracle instance started Total System Global Area 1269366784 bytes Fixed Size 2227984 bytes Variable Size 1090519280 bytes Database Buffers 167772160 bytes Redo Buffers 8847360 bytes contents of Memory Script: { sql clone 'alter database mount standby database'; } executing Memory Script sql statement: alter database mount standby database contents of Memory Script: { set newname for clone tempfile 1 to new; switch clone tempfile all; set newname for clone datafile 1 to new; set newname for clone datafile 2 to new; set newname for clone datafile 3 to new; set newname for clone datafile 4 to new; backup as copy reuse datafile 1 auxiliary format new datafile 2 auxiliary format new datafile 3 auxiliary format new datafile 4 auxiliary format new ; sql 'alter system archive log current'; } executing Memory Script executing command: SET NEWNAME renamed tempfile 1 to +DATA in control file executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting backup at 12-JAN-16 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00001 name=+DATA/w5005prg/datafile/system.264.900546429 output file name=+DATA/w5005pr/datafile/system.274.900894219 tag=TAG20160112T002338 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45 channel ORA_DISK_1: starting datafile copy input datafile file number=00002 name=+DATA/w5005prg/datafile/sysaux.265.900546465 output file name=+DATA/w5005pr/datafile/sysaux.275.900894265 tag=TAG20160112T002338 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35 channel ORA_DISK_1: starting datafile copy input datafile file number=00003 name=+DATA/w5005prg/datafile/undotbs1.266.900546499 output file name=+DATA/w5005pr/datafile/undotbs1.276.900894299 tag=TAG20160112T002338 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=+DATA/w5005prg/datafile/users.267.900546507 output file name=+DATA/w5005pr/datafile/users.277.900894303 tag=TAG20160112T002338 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 Finished backup at 12-JAN-16 sql statement: alter system archive log current contents of Memory Script: { switch clone datafile all; } executing Memory Script datafile 1 switched to datafile copy input datafile copy RECID=5 STAMP=900894305 file name=+DATA/w5005pr/datafile/system.274.900894219 datafile 2 switched to datafile copy input datafile copy RECID=6 STAMP=900894305 file name=+DATA/w5005pr/datafile/sysaux.275.900894265 datafile 3 switched to datafile copy input datafile copy RECID=7 STAMP=900894305 file name=+DATA/w5005pr/datafile/undotbs1.276.900894299 datafile 4 switched to datafile copy input datafile copy RECID=8 STAMP=900894305 file name=+DATA/w5005pr/datafile/users.277.900894303 Finished Duplicate Db at 12-JAN-16 RMAN> exit Recovery Manager complete. [oracle@rac2 ~]$
5. Enable old primary database
[oracle@rac2 ~]$ dgmgrl DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production Copyright (c) 2000, 2009, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. DGMGRL> connect sys/sys@w5005prg Connected. DGMGRL> show configuration; Configuration - w5005pr Protection Mode: MaxPerformance Databases: w5005prg - Primary database w5005pr - Physical standby database (disabled) ORA-16795: the standby database needs to be re-created Fast-Start Failover: DISABLED Configuration Status: SUCCESS DGMGRL> DGMGRL> enable database w5005pr; Enabled. DGMGRL> show configuration; Configuration - w5005pr Protection Mode: MaxPerformance Databases: w5005prg - Primary database w5005pr - Physical standby database <-- Old Primary Fast-Start Failover: DISABLED Configuration Status: SUCCESS DGMGRL>
Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.