Mir Sayeed Hassan – Oracle Blog

How to perform the Manual Data Guard Switchover on Oracle 19c Database

To perform this data guard switchover operation., You need to planned according to the requirement.

In this case., your primary database role will be changed/converted as STANDBY DB., Standby database role will be changed/converted as PRIMARY DB & There is no loss of data in this method.

Consider the below Database Environment on my server.

Environment              SOURCE DB               TARGET DB
----------------------------------------------------------------
DB Version               19.0.0.0.0             19.0.0.0.0 
db_sid                     ora19c                 ora19c
instance_name              ora19c               ora19cstd 
db_unique_name             ora19c               ora19cstd 
DB Role                   Primary            Physical Standby
IP Address             192.168.140.101       192.168.140.102
OS Version                RHEL 8.2               RHEL 8.2

MANDATORY PRE-REQUISITE TO PERFORM BEFORE SWITCHOVER PROCESS.

Check the status of the database version on both Primary & Standby

Primary DB (192.168.140.101)

SQL> select instance_name, status, version, open_mode, database_role from V$database, v$instance;

INSTANCE_NAME         STATUS     VERSION         OPEN_MODE       DATABASE_ROLE
-------------------------------------------------------------------------------
ora19c                 OPEN      19.0.0.0.0      READ WRITE         PRIMARY

Standby DB (192.168.140.102)

SQL> select instance_name, status, version, open_mode, database_role from V$database, v$instance;

INSTANCE_NAME      STATUS      VERSION        OPEN_MODE       DATABASE_ROLE
-----------------------------------------------------------------------------
ora19cstd         MOUNTED     19.0.0.0.0       MOUNTED      PHYSICAL STANDBY

Verify the Primary & Standby database are sync., Issue the below query.

On Primary DB (192.168.140.101)

SQL> SELECT a.thread#, b. last_seq, a.applied_seq, a. last_app_timestamp, b.last_seq-a.applied_seq ARC_DIFF FROM (SELECT thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp FROM gv$archived_log WHERE dest_id=2 and
applied = 'YES' GROUP BY thread#) a,(SELECT thread#, MAX (sequence#) last_seq FROM gv$archived_log GROUP BY thread#) b WHERE a.thread# = b.thread#;

THREAD#     LAST_SEQ      APPLIED_SEQ     LAST_APP_    ARC_DIFF
----------------------------------------------------------------
1            4062           4062          13-DEC-21       0

On Standby DB (192.168.140.102)

SQL> SELECT thread#, max(SEQUENCE#) FROM V$ARCHIVED_LOG where APPLIED='YES' group by thread#;

THREAD#      MAX(SEQUENCE#)
------------------------------
1                4062

Check the Status of Dataguard On PRIMARY

SQL> select switchover_status from V$database;

SWITCHOVER_STATUS
--------------------
TO STANDBY

Check the Status of Dataguard On PRIMARY

SQL> select switchover_status from V$database;

SWITCHOVER_STATUS
--------------------
NOT ALLOWED

Note: In Oracle documentation explain SWITCHOVER_STATUS column of v$database can have the following values:

NOT ALLOWED – Either this is a standby database and the primary database has not been switched first, or this is a primary database and there are no standby databases.
SESSIONS ACTIVE – Indicates that there are active SQL sessions attached to the primary or standby database that need to be disconnected before the switchover operation is permitted.
SWITCHOVER PENDING – This is a standby database and the primary database switchover request has been received but not processed.
SWITCHOVER LATENT – The switchover was in pending mode, but did not complete and went back to the primary database.
TO PRIMARY – This is a standby database, with no active sessions, that is allowed to switch over to a primary database.
TO STANDBY – This is a primary database, with no active sessions, that is allowed to switch over to a standby database.
RECOVERY NEEDED – This is a standby database that has not received the switchover request.

By considering all the above information., therefore the database is sync & all the parameter are set correctly.

Try to create a restore point for primary database

Check the retention policy of database, archivelog mode & flashback status

SQL> show parameter db_flashback_retention_target

NAME                                  TYPE          VALUE
---------------------------------------------------------
db_flashback_retention_target       integer         1440

SQL> select log_mode, flashback_on, open_mode from v$database;

LOG_MODE         FLASHBACK_ON         OPEN_MODE
-------------------------------------------------
ARCHIVELOG          YES             READ WRITE

Create a Restore Point

SQL> create restore point pridb_restore_13dec21 guarantee flashback database;
Restore point created.

Verify

SQL> col name for a50

SQL> select scn, time, name,STORAGE_SIZE from v$restore_point;

SCN            TIME                                   NAME                                  STORAGE_SIZE
---------------------------------------------------------------------------------------------------------
672602033      13-DEC-21 11.49.23.000000000 PM     SWITCHOVER_14DEC21_12_11_CDB_PDB          2936012800

Note:

Restore point is created for safer side., In any reason if your data guard switchover operation failed., you can revert it back to the previous state by restoring above created restore point.

NOW LTES START THE SWITCHOVER PROCESS IN REAL-TIME

ON PRIMARY DB (192.168.140.101)

Here Primary database is converted into standby database by using below command.

SQL> alter database commit to switchover to standby with session shutdown;
Database Altered.

Shutdown the Database

SQL> shudown immediate;

Startup Database with nomount fallowed by mount as standby database or directly started database with mount state.

SQL> startup nomount;
SQL> alter database mount standby database;

Start the recovery process on New Switchover Standby database (192.168.140.101).

SQL> alter database recover managed standby database using current logfile disconnect from session;

Check the status of Switchover database., it should changed from Primary to Physical Standby.

SQL> select name,open_mode, database_role from V$database;

NAME         OPEN_MODE              DATABASE_ROLE
-------------------------------------------------
ORA19C       MOUNTED             PHYSICAL STANDBY

Note: Still not complete., now need to perform on primary database & then do some log switch on primary database & confirm

ON STANDBY DB (192.168.140.101)

Conversion of standby to Primary DB

SQL> alter database commit to switchover to primary with session shutdown;
Database Altered.

Check the Swichover DB Status

SQL> select name,open_mode, database_role from V$database;

NAME          OPEN_MODE          DATABASE_ROLE
---------------------------------------------
ORA19C       READ WRITE            PRIMARY

SWITCHOVER IS COMPLETED SUCCESSFULLY

TO CONFIRM MORE INTO IT., PERFORM SOME alter system switch logfile on new primary database (192.168.140.102) & Verify.

=====Hence tested & verified in our test env=====