Uncategorized

Data guard Broker setup

Overview:
In this article we explain the process of  Data guard Broker setup

DG Broker maintains, manage, and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions

Environment Details
Hostname
Primary server :–Primary
Standby Server :– Standby
Operating system
Linux
Database Environment
NSMPRD and NSMDR


Ensure that both the primary and standby databases are up and running as shown in the following query

NSMPRD@PRIMARY

SQL> select db_unique_name,open_mode,database_role from v$database;

DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE
—————————— ——————– —————-
nsmprd                        READ WRITE           PRIMARY


NSMDR@STANDBY

SQL> select db_unique_name,open_mode,database_role from v$database;

DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE
—————————— ——————– —————-
nsmdr                        READ WRITE           PRIMARY

Ensure that both the primary and standby databases are using server parameter files, so that the broker can form a healthy relationship between the broker properties and parameter values as follows

NSMPRD@PRIMARY

SQL> show parameter spfile

NAME                                 TYPE                             VALUE
———————————— ——————————-
spfile                               string                           /u01/app/oracle/product/11.2.0
                                  .4/db/dbs/spfilensmprd.ora
NSMDR@STANDBY

SQL> show parameter spfile

NAME                                 TYPE                             VALUE
———————————— ——————————-
spfile                               string                                       /u01/app/oracle/product/11.2.0.4/db/dbs/spfilensmdr.ora

Set configuration file parameter locations on both primary and standby

NSMPRD@PRIMARY

SQL> ALTER SYSTEM SET dg_broker_config_file1 = ‘/u01/oraadmin1/nsmprd/dbs/dr1nsmprd.dat’ scope=both;

System altered.

SQL> ALTER SYSTEM SET dg_broker_config_file2 = ‘/u01/oraadmin1/nsmprd/dbs/dr2nsmprd.dat’ scope=both;

System altered.

NSMDR@STANDBY

SQL> ALTER SYSTEM SET dg_broker_config_file1 = ‘/u01/oraadmin1/nsmdr/dbs/dr1nsmdr.dat’ scope=both;

System altered.

SQL> ALTER SYSTEM SET dg_broker_config_file2 = ‘/u01/oraadmin1/nsmdr/dbs/dr2nsmdr.dat’ scope=both;

System altered.

Start the DMON process on both the primary and standby databases by setting the DG_BROKER_START parameter as follows

NSMPRD@PRIMARY

SQL> alter system set dg_broker_start=TRUE scope=both;

System altered.

NSMDR@STANDBY

SQL> alter system set dg_broker_start=TRUE scope=both;

System altered.

The configuration files will be created under the specified location or in the default directory automatically. The Data Guard broker will maintain two copies of its configuration files as follows: Primary and standby

NSMPRD@PRIMARY

SQL> show parameter DG_BROKER_CONFIG_FILE
NAME                                 TYPE                             VALUE
———————————— ——————————-
dg_broker_config_file1               string                           
                           /u01/oraadmin1/nsmprd/dbs/dr1 nsmprd.dat
dg_broker_config_file2           string                   /u01/oraadmin1/nsmprd/dbs/dr2nsmprd.dat

NSMDR@STANDBY

SQL> show parameter DG_BROKER_CONFIG_FILE
NAME                                 TYPE                             VALUE
———————————— ——————————-
dg_broker_config_file1               string                                                    /u01/oraadmin1/nsmdr/dbs/dr1nsmdr.dat
dg_broker_config_file2               string                                                    /u01/oraadmin1/nsmdr/dbs/dr2nsmdr.dat

NSMPRD@PRIMARY

Connect DGMGRL on the primary system and create the configuration as follows

Note: Make sure all the required directories are created before proceeding with enable configuration

$dgmgrl  /
DGMGRL for Linux: Version 11.2.0.4.0 – 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type “help” for information.
Connected.
DGMGRL>

DGMGRL> CREATE CONFIGURATION ‘nsmprd’ AS PRIMARY DATABASE IS ‘nsmprd’ CONNECT IDENTIFIER IS nsmprd;
Configuration “nsmprd created with primary database “nsmprd”

DGMGRL>  ADD DATABASE ‘nsmdr’ AS CONNECT IDENTIFIER IS ‘nsmdr’ maintained as physical;
Dataase “nsmdr” added

Enable DG broker config

$dgmgrl  /
DGMGRL for Linux: Version 11.2.0.4.0 – 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type “help” for information.
Connected.
DGMGRL>
DGMGRL> show configuration

Configuration – nsmprd

  Protection Mode: MaxPerformance
  Databases:
    nsmprd – Primary database
    nsmdr  – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration

Configuration – nsmprd

  Protection Mode: MaxPerformance
  Databases:
    nsmprd – Primary database
    nsmdr  – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Update Listener entries on both primary and standby for DG broker

On Standby :

LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))            # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
ADR_BASE_LISTENER=/u01/oraadmin1

SID_LIST_LISTENER =
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=nsmdr_DGMGRL)
      (ORACLE_HOME= /u01/app/oracle/product/11.2.0.4/db)
      (SID_NAME=nsmdr))
   )

On Primary:

LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))            # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
ADR_BASE_LISTENER=/u01/oraadmin1

SID_LIST_LISTENER =
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=nsmprd_DGMGRL)
      (ORACLE_HOME= /u01/app/oracle/product/11.2.0.4/db)
      (SID_NAME=nsmprd))
   )

$dgmgrl /
DGMGRL for Linux: Version 11.2.0.4.0 – 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type “help” for information.
Connected.
DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration

Configuration – nsmprd

  Protection Mode: MaxPerformance
  Databases:
    nsmprd – Primary database
    nsmdr  – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>

 Conclusion
In this article we have learnt the steps to the data guard broker.