Saturday, May 8, 2021

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>


1 comment: