Oracle data guard broker configurations - step by step

1. Set DG_BROKER_START initialization parameter

SQL> connect sys@primarydb as sysdba
SQL> SHOW PARAMETER DB_BROKER_START;
SQL> ALTER SYSTEM SET DB_BROKER_START=TRUE;

2. Configure data broker service

Define a listener service for data guard broker.
Edit listener $ORACLE_HOME/network/admin/listener.ora file should contain entries as:

ISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS=(PROTOCOL=IPC)(KEY = EXTPROC1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.231)(PORT = 1521)(IP=FIRST))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DNAME = logicaldb)
(ORACLE_HOME = /oradisk/oracle/product/11.1.0/db)
(SID_NAME = orcl))
(SID_DESC =
(GLOBAL_DNAME = logicaldb_DGMGRL)
(ORACLE_HOME = /oradisk/oracle/product/11.1.0/db)
(SID_NAME = ORCL))
)

NOTE: The value for the GLOBAL_DBNAME should be a concatenation of the initialization
parameters _DGMGRL. in listener.ora on all instances of both primary and standby databases. After making necessary changes in listener on all nodes reload listener.

$> lsnrctl reload;

3. Configure/update TNS services on primary and standby databases
Edit listener $ORACLE_HOME/network/admin/ tnsnames.ora file should contain entries as:

PRIMARYDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL=TCP)(HOST=primarydb)(PORT=1521)))
(CONNECT_DATA =(SERVICE_NAME=orcl))
)

LISTENER_PHYSICALDB =(ADDRESS=(PROTOCOL=TCP)(HOST=physicaldb)(PORT=1521))
PHYSICALDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS=(PROTOCOL=TCP)(HOST=physicaldb)(PORT=1521)))
(CONNECT_DATA =(SID=orcl))
)

LISTENER_LOGICALDB=(ADDRESS=(PROTOCOL=TCP)(HOST=logicaldb)(PORT=1521))
LOGICALDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST=logicaldb)(PORT=1521)))
(CONNECT_DATA =(SID=orcl))
)
Note: update tnsnames.ora file carefully, and update it on all standby nodes include in configuration. Also check user can connect to all nodes/databases. If communication problems first solve then proceed.

[oracle@primarydb ]$ sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Jun 4 12:48:25 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

SQL> connect sys/password@primarydb as sysdba
Connected.
SQL> connect sys/password@physicaldb as sysdba
Connected.
SQL> connect sys/password@logicaldb as sysdba
Connected.


4. Data Guard Broker configuration

Now here we create data guard broker configuration and add standby databases, verification and enable configuration.

1. Invoke DGMGRL and connect to your primary database

[oracle@primarydb ~]$ dgmgrl
DGMGRL for Linux: Version 11.1.0.6.0 - 64bit Production
Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/password@primarydb
Connected.

DGMGRL> show configuration
Error: ORA-16525: the Data Guard broker is not yet available

Configuration details cannot be determined by DGMGRL
DGMGRL>

2. Create the broker configuration including a profile for the primary database.

DGMGRL> CREATE CONFIGURATION ‘DGCONFIG’ AS
Primary Database is ‘orcl’
Connect identifier is primarydb;

3. Display information about the configuration.
DGMGRL> show configuration

4. Add physical standby database in broker configuration.
DBMGRL> add database ‘physicaldb’ as connect identifier is physicaldb
Database “standby” added

Apply following if oracle 10.2.0
DBMGRL> add database ‘physicaldb’ as connect identifier is standby
maintained as physical;

5. Use the SHOW CONFIGURATION command to verify that the standby database was added to the configuration.
DGMGRL> show configuration

6. Enabling the Broker Configuration
With the Data Guard environment set successfully for your primary and standby databases. You can now enable the broker configuration.
Enable the entire configuration. This may take some time to complete.

DGMGRL> enable configuration
Enabled.


DGMGRL> show database physicaldb

Database
Name: physicaldb
Role: PHYSICAL STANDBY
Enabled: YES
Intended State: APPLY-ON
Instance(s):
Orcl

Current status for "physicaldb":
SUCCESS

5. Remove configuration
Remove standby data guard broker configuration, repeat for all target standby database.

DGMGRL> remove database ‘physicaldb’;

Remove data gurad broket configuration

DGMGRL> REMOVE CONFIGURATION;

DGMGRL> show configuration
Error: ORA-16525: the Data Guard broker is not yet available

Configuration details cannot be determined by DGMGRL
DGMGRL>

6. Troubleshooting

DGMGRL> HELP;
DGMGRL> SHOW CONFIGURATION;
DGMGRL> SHOW DATABASE VERBOSE 'dbname';

Monitor able properties to troubleshoot

DGMGRL> SHOW DATABASE 'dbname' 'StatusReport';
DGMGRL> SHOW DATABASE 'dbname' 'LogXptStatus';
DGMGRL> SHOW DATABASE 'dbname' 'InconsistentLogXptProps';
DGMGRL> SHOW DATABASE 'dbname' 'LogXptStatus';
DGMGRL> SHOW DATABASE 'dbname' 'InconsistentProperties';

DGMGRL> SHOW DATABASE 'dbname' 'StatusReport';

Issue
DGMGRL> CONNECT sys/password
ORA-16525: the Data Guard broker is not yet available

Fix
Set dg_broker_start=true

Issue

DGMGRL> SHOW DATABASE 'physicaldb' 'StatusReport';
STATUS REPORT
INSTANCE_NAME SEVERITY ERROR_TEXT
physicaldb WARNING ORA-16714: The value of property ArchiveLagTarget is inconsistent with the database setting.
physicaldb WARNING ORA-16714: The value of property LogArchiveMaxProcesses is inconsistent with the database setting.

Note: See data guard alert log and fixed Inconsistent Properties

Issue

SHOW DATABASE 'physicald' 'LogArchiveMaxProcesses';
LogArchiveMaxProcesses = '4'

Fix
EDIT DATABASE 'physicaldb' SET PROPERTY 'LogArchiveMaxProcesses'=2;

Equivalent Broker Commands to 'ALTER SYSTEM'

SQL> alter database recover managed standby database cancel;
DGMGRL> edit database 'dbname' set state='LOG-APPLY-OFF';

SQL> alter database recover managed standby database disconnect;
DGMGRL> edit database 'sdbname' set state='ONLINE';

SQL> alter system set log_archive_max_processes=4;
DGMGRL> edit database 'dbname' set property 'LogArchiveMaxProcesses'=4;

SQL> alter system set log_archive_dest_state_2='enable' cope=both;
DGMGRL> edit database 'dbname' set property 'LogShipping'='ON';

SQL> alter system set log_archive_dest_state_2='defer' scope=both;
DGMGRL> edit database 'dbname' set property 'LogShipping'='OFF';