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 addedThis 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