In this Document
| Goal |
| Solution |
| Single Instance Database |
| Single Instance Database with Oracle Restart |
| RAC One Node Database |
| Administrator Managed RAC Database |
| Policy Managed RAC Database |
| StaticConnectIdentifier and LOCAL_LISTENER |
| Enterprise Manager |
APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.1.0.2 to 12.1.0.1 [Release 10.1 to 12.1]Information in this document applies to any platform.
GOAL
As per the Oracle Data Guard Broker manuals since Oracle Database 10g Release 2, (see the Oracle Data Guard Broker 11g Release 2 (11.2) for the current documentation), all Data Guard Broker configurations must have a specific static listener entry configured in the local listener of each instance in the Data Guard configuration for the Primary database and all standby databases. A static entry is needed for the Broker to be able to connect remotely to a database that has been shut down by the Broker during certain operations. They are required for the Broker to complete the following operations:
- Switchover from the Primary database to a Physical Standby database
- Reinstatement of failed Primary database after a manual or automatic failover has occurred.
- Conversion of a Snapshot Standby database back to a Physical Standby database
To access a database that has been shutdown in one of the above situations, the Broker uses a default name for the static entry using the information from the LOCAL_LISTENER parameter of the instance and the keyword "_DGMGRL" and stores that information in the broker StaticConnectIdentifier property associated with each instance of the database. 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 as the value of the StaticConnectIdentifier property will be automatically updated by the Broker whenever the instance specified by the SidName property is started on a different host.
Because each static service registration is associated with a specific host, the registration is always made with the local (host-specific) listener.
As a general rule, a static service registration is required for each possible instance on each possible host. The examples in this note will help to ensure that users correctly configure any needed static services.
Since a Data Guard configuration can comprise of multiple types of Oracle databases, care must be taken to configure the static entries for the specific type of database. There are five possible types of database that must be taken into account.
- Single Instance Database
- Single Instance Database with Oracle Restart
- RAC One Node Database
- Administrator Managed RAC Database
- Policy Managed RAC Database
SOLUTION
Single Instance Database
In this case, there is no clusterware and the local listener LISTENER has its LISTENER.ORA located in the /network/admin directory of the database home. For this type of database the static service registration is formatted as follows:
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=db_unique_name_DGMGRL.db_domain)
(ORACLE_HOME=oracle_home)
(SID_NAME=sid_name)
)
)
The SID_NAME value sid_name will default to the db_unique_name.
Single Instance Database with Oracle Restart
Here there is no cluster, but clusterware has been installed to enable the Oracle Restart feature. The local listener LISTENER has its LISTENER.ORA located in the /network/admin directory of the Oracle Grid Infrastructure home. In this case the static service registration is:
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=db_unique_name_DGMGRL.db_domain)
(ORACLE_HOME=oracle_home)
(SID_NAME=sid_name)
)
)
As with Single Instance databases, the SID_NAME value sid_name will default to the db_unique_name.
RAC One Node Database
As above, the local listener LISTENER has its LISTENER.ORA located in the /network/admin directory of the Oracle Oracle Grid Infrastructure home. Using Oracle RAC One Node, the Oracle RAC One Node instance can run on any node defined in the "candidate servers list" (administrator-managed RAC One Node databases) or the server pool (policy managed databases). Here the static service registration has to include both possible instances and is the same for each RAC One Node database host:
(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)
)
)
For RAC One Node databases the SID_LIST entries in the listener.ora files on each possible host must each contain two SID_DESC clauses, one for each of the SID_NAME values that correspond to the two possible RAC One Node instances. This will be true for both administrator and policy managed RAC One Node databases so for both types the static service registration is the same. The static entry needs to be done on any node in the candidate servers list or the server pool, which can include more than two nodes.
Administrator Managed RAC Database
The traditional configuration for Oracle RAC databases is an administrator managed configuration.
All RAC databases prior to Oracle 11g Release 2 are administrator managed and for these prior version databases, the local listener LISTENER has its LISTENER.ORA located in the /network/admin directory of the database home.
For Oracle 11g Release 2 configurations and later (deployed with SCAN listeners), the local listener LISTENER has its LISTENER.ORA located in the /network/admin directory of the Oracle Oracle Grid Infrastructure home.
For an administrator managed configuration, each RAC instance is associated with a specific host and there is one static service registration on each host which has a single SID_DESC clause that differs only in the SID_NAME value from the other hosts in the cluster:
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=db_unique_name_DGMGRL.db_domain)
(ORACLE_HOME=oracle_home)
(SID_NAME=sid_name#)
)
)
The SID_NAME value, sid_name#, differs on each host where # is the instance number associated with that host.
Policy Managed RAC Database
For Oracle 11g Release 2 configurations and later (deployed with SCAN listeners) users have the additional option of creating a policy managed database. The local listener LISTENER in this case also has its LISTENER.ORA located in the /network/admin directory of the Oracle Oracle Grid Infrastructure home.
For a policy managed configuration, based on the user-specified policy, Oracle Clusterware will start a number of instances on a corresponding number of possible hosts. Each possible RAC instance is not associated with a specific host and over time, the number of instances and the host on which each instance runs may change. This requires that the Broker static entry contains every possible instance name where the database might run and this static entry is put in the LISTENER.ORA file on every possible host where the database may be started.
This means that the static service registration for the Broker is the same for each host in the server pool associated with the database and must contain a SID_LIST clause that contains a SID_DESC clause for each possible instance where the database might be running:
(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_#)
)
)
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.
For example, a server pool of five nodes requires that the entry has five SID_DESC entries, sid_name_1 through sid_name_5 and is entered into the LISTENER.ORA file of all five nodes in the server pool. If the size of the server pool changes, the static entry must be changed and each node's LISTENER.ORA file updated with the new entry.
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.
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.
Enterprise Manager
Enterprise Manager can be used to manage Data Guard configurations and the use of the Broker is required for any Data Guard configuration managed by Enterprise Manger. Typically, Enterprise Manager does not require static service registration as Enterprise Manager relies upon its agent for performing actions like an instance restart.
Because Broker initiated actions, such as Fast-Start Failover, do rely on static service registrations; it is a best practice to always configure the appropriate static service registrations for any Data Guard configuration managed through Enterprise Manager, especially for RAC One Node or Policy Managed RAC databases.
Currently Enterprise Manager checks for the _DGMGRL static service during the Verify Configuration operation, and also during Fast-Start Failover enable and standby creation. The service is added if it is not there during Fast-Start Failover enabling and standby creation, but not during the Verify Configuration operation. In a manner similar to the one noted above for the Data Guard Broker, if the StaticConnectIdentifier property is modified to contain an alias or a non-_DGMGRL service, Enterprise Manager will not check for the static service on the assumption that the user has assumed responsibility for the settings.