Introduction
Steps to configure Oracle – Data Guard Broker
Posted by Sundaravel Ramasubbu
In this document,
Primary Server: Chennai
Secondary Server: Trichy
Primary DB Unique Name : PROD_DGP
Standby DB Unique Name : PROD_DGS
Prerequisite
- Dataguard is configured and redo shipping and redo apply is happening, this document intended for enable Data Guard Broker for an existing standby environment.
- Database should be running in spfile.
- Listener port should be opened in firewall between Primary DB server and Standby DB Server.
Check DG_BROKER_START parameter on Primary and Standby
On Primary and Standby
Login as Sys
sqlplus “/as sysdba”
show parameter DG_BROKER_START
Set DG_BROKER_START to true on Primary and Standby
On Primary and Standby
alter system set DG_BROKER_START = true scope=both;
Update listener.ora on Primary and Standby
On Primary
LISTENER_PROD_DGP =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Chennai)(PORT = 1537))
)
SID_LIST_LISTENER_PROD_DGP =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PROD_DGP_DGMGRL)
(ORACLE_HOME = /oracle/app/oracle/product/PROD/11204)
(SID_NAME = PROD)
)
(SID_DESC =
(GLOBAL_DBNAME = PROD_DGP_DGB)
(ORACLE_HOME = /oracle/app/oracle/product/PROD/11204)
(SID_NAME = PROD)
)
)
Note: If db_domain parameter is not null, GLOBAL_DBNAME should be like db_unique_name_DGMGRL.db_domain
On Standby
LISTENER_PROD_DGS =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Trichy)(PORT = 1537))
)
SID_LIST_LISTENER_PROD_DGS =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PROD_DGS_DGMGRL)
(ORACLE_HOME = /oracle/app/oracle/product/PROD/11204)
(SID_NAME = PROD)
)
(SID_DESC =
(GLOBAL_DBNAME = PROD_DGS_DGB)
(ORACLE_HOME = /oracle/app/oracle/product/PROD/11204)
(SID_NAME = PROD)
)
)
Note: If db_domain parameter is not null, GLOBAL_DBNAME should be like db_unique_name_DGMGRL.db_domain
Sample TNSNAMES.ora
Primary and Standby
PROD_DGS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Trichy) (PORT = 1537))
)
(CONNECT_DATA =
(SERVICE_NAME = PROD_DGS_DGMGRL)
)
)
PROD_DGP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Chennai) (PORT = 1537))
)
(CONNECT_DATA =
(SERVICE_NAME = PROD_DGMGRL)
)
)
Create the data guard broker configuration
On Primary DB Node
DGMGRL> connect sys@PROD
Password:
Connected.
DGMGRL> CREATE CONFIGURATION PROD_DG_CONFIG AS PRIMARY DATABASE IS PROD CONNECT IDENTIFIER IS PROD_DGP;
Configuration “PROD_dg_config” created with primary database “PROD”
Note: For 12c DB , you may get the below error when you create the configuration.
Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added
Solution:
On Primary and Standby DB, Connect as sysdba
alter system set log_Archive_dest_2=”;
DGMGRL> ADD DATABASE PROD_DGS AS CONNECT IDENTIFIER IS PROD_DGS MAINTAINED AS PHYSICAL;
Database “PROD_dgs” added
DGMGRL> SHOW CONFIGURATION;
Configuration – PROD_dg_config
Protection Mode: MaxPerformance
Databases:
PROD – Primary database
PROD_dgs – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
DGMGRL> SHOW DATABASE PROD_dgs
Database – PROD_dgs
Role: PHYSICAL STANDBY
Intended State: OFFLINE
Transport Lag: (unknown)
Apply Lag: (unknown)
Apply Rate: (unknown)
Real Time Query: OFF
Instance(s):
PROD
Database Status:
DISABLED
Enable configuration
On Primary DB Node
DGMGRL> connect sys@PROD
Password:
Connected.
DGMGRL> ENABLE CONFIGURATION;
Enabled.
DGMGRL> show configuration
Configuration – PROD_dg_config
Protection Mode: MaxPerformance
Databases:
PROD – Primary database
PROD_dgs – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
This step is needed if you want to set Archival removal policy Standby Database.
[oracle@dbbocoprod13 DTPROD]$ dgmgrl
DGMGRL for Linux: Version 12.1.0.2.0 – 64bit Production
Copyright (c) 2000, 2013, Oracle. All rights reserved.
Welcome to DGMGRL, type “help” for information.
DGMGRL> connect /
Connected as SYSDG.
DGMGRL> edit database prod_dgs set property Binding=’mandatory’;
Property “binding” updated
Database switchover
- Make sure redo shipping is happening and redo is getting applied into the standby.
- In the DGMGRL console, input “show configuration”, make sure there is no error reported and configuration status should be SUCCESS.
- Verify no error is reported in alert log.
- Make sure DG listener is up and running on primary and standby.
On Primary DB Node
dgmgrl sys/*****@PROD_dgs
DGMGRL for Linux: Version 11.2.0.4.0 – 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type “help” for information.
Connected.
DGMGRL> switchover to PROD
Performing switchover NOW, please wait…
Operation requires a connection to instance “PROD” on database “PROD”
Connecting to instance “PROD”…
Connected.
New primary database “PROD” is opening…
Operation requires startup of instance “PROD” on database “PROD_dgs”
Starting instance “PROD”…
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is “PROD”
