How to do a switchover from data guard broker

Without the Broker, Data Guard Role Changes require a complex sequence of steps (versions before 12c) on both sides that differ between Logical and Physical Standby.When using DGMGRL, you need to issue only one SWITCHOVER command to specify the name of the standby database that you want to change into the primary role.

How the Broker Performs a Switchover

Once you start the switchover, the broker:

1. Verifies that the primary and the target standby databases are in the following states:
1. The primary database is enabled and is in the TRANSPORT-ON state.
2. The target standby database is enabled and is in the APPLY-ON state.

The broker allows the switchover to proceed as long as there are no errors for the primary database and the standby database that you selected to participate in the switchover operation. Errors occurring for any other bystander standby databases will not impede the switchover.

2. Shuts down all instances except one, if required.

If you are switching over to a physical standby database, the broker shuts down all but one instance on the current primary database. No instances will be shut down on the target physical standby database.

No instances will be shut down if switching over to a logical standby database. You cannot switch over to a snapshot standby database.

3. Switches roles between the primary and standby databases.

The broker first converts the original primary database to run in the standby role. Then, the broker transitions the target standby database to the primary role.Updates the broker configuration file to record the change in roles.

This ensures that each database will run in the correct role and state should it be restarted later for any reason.

4. Restarts the new standby (former primary) database if the switchover occurs to a physical standby database, and Redo Apply begins applying redo data from the new primary database. If this is an Oracle RAC physical standby database, the broker directs Oracle Clusterware to restart the instances that were shut down prior to the switchover. In a configuration operating in maximum protection mode, the new primary database will also be restarted.
5. The new primary database is opened in read/write mode and redo transport services are started.

If the former physical standby database was running with real-time query enabled, the new physical standby database will run with real-time query enabled.

C:\Users\mahesh>dgmgrl

DGMGRL for 64-bit Windows: Version 11.2.0.1.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/Orcl1234@dstudy -->   You can connect to any of the participant database.

Connected.

DGMGRL> show configuration ;

Configuration - DgStudyConfig

Protection Mode: MaxPerformance

  Databases:

    dstudy - Primary database

    study  - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

DGMGRL>

DGMGRL> show database verbose 'dstudy';

Database – dstudy

  Role:            PRIMARY

  Intended State:  TRANSPORT-ON

  Instance(s):

    dstudy

    Properties:

    DGConnectIdentifier             = 'dstudy'

    ObserverConnectIdentifier       = ''

    LogXptMode                      = 'ASYNC'

    DelayMins                       = '0'

    Binding                         = 'optional'

    MaxFailure                      = '0'

    MaxConnections                  = '1'

    ReopenSecs                      = '300'

    NetTimeout                      = '30'

    RedoCompression                 = 'DISABLE'

    LogShipping                     = 'ON'

    PreferredApplyInstance          = ''

    ApplyInstanceTimeout            = '0'

    ApplyParallel                   = 'AUTO'

    StandbyFileManagement           = 'AUTO'

    ArchiveLagTarget                = '0'

    LogArchiveMaxProcesses          = '4'

    LogArchiveMinSucceedDest        = '1'

    DbFileNameConvert               = ''

    LogFileNameConvert              = ''

    FastStartFailoverTarget         = 'study'

    StatusReport                    = '(monitor)'

    InconsistentProperties          = '(monitor)'

    InconsistentLogXptProps         = '(monitor)'

    SendQEntries                    = '(monitor)'

    LogXptStatus                    = '(monitor)'

    RecvQEntries                    = '(monitor)'

    HostName                        = 'BDC7-L-2JRRWQ1'

    SidName                         = 'dstudy'

    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=BDC7-L-2JRRWQ1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DSTUDY_DGMGRL)(INSTANCE_NAME=dstudy)(SERVER=DEDICATED)))'

    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'

    AlternateLocation               = ''

    LogArchiveTrace                 = '0'

    LogArchiveFormat                = 'ARC%S_%R.%T'

    TopWaitEvents                   = '(monitor)'

Database Status:

SUCCESS

DGMGRL>

 

DGMGRL> show database verbose 'study';

Database - study

  Role:            PHYSICAL STANDBY

  Intended State:  APPLY-ON

  Transport Lag:   0 seconds

  Apply Lag:       0 seconds

  Real Time Query: ON

  Instance(s):

    study

 Properties:

    DGConnectIdentifier             = 'study'

    ObserverConnectIdentifier       = ''

    LogXptMode                      = 'ASYNC'

    DelayMins                       = '0'

    Binding                         = 'OPTIONAL'

    MaxFailure                      = '0'

    MaxConnections                  = '1'

    ReopenSecs                      = '300'

    NetTimeout                      = '30'

    RedoCompression                 = 'ENABLE'

    LogShipping                     = 'ON'

    PreferredApplyInstance          = ''

    ApplyInstanceTimeout            = '0'

    ApplyParallel                   = 'AUTO'

    StandbyFileManagement           = 'MANUAL'

    ArchiveLagTarget                = '0'

    LogArchiveMaxProcesses          = '4'

    LogArchiveMinSucceedDest        = '1'

    DbFileNameConvert               = ''

    LogFileNameConvert              = ''

    FastStartFailoverTarget         = 'dstudy'

    StatusReport                    = '(monitor)'

    InconsistentProperties          = '(monitor)'

    InconsistentLogXptProps         = '(monitor)'

    SendQEntries                    = '(monitor)'

    LogXptStatus                    = '(monitor)'

    RecvQEntries                    = '(monitor)'

    HostName                        = 'BDC7-L-2JRRWQ1'

    SidName                         = 'study'

    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=BDC7-L-2JRRWQ1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=study_DGMGRL)(INSTANCE_NAME=study)(SERVER=DEDICATED)))'

    StandbyArchiveLocation          = 'C:\oradata\archive'

    AlternateLocation               = ''

    LogArchiveTrace                 = '0'

    LogArchiveFormat                = 'log%t_%s_%r_%d.arc'

    TopWaitEvents                   = '(monitor)'

Database Status:

SUCCESS

DGMGRL>

 

Everything seems to be good now , so we will do a switchover 

DGMGRL> switchover to study;

Performing switchover NOW, please wait...

New primary database "study" is opening...

Operation requires shutdown of instance "dstudy" on database "dstudy"

Shutting down instance "dstudy"...

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance "dstudy" on database "dstudy"

Starting instance "dstudy"...

ORACLE instance started.

Database mounted.

Database opened.

Switchover succeeded, new primary is "study"

DGMGRL>

 

Switchover is completed , verify the current configuration 

DGMGRL> show configuration ;

Configuration - DgStudyConfig

Protection Mode: MaxPerformance

  Databases:

    study  - Primary database

    dstudy - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

DGMGRL>

Try that with SQL commands and you spend a significant amount of time reading the documentation in order to get these steps right. Furthermore, I don’t need to bother about LOG_ARCHIVE_DEST_2 because the Broker sets it correctly – without manual intervention and without VALID_FOR. The other Role Changes are also one-liners with the Broker: