Scenario 9: Performing a Switchover Operation

You can switch the role of the primary database and a standby database using the SWITCHOVER command.

Before you issue the SWITCHOVER command, you must ensure:

The state of the primary and standby databases are TRANSPORT-ON and APPLY-ON, respectively.
All participating databases are in good health, without any errors or warnings present.
The standby database properties were set on the primary database, so that the primary database can function correctly when transitioning to a standby database (shown in the following examples in boldface type).
Standby redo log files are configured on the primary database.
If the configuration is in maximum availability mode, then the current primary is configured to receive redo via SYNC or FASTSYNC or ASYNC mode if it will receive redo directly from the new primary. If it will receive redo via a far sync instance, then the far sync instance is configured to receive redo via SYNC or FASTSYNC mode and the current primary is configured to receive redo via ASYNC mode. If the configuration is in maximum protection mode, then the current primary is configured to receive redo via SYNC mode.
If fast-start failover is enabled, you can perform a switchover only to the standby database that was specified as the target standby database.

The following are the tasks necessary to perform a switchover using the SWITCHOVER command:

Using the SWITCHOVER Command Task 1: Check the Primary Database

Use the SHOW DATABASE VERBOSE command to check the state, health, and properties of the primary database.

For example:

DGMGRL> SHOW DATABASE VERBOSE 'North_Sales';

Database - North_Sales

  Role:                PRIMARY
  Intended State:      TRANSPORT-ON
  Redo Rate:           104 Byte/s  in 15 seconds (computed 9 seconds ago) 
  Instance(s):
    NorthSales

  Properties:
    AlternateLocation               = ''
    ApplyInstanceTimeout            = '0'
    ApplyInstances                  = '0'
    ApplyLagThreshold               = '30'
    ApplyParallel                   = 'AUTO'
    ArchiveLocation                 = ''
    Binding                         = 'OPTIONAL'
    DGConnectIdentifier             = 'north_sales'
    DelayMins                       = '0'
    FastStartFailoverTarget         = 'South_Sales'
    HostName                        = 'sales1'
    InconsistentLogXptProps         = '(monitor)'
    LogShipping                     = 'ON'
    LogXptMode                      = 'SYNC'
    LogXptStatus                    = '(monitor)'
    MaxFailure                      = '0'
    NetTimeout                      = '30'
    ObserverConnectIdentifier       = ''
    PreferredApplyInstance          = ''
    PreferredObserverHosts          = ''
    RecvQEntries                    = '(monitor)'
    RedoCompression                 = 'DISABLE'
    RedoRoutes                      = ''
    ReopenSecs                      = '300'
    SendQEntries                    = '(monitor)'
    SidName                         = '(monitor)'
    StandbyAlternateLocation        = ''
    StandbyArchiveLocation          = ''
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sales1.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=North_Sales_DGMGRL.example.com)(INSTANCE_NAME=NorthSales)(SERVER=DEDICATED)))'
    TopWaitEvents                   = '(monitor)'
    TransportDisconnectedThreshold  = '30'
    TransportLagThreshold           = '30'

  Log file locations:
    Alert log               : /sales/oracle/diag/rdbms/north_sales/NorthSales/trace/alert_NorthSales.log
    Data Guard Broker log   : /sales/oracle/diag/rdbms/north_sales/NorthSales/trace/drcNorthSales.log

Database Status:
SUCCESS

In particular, you should examine the boldface properties and the current status of the primary database.

Using the SWITCHOVER Command Task 2: Check the Standby Database That is the Target of the Switchover

Use the SHOW DATABASE command to check the status of the standby database that is the target of the switchover.

For example:

DGMGRL> SHOW DATABASE 'South_Sales';
 
Database - South_Sales
 
  Role: PHYSICAL STANDBY
  Intended State: APPLY-ON
  Transport Lag: 0 seconds (computed 0 seconds ago)
  Apply Lag: 0 seconds (computed 0 seconds ago)
  Apply Rate: 1.44 MByte/s
  Real Time Query: OFF
  Instance(s):
    south_sales1
 
Database Status:
SUCCESS

Using the SWITCHOVER Command Task 3: Confirm That the Database Is Ready for a Role Change

Prior to performing a role change, you can use the VALIDATE DATABASE command to perform an exhaustive set of checks on the database to confirm that it is ready for a role change.

The examples shown in this step use the VALIDATE DATABASE command for all three databases in the DRSolution configuration: a primary, logical standby, and physical standby database. The configuration looks as follows:

DGMGRL> SHOW CONFIGURATION;

Configuration - DRSolution

  Protection Mode: MaxAvailability
  Members:
  North_Sales - Primary database
    South_Sales - Physical standby database 
    West_Sales  - Logical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 46 seconds ago)

Example: Validate the Primary Database

DGMGRL> VALIDATE DATABASE North_Sales;

  Database Role:    Primary database

  Ready for Switchover:  Yes

  Managed by Clusterware:
    North_Sales:  NO             
    The static connect identifier allows for a connection to database "North_Sales".

Example: Validate the Logical Standby Database

Validate the logical standby database, as follows:

DGMGRL> VALIDATE DATABASE West_Sales;

  Database Role:     Logical standby database
  Primary Database:  North_Sales

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)
    Warning: Physical and snapshot standby databases will
    be disabled if a role change is performed to this database

  Flashback Database Status:
    Database     Status           Retention Target
    North_Sales  On               1440   
    West_Sales   Off              1440   

  Managed by Clusterware:
    North_Sales:  NO             
    West_Sales :  NO             
    The static connect identifier allows for a connection to database "North_Sales".

  Parameter Settings:
    Parameter                       North_Sales Value        West_Sales Value
    DB_BLOCK_CHECKING               FALSE                    FALSE
    DB_BLOCK_CHECKSUM               TYPICAL                  TYPICAL
    DB_LOST_WRITE_PROTECT           AUTO                     AUTO

Example: Validate the Physical Standby Database

Validate the physical standby database, as follows:

sentences.
DGMGRL> VALIDATE DATABASE South_Sales;

  Database Role:     Physical standby database
  Primary Database:  North_Sales

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

  Managed by Clusterware:
    North_Sales:  NO             
    South_Sales:  NO             
    The static connect identifier allows for a connection to database "North_Sales".

  Transport-Related Property Settings:
    Property                        North_Sales Value        South_Sales Value
    LogXptMode                      ASYNC                    SYNC

  Parameter Settings:
    Parameter                       North_Sales Value        South_Sales Value
    DB_BLOCK_CHECKING               FALSE                    FALSE
    DB_BLOCK_CHECKSUM               TYPICAL                  TYPICAL
    DB_LOST_WRITE_PROTECT           AUTO                     AUTO

Note that database South_Sales is the only database that has its LogXptMode property set to SYNC. This will not prevent a switchover, but after the switchover the configuration will not be able to function in Maximum Availability mode and will display an error if the problem was not corrected prior to the role change (see Task 5).

Using the SWITCHOVER Command Task 4: Issue the Switchover Command

Issue the SWITCHOVER command to swap the roles of the primary and standby databases.

The following example shows how the broker automatically shuts down and restarts the old primary database as a part of the switchover. (See the usage notes in DGMGRL Command Usage Notes for information about how to set up the broker environment so that DGMGRL can automatically restart the primary and standby databases for you.)

DGMGRL> SWITCHOVER TO 'South_Sales';
2023-01-11T20:52:23.010+00:00
Performing switchover NOW, please wait...

2023-01-11T20:52:23.233+00:00
Operation requires a connection to database "South_Sales"
Connecting ...
Connected to "South_Sales"
Connected as SYSDBA.

2023-01-11T20:52:24.189+00:00
Continuing with the switchover...

2023-01-11T20:52:36.113+00:00
New primary database "South_Sales" is opening...

2023-01-11T20:52:36.113+00:00
Operation requires start up of instance "NorthSales" on database "North_Sales"
Starting instance "NorthSales"...
Connected to an idle instance.
ORACLE instance started.
Connected to "North_Sales"
Database mounted.
Database opened.

2023-01-11T20:53:41.190+00:00
Switchover succeeded, new primary is "South_Sales"

2023-01-11T20:53:41.196+00:00
Switchover processing complete, broker ready.

After the switchover completes, use the SHOW CONFIGURATION and SHOW DATABASE commands to verify that the switchover operation was successful.

Using the SWITCHOVER Command Task 5: Show the Configuration

Use the SHOW CONFIGURATION command to verify that the switchover was successful.

DGMGRL> SHOW CONFIGURATION;

Configuration - DRSolution

  Protection Mode: MaxAvailability
  Members:
  South_Sales - Primary database
    Warning: ORA-16627: No member available to support the protection mode.

    North_Sales - Physical standby database 
    West_Sales  - Logical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
WARNING   (status updated 46 seconds ago)

As noted in Task 3, because redo received by database North_Sales was configured to use ASYNC transport mode, the configuration is not able to function in Maximum Availability mode after the role change and a warning is reported. To fix this issue, either set the LogXptMode property value for database North_Sales to receive redo using SYNC transport mode or configure the RedoRoutes property value for database South_Sales to send redo using SYNC transport mode. Note that when a database receives redo from a database or a far sync instance that has the RedoRoutes property configured with a redo transport mode, that mode overrides the transport mode specified by LogXptMode.