MANUAL DATAGUARD SWITCHOVER 19c
Switch over:
It is reversal of role between a standby database and a primary database. The primary database assumes role of standby database and the standby database assumes role of primary database after the switchover. There is no data loss in a switchover. This is generally a planned activity.
Overview steps:
Step:1 Check the db name, open mode, database role of the primary and standby databases.
Step:2 Select switchover status on primary & standby db.
Step:3 Check that there is no active users connected to the databases.
Step:4 Switch the current online redo log file on primary database and verify that it has been applied in the standby database.
Step:5 Connect with primary database and initiate the switchover.
Step:6 Bounce the primary db and check the switchover status.
Step:7 Then convert the physical standby into primary db.(stop the MRP process)
Step:8 Open the new standby db in read only mode.
Step:9 Apply the redo log files in newly created standby.(start the MRP process).Check whether the logs are applying in the new standby db.
Step:1 Check the db name, open mode, database role of the primary and standby databases.
On Primary:
SQL> select name,open_mode,database_role,switchover_status from v$database;
NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
--------- -------------------- ---------------- --------------------
PROD READ WRITE PRIMARY TO STANDBY
On Standby:
SQL> select name,open_mode,database_role,switchover_status from v$database;
NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
--------- -------------------- ---------------- --------------------
PROD READ ONLY PHYSICAL STANDBY TO PRIMARY
Step:2 Check that there is no active users connected to the databases.
SQL> column OSUSER format a10
SQL> column USERNAME format a10
SQL> select osuser,username from v$session;
OSUSER USERNAME
---------- ----------
oracle
oracle
oracle
oracle
oracle
oracle
oracle
oracle
oracle
oracle
oracle SYS
Step:3 Check the log sequence number in primary and standby db(before switchover).
On Primary:
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1; Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ---------- 1 61 61 0
On Standby:
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1; Thread Last Sequence Received Last Sequence Applied Difference ---------- ---------------------- --------------------- ---------- 1 61 61 0
Step:4 In this step primary db is converted into standby by giving the following command.
SQL> alter database commit to switchover to physical standby with session shutdown; Database altered.SQL> alter database mount standby database; Database altered.
Step:5 Check name, open_mode, database_role of new standby database.
SQL> select name, open_mode, database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
PROD READ ONLY PHYSICAL STANDBY
Step:6 Then start to apply the redo log (MRP process) on primary(current standby).
SQL> alter database recover managed standby database disconnect from session; Database altered.SQL> select max(sequence#) from v$archived_log where applied='YES'; MAX(SEQUENCE#) -------------- 62
Step:8 Now we have to stop the MRP process in old standby.
SQL> alter database recover managed standby database cancel; Database altered.
Step:9 Conversion of standby to primary db and also check the name, open mode, database role of standby db.
SQL> alter database commit to switchover to primary with session shutdown; Database altered.
Step:10 Check name, open_mode, database_role of new primary database.
SQL> select name, open_mode, database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
PROD READ WRITE PRIMARY
Switch over activity has been completed successfully…..!!!
