How to Configure Dataguard Broker for the standby database?
DGMGRL Configuration quick steps
This note describes the commands used to create a Data Guard broker configuration using the command line dgmgrl interface.
Primary Database: sbi
Standby Database: sbistby
On both Primary as well as Standby database start the Data Guard Broker process
SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;
System altered.
SQL> show parameter dg_broker_config_file
SQL> show parameter dg_broker_start
Edit the listener.ora on both nodes to add a static entry for DGMGRL
This is to prevent the ORA-12154 error which can be observed on startup of the standby database after performing a switchover.
At primary: Ensure that the GLOBAL_DBNAME is set to db_unique_name_DGMGRL.db_domain
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = sbi_dgmgrl)
(ORACLE_HOME = /u01/oracle/product/11.1.0/db_1)
(SID_NAME = sbi)
)
)
At Standby:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = sbistby_dgmgrl)
(ORACLE_HOME = /u01/oracle/product/11.1.0/db_1)
(SID_NAME = sbistby)
)
)
Create the configuration genoa1:/u01/oracle> dgmgrl
DGMGRL> connect sys/xxxx
Connected.
DGMGRL> CREATE CONFIGURATION 'SBIDR' AS PRIMARY DATABASE IS 'sbi' CONNECT IDENTIFIER IS 'sbi';
Configuration "SBIDR" created with primary database "sbi"
Add the standby database to the configuration
DGMGRL> ADD DATABASE 'sbistby' AS CONNECT IDENTIFIER IS 'sbistby' maintained as physical;
Database "sbistby" added
Check the configuration
DGMGRL> SHOW CONFIGURATION
Configuration
Name: SBIDR
Enabled: NO
Protection Mode: MaxAvailability
Databases:
sbi - Primary database
sbistby - Physical standby database
Fast-Start Failover: DISABLED
Current status for "SBIDR":
DISABLED
Enable the configuration
DGMGRL> ENABLE CONFIGURATION
Enabled.
DGMGRL> SHOW CONFIGURATION
Configuration
Name: SBIDR
Enabled: YES
Protection Mode: MaxAvailability
Databases:
sbi - Primary database
sbistby - Physical standby database
Fast-Start Failover: DISABLED
Current status for "SBIDR":
SUCCESS
View the Standby and Primary database properties
DGMGRL> show database sbi
Database
Name: sbi
Role: PRIMARY
Enabled: YES
Intended State: TRANSPORT-ON
Instance(s): sbi
Current status for "sbi":
SUCCESS
DGMGRL> show database sbistby
Database
Name: sbistby
Role: PHYSICAL STANDBY
Enabled: YES
Intended State: APPLY-ON
Instance(s): sbistby
Current status for "sbistby":
SUCCESS
DGMGRL> show database verbose sbistby
Change the properties of a configured database
DGMGRL> EDIT DATABASE 'sbi' SET PROPERTY 'LogXptMode'='SYNC';
Property "LogXptMode" updated
Performing a switchover
Note: In this case, currently the Primary Database is sbi and the Standby database is sbistby.
DGMGRL> switchover to 'sbistby'
Performing switchover NOW, please wait...
New primary database "genoa1_js" is opening...
Operation requires shutdown of instance "sbi" on database "sbistby"
Shutting down instance "sbi"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "sbistby" on database "sbi"
Starting instance "sbi"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "sbistby"
DGMGRL> show configuration;
Configuration
Name: SBIDR
Enabled: YES
Protection Mode: MaxAvailability
Databases:
sbistby - Primary database
sbi - Physical standby database
Fast-Start Failover: DISABLED
Current status for "SBIDR":
SUCCESS
Monitoring the Data Guard Broker Configuration
If we receive any error or warnings we cab obtain more information about the same ny running the commands as shown below. In this case there is no output seen because currently we are not experiencing any errors or warning.
DGMGRL> show database sbi statusreport
STATUS REPORT
INSTANCE_NAME SEVERITY ERROR_TEXT
DGMGRL> show database sbi logxptstatus
DGMGRL> show database sbi InconsistentProperties
INCONSISTENT PROPERTIES
INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE
DGMGRL>switchover to 'sbistby'
DGMGRL>switchover to 'sbi';
Setting Data protection modes using DGMRGL
Step 1- Edit LogXptMode
DGMGRL> edit database TESTMP2 set property 'LogXptMode'='SYNC';
Property "LogXptMode" updated
Step 2
DGMGRL> edit configuration set protection mode as maxavailability;
Succeeded.
Listener.ora and Tnsnames.ora file at primary
The Oracle Net listener must be also configured with an additional static service identifier. The value of the GLOBAL_DBNAME attribute must be set to a concatenation of _DGMGRL.
The Oracle Net listener on the primary database should have static service definitions:
[oracle@dg1 ~]$ cat $ORACLE_HOME/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = chicago)(PORT = 1521))
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=sbi
(SID_NAME=sbi)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
)
(SID_DESC=
(GLOBAL_DBNAME=sbi_DGMGRL)
(SID_NAME=sbi)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
[oracle@dg1 ~]$ lsnrctl start
(Entry truncated)
Services Summary...
Service "dg1" has 1 instance(s).
Instance "dg1", status UNKNOWN, has 1 handler(s) for this service...
Service "dg1_DGMGRL" has 1 instance(s).
Instance "dg1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
The Oracle Net listener on the primary database should have static service definitions:
[oracle@dg1 ~]$ cat $ORACLE_HOME/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = chicago)(PORT = 1521))
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=sbi
(SID_NAME=sbi)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
)
(SID_DESC=
(GLOBAL_DBNAME=sbi_DGMGRL)
(SID_NAME=sbi)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
[oracle@dg1 ~]$ lsnrctl start
(Entry truncated)
Services Summary...
Service "dg1" has 1 instance(s).
Instance "dg1", status UNKNOWN, has 1 handler(s) for this service...
Service "dg1_DGMGRL" has 1 instance(s).
Instance "dg1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@dg1 ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
sbi =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = chicago)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sbi)
)
)
sbistby =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = texas)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sbistby)
)
)
sbi =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = chicago)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sbi)
)
)
sbistby =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = texas)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sbistby)
)
)