DGMGRL Switchover
Suppose that you have configured data guard broker for 19c primary and standby databases, we can test switchover by DGMGRL, an interactive broker utility.
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.