Thinking Out Loud

My Data Guard Notes

Oracle Data Guard Broker 12c NF

A new DGMGRL command, VALIDATE DATABASE, enables you to quickly and easily determine whether a database is ready to perform a role change.

Performs a comprehensive set of database checks prior to a role change.
The checks use information available in various Oracle Data Guard views as well as the Automatic Diagnostic Repository.

Oracle Data Guard Broker and Static Service Registration (Doc ID 1387859.1)

Note:  Static entries are only used by the Broker itself via the Broker defined StaticConnectIdentifier property.  
They are NEVER to be used for any other purpose by the user.  
This includes using it for the normal DGConnectIdentifier, RMAN connections or anything else.  
Also note that you must never define a static entry for the Broker 'DGB' service.  
That service is for the Broker exclusive use.  Also note that using the "_DGMGRL" or "_DGB" in your TNSNAMES definition is expressly forbidden.

Note: Static "_DGMGRL" entries are no longer needed as of Oracle Database 12.1.0.2 in Oracle Data Guard Broker configurations 
that are managed by Oracle Restart, RAC One Node or RAC as the Broker will use the clusterware to restart an instance.

Note that as a best practice, users should not change the default values assigned to the Broker StaticConnectIdentifier property 
or to the database LOCAL_LISTENER initialization parameter. 
LOL - I have demonstrated this is not accurate.

Single Instance Database / Single Instance Database with Oracle Restart

SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
     (GLOBAL_DBNAME=db_unique_name_DGMGRL.db_domain)
     (ORACLE_HOME=oracle_home)
     (SID_NAME=sid_name)
    )
  )

RAC One Node Database

SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
     (GLOBAL_DBNAME=db_unique_name_DGMGRL.db_domain)
     (ORACLE_HOME=oracle_home)
     (SID_NAME=sid_name_1)
    )
    (SID_DESC=
     (GLOBAL_DBNAME=db_unique_name_DGMGRL.db_domain)
     (ORACLE_HOME=oracle_home)
     (SID_NAME=sid_name_2)
    )
  )

Administrator Managed RAC Database

The traditional configuration for Oracle RAC databases is an administrator managed configuration.

The SID_NAME value, sid_name#, differs on each host where # is the instance number associated with that host.

SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
     (GLOBAL_DBNAME=db_unique_name_DGMGRL.db_domain)
     (ORACLE_HOME=oracle_home)
     (SID_NAME=sid_name#)
    )
  )

Policy Managed RAC Database

The SID_LIST entries in the listener.ora files on each possible host must each contain a SID_DESC clause for each of the SID_NAME values that correspond to all possible RAC instances.

SID_LIST_LISTENER=
  (SID_LIST=
   (SID_DESC=
    (GLOBAL_DBNAME=db_unique_name_DGMGRL.db_domain)
    (ORACLE_HOME=oracle_home)
    (SID_NAME=sid_name_1)
   )

...

   (SID_DESC=
    (GLOBAL_DBNAME=db_unique_name_DGMGRL.db_domain)
    (ORACLE_HOME=oracle_home)
    (SID_NAME=sid_name_#)
   )
  )

StaticConnectIdentifier and LOCAL_LISTENER

If the user modifies the Broker StaticConnectIdentifier property default value, then the Broker assumes that the user will from this point forward continue to maintain the value. If an instance is started on a different host than the static entry is configured for, the Broker will not be able to connect to the instance when it is shut down.

Similarly, once the database LOCAL_LISTENER initialization parameter has been modified by the user, it will no longer automatically be updated by Oracle Clusterware and will contain an incorrect value if a database instance is relocated to a new host system.

In addition, if the LOCAL_LISTENER initialization parameter for a database is modified by the user after the database has been added to a Data Guard Broker configuration, the existing StaticConnectIdentifier property value does not automatically change to reflect the new LOCAL_LISTENER value.

Note that as a best practice, users should not change the default values assigned to the Broker StaticConnectIdentifier property or to the database LOCAL_LISTENER initialization parameter.

If there is a requirement that the LOCAL_LISTENER parameter be changed then the Broker configuration needs to be manually updated as follows to restore automatic Broker updates of the StaticConnectIdentifier property values for a database instance.

For a Standby database use the DGMGRL CLI to remove the database from the Broker configuration and then add it back to the configuration.

For a Primary database use the DGMGRL CLI to remove and then recreate the entire broker configuration.

Data Guard Broker 11gR2 Prerequisites

LISTENER = (DESCRIPTION =
(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=host_name)
(PORT=port_num))))
SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=sid_name)
(GLOBAL_DBNAME=db_unique_name_DGMGRL.db_domain)
(ORACLE_HOME=oracle_home)
(ENVS="TNS_ADMIN=oracle_home/network/admin")))

Alternatively, you can use a different static service name. If you do, be sure to modify the StaticConnectIdentifier instance-specific property to reflect the static service that has been registered.

You should also be aware of the following additional considerations in an Oracle Clusterware environment:

  • The static service must be set in the listener.ora file in the GRID_HOME of all the nodes.
  • The ORACLE_HOME listener.ora parameter in the static service definition must be set to the ORACLE_HOME of the instance, not the GRID_HOME.
  • The ENVS listener.ora parameter must be used in the static service definition to explicitly set the TNS_ADMIN environment variable to the appropriate network admin directory, which is usually the network admin directory of the Oracle Home for the Oracle database.
  • In an Oracle RAC One Node or Policy Managed Oracle RAC environment, the SID_NAME of each possible instance must be specified in the SID_LIST. The SID_NAME of each instance must match the INSTANCE_NAME database initialization parameter of that instance. In an Oracle RAC One Node or Policy Managed Oracle RAC environment, the default value of the INSTANCE_NAME parameter is db_unique_name_instance_number.

Diagnosing Connection Problems with an enabled Data Guard Broker Configuration (Doc ID 745201.1)

  • Ensure the TNS-Alias if specified in ‘DGConnectIdentifier’ is setup on all TNSNAMES.ORA in the Data Guard Environment (11g onward)
  • Verify that the specified Service in the ‘DGConnectIdentifier’ exists and is registered with the corresponding Listener
Enable tracing:
DGMGRL> edit configuration set property ‘TraceLevel’ = ‘SUPPORT’;
Disable Tracing:
DGMGRL> edit configuration set property ‘TraceLevel’ = ‘USER’;

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: