Switchover and Failover with DataGuard Broker on Oracle 19c

8-minute read
Oracle
Oracle • DataGuard • Standby

Introduction

In a previous note we reviewed how to perform a Switchover in Oracle DataGuard 19c using Oracle commands.
This time we are going to perform the Switchover and go through the Failover and Reinstate processes using the DataGuard Broker.

Prerequisites

We are using a DataGuard environment with a Primary instance and one Physical Standby on Oracle 19c multitenant with one pluggable database. For more information on how to create a DataGuard environment please review Create an Oracle DataGuard Physical Standby.

We also need this DataGuard environment to be configured for DataGuard Broker. For more information on how this is done please review Setup DataGuard Broker on Oracle 19c.

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.

Access the DataGuard Broker

Issue the command dgmgrl in the Primary instance to access the DataGuard Broker, then connect to the database with the SYSDG user:

[oracle@patodgprmy ~]$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu Jul 29 20:41:37 2020
Version 19.3.0.0.0

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sysdg
Password:
Connected to "prmydb"
Connected as SYSDG.

Alternatively you can use the SYS user if you didn’t configure a user for DataGuard in the orapwd file.

Validate Databases are Ready for Switchover

First we need to validate that both databases are ready for the Switchover process, issuing the command validate database:

DGMGRL> validate database prmydb

  Database Role:    Primary database

  Ready for Switchover:  Yes

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

DGMGRL> validate database stbydb

  Database Role:     Physical standby database
  Primary Database:  prmydb

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

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

As both databases are shown as Ready for Switchover: Yes we can proceed.

Perform the Switchover

Issue the command switchover to indicating the database we want to be the new Primary:

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

Then check the new roles by issuing the command show configuration:

DGMGRL> show configuration

Configuration - dgpato

  Protection Mode: MaxPerformance
  Members:
  stbydb - Primary database
    prmydb - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 43 seconds ago)

DGMGRL>

As we can see the instance stbydb is now the Primary database and the instance prmydb is now the Physical Standby, the roles has been interchanged so the Switchover was successful.

Failover

In a DataGuard environment when the Primary instance fails you need to go through the Failover and Reinstate processes in order to restore the database service, as described in the documentation:

Changes a standby database to the primary role in response to a primary database failure.
If Flashback Database is enabled on the primary database, it can be reinstated as a standby for the new primary database once the reason for the failure is corrected.

For this exercise we have stopped the Primary instance and listener.

Access the DataGuard Broker

Issue the command dgmgrl in the Standby instance to access the DataGuard Broker, then connect to the database with the SYSDG user:

[oracle@patodgstby ~]$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu Jul 29 21:36:11 2020
Version 19.3.0.0.0

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sysdg
Password:
Connected to "stbydb"
Connected as SYSDG.

Validate Standby is Ready for Failover

First we need to validate that the Standby database is ready for the Failover process, issuing the command validate database:

DGMGRL> validate database stbydb

  Database Role:     Physical standby database
  Primary Database:  prmydb
    Warning: primary database was not reachable

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

  Flashback Database Status:
    prmydb:  Unknown
    stbydb:  On

  Managed by Clusterware:
    prmydb:  Unknown
    stbydb:  NO
    Validating static connect identifier for the primary database prmydb...
Unable to connect to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.20.0.10)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=prmydb_DGMGRL)(INSTANCE_NAME=prmydb)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))
ORA-12541: TNS:no listener

Failed.
    Warning: Ensure primary database's StaticConnectIdentifier property
    is configured properly so that the primary database can be restarted
    by DGMGRL after switchover

  Temporary Tablespace File Information:
    prmydb TEMP Files:  Unknown
    stbydb TEMP Files:  3

  Data file Online Move in Progress:
    prmydb:  Unknown
    stbydb:  No

  Transport-Related Information:
    Transport On:  No
    Gap Status:    Unknown
    Transport Lag:  0 seconds (computed 351 seconds ago)
    Transport Status:  Success

  Log Files Cleared:
    prmydb Standby Redo Log Files:  Unknown
    stbydb Online Redo Log Files:   Unknown
    stbydb Standby Redo Log Files:  Unknown

DGMGRL>

From the output we can see that no information about the Primary is available as it’s failed.
As the Standby database is marked as Ready for Failover: Yes (Primary Not Running) we can proceed.

Perform the Failover

Issue the command failover to indicating the database we want to be now the Primary:

DGMGRL> failover to stbydb
Performing failover NOW, please wait...
Failover succeeded, new primary is "stbydb"
DGMGRL>

Then we can check if the Failover worked by issuing the command show configuration:

DGMGRL> show configuration

Configuration - dgpato

  Protection Mode: MaxPerformance
  Members:
  stbydb - Primary database
    prmydb - Physical standby database (disabled)
      ORA-16661: the standby database needs to be reinstated

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 11 seconds ago)

The Failover was successful as we can see that the instance stbydb is now the Primary database. The instance prmydb (that is still in failed status) is marked as a potential Physical Standby that needs to be reinstated.

Reinstate

When the failure in the former Primary instance or server is fixed, we need to reinstate this database so it can return to work as part of this DataGuard configuration.

First we need to startup mount the fixed instance:

[oracle@patodgprmy trace]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jul 29 21:42:30 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1610609888 bytes
Fixed Size                  9135328 bytes
Variable Size            1023410176 bytes
Database Buffers          570425344 bytes
Redo Buffers                7639040 bytes
Database mounted.
SQL>

Then in the current Primary instance (former Standby) we access the DataGuard Broker and connect to the database with the SYSDG user:

[oracle@patodgstby trace]$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu Jul 29 21:43:16 2020
Version 19.3.0.0.0

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sysdg
Password:
Connected to "STBYDB"
Connected as SYSDG.

Then we can issue the command reinstate database indicating the database that was in failed status:

DGMGRL> reinstate database prmydb
Reinstating database "prmydb", please wait...
Reinstatement of database "prmydb" succeeded
DGMGRL>

Finally we can check if the Reinstate worked by issuing the command show configuration:

DGMGRL> show configuration

Configuration - dgpato

  Protection Mode: MaxPerformance
  Members:
  stbydb - Primary database
    prmydb - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 45 seconds ago)

As we can see the instance stbydb is still the Primary database but now the instance prmydb is the Physical Standby, so the Reinstate process was successful.

Switchback

As a final step after a Failover/Reinstate we want to go back to the original roles on this DataGuard environment.
So we need to do the same steps for a Switchover review in previous section, but this time pointing to the prmydb database:

Validate Databases are Ready for Switchover

DGMGRL> validate database stbydb

  Database Role:    Primary database

  Ready for Switchover:  Yes

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

DGMGRL> validate database prmydb

  Database Role:     Physical standby database
  Primary Database:  stbydb

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

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

Perform the Switchover

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

Then check the new roles by issuing the command show configuration:

DGMGRL> show configuration

Configuration - dgpato

  Protection Mode: MaxPerformance
  Members:
  prmydb - Primary database
    stbydb - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 49 seconds ago)

As we can see the instance prmydb is now the Primary database and the instance stbydb is now the Physical Standby, so the roles has been returned to the original ones successfully.

Conclusion

We successfully performed a Switchover and a Failover in our DataGuard environment on Oracle 19c multitenant. We can see the DataGuard Broker eases the process and validation of the role interchanging when you face an scenario of maintenance or failure in a DataGuard environment.