DATA GUARD BROKER CONFIGURATION

In this article we are going to learn how to set up Data Guard broker for managing data guard.

Primary and standby database server details:

Primary Server: ora1-1.mydomain Database: TESTDB
Standby Server: ora1-2.mydomain Database: STANDBY
Primary Database Details:

 

[oracle@NVMBD1BZY150D00 ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 12 16:06:52 2015

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;

 

STATUS       INSTANCE_NAME    DATABASE_ROLE    PROTECTION_MODE

------------ ---------------- ---------------- --------------------

OPEN         TESTDB           PRIMARY          MAXIMUM PERFORMANCE

 

SQL> select max(sequence#) from v$archived_log;

 

MAX(SEQUENCE#)

--------------

           803

 

Standby Database Details:

SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;

 

STATUS       INSTANCE_NAME    DATABASE_ROLE    PROTECTION_MODE

------------ ---------------- ---------------- --------------------

MOUNTED      STANDBY          PHYSICAL STANDBY MAXIMUM PERFORMANCE

 

SQL> select max(sequence#) from v$archived_log;

 

MAX(SEQUENCE#)

--------------

           803

 

On both primary and standby databases, enable the dataguard broker process by setting the value of the parmater “DG_BROKER_START” to TRUE.

SQL> alter system set dg_broker_start=true;

 

System altered.

 

Add a static entry for the DGMGRL in the listener.ora file on both the primary and standby servers.

On Primary:

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = TESTDB_DGMGRL)

      (ORACLE_HOME = /data/oracle/app/oracle/product/11.2.0/dbhome_1)

      (SID_NAME = TESTDB)

    )

On Standby:

   (SID_DESC =

      (GLOBAL_DBNAME = STANDBY_DGMGRL)

      (ORACLE_HOME = /data/oracle/app/oracle/product/11.2.0/dbhome_1)

      (SID_NAME = STANDBY)

    )

Listener.ora file contents on primary server:

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

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

    )

    (DESCRIPTION =

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

    )

  )

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = TESTDB_DGMGRL )

      (ORACLE_HOME = /data/oracle/app/oracle/product/11.2.0/dbhome_1)

      (SID_NAME = TESTDB)

    )

  )

Listener.ora file contents on standby server:

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

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

    )

    (DESCRIPTION =

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

    )

  )

 

 

SID_LIST_LISTENER =

   (SID_DESC =

      (GLOBAL_DBNAME = STANDBY_DGMGRL )

      (ORACLE_HOME = /data/oracle/app/oracle/product/11.2.0/dbhome_1)

      (SID_NAME = STANDBY)

    )

 

  )

On the primary server, create the Dataguard Broker configuration.

 

[oracle@NVMBD1BZY150D00 ~]$ dgmgrl

 

DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

 

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

 

Welcome to DGMGRL, type "help" for information.

 

DGMGRL> connect sys/sys@TESTDB

 

Connected.

 

DGMGRL> create configuration 'dgtest' as primary database is 'TESTDB' connect identifier is TESTDB;

 

Configuration "dgtest" created with primary database "TESTDB"

 

DGMGRL> show configuration;

 

Configuration - dgtest

 

  Protection Mode: MaxPerformance

  Databases:

    TESTDB - Primary database

 

Fast-Start Failover: DISABLED

 

Configuration Status:

DISABLED

 

Add the standby database “SRPSTB” to the configuration created above.

DGMGRL> add database 'STANDBY' as connect identifier is STANDBY maintained as physical;

 

Database "STANDBY" added

 

DGMGRL> show configuration;

 

Configuration - dgtest

 

  Protection Mode: MaxPerformance

  Databases:

    TESTDB  - Primary database

    STANDBY - Physical standby database

 

Fast-Start Failover: DISABLED

 

Configuration Status:

DISABLED

 

The configuration added, needs to be enabled.

 

DGMGRL> enable configuration;

 

Enabled.

 

DGMGRL> show configuration

 

Configuration - dgtest

 

  Protection Mode: MaxPerformance

  Databases:

    TESTDB  - Primary database

    STANDBY - Physical standby database

 

Fast-Start Failover: DISABLED

 

Configuration Status:

SUCCESS

 

 

Setup is complete, you are ready to use DG Broker.

 

Few Monitorable properties to troubleshoot

DGMGRL> SHOW DATABASE 'TESTPRI' 'StatusReport';
DGMGRL> SHOW DATABASE 'TESTPRI' 'LogXptStatus';
DGMGRL> SHOW DATABASE 'TESTPRI' 'InconsistentProperties';
DGMGRL> SHOW DATABASE 'TESTPRI' 'InconsistentLogXptProps';
DGMGRL> SHOW DATABASE 'TESTDG' 'StatusReport';
DGMGRL> SHOW DATABASE 'TESTDG' 'LogXptStatus';
DGMGRL> SHOW DATABASE 'TESTDG' 'InconsistentProperties';
DGMGRL> SHOW DATABASE 'TESTDG' 'InconsistentLogXptProps';

Equivalent Broker Commands to 'ALTER SYSTEM'

SQL> alter database recover managed standby database cancel;
DGMGRL> edit database 'stby_dbname' set state='LOG-APPLY-OFF';

SQL> alter database recover managed standby database disconnect;
DGMGRL> edit database 'stby_dbname' set state='ONLINE';

SQL> alter system set log_archive_max_processes=4;
DGMGRL> edit database 'dbname' set property 'LogArchiveMaxProcesses'=4;

SQL> alter system set log_archive_dest_state_2='enable' scope=both;
DGMGRL> edit database 'stby_dbname' set property 'LogShipping'='ON';

SQL> alter system set log_archive_dest_state_2='defer' scope=both;
DGMGRL> edit database 'stby_dbname' set property 'LogShipping'='OFF';

DGMGRL> edit database 'pri_dbname' set state='LOG-TRANSPORT-OFF';
This will defer all standby databases

 

 

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!