UNPLANNED FAILOVER and ReInitializing the OLD Primary as the NEW Standby(Using Flashback).
A failover is done when the primary database (all instances of an Oracle RAC primary database) fails or has become unreachable and one of the standby databases is transitioned to take over the primary role. Failover should be performed when the primary database cannot be recovered in a timely manner. Failover may or may not result in data loss depending on the protection mode in effect at the time of the failover.
–> In this article, I will explain when we unplanned for failover, In that case how to build standby database from old primary database using Flashback concept.
Primary –> prod(dba12)
Standby –>stand(dba13)
Primary Side:(dba12)
Check the information Primary database and max log applied.
|
1 2 3 4 5 6 7 8 |
SYS>>select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE ----- -------------- ---------------- PROD READ WRITE PRIMARY SYS>>select max(sequence#) from v$archived_log; MAX(SEQUENCE#) -------------- 267 |
Standby Side: (dba13)
Check the information standby database and logs are syncing with primary or not.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
[oracle@dba13 ~]$ export ORACLE_SID=stand [oracle@dba13 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Mon Jul 30 20:58:38 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SYS>>select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE ------- ---------------------- ---------------- PROD READ ONLY WITH APPLY PHYSICAL STANDBY SYS>>select max(sequence#) from v$archived_log; MAX(SEQUENCE#) -------------- 267 |
Stop the managed recovery at standby side.
|
1 2 |
SYS>>alter database recover managed standby database cancel; Database altered. |
Primary Side: (dba12)
Create a guaranteed Restore point on current PRIMARY DATABASE.
|
1 2 3 4 5 6 |
SYS>>create restore point III_PROD_STAND_RP_A20 guarantee flashback database; Restore point created. SYS>>select NAME,GUARANTEE_FLASHBACK_DATABASE,scn from v$restore_point; NAME GUARANTEE_FLASHBACK_DATABASE SCN ------------------------- ---------------------------- ---------- III_PROD_STAND_RP_A20 YES 550966 |
Verify physical standby databases are current
|
1 2 3 4 5 6 |
SYS>>select status, gap_status from v$archive_dest_status where dest_id in (2,3); STATUS GAP_STATUS ---------- ------------------------ VALID NO GAP INACTIVE |
Standby Side: (dba13)
Activate standby by using following syntax.
|
1 2 3 4 5 6 |
SYS>>alter database activate standby database; Database altered. SYS>>select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE ------- ----------- ---------------- PROD MOUNTED PRIMARY |
NEW_PRIMARY SIDE :(dba13)
Shutdown new primary database and keep in state and obtain the SCN from the new primary
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SYS>>shutdown immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down. SYS>>startup mount ORACLE instance started. Total System Global Area 222298112 bytes Fixed Size 2922760 bytes Variable Size 163579640 bytes Database Buffers 50331648 bytes Redo Buffers 5464064 bytes Database mounted. SYS>>select to_char(standby_became_primary_scn) from v$database; TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) ---------------------------------------- 549307 SYS>>alter database open; Database opened. |
Check if the remote archive destinations are in “DEFER” state.
|
1 2 3 |
SYS>>alter system set log_archive_dest_state_2=DEFER scope=BOTH sid='*'; System altered. |
OLD_PRIMARY SIDE :(dba12)
Flash back the old primary to SCN captured. but before doing flashback OLD_PRIMARY should be in mount state.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SYS>>shut immediate Database closed. Database dismounted. ORACLE instance shut down. SYS>>startup mount ORACLE instance started. Total System Global Area 222298112 bytes Fixed Size 2922760 bytes Variable Size 163579640 bytes Database Buffers 50331648 bytes Redo Buffers 5464064 bytes Database mounted. SYS>>select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE ------------------------- -------------------- ---------------- PROD MOUNTED PRIMARY SYS>>flashback database to scn 549307; Flashback complete. |
Once we done with flashback the database can’t become Physical standby . we need to manually.
|
1 2 3 4 5 6 7 8 9 10 |
SYS>>select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE -------- ------------ ---------------- PROD MOUNTED PRIMARY SYS>>alter database convert to physical standby; Database altered. SYS>>select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE --------- -------------- ---------------- PROD MOUNTED PHYSICAL STANDBY |
NEW_PRIMARY SIDE :(dba13)
Give few log switches.
|
1 2 3 4 |
SYS>>alter system switch logfile; System altered. SYS>>/ System altered. |
OLD_PRIMARY SIDE :(dba12)
Check logs are reflected and applied are not.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
SYS>>select name,applied from v$archived_log; NAME APPLIED -------------------------------------------------- --------- /u01/oradata/prod/arch/1_260_976255745.dbf YES /u01/oradata/prod/arch/1_261_976255745.dbf YES /u01/oradata/prod/arch/1_262_976255745.dbf YES /u01/oradata/prod/arch/1_263_976255745.dbf YES /u01/oradata/prod/arch/1_264_976255745.dbf YES /u01/oradata/prod/arch/1_265_976255745.dbf YES /u01/oradata/prod/arch/1_266_976255745.dbf YES to_stand YES to_stand YES to_stand YES to_stand YES to_stand YES /u01/oradata/prod/arch/1_267_976255745.dbf NO to_stand YES to_stand YES /u01/oradata/prod/arch/1_268_976255745.dbf NO /u01/oradata/prod/arch/1_267_976255745.dbf NO /u01/oradata/prod/arch/1_2_982876786.dbf NO /u01/oradata/prod/arch/1_1_982876786.dbf NO /u01/oradata/prod/arch/1_267_976255745.dbf NO /u01/oradata/prod/arch/1_3_982876786.dbf NO /u01/oradata/prod/arch/1_4_982876786.dbf NO |
Now archives are reaching to standby side.
So,enable MANAGED RECOVERY mode at standby side.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
SYS>>alter database recover managed standby database using current logfile disconnect; Database altered. SYS>>select name,applied from v$archived_log; /u01/oradata/prod/arch/1_262_976255745.dbf YES /u01/oradata/prod/arch/1_263_976255745.dbf YES /u01/oradata/prod/arch/1_264_976255745.dbf YES /u01/oradata/prod/arch/1_265_976255745.dbf YES /u01/oradata/prod/arch/1_266_976255745.dbf YES to_stand YES to_stand YES to_stand YES to_stand YES to_stand YES /u01/oradata/prod/arch/1_267_976255745.dbf NO to_stand YES to_stand YES /u01/oradata/prod/arch/1_268_976255745.dbf NO /u01/oradata/prod/arch/1_267_976255745.dbf NO /u01/oradata/prod/arch/1_2_982876786.dbf YES /u01/oradata/prod/arch/1_1_982876786.dbf IN-MEMORY /u01/oradata/prod/arch/1_267_976255745.dbf YES /u01/oradata/prod/arch/1_3_982876786.dbf YES /u01/oradata/prod/arch/1_4_982876786.dbf YES |
NOTE :
Here we converted the PRIMARY as STANDBY and STANDBY as PRIMARY.
Just try with opposite by following same steps.
THANK YOU
Loading...
