Setup Data Guard Broker Configuration in Oracle Database

How to setup Data Guard Broker Configuration

=================================================

Primary database : india

Standby database : india_stby

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

Standby listener.ora

================================

oracle@ip-10-0-1-37 admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

      (SID_LIST =

        (SID_DESC =

          (GLOBAL_DBNAME = india_stby)

          (SID_NAME = india_stby)

          (ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1)

       )

        (SID_DESC =

          (GLOBAL_DBNAME = india_dgmgrl)

          (SID_NAME = india_stby)

          (ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1)

               )

        (SID_DESC =

          (GLOBAL_DBNAME = india_stby_dgmgrl)

          (SID_NAME = india_stby)

          (ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1)

               )

          )

[oracle@ip-10-0-1-37 admin]$

Primary  listener.ora

==============================

SID_LIST_LISTENER =

      (SID_LIST =

        (SID_DESC =

          (GLOBAL_DBNAME = india)

          (SID_NAME = india)

          (ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1)

       )

        (SID_DESC =

          (GLOBAL_DBNAME = india_dgmgrl)

          (SID_NAME = india_stby)

          (ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1)

               )

        (SID_DESC =

          (GLOBAL_DBNAME = india_stby_dgmgrl)

          (SID_NAME = india_stby)

          (ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1)

               )

          )

 

ALTER SYSTEM SET dg_broker_start=true scope=both;

SQL>

SQL> ALTER SYSTEM SET dg_broker_start=true scope=both;

System altered.

SQL>

SQL>

SQL> show parameter broker

NAME                                 TYPE        VALUE

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

connection_brokers                   string      ((TYPE=DEDICATED)(BROKERS=1)),

                                                  ((TYPE=EMON)(BROKERS=1))

dg_broker_config_file1               string      /u01/app/oracle/product/12.2.0

                                                 /dbhome_1/dbs/dr1india_stby.da

                                                 t

dg_broker_config_file2               string      /u01/app/oracle/product/12.2.0

                                                 /dbhome_1/dbs/dr2india_stby.da

                                                 t

dg_broker_start                      boolean     TRUE

use_dedicated_broker                 boolean     FALSE

SQL>

SQL>

SQL>

SQL>

on stanndby 

==============

SQL> /

    Thread Last Sequence Received Last Sequence Applied Difference

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

         1                     29                    29          0

SQL>

SQL>

SQL> ALTER SYSTEM SET dg_broker_start=true scope=both;

System altered.

SQL>

SQL>

SQL> show parameter broker

NAME                                 TYPE        VALUE

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

connection_brokers                   string      ((TYPE=DEDICATED)(BROKERS=1)),

                                                  ((TYPE=EMON)(BROKERS=1))

dg_broker_config_file1               string      /u01/app/oracle/product/12.2.0

                                                 /dbhome_1/dbs/dr1india_stby.da

                                                 t

dg_broker_config_file2               string      /u01/app/oracle/product/12.2.0

                                                 /dbhome_1/dbs/dr2india_stby.da

                                                 t

dg_broker_start                      boolean     TRUE

use_dedicated_broker                 boolean     FALSE

SQL>

SQL>

SQL>

SQL>

DGMGRL>

DGMGRL>

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

Configuration "india" created with primary database "india"

DGMGRL>

DGMGRL>

DGMGRL> show configuration;

Configuration - india

  Protection Mode: MaxPerformance

  Members:

  india - Primary database

Fast-Start Failover: DISABLED

Configuration Status:

DISABLED

DGMGRL>

DGMGRL>

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

Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set

Failed.

DGMGRL>

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

Database "india_stby" added

DGMGRL>

DGMGRL>

DGMGRL> show configuration

Configuration - india

  Protection Mode: MaxPerformance

  Members:

  india      - Primary database

    india_stby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

DISABLED

DGMGRL>

DGMGRL>

DGMGRL> enable configuration;

Enabled.

DGMGRL>

DGMGRL>

DGMGRL> show configuration

Configuration - india

  Protection Mode: MaxPerformance

  Members:

  india      - Primary database

    india_stby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS   (status updated 51 seconds ago)

DGMGRL>

DGMGRL> show database india InconsistentProperties

INCONSISTENT PROPERTIES

   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE

DGMGRL>

DGMGRL>

DGMGRL> show database india_stby InconsistentProperties

INCONSISTENT PROPERTIES

   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE

DGMGRL>

DGMGRL>

DGMGRL> show database india

Database - india

  Role:               PRIMARY

  Intended State:     TRANSPORT-ON

  Instance(s):

    india

Database Status:

SUCCESS

DGMGRL> show database india_stby

Database - india_stby

  Role:               PHYSICAL STANDBY

  Intended State:     APPLY-ON

  Transport Lag:      0 seconds (computed 1 second ago)

  Apply Lag:          0 seconds (computed 1 second ago)

  Average Apply Rate: 1.00 KByte/s

  Real Time Query:    ON

  Instance(s):

    india_stby

Database Status:

SUCCESS

DGMGRL>

DGMGRL>

DGMGRL> show database india_stby statusreport

STATUS REPORT

       INSTANCE_NAME   SEVERITY ERROR_TEXT

DGMGRL>

DGMGRL>

DGMGRL> show database india statusreport

STATUS REPORT

       INSTANCE_NAME   SEVERITY ERROR_TEXT

DGMGRL>