Failover with No DG Broker using Flashback Database
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.
Primary: Platform: Linuxx86_64 Server Name: RAC1.DBA Learning Hub.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.DBA Learning Hub.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> select flashback_on from v$database; FLASHBACK_ON ------------------ YES 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> select flashback_on from v$database; FLASHBACK_ON ------------------ YES SQL>
- Configre Physical Standby database. Please click below for step by step configuration. Please click here - Make sure that flashback database is enabled on both primary and standby database.
2. 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>
3. Steps to perform Failover with Physical Standby database
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> Find SCN on the new primary database (w5005prg) SQL> select standby_became_primary_scn from v$database; STANDBY_BECAME_PRIMARY_SCN -------------------------- 1618441 SQL> On OLD Primary (W5005PR) SQL> startup mount; ORACLE instance started. Total System Global Area bytes Fixed Size 2227984 bytes Variable Size bytes Database Buffers bytes Redo Buffers 8847360 bytes Database mounted. SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ YES SQL> SQL> select name,INSTANCE_NAME from v$database,v$instance; NAME INSTANCE_NAME --------- ---------------- W5005PR w5005pr SQL> flashback database to scn 1618441; <--- Flashback complete. SQL> alter database convert to physical standby; Database altered. SQL> shutdown immediate; ORA-01507: database not mounted ORACLE instance shut down. SQL> startup nomount; ORACLE instance started. Total System Global Area bytes Fixed Size 2227984 bytes Variable Size bytes Database Buffers bytes Redo Buffers 8847360 bytes SQL> alter database mount standby database; Database altered. 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> SQL> select process,status,sequence# from v$managed_standby; PROCESS STATUS SEQUENCE# --------- ------------ ---------- ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 PROCESS STATUS SEQUENCE# --------- ------------ ---------- ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CLOSING 4 ARCH CONNECTED 0 ARCH CLOSING 5 ARCH CONNECTED 0 PROCESS STATUS SEQUENCE# --------- ------------ ---------- ARCH CLOSING 6 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 RFS IDLE 0 RFS IDLE 0 RFS IDLE 0 PROCESS STATUS SEQUENCE# --------- ------------ ---------- RFS IDLE 0 MRP0 APPLYING_LOG 7 <-- RFS IDLE 7 RFS IDLE 0 37 rows selected. 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.