Oracle DBA usually perform the task of database failover when the primary database is down due to any reason like database issue,bug hit,hardware failure etc. and anyhow we've convert the physical standby into primary to make the database available for the users , So in order to perform failover operation DBA need to execute below steps.
Step 1st: Disable or stop all the database jobs for failed primary database
In a real time, Since the primary
database is down so our first task is to disable or stop primary database’s
jobs to prevent any unnecessary alerts prior to start failover operation.
Step 2nd: Enable Blackout
for both primary and standby database
Step 3rd: Disable all the database jobs in standby side until the failover will be done
Step 4th: Stop MRP with finish keyword
Our first step in failover
operation is to stop the current running MRP with “finish” qualifier.
NOTE: Finish qualifier instruct
the physical standby database that the current primary is down and now it’s
going to become a primary database, So apply all the archive log that it has.
SQL> alter database recover managed
standby database finish;
Database altered.
Alert Log Of
standby after stopping MRP with finish keyword
2024-01-26T17:38:42.116993+05:30
Terminal Recovery requested in process
5035
.... (PID:5035): The Time Management
Interface (TMI) is being enabled for role transition
.... (PID:5035): information. This will result in messages beingoutput to
the alert log
.... (PID:5035): file with the prefix
'TMI: '. This is being enabled to make
the timing of
.... (PID:5035): the various stages of
the role transition available for diagnostic purposes.
.... (PID:5035): This output will end
when the role transition is complete.
TMI: adbdrv termRecovery BEGIN
2024-01-26 17:38:42.119452
.... (PID:5035): Terminal Recovery:
Stopping real time apply
Recovered data files to a consistent state at change 2170692
Incomplete Recovery
applied until change 2170693 time 01/26/2024 17:38:12
Media Recovery Complete (INDDR)
Completed: alter database recover managed standby database finish
Step5th: Convert physical standby into primary
SQL> select
name,database_role,switchover_status from v$database;
NAME DATABASE_ROLE SWITCHOVER_STATUS
--------- ----------------
--------------------
US_PRIM
PHYSICAL STANDBY TO PRIMARY
SQL> alter database commit to
switchover to primary;
Database altered.
Alert Log Afer
executing convert to primary command
2024-01-26T17:43:59.013349+05:30
ALTER DATABASE SWITCHOVER TO PRIMARY
(INDDR)
2024-01-26T17:43:59.013861+05:30
TMI: kcv_commit_to_so_to_primary Switchover from physical BEGIN 2024-01-26 17:43:59.014705
Standby terminal recovery start SCN: 2170692
RESETLOGS after incomplete recovery
UNTIL CHANGE 2170693 time 01/26/2024
NET
(PID:5035): ORL pre-clearing operation disabled by switchover
Standby became primary SCN: 2170691 Ã this scn is very useful to reintentiate failed primary
Setting recovery target incarnation to
3
Switchover: Complete - Database
mounted as primary
Completed: alter database commit to switchover to primary
Step 6th: Validate if
database has been converted into primary role
NAME DATABASE_ROLE OPEN_MODE
--------- ---------------- --------------------
US_PRIM
PRIMARY MOUNTED
NOTE: If standby
database is running in read-only mode, then we can still execute failover
command and in addition to convert it into primary it also bring it in mount
mode.
step 7th : open the new
primary database
SQL> alter database open;
Database altered.
Step 8th: Validate if new
primary is running in required role
SQL> select
name,database_role,open_mode from v$database;
NAME DATABASE_ROLE OPEN_MODE
--------- ----------------
--------------------
US_PRIM
PRIMARY READ WRITE
SQL> show parameter db_unique_name
NAME TYPE
VALUE
------------------------------------
----------- ------------------------------
db_unique_name string IND_DR
Step 9th: Validate switchover
status , it'll show as failed destination
SQL>
select name,role,sequence#,thread#,block# from v$dataguard_process;
NAME ROLE SEQUENCE# THREAD#
BLOCK#
-----
------------------------ ---------- ---------- ----------
LGWR log writer 0
0 0
TMON redo transport monitor 0 0 0
TT00 gap manager 0
0 0
TT01 redo transport timer 0 0 0
ARC0 archive local 0
0 0
ARC1 archive redo 0
0 0
ARC2 archive redo 0
0 0
ARC3 archive redo 0
0 0
NET
recovery coordinator 29 1 139
TT02 async ORL multi 2 1
0
TT03 async ORL single 2 1
0
TT04 heartbeat redo informer 0
0 0
SQL> select switchover_status from
v$database;
SWITCHOVER_STATUS
--------------------
FAILED DESTINATION
Step 10th: Enable all the
database jobs
Step 11th: Remove blackout
for the standby database which is running in primary role now.
