Configure dataguard broker in Oracle 11g

Data Guard Broker /DGMGRL Configuration quick steps:

This note describes the commands used to create a Data Guard broker configuration using the command line dgmgrl interface. This can also be done via the Enterprise Manager Grid Control GUI as well and the assumption is that there is a Physical Standby database in place and redo transport and redo apply have already been configured and that both the Primary and Standby database are in sync.

Assumptions:
1) Primary Database:  PRIMARY and Standby Database: STANDBY
2) Both are configured with Oracle 11g Active dataguard
3) Configured protection mode as 'High performance'.

Steps:
1) Stop the MRP
2) Modify the Listener.ora files
2) Modify the init Parameters
4) Create Configuration
5) Enable Configuration
6) Verify the Configuration

1) Stop MRP ( on STANDBY database)

SQL> alter database recover managed standby database cancel;

2) Modify the Listener.ora files

Update the listener.ora on all the nodes in Dataguard configuration to register a service with the local listener for each instance for the proper functioning of DGMGRL. Add the below lines to the listener.ora file for each of the specified instances. Ensure that the GLOBAL_DBNAME is set to db_unique_name_DGMGRL.db_domain

Primary :

SID_LIST_LISTENER =
  (SID_LIST =
  (SID_DESC =
        (GLOBAL_DBNAME = PRIMARY_DGMRGL.EH-UAT-DB)
        (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
        (SID_NAME = PRIMARY)
        )
)

Standby :

SID_LIST_LISTENER =
  (SID_LIST =
  (SID_DESC =
        (GLOBAL_DBNAME = STANDBY_DGMRGL.eh-uatdb1)
        (ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1)
        (SID_NAME = STANDBY)
        )
)

Note: Add the above along with exiting setup of listener.

3) Modify the init Parameters:

DB_BROKER_CONFIG_FILEn parameter is used to specify the location of the dataguard configuration.    DG_BROKER_START parameter is used to start the broker automatically when the instance starts.

Primary:
$ sqlplus / as sysdba
SQL>
alter system set dg_broker_config_file1='/u01/app/oracle/product/11.2.0/db_1/dbs/dgb1PRIMARY.ora' sid='*';
alter system set dg_broker_config_file2='/u01/app/oracle/product/11.2.0/db_1/dbs/dgb2PRIMARY.ora' sid='*';
alter system set dg_broker_start=TRUE  sid='*';

Standby:
$ sqlplus / as sysdba
SQL>
alter system set dg_broker_config_file1='/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/dgb1STANDBY.ora' sid='*';
alter system set dg_broker_config_file2='/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/dgb2STANDBY.ora' sid='*';
alter system set dg_broker_start=TRUE  sid='*';

Note : This is to prevent the ORA-12514 error which can be observed on startup of the standby database after performing a switchover.

4) Create Configuration:

Connect to the DGMGRL and run the below statements to create and enable the DataGuard configuration. Verify that the DG Broker is configured correctly and it has all the databases and instances registered as expected.

PRIMARY:

– create configuration:
$ dgmgrl
DGMRGL> connect sys/system;

DGMGRL> CREATE CONFIGURATION 'DR_Config' AS PRIMARY DATABASE IS 'PRIMARY' CONNECT IDENTIFIER IS PRIMARY;
Configuration "DR_Config" created with primary database "PRIMARY"

DGMGRL> 

DGMGRL> show configuration;

Configuration - DR_Config
  Protection Mode: MaxPerformance
  Databases:
    PRIMARY - Primary database
Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> 

– Add standby database 

DGMGRL> ADD DATABASE 'STANDBY' AS CONNECT IDENTIFIER IS STANDBY MAINTAINED AS PHYSICAL;
Database "STANDBY" added

DGMGRL> 
DGMGRL> show configuration;

Configuration - DR_Config

  Protection Mode: MaxPerformance
  Databases:
    PRIMARY - Primary database
    STANDBY - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> 

5) Enable Configuration:
The "DR_config" setup is disabled, which means it is not under the control of the Data Guard broker. When you finish configuring the databases into a broker configuration and setting any necessary database properties, you must enable the configuration to allow the Data Guard broker to manage it.

You can enable:
The entire configuration, including all of its databases
A standby database

DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;

Configuration - DR_Config
  Protection Mode: MaxPerformance
  Databases:
    PRIMARY - Primary database
    STANDBY - Physical standby database
Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> 

6) Verify Configuration:

show configuration;
show database verbose 'PRIMARY';
show database verbose 'STANDBY';


Verification in STANDBY side:

$ dgmgrl
DGMGRL> show configuration;
not logged on
DGMGRL> 
DGMGRL> connect sys/system@STANDBY;
Connected.
DGMGRL> show configuration;

Configuration - DR_Config
  Protection Mode: MaxPerformance
  Databases:
    PRIMARY - Primary database
    STANDBY - Physical standby database
Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> 
DGMGRL> show database 'PRIMARY';

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

DGMGRL> show database 'STANDBY';

Database - STANDBY

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: ON
  Instance(s):
    STANDBY

Database Status:
SUCCESS

Enable the database:

This step is unnecessary except if the standby database was previously disabled with the DISABLE DATABASE command. Normally, enabling the configuration also enables the standby database.

DGMGRL> ENABLE DATABASE 'STANDBY';
Enabled.
DGMGRL> show database 'STANDBY';

Database - STANDBY
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: ON
  Instance(s):
    STANDBY
Database Status:
SUCCESS

Alter the State of a Standby Database :

a) See the STANDBY database:

DGMGRL> show database 'STANDBY';

Database - STANDBY
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: ON
  Instance(s):
    STANDBY
Database Status:
SUCCESS

b) Edit the STANDBY database as redo-log off mode:

DGMGRL> EDIT DATABASE 'STANDBY' SET STATE='APPLY-OFF';
Succeeded.
DGMGRL> show database 'STANDBY';

Database - STANDBY
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-OFF
  Transport Lag:   0 seconds
  Apply Lag:       8 seconds
  Real Time Query: OFF
  Instance(s):
    STANDBY
Database Status:
SUCCESS

c) Again edit the database in redo-apply mode:

DGMGRL> EDIT DATABASE 'STANDBY' SET STATE='APPLY-ON';
Succeeded.
DGMGRL> show database 'STANDBY';

Database - STANDBY
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: ON
  Instance(s):
    STANDBY
Database Status:
SUCCESS

DGMGRL> 

Alter the State of a Primary Database :

You might want to stop the transmittal of redo data to the standby database. To change the state of the primary database to accommodate this, use the following command:

a) View the state of the primary database:

DGMGRL> show database 'PRIMARY';

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

b) change the state of the primary database as TRANSPORT-OFF:

DGMGRL> edit database 'PRIMARY' set STATE='TRANSPORT-OFF';
Succeeded.
DGMGRL> show database 'PRIMARY';

Database - PRIMARY
  Role:            PRIMARY
  Intended State:  TRANSPORT-OFF
  Instance(s):
    PRIMARY
Database Status:
SUCCESS

c) change the state of the primary database back to TRANSPORT-ON:

DGMGRL> edit database 'PRIMARY' set STATE='TRANSPORT-ON';
Succeeded.
DGMGRL> show database 'PRIMARY';

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

Hope this document help more. In case of any issues or doubts please post a comment.