DataGuard Broker configuration

Steps to configure Oracle 11gR2 DataGuard Broker

Primary database: IMPHAL
Secondary database : IMPHALDG

On both the primary and secondary database start the DG_BROKER process

SQL > alter system set DG_BROKER_START=TRUE SCOPE=TRUE


Edit the listener.ora on both the nodes


Create the configuration

• On the primary node,connect to dgmgrl

            DGMGRL> connect sys/Password@IMPHAL
            Connected

            Here Primary is the SID of the primary/production database

• Check the configuration.Since the DG broker has not been configured, it will give the following error

          DGMGRL> show configuration

          ORA-16532: Data Guard broker configuration does not exist

          Configuration details cannot be determined by DGMGRL

• Create the DG Broker configuration

             Primary Database

             DGMGRL> create configuration 'IMPHAL'

              as

              IMPHAL database is 'IMPHAL'

              connect identifier is 'IMPHAL'

              ;

            Configuration "IMPHAL" created with IMPHAL database "IMPHAL"

            Add the secondary database

           DGMGRL> add database 'IMPHALDG'
           as
           connect identifier is 'IMPHALDG';

           Database "IMPHALDG" added

• Show the DG Broker configuration

          DGMGRL> show configuration

          Configuration - IMPHAL

          Protection Mode: MaxPerformance

          Databases:

         IMPHAL   - IMPHAL database

        IMPHALDG - Physical standby database

         Fast-Start Failover: DISABLED

        Configuration Status:

         DISABLED

• Enable the DG broker and check the configuration configuration

      DGMGRL> enable configuration;

      Enabled.

       DGMGRL> show configuration;

      Configuration - IMPHAL

      Protection Mode: MaxPerformance

      Databases:

      IMPHAL   - IMPHAL database

     IMPHALDG - Physical standby database

     Fast-Start Failover: DISABLED

    Configuration Status:

    SUCCESS

• Check the database status

    DGMGRL>  show database verbose 'IMPHAL';

Database - IMPHAL

  Role:            IMPHAL

  Intended State:  TRANSPORT-ON

  Instance(s):

    IMPHAL

  Properties:

    DGConnectIdentifier             = 'IMPHAL'

    ObserverConnectIdentifier       = ''

    LogXptMode                      = 'ASYNC'

    DelayMins                       = '0'

    Binding                         = 'optional'

    MaxFailure                      = '0'

    MaxConnections                  = '1'

    ReopenSecs                      = '300'

    NetTimeout                      = '30'

    RedoCompression                 = 'DISABLE'

    LogShipping                     = 'ON'

    PreferredApplyInstance          = ''

    ApplyInstanceTimeout            = '0'

    ApplyParallel                   = 'AUTO'

    StandbyFileManagement           = 'AUTO'

    ArchiveLagTarget                = '0'

    LogArchiveMaxProcesses          = '4'

    LogArchiveMinSucceedDest        = '1'

    DbFileNameConvert               = '+DATA_DG1, +DATA_DG2, +RECO_DG1, +RECO_DG2'

    LogFileNameConvert              = '+DATA_DG1, +DATA_DG2, +RECO_DG2, +RECO_DG2'

    FastStartFailoverTarget         = ''

    InconsistentProperties          = '(monitor)'

    InconsistentLogXptProps         = '(monitor)'

    SendQEntries                    = '(monitor)'

    LogXptStatus                    = '(monitor)'

    RecvQEntries                    = '(monitor)'

    SidName                         = 'IMPHAL'

    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=IMPHAL_DGMGRL)(INSTANCE_NAME=IMPHAL)(SERVER=DEDICATED)))'

    StandbyArchiveLocation          = '+RECO_DG1'

    AlternateLocation               = ''

    LogArchiveTrace                 = '0'

    LogArchiveFormat                = 'IMPHAL_%t_%s_%r.arch'

    TopWaitEvents                   = '(monitor)'

Database Status:

SUCCESS

DGMGRL> show database verbose 'IMPHALDG';

Database - IMPHALDG

  Role:            PHYSICAL STANDBY

  Intended State:  APPLY-ON

  Transport Lag:   1 minute 48 seconds

  Apply Lag:       3 minutes 41 seconds

  Real Time Query: OFF

  Instance(s):

    IMPHAL

  Properties:

    DGConnectIdentifier             = 'IMPHALDG'

    ObserverConnectIdentifier       = ''

    LogXptMode                      = 'ASYNC'

    DelayMins                       = '0'

    Binding                         = 'OPTIONAL'

    MaxFailure                      = '0'

    MaxConnections                  = '1'

    ReopenSecs                      = '300'

    NetTimeout                      = '30'

    RedoCompression                 = 'DISABLE'

    LogShipping                     = 'ON'

    PreferredApplyInstance          = ''

    ApplyInstanceTimeout            = '0'

    ApplyParallel                   = 'AUTO'

    StandbyFileManagement           = 'auto'

    ArchiveLagTarget                = '0'

    LogArchiveMaxProcesses          = '4'

    LogArchiveMinSucceedDest        = '1'

    DbFileNameConvert               = '+DATA_DG2, +DATA_DG1, +RECO_DG2, +RECO_DG1'

    LogFileNameConvert              = ''

    FastStartFailoverTarget         = ''

    InconsistentProperties          = '(monitor)'

    InconsistentLogXptProps         = '(monitor)'

    SendQEntries                    = '(monitor)'

    LogXptStatus                    = '(monitor)'

    RecvQEntries                    = '(monitor)'

    SidName                         = 'IMPHAL'

    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=IMPHALDG_DGMGRL)(INSTANCE_NAME=IMPHAL)(SERVER=DEDICATED)))'

    StandbyArchiveLocation          = '+RECO_DG2'

    AlternateLocation               = ''

    LogArchiveTrace                 = '0'

    LogArchiveFormat                = 'IMPHAL_%t_%s_%r.arch'

    TopWaitEvents                   = '(monitor)'

Database Status:

SUCCESS

• Performing a switch-over

Performing a switchover

Note: In this case, currently the Primary Database is IMPHAL and the Standby database is IMMPHALDG.

DGMGRL> switchover to 'IMPHALDG'

Performing switchover NOW, please wait...

New primary database "IMPHALDG" is opening...

Operation requires shutdown of instance "IMPHAL" on database "IMPHAL"

Shutting down instance "IMPHAL"...

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance "IMPHAL" on database "IMPHAL"

Starting instance "IMPHAL"...

ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is "IMPHALDG"