Data Guard Broker Setup 11g

Posted: May 28, 2013 in StandBy
Tags: , ,

In this post i will setup dataguard broker services for managing dataguard setup. Follwing are prerequisites for this setup:

1) Fully functional Primary and Standby setup
2) Spfile must be in use at primary and standby database
3) DG_BROKER_START must be set TRUE at both primary and standby
4) DG_BROKER_CONFIG_FILEn parameter is optional
5) GLOBAL_DBNAME must be set in listener.ora with predefined format

My assumption in this post are Primary database name is CORE and standby database name is CORESTD. Standby setup is running fine. Among the above requirements, we have already fulfilled the requirements 1 – 4 during our init.ora parameters settings. Following is the entry for GLOBAL_DBNAME in listener.ora file of both the primary and standby databases. This is a basic requirement for broker setup and following is example from the standby database. Make necessary change at primary DB listener too, only host name will be changed in most of the cases.

SID_LIST_LISTENER_CORE =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/incore/product/11.2.0.1/dbhome_1)
      (PROGRAM = extproc)
    )
   (SID_DESC =
      (GLOBAL_DBNAME = CORE)
      (ORACLE_HOME = /u01/app/incore/product/11.2.0.1/dbhome_1)
      (SID_NAME = CORE)
    )
   (SID_DESC =
      (GLOBAL_DBNAME = CORE_DGMGRL.in.isol.net)
      (ORACLE_HOME = /u01/app/incore/product/11.2.0.1/dbhome_1)
      (SID_NAME = CORE)
    )
 )
LISTENER_CORE =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = ind-del-db02-s)(PORT = 1522))
      )
    )
  )

Add the standby redo logfiles at both primary and standby side, if they are not already added. Once these are added, we are ready for our broker setup. use “DGMGRL” prompt for broker setup.

incore@ind-del-db01-s $ dgmgrl
DGMGRL for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@core
Password:
Connected.
DGMGRL>
DGMGRL>
DGMGRL> create configuration 'DRConfig' as primary database is 'CORE' connect identifier is 'CORE';
Configuration "DRConfig" created with primary database "CORE"
DGMGRL>
DGMGRL>
DGMGRL> show configuration;

Configuration - DRConfig

  Protection Mode: MaxPerformance
  Databases:
    CORE - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> add database 'CORESTD' as connect identifier is 'CORESTD';
Database "CORESTD" added
DGMGRL> show configuration;

Configuration - DRConfig

  Protection Mode: MaxPerformance
  Databases:
    CORE    - Primary database
    CORESTD - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;

Configuration - DRConfig

  Protection Mode: MaxPerformance
  Databases:
    CORE    - Primary database
    CORESTD - Physical standby database
      Error: ORA-16664: unable to receive the result from a database

Fast-Start Failover: DISABLED

Configuration Status:
ERROR

As the configuration status is error, we need to resolve this issue before we can use broker to manage our standby. Use the following command to see the error in Standby configuration.

DGMGRL> show database verbose 'CORESTD';

Database - CORESTD

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       3 minutes 45 seconds
  Real Time Query: OFF
  Instance(s):
    CORE
      Warning: ORA-16714: the value of property ArchiveLagTarget is inconsistent with the database setting
      Warning: ORA-16714: the value of property LogArchiveMaxProcesses is inconsistent with the database setting
      Warning: ORA-16714: the value of property LogArchiveMinSucceedDest is inconsistent with the database setting
      Warning: ORA-16714: the value of property LogArchiveTrace is inconsistent with the database setting
      Warning: ORA-16714: the value of property LogArchiveFormat is inconsistent with the database setting

  Database Warning(s):
    ORA-16826: apply service state is inconsistent with the DelayMins property

  Properties:
    DGConnectIdentifier             = 'CORESTD'
    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               = '/u04/oradata/CORE, /u04/oradata/CORE/CORE'
    LogFileNameConvert              = '/u02/oradata/CORE, /u02/oradata/CORE/CORE, /u03/oradata/CORE, /u03/oradata/CORE/CORE'
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'ind-del-db02-s'
    SidName                         = 'CORE'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ind-del-db02-s)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=CORESTD_DGMGRL.in.isol.net)(INSTANCE_NAME=CORE)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
WARNING

DGMGRL> edit database 'CORESTD' set property 'ArchiveLagTarget'=0;
Property "ArchiveLagTarget" updated
DGMGRL>
DGMGRL> edit database 'CORESTD' set property 'LogArchiveMaxProcesses'=4;
Property "LogArchiveMaxProcesses" updated
DGMGRL> edit database 'CORESTD' set property 'LogArchiveMinSucceedDest'=1;
Property "LogArchiveMinSucceedDest" updated
DGMGRL> edit database 'CORESTD' set property 'LogArchiveTrace'=0;
Property "LogArchiveTrace" updated
DGMGRL>
DGMGRL> edit database 'CORESTD' set property 'LogArchiveFormat'='%t_%s_%r.dbf';
Property "LogArchiveFormat" updated
DGMGRL>
DGMGRL> edit database 'CORESTD' set property 'DelayMins'=0;
Property "DelayMins" updated

Once, you corrected all the warning messages, now command should return the with success message.

DGMGRL> show database verbose 'CORESTD';

Database - CORESTD

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    CORE

  Properties:
    DGConnectIdentifier             = 'CORESTD'
    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               = '/u04/oradata/CORE, /u04/oradata/CORE/CORE'
    LogFileNameConvert              = '/u02/oradata/CORE, /u02/oradata/CORE/CORE, /u03/oradata/CORE, /u03/oradata/CORE/CORE'
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'ind-del-db02-s'
    SidName                         = 'CORE'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ind-del-db02-s)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=CORESTD_DGMGRL.in.isol.net)(INSTANCE_NAME=CORE)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS

Now, check the configuration, it should be success.

DGMGRL> show configuration;

Configuration - DRConfig

  Protection Mode: MaxPerformance
  Databases:
    CORE    - Primary database
    CORESTD - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Now, we are ready to use the broker service for managing our standby by setup. To disable the archive shipping to standby we use the following command in SQL:

SQL> alter system set log_archive_dest_state_2='DEFER' scope=both;

System altered.

Now, we will be using below command for disabling the archive shipping.

DGMGRL> edit database 'CORE' set state=transport-off;
Succeeded.
DGMGRL>

Similarly, to cancel the managed mode of standby database use the below command.

DGMGRL> connect sys@corestd
Password:
Connected.
DGMGRL>
DGMGRL>
DGMGRL> edit database 'CORESTD' set state='APPLY-OFF';
Succeeded.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s