Monday, June 1, 2015

Creating Data Guard Broker on 12c

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 ent12c1s. 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.
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 ENT12c1;
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:
DISABLED
6. 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