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:

Data guard broker - its concept and configuration

In this topic I will discuss about data guard broker - Before explaining about broker concepts and its configuration , you need to have good idea about oracle data guard and its functionality . 

So  an Oracle Data Guard configuration consists of one primary database and a combination of standby databases and far sync instances (12 feature ) that receive redo directly from the primary database. The databases in an Oracle Data Guard configuration are connected by Oracle Net and may be dispersed geographically. There are no restrictions on where the databases are located as long as they can communicate with each other. For example, you can have a standby database on the same system as the primary database, along with two standby databases on another system. In this demo I configured my data guard set up in my local computer -

The Oracle Data Guard broker logically groups these primary and standby databases into a broker configuration that allows the broker to manage and monitor them together as an integrated unit. You can manage a broker configuration using either Oracle Enterprise Manager Cloud Control (Cloud Control) or the Oracle Data Guard command-line interface.

The advantage of using data guard broker over  SQL*Plus to manage data guard you can read here 

https://docs.oracle.com/database/121/DGBKR/concepts.htm#DGBKR3678 

Data Guard Broker is part of the normal Oracle Database Enterprise Edition installation and an integral part of Data Guard. You can do Data Guard related configuration, set transport methods, apply set-up and role change services and also change overall protection mode. The Broker consists of three major parts 

A) BACKGROUND PROCESSES ON EACH NODE

B) BROKER CONFIGURATION FILES

C) COMMAND LINE INTERFACE KNOWN AS DGMGRL.

---

A) BACKGROUND PROCESSES ON EACH NODE

All of the processes run on every node of Data Guard setup.

i) Data Guard Monitor (DMON)

Coordinates all Broker actions as well as maintains the Broker configuration files .Enabled or disabled with the DG_BROKER_START parameter.

ii) Broker Resource Manager (RSM)

RSM process comes in play whenever broker need to run any SQL command in the database. SQL may be required to be run during Data Guard setup or because of result of a change to the configuration made through DGMGRL

iii) Data Guard Net Server (NSVn)

NSV processes are responsible for making connection with the remote database and to send across any work items to the remote database.So, DMON or RSM process may ask NSV process to connect to other node for any work request. DMON may need to send some communication to the other node and RSM may have to get some data through SQL from other node.

 

iv) DRCn

NSV processes contact DRC process running on other node to establish the connection, so DRC process acts like a receiver on other node. Each NSV process will have a partner DRC process on the target database, which will perform the actual work on behalf of the source database NSV process and return the results or status.

v) Internode servers (INSVs)

If Data Guard setup involves Real Application Clusters (RAC), INSV process come into play. It maintain a connection between the RAC database nodes in the cluster to ensure that the Broker on each node knows the state of the cluster.

WHAT HAPPENS AT DATABASE STARTUP:

Step 1) Primary database startup.

Step 2) DMON process at primary ask local NSV process to connect to each standby database.

Step 3) NSV process in turn will contact DRC process on standby and get confirmation.

Step 4) DMON process at primary will then ask local RSM to send the setup commands to the standby database.

Step 5) RSM will send the setup commands to NSV.

Step 6) NSV process in turn will contact DRC process on standby to run the setup commands.

B) BROKER CONFIGURATION FILES

Broker keeps its configuration details in flat file. The files are stored at each database nodes in the Data Guard configuration. Additionally two copies of the configuration files are always stored on each database for redundancy. Below parameters control where the configuration files will be stored.

DG_BROKER_CONFIG_FILE1
DG_BROKER_CONFIG_FILE2

By default they are stored in $ORACLE_HOME/dbs directory with the filename of dr1<DB_UNIQUE_NAME>.dat and dr2<DB_UNIQUE_NAME>.dat, 

For RAC instances, you need to keep the files in shared location where all database instances can access it. We will set following Initialization parameters in Primary and standby database before enabling data guard broker in RAC environment.  

At primary ,

alter system set dg_broker_config_file1='+DATA/PRIM/DGBROKER/dr1tintin.dat' scope=both sid='*';

alter system set dg_broker_config_file2='+FLASH/PRIM/DGBROKER/dr2tintin.dat' scope=both sid='*'

At standby,

alter system set dg_broker_config_file1='+DATA/STBY/DGBROKER/dr1tintin.dat' scope=both sid='*';

alter system set dg_broker_config_file2='+FLASH/STBY/DGBROKER/dr2tintin.dat' scope=both sid='*'

Broker keeps in sync all of these configuration files but DMON process running on the primary database is the owner of the master copy of the files.This means that if you started the standby database while Data guard process are down at primary node, then no data guard related activities will be done until the standby database can connect to the primary database. Primary purpose of keeping configuration files on each node is when your primary node is gone and you need to failover, Data Guard processes can determine the original settings for the entire configuration from the configuration files on other surviving node.

C) COMMAND LINE INTERFACE (CLI) KNOWN AS DGMGRL

The third important part of Broker setup is the interface through which users will interact with Broker. The Broker CLI DGMGRL is included in the Oracle Database Enterprise Edition

To access DGMGRL, type dgmgrl at the command prompt. Use the CONNECT command after you have started DGMGRL to connect to databases.

Step-By-Step Configuration Of Data Guard Broker in Oracle 11g

As  we  have already  discuss  about  the Data Guard Broker  and its components . Here we will configure the data Guard Broker . I configured my data guard set up in my personal computer , so here is the steps to configure data guard broker

Primary Databse   =  dstudy
Standby Database =  study

Step 1 :  Check the Data Guard Broker process 
SQL> sho parameter dg_broker
NAME                                   TYPE             VALUE
-----------------                    ----------          ----------
dg_broker_start                boolean          FALSE

Step 2  : Start the Data Guard Broker Process on Primary database   
SQL>alter system set dg_broker_start=true scope=both;
System altered.

Step 3 : Check DG_BROKER on standby database and start it 
SQL> sho parameter dg_broker
NAME                                    TYPE             VALUE
-----------------                      ----------         ----------
dg_broker_start                  boolean         FALSE

SQL>alter system set dg_broker_start=true scope=both ;
System altered.

Note:-  

This will start Data Guard monitor process(DMON) - 
if your database is  in RAC take care DG_BROKER_CONFIG_FILE1 , DG_BROKER_CONFIG_FILE2 parameter before setting dg_broker_start=true

Step 4 :   Edit the listener.ora file

Edit the listener.ora file which includes the db_unique_name_DGMGRL.db_domain values for the GLOBAL_DBNAME in both primary and standby database . To set the value, lets check the db_domain value .

SQL> show parameter db_domain
NAME                              TYPE               VALUE
--------------                  -----------         --------------
db_domain                      string

Since the value of db_domain  is null so the the value of  GLOBAL_DBNAME = DSTUDY_DGMGRL for primary database and for standby  GLOBAL_DBNAME = STUDY_DGMGRL. The primary listener.ora file  is as 

LISTENER_NEW =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = Mypc)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

)

ADR_BASE_LISTENER_NEW = C:\app\mahekarthya

SID_LIST_LISTENER_NEW =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = CLRExtProc)

      (ORACLE_HOME = C:\app\mahekarthya\product\11.2.0\dbhome_1)

      (PROGRAM = extproc)

      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\mahekarthya\product\11.2.0\dbhome_1\bin\oraclr11.dll")

    )

    

    (SID_DESC =

       (GLOBAL_DBNAME = DSTUDY_DGMGRL)

      (SID_NAME = DSTUDY)

      (ORACLE_HOME = C:\app\mahekarthya\product\11.2.0\dbhome_1)

      )

      (SID_DESC =

       (GLOBAL_DBNAME = STUDY_DGMGRL)

      (SID_NAME = STUDY)

      (ORACLE_HOME = C:\app\mahekarthya\product\11.2.0\dbhome_1)

      )

  )

Here I have configured both my primary and standby databases  in the same machine ,so I added these entries in the same listener.log file.

Step 5 : Configure the Data Guard Configuration 
C:\> dgmgrl
DGMGRL for 32-bit Windows: Version 11.2.0.1.0 - Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.

DGMGRL> Now connect either to primary or to standby database , Here I connected to my primary database.
DGMGRL> connect sys/xxxx@dstudy 
Connected.

DGMGRL> show configuration

ORA-16532: Data Guard broker configuration does not exist

Since this is new setup, there is no configuration currently in database. Let's add 'dstudy' database to the configuration.

Now we have to add  data guard configuration, the syntax is

CREATE CONFIGURATION configuration-name AS

PRIMARY DATABASE IS database-name

CONNECT IDENTIFIER IS connect-identifier;

Where

database-name:-

The name that will be used by the broker to refer to the primary database. It must match (case-insensitive) the value of the corresponding database DB_UNIQUE_NAME initialization parameter.

connect-identifier:-

A fully specified connect descriptor or a name to be resolved by an Oracle Net Services naming method (for example, TNS). The value you specify is also used as the initial value of the DGConnectIdentifier configurable database property.

DGMGRL> create configuration 'DgStudyConfig'
> as primary database is 'dstudy'
> connect identifier is dstudy ;

Configuration "DgStudyConfig" created with primary database "dstudy"

Once the configuration is created then check the status of configuration .

DGMGRL> show configuration
Configuration            - dgdstudy
Protection Mode       : MaxPerformance
Databases                : dstudy - Primary database
Fast-Start Failover    : DISABLED
Configuration Status : DISABLED

Step  6 :  Add standby database to the data broker configuration 

Creates a new standby database profile and adds it to the existing broker configuration. The AS CONNECT IDENTIFIER clause is optional. If you do not specify this clause, the broker will search the LOG_ARCHIVE_DEST_n initialization parameters on the primary database for an entry that corresponds to the database being added.

Format

ADD DATABASE database-name

[AS CONNECT IDENTIFIER IS connect-identifier]

Where

database-name:-

The name that will be used by the broker to refer to this standby database. It must match (case-insensitive) the value of the corresponding database DB_UNIQUE_NAME initialization parameter.

connect-identifier:-

A fully specified connect descriptor or a name to be resolved by an Oracle Net Services naming method (for example, TNS). The value you specify is also used as the initial value of the DGConnectIdentifier configurable database property. If you do not specify this option, the broker will search the primary database LOG_ARCHIVE_DEST_n parameters for a corresponding entry to the standby database and use its SERVICE value for the connect-identifier.

DGMGRL>  add database 'study' as
> connect identifier is study
> maintained as physical ;
Database "study" added

DGMGRL> show configuration
Configuration             -  dgdstudy
Protection Mode         :  MaxPerformance
Databases                   :  dstudy - Primary database
                                    : study - Physical standby database
Fast-Start Failover      :  DISABLED
Configuration Status    :  DISABLED

Now we see that show configuration is showing that both databases have been added to configuration. Configuration Status is still set to Disabled. You can use below command to enable the configuration (meaning DG broker will start managing dataguard now)

Step 7 : Enable the configuration
DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration

Configuration - DgStudyConfig

Protection Mode: MaxPerformance

  Databases:

    dstudy - Primary database

    study  - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

ORA-16610: command "Broker automatic health check" in progress

DGM-17017: unable to determine configuration status

DGMGRL> show configuration

Configuration - DgStudyConfig

Protection Mode: MaxPerformance

  Databases:

    dstudy - Primary database

    study  - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

DGMGRL>

Step 8 : View the Primary and Standby database properties 

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         = ''

    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         = ''

    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>

Our data guard broker set up is ready now - we will do a switch over in the next topic using data guard broker.