DATABASE SWITCHOVER ORACLE 19c USING DGMGRL

 

Description:
In this blog, we are going to see data guard switch over by using data guard broker DGMGRL.

Data Guard Switchover:

• A database can be in one of two mutually exclusive modes (primary or standby). These roles can be altered at runtime without loss of data or resetting of redo logs. A switchover is a role reversal between the primary database and one of its standby databases. A switchover guarantees no data loss.
• This is typically done for planned maintenance of the primary system.
• During a switchover, the primary database transitions to a standby role, and the standby database transitions to the primary role.
• The transition occurs without having to re-enable either database.

Switchover

We do a Switchover by interchanging the roles of the instances participating in the DataGuard environment as described in the documentation:

During a switchover, the primary database transitions to a standby role, and the standby database transitions to the primary role.
A switchover guarantees no data loss and is typically done for planned maintenance of the primary system.

 

Overall steps:

1. Connect the Primary database with DGMGRL Data Guard broker.
2. Switch over Primary to Standby database.
3. Connect Standby database.
4. Switch over Standby to Primary.

SELECT (SELECT DATABASE_ROLE FROM V$DATABASE) AS "Database Role", INSTANCE_NAME, TO_CHAR(STARTUP_TIME, 'YYYY-MM-DD HH24:MI:SS') AS "Instance Open Time" FROM V$INSTANCE;

Primary :-

Database Role    INSTANCE_NAME    Instance Open Time

---------------- ---------------- -------------------

PRIMARY          orcl             2024-11-01 12:03:38

Standby :-

Database Role    INSTANCE_NAME    Instance Open Time

---------------- ---------------- -------------------

PHYSICAL STANDBY orcldg           2024-11-01 12:42:32

Step 1: Connect the primary database with the DGMGRL Data Guard broker.

[oracle@rac201 ~]$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Fri Nov 1 12:59:27 2024
Version 19.21.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL>
DGMGRL>
DGMGRL> connect sys/sys123@orcl;
Connected to "orcl"
Connected as SYSDBA.
DGMGRL>

Step 2: Check the configuration of the Data guard .

DGMGRL> show configuration;

Configuration - dgconfig

  Protection Mode: MaxPerformance
  Members:
  orcl   - Primary database
    orcldg - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 22 seconds ago)

Step 3: Validate the Primary and Standby database 

DGMGRL> validate database orcl;

  Database Role:    Primary database

  Ready for Switchover:  Yes

  Flashback Database Status:
    orcl:  Off

  Managed by Clusterware:
    orcl:  NO
    Validating static connect identifier for the primary database orcl...
    The static connect identifier allows for a connection to database "orcl".

DGMGRL>
DGMGRL> validate database orcldg;

  Database Role:     Physical standby database
  Primary Database:  orcl

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:
    orcl  :  Off
    orcldg:  Off

  Managed by Clusterware:
    orcl  :  NO
    orcldg:  NO
    Validating static connect identifier for the primary database orcl...
    The static connect identifier allows for a connection to database "orcl".

Step 4: Validate static connect identifier for all before switcher 

DGMGRL>
DGMGRL> validate static connect identifier for all;
Oracle Clusterware is not configured on database "orcldg".
Connecting to database "orcldg" using static connect identifier "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.202)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcldg)(STATIC_SERVICE=TRUE)))" ...
Succeeded.
The static connect identifier allows for a connection to database "orcldg".

Oracle Clusterware is not configured on database "orcl".
Connecting to database "orcl" using static connect identifier "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.201)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)(STATIC_SERVICE=TRUE)))" ...
Succeeded.
The static connect identifier allows for a connection to database "orcl".

Note:- It is so important to validate static connections otherwise switchover can be failed.

Step 5: Perform the Switchover operation.

DGMGRL> switchover to orcldg;
Performing switchover NOW, please wait...
Operation requires a connection to database "orcldg"
Connecting ...
Connected to "orcldg"
Connected as SYSDBA.
New primary database "orcldg" is opening...
Operation requires start up of instance "orcl" on database "orcl"
Starting instance "orcl"...
Connected to an idle instance.
ORACLE instance started.
Connected to "orcl"
Database mounted.
Switchover succeeded, new primary is "orcldg"

Step 6: Perform the Post-check.

Database Role    INSTANCE_NAME    Instance Open Time
---------------- ---------------- -------------------
PRIMARY          orcldg           2024-11-01 12:42:32



Database Role    INSTANCE_NAME    Instance Open Time
---------------- ---------------- -------------------
PHYSICAL STANDBY orcl             2024-11-01 13:26:09