Failover with No DG Broker (No Flashback) / Failover using Manual Method.
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
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.
0. Enviroment
1. Simulate failover
2. Initiate Failover
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 On Primary SQL> select instance_name,status,database_role from v$database,v$instance; INSTANCE_NAME STATUS DATABASE_ROLE ---------------- ------------ ---------------- w5005pr OPEN PRIMARY SQL> select switchover_status from v$database; SWITCHOVER_STATUS -------------------- TO STANDBY SQL> On Standby SQL> select instance_name,status,database_role from v$database,v$instance; INSTANCE_NAME STATUS DATABASE_ROLE ---------------- ------------ ---------------- w5005prg OPEN PHYSICAL STANDBY SQL> select switchover_status from v$database; SWITCHOVER_STATUS -------------------- NOT ALLOWED SQL>
1. Simulate failover: Bring down the primary database w5005pr
DISCLAIMER: I will not take any responsibility of any consequences or loss of data caused by this command. Please do not use this command on production.
SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down. SQL>
On Standby (w5005prg)
SQL> alter database recover managed standby database cancel; Database altered. SQL> alter database recover managed standby database finish force; Database altered. SQL> Note: If Any error from above step then only follow below command. alter database activate physical standby database; SQL> select switchover_status from v$database; SWITCHOVER_STATUS -------------------- TO PRIMARY SQL> alter database commit to switchover to primary; Database altered. SQL> alter database open; Database altered. SQL> select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance; NAME OPEN_MODE DATABASE_ROLE INSTANCE_NAME --------- -------------------- ---------------- ---------------- W5005PR READ WRITE PRIMARY w5005prg SQL> On Old Primary (W5005PR) [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 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> From New Primary Database Rebuild Standby database for new primary [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 ~]$ SQL> alter database open; Database altered. SQL> alter database recover managed standby database disconnect from session using current logfile; Database altered. SQL> select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance; NAME OPEN_MODE DATABASE_ROLE INSTANCE_NAME --------- -------------------- ---------------- ---------------- W5005PR READ ONLY WITH APPLY PHYSICAL STANDBY w5005pr SQL>
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.