How to Switchover to Standby Database in 19c by DGMGRL

  • by

Suppose that you have configured data guard broker for 19c primary and standby databases, we can test switchover by using the broker.

The switchover operation in 19c data guard environments remains the same as we did in 11g. In this post, we take the following steps to make a switchover.

Check Static Service

For switching over more smoothly, we have to make sure that static services for DGMGRL are working on the listeners.

Primary Listener

[oracle@primary-19c ~]$ lsnrctl status
...
Services Summary...
Service "COMPDB_CFG" has 1 instance(s).
  Instance "PRIMDB", status READY, has 1 handler(s) for this service...
Service "PRIMDB" has 1 instance(s).
  Instance "PRIMDB", status READY, has 1 handler(s) for this service...
Service "PRIMDBXDB" has 1 instance(s).
  Instance "PRIMDB", status READY, has 1 handler(s) for this service...
Service "PRIMDB_DGMGRL" has 1 instance(s).
  Instance "PRIMDB", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Standby Listener

[oracle@standby-19c ~]$ lsnrctl status
...
Services Summary...
Service "COMPDB_CFG" has 1 instance(s).
  Instance "STANDB", status READY, has 1 handler(s) for this service...
Service "STANDB" has 1 instance(s).
  Instance "STANDB", status READY, has 1 handler(s) for this service...
Service "STANDBXDB" has 1 instance(s).
  Instance "STANDB", status READY, has 1 handler(s) for this service...
Service "STANDB_DGMGRL" has 1 instance(s).
  Instance "STANDB", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

If you don't know how to add static services for DGMGRL, please refer to: How to Configure 19c Data Guard Broker.

Question: Are those static services required conditions for making a switch?
Answer: No, they are not really required, but if you don't have them, error message ORA-12514 appeared during switchover may confuse you for a while. So I recommend you to use static services for DGMGRL switchover.

Check Broker Status

To know whether every thing is ready, we use DGMGRL to check all statuses.

Logon DGMGRL

[oracle@primary-19c ~]$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu Aug 15 20:59:03 2019
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 sys@primdb;
Password:
Connected to "PRIMDB"
Connected as SYSDBA.

Please note that, using a slash (/) to connect current instance will make the broker throw ORA-01017 during switchover operations.

Show Broker Configuration

DGMGRL> show configuration verbose;

Configuration - drconf

  Protection Mode: MaxPerformance
  Members:
  primdb - Primary database
    standb - Physical standby database

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    TraceLevel                      = 'USER'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'
    ExternalDestination1            = ''
    ExternalDestination2            = ''
    PrimaryLostWriteAction          = 'CONTINUE'
    ConfigurationWideServiceName    = 'COMPDB_CFG'

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS

The current standby database is standb. We should memorize the information.

Check Log Lags

We should focus on transport and apply Lags. Both lags should be in 0 second or so. So we show the status of current standby database standb.

DGMGRL> show database standb;

Database - standb

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 3.00 KByte/s
  Real Time Query:    ON
  Instance(s):
    STANDB

Database Status:
SUCCESS

Make a Switchover

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

Show Broker Configuration Again

DGMGRL> show configuration;

Configuration - drconf

  Protection Mode: MaxPerformance
  Members:
  standb - Primary database
    primdb - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 20 seconds ago)

The new primary is standb now.

Leave a Reply

Your email address will not be published. Required fields are marked *