The data guard configuration used in this post is a
physical standby with standalone servers using ASM for data storage. The primary database is ent12c1 and standby is ent12c1
s. This post lists the steps of adding a data guard broker configuration to the existing data guard configuration in 12c.
1. Modify the listener.ora files in both primary and standby add the default static service name entries which are of the "
db_unique_name_DGMGRL.db_domain" form. In this case the database domain name is "
domain.net". If a non default service name is used then this must be set with StaticConnectIdentifier parameter. Oracle documentation states as of Oracle Database 12.1.0.2 "
a static service needs to be defined and registered only if Oracle Clusterware or Oracle Restart is not being used. Broker will use the clusterware to restart an instance". In this case the static _DGMGRL service was created to illustrate the steps that could be used in non-RAC and non Oracle restart setups.
On Primary
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ent12c1.domain.net)
(SID_NAME = ent12c1)
(ORACLE_HOME = /opt/app/oracle/product/12.1.0/dbhome_2)
)
(SID_DESC =
(GLOBAL_DBNAME = ent12c1_DGMGRL.domain.net)
(SID_NAME = ent12c1)
(ORACLE_HOME = /opt/app/oracle/product/12.1.0/dbhome_2)
)
)
On Standby
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ent12c1s.domain.net)
(SID_NAME = ent12c1s)
(ORACLE_HOME = /opt/app/oracle/product/12.1.0/dbhome_2)
)
(SID_DESC =
(GLOBAL_DBNAME = ent12c1s_DGMGRL.domain.net)
(SID_NAME = ent12c1s)
(ORACLE_HOME = /opt/app/oracle/product/12.1.0/dbhome_2)
)
)2. Data guard broker configuration files would be stored in different types of locations (ASM, Cluster FS, ext3). For this setup these files are stored in ASM. For this first create a directory in ASM (if already does not exists) and then the dg broker configuration file location.
On primary
ASMCMD>cd data/ent12c1
ASMCMD> mkdir dgbroker
ASMCMD>cd flash/ent12c1
ASMCMD> mkdir dgbroker
alter system set dg_broker_config_file1='+DATA/ent12c1/dgbroker/dr1ent12c1.dat' scope=both sid='*';
alter system set dg_broker_config_file2='+FLASH/ent12c1/dgbroker/dr2ent12c1.dat' scope=both sid='*';
On standby
ASMCMD>cd dg_data/ent12c1s
ASMCMD> mkdir dgbroker
ASMCMD>cd dg_flash/ent12c1s
ASMCMD> mkdir dgbroker
alter system set dg_broker_config_file1='+DG_DATA/ent12c1s/dgbroker/dr1ent12c1s.dat' scope=both sid='*';
alter system set dg_broker_config_file2='+DG_FLASH/ent12c1s/dgbroker/dr2ent12c1s.dat' scope=both sid='*';
Finally on both primary and standby set the dg broker start to true.
alter system set dg_broker_start=true scope=both sid='*';
3. In previous versions at this point it would be possible to create the dg broker configuration. But in 12c trying to create the configuration would fail at this stage
DGMGRL> create configuration ent12c1_db as primary database is ent12c1 connect identifier is ENT12c1TNS;
Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added
This is because "
as of Oracle Database 12c Release 1 (12.1), for all databases to be added to a broker configuration, any LOG_ARCHIVE_DEST_n parameters that have the SERVICE attribute set, but not the NOREGISTER attribute, must be cleared". To fix this clear the log_archive_dest parameter that does the remote log shipping both on primary and standby.
SQL> alter system set log_Archive_dest_2='' scope=both;
4. Once log_archive_dest is cleared, creation of dg broker configuration succeed.
DGMGRL> create configuration ent12c1_db as primary database is ent12c1 connect identifier is ENT12c1TNS;
Configuration "ent12c1_db" created with primary database "ent12c1"
DGMGRL> show configuration
Configuration - ent12c1_db
Protection Mode: MaxPerformance
Members:
ent12c1 - Primary database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
5. Add the standby instance to the configuration
DGMGRL> add database ent12c1s as connect identifier is ENT12c1STNS;
Database "ent12c1s" added
DGMGRL> show configuration
Configuration - ent12c1_db
Protection Mode: MaxPerformance
Members:
ent12c1 - Primary database
ent12c1s - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED6. Finally enable the configuration
DGMGRL> enable configuration;
DGMGRL> show configuration
Configuration - ent12c1_db
Protection Mode: MaxPerformance
Members:
ent12c1 - Primary database
ent12c1s - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 7 seconds ago) Verify the StaticConnectIdentifier is using the default service name
DGMGRL> show database ent12c1s StaticConnectIdentifier
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.86)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ent12c1s_DGMGRL.domain.net)(INSTANCE_NAME=ent12c1s)(SERVER=DEDICATED)))'
DGMGRL> show database ent12c1 StaticConnectIdentifier
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.85)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ent12c1_DGMGRL.domain.net)(INSTANCE_NAME=ent12c1)(SERVER=DEDICATED)))'
7. At this stage the dg broker configuration creation is complete and data guard is running in maximum performance mode. However the default log transport mode ASYNC will not allow protection mode to be increased to maximum availability.
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability;
Error: ORA-16627: operation disallowed since no standby databases would remain to support protection mode
Failed.
Change the redo transport service to SYNC using LogXptMode. 12c also has a new redo transport service called FASTSYNC (equivalent to setting SYNC NOAFFIRM) which is only available with maximum protection mode.
DGMGRL> show database ent12c1s LogXptMode
LogXptMode = 'ASYNC'
DGMGRL> edit database ent12c1s SET PROPERTY LogXptMode='SYNC';
Property "logxptmode" updated
DGMGRL> edit database ent12c1 SET PROPERTY LogXptMode='SYNC';
Property "logxptmode" updated
DGMGRL> show configuration
Configuration - ent12c1_db
Protection Mode: MaxPerformance
Members:
ent12c1 - Primary database
ent12c1s - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 60 seconds ago)
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability;
Succeeded.
DGMGRL> show configuration
Configuration - ent12c1_db
Protection Mode: MaxAvailability
Members:
ent12c1 - Primary database
ent12c1s - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 51 seconds ago)
8. 12c has also introduced a new dg broker command "validate database" which checks the readiness of a database to perform a role change.
On primary
DGMGRL> validate database ent12c1
Database Role: Primary database
Ready for Switchover: Yes
On Standby
DGMGRL> validate database ent12c1s
Database Role: Physical standby database
Primary Database: ent12c1
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
DGMGRL> switchover to ent12c1s
Performing switchover NOW, please wait...
New primary database "ent12c1s" is opening...
Oracle Clusterware is restarting database "ent12c1" ...
Switchover succeeded, new primary is "ent12c1s"
DGMGRL> show configuration;
Configuration - ent12c1_db
Protection Mode: MaxAvailability
Members:
ent12c1s - Primary database
ent12c1 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 38 seconds ago)
DGMGRL> validate database ent12c1s
Database Role: Primary database
Ready for Switchover: Yes
DGMGRL> validate database ent12c1
Database Role: Physical standby database
Primary Database: ent12c1s
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)This concludes the creation of data guard broker on 12c.
Useful metalink notes
Create Configuration Failing with ORA-16698
[ID 1582179.1]
12c Create Dataguard Broker Configuration - DGMGRL
[ID 1583588.1]
Related Posts
Adding Far Sync Instances to Existing Data Guard Configuration
11gR2 Standalone Data Guard (with ASM and Role Separation)
11gR2 RAC to RAC Data Guard
Upgrading from 11.2.0.2 to 11.2.0.3 with Physical Standby - 1
RAC to Single Instance Physical Standby
