Creating a Data Guard Broker Configuration

Creating a Data Guard Broker Configuration.
Enable Flashback Database on the Primary and Standby databases.

On Primary
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/orcl/spfileorcl.ora

SQL> show parameter undo_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                  string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
SQL>

On Standby
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 /db_1/dbs/spfilestdy.ora

SQL> show parameter undo_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL> alter database flashback on;

Implementation of Data Guard Broker

SQL> show parameter DG_BROKER_START

NAME TYPE VALUE
------------------------------------ ----------- -------
dg_broker_start boolean FALSE

Enable Data Guard Broker Start on the Primary and Standby databases

SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;
System altered

Alert log shows

DMON started with pid=36, OS id=26022
2012-03-04 12:06:42.362000 +05:30
Starting Data Guard Broker (DMON)
2012-03-04 12:06:50.601000 +05:30
INSV started with pid=38, OS id=26039

SQL> show parameter dg_broker

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u01/app/oracle/product/11.1.0
                                                 /db_1/dbs/dr1stdy.dat
dg_broker_config_file2               string      /u01/app/oracle/product/11.1.0
                                                 /db_1/dbs/dr2stdy.dat
dg_broker_start                      boolean     TRUE

Setup the Local_Listener parameter on both the Primary and Standby databases
SQL> show parameter local_listener

NAME TYPE VALUE
------------------------------------ ----------- ------------------
local_listener string

If not Set
SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST= 11gr2.rhel5.com)(PORT=1521))' scope=both;

SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST= 11gr2sb.rhel5.com)(PORT=1521))' scope=both;

Setup the tnsnames & listener to enable communication with both the Primary and Standby databases

To enable the Data Guard broker to restart instances during the course of broker
operations, a service with a specific name must be statically registered with the local
listener of each instance. The value of the GLOBAL_DBNAME attribute must be set to a
concatenation of db_unique_name_DGMGRL.db_domain

Prior to Oracle11g Release 2, you had to use the static entry format of  “GLOBAL_DBNAME attribute of db_unique_name_DGMGRL.db_domain”, or in our case, “orcl_DGMGRL” and “stdy_DGMGRL”. The Broker in Oracle11g Release 2 has a new property called StaticConnectIdentifier that you can set to tell the Broker to use your own static listener entry.
On Primary

$ cd /u01/app/oracle/product/11.2.0/grid/network/admin/
$ vi listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl_DGMGRL.rhel5.com)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = orcl)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 11gr2.rhel5.com)(PORT = 1521))
  )

ADR_BASE_LISTENER = /u01/app/oracle

$ . oraenv
+ASM

$ lsnrctl reload

On Primary

$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin/
$ vi tnsnames.ora

STDY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 11gr2sb.rhel5.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = stdy_DGMGRL)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 11gr2.rhel5.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl_DGMGRL.rhel5.com)
    )
  )

On Standby
$ cd /u01/app/oracle/product/11.2.0/grid/network/admin/

$ vi listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = stdy_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = stdy)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 11gr2sb.rhel5.com)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON

$ lsnrctl reload

On Standby
$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin/
$ vi tnsnames.ora

STDY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 11gr2sb.rhel5.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = stdy_DGMGRL)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 11gr2.rhel5.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl_DGMGRL.rhel5.com)
    )
  )

Setup the tnsnames to enable communication with both the Primary, Standby & Observer databases
$ tnsping orcl

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 11gr2.rhel5.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl_DGMGRL.rhel5.com)))
OK (10 msec)
$ tnsping stdy

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 11gr2sb.rhel5.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = stdy_DGMGRL)))
OK (10 msec)

On Primary:

SQL> show parameter dg_broker_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u01/app/oracle/product/11.1.0
                                                 /db_1/dbs/dr1orcl.dat
dg_broker_config_file2               string      /u01/app/oracle/product/11.1.0
                                                 /db_1/dbs/dr2orcl.dat

On Standby:

SQL> show parameter dg_broker_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u01/app/oracle/product/11.1.0
                                                 /db_1/dbs/dr1stdy.dat
dg_broker_config_file2               string      /u01/app/oracle/product/11.1.0
                                                 /db_1/dbs/dr2stdy.dat

Next create from within the DGMGRL the configuration
On Primary

$ dgmgrl

DGMGRL>

DGMGRL> CONNECT sys/oracle
Connected.

DGMGRL> CREATE CONFIGURATION ORCL_STDY AS PRIMARY DATABASE IS ORCL CONNECT IDENTIFIER IS ORCL;

Configuration "orcl" created with primary database "orcl"
DGMGRL> ADD DATABASE STDY AS CONNECT IDENTIFIER IS STDY MAINTAINED AS PHYSICAL;

Database "stdy" added

DGMGRL> edit database orcl set property StaticConnectIdentifier='orcl';

DGMGRL> edit database stdy set property StaticConnectIdentifier='stdy';

DGMGRL> SHOW CONFIGURATION;
Configuration
Name: orcl
Enabled: NO
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
orcl - Primary database
stdy - Physical standby database
Current status for "orcl":
DISABLED

DGMGRL> SHOW DATABASE VERBOSE STDY;

Database - stdy

  Role:            PHYSICAL STANDBY
  Intended State:  OFFLINE
  Transport Lag:   (unknown)
  Apply Lag:       (unknown)
  Real Time Query: OFF
  Instance(s):
    stdy

  Properties:
    DGConnectIdentifier             = 'stdy'
    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          = '5'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = '11gr2sb.rhel5.com'
    SidName                         = 'stdy'
    StaticConnectIdentifier         = 'stdy'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
DISABLED

DGMGRL> SHOW DATABASE VERBOSE ORCL;

Database - orcl

  Role:            PRIMARY
  Intended State:  OFFLINE
  Instance(s):
    orcl

  Properties:
    DGConnectIdentifier             = 'orcl'
    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               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = '11gr2.rhel5.com'
    SidName                         = 'orcl'
    StaticConnectIdentifier         = 'orcl'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
DISABLED

DGMGRL> enable configuration;
Enabled.
DGMGRL>

DGMGRL> enable database STDY;
Enabled.

DGMGRL> show configuration;
Configuration
  Name:                orcl
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Databases:
    orcl - Primary database
    stdy - Physical standby database

Fast-Start Failover: DISABLED
Current status for "orcl":
SUCCESS