Oracle Dataguard 12c: How to perform Switchover using DGMGRL

This post offers Step by Step method to perform Switchover using Dataguard Broker command prompt DGMGRL. For the purposes of this document, the following fictitious environment is used as an example to describe the procedure:

DG configuration : 12c
Primary db_unique_name : Sydney
Standby db_unique_name : Washington

SWITCHOVER Considerations

1. DG Broker will not downgrade the PROTECTION mode during the process of Switchover.
2. Configure suitable REDO transport mode on current standby that becomes Primary after switchover.
3. Configure SRL (Standby Redo Logs) and local archiving destination on current Primary.

Broker switchover command verifies the availability of SRL on current Primary Database and suitable Redo transport method on current Standby.

DB Restart Considerations

1. If the switchover target is Physical Standby then Broker shutdown the Primary as a process of switchover.
2. If the switchover environment in MAX_PROTECTION mode and primary has only one Standby then broker shuts down both primary and standby as a process of switchover. If primary has multiple standby then broker shuts down only the new Primary.

Pre-Switchover Checks

1. Verify Dataguard Broker Configuration – Use following command s to verify broker status before switchover.

DGMGRL>show configuration;
DGMGRL>show database [primary];
DGMGRL>show database [standby];
DGMGRL>show database verbose [primary];
DGMGRL>show database verbose [standby];

Example outputs in our setup:

DGMGRL> show configuration;

Configuration - 12c
  Protection Mode: MaxPerformance
  Databases:
  sydney  - Primary database
    washington - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database sydney;

Database - sydney
  Role:              PRIMARY
  Intended State:    TRANSPORT-ON
  Instance(s):
    sydney

Database Status:
SUCCESS

DGMGRL> show database washington

Database - washington
  Role:              PHYSICAL STANDBY
  Intended State:    APPLY-ON
  Transport Lag:     0 seconds (computed 1 second ago)
  Apply Lag:         0 seconds (computed 1 second ago)
  Apply Rate:        0 Byte/s
  Real Time Query:   ON
  Instance(s):
    washington

Database Status:
SUCCESS

NOTE: For further troubleshooting, use:

DGMRL>show database db_name logxptstatus;
DGMGRL>show database db_name inconsistentlogxptprops;

Validate Database

Validate database verify following, no need to explicitly check whether ORLs/SRLS cleared.

Whether there is missing redo data on a standby database
Whether flashback is enabled
The number of temporary tablespace files configured
Whether an online data file move is in progress
Whether online redo logs are cleared for a physical standby database
Whether standby redo logs are cleared for a primary database
The online log file configuration
The standby log file configuration
Apply-related property settings
Transport-related property settings

DGMGRL> validate database sydney

  Database Role:    Primary database
  Ready for Switchover:  Yes    <-----------Ready for Switchover
  Flashback Database Status:
    sydney:  Off

DGMGRL> validate database washington

  Database Role:     Physical standby database
  Primary Database:  sydney
  Ready for Switchover:  Yes    <--------This confirms Switchover readiness
  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:
    sydney:   Off
    washington:  Off

  Future Log File Groups Configuration:

    Thread #  Online Redo Log Groups   Standby Redo Log Groups
              (washington)                (sydney)
    1         4                        4

NOTE: For further troubleshoot use validate database verbose.

DGMGRL> validate database verbose sydney
DGMGRL> validate database verbose washington

Preset Redo Transport related parameter

Verify below parameters are set based on PROTECTION MODE.

LogXptMode
NetTimeout
StandbyArchiveLocation
AlternateLocation
RedoRoutes

SWITCHOVER

Disable Apply Delay

To increase the speed of switchover disable delaymins property.

DGMGRL>edit database washington set property delaymins=0;

Turn on Data Guard tracing on primary and standby:

DGMGRL> edit configuration set property tracelevel=support;
DGMGRL> edit database sydney set property LogArchiveTrace=8191;
DGMGRL> edit database washington set property LogArchiveTrace=8191;

for RAC instance,

DGMGRL> EDIT INSTANCE * ON DATABASE 'sydney' SET PROPERTY LogArchiveTrace=8191;

Tail Alert Logs and DRC (optional) on all instances

Tail alert log and DRC log which is present in same location of alert log(text) from all the nodes of primary and standby.

tail –f [alert log of primary]
tail –f [alert log of standby]
tail –f [drc[SID] log of primary]
tail –f [drc[SID] log of standby]

Perform Switchover

Connect to sysdg:

DGMGRL> connect sysdg
Password:
Connected as SYSDG.

DGMGRL> switchover to washington
Performing switchover NOW, please wait...
Operation requires a connection to instance "washington" on database "washington"
Connecting to instance "washington"...
Connected as SYSDBA.
New primary database "washington" is opening...
Operation requires startup of instance "sydney" on database "sydney"
Starting instance "sydney"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "washington"

Sample Alert Logs

Primary Alert log - alert_sydney.log:

ALTER DATABASE SWITCHOVER TO 'washington'
Starting switchover [Process ID: 10402]
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 10402] (sydney)
...
Switchover End-Of-Redo Log thread 1 sequence 131 has been fixed
Switchover: Primary highest seen SCN set to 0x0.0x310a3a
ARCH: Noswitch archival of thread 1, sequence 131
ARCH: End-Of-Redo Branch archival of thread 1 sequence 131
ARCH: Evaluating archive   log 1 thread 1 sequence 131
...
Switchover complete. Database shutdown required
Completed: ALTER DATABASE SWITCHOVER TO 'washington'

Standby Alert log - alert_washington.log:

SWITCHOVER: received request 'ALTER DATABASE COMMIT TO SWITCHOVER  TO PRIMARY' from primary database.
ALTER DATABASE SWITCHOVER TO PRIMARY (washington)
...
Standby became primary SCN: 3213880  <----------Record this SCN
...
Switchover: Complete - Database mounted as primary
SWITCHOVER: completed request from primary database.

Primary Drc log - /path/diag/rdbms/sydney/sydney/trace/drcsydney.log:

SWITCHOVER TO washington
...
Broker Configuration:       "12c"
      Protection Mode:            Maximum Performance
      Fast-Start Failover (FSFO): Disabled, flags=0x0, version=0
      Primary Database:           washington (0x02010000)  <-------washington as Primary

Standby DRC log - /path/diag/rdbms/washington/washington/trace/drcwashington.log:

Notifying Oracle Clusterware to prepare target standby database for switchover
Notifying DMON of db close
Notifying RSM0 of db close
...
Deferring associated archivelog destinations of sites permanently disabled due to Switchover
Notifying Oracle Clusterware to buildup primary database after switchover

Post-Switchover Check

Reset Delaymins property

Change the delaymins property to prior on new standby after switchover based on business requirement.

DGMGRL> edit database [new standby] set property delaymins=[old MRP delay value];

Set Trace to Prior Value

DGMGRL> edit configuration reset property tracelevel;
DGMGRL> edit database sydney reset property logarchivetrace;
DGMGRL> edit database washington reset property logarchivetrace;

Verify Broker Configuration

DGMGRL> show configuration;

Configuration - 12c
  Protection Mode: MaxPerformance
  Databases:
  washington - Primary database
  sydney  - Physical standby database <-----sydney is Physical Standby
Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS