How to Configure client connectivity in Oracle Data Guard configuration (Implement failover procedures)

Listeners use registration details to determine which instances support a particular service at a particular moment in time. Listeners then direct connection requests to the correct instances; otherwise, the appropriate error is returned.

Clients who send connection requests to the wrong host might be connected to the wrong database instance, or they might receive an error message such as the following:

 ORA-01033: ORACLE initialization or shutdown in progress

Managing Services

Database services are implemented with the DBMS_SERVICE package. This package provides for the creation, deletion, starting, and stopping of services for a single database instance. Database services attributes:

• Service Name: Name of the service for administration Service Name: Name of the service for administration
• Network Name: Network name of the service as used in SQLNet connect descriptors for client connections
• Parameter Array: Associative array with name-value pairs used to define:
  • Transparent Application Failover (TAF) attributes
  • Workload management goal directives for the service
  • Fast Application Notification (FAN)
  • Distributed Transaction Processing (DTP or XA)

The SRVCTL utility for Oracle Real Application Clusters should always be used to manage role-based services for a cluster environment.

Creating Services for the Data Guard Configuration Databases

By using the DBMS_SERVICE.CREATE_SERVICE procedure, you define a service to represent each role or state in which the databases in your Data Guard configuration can operate. A service created with DBMS_SERVICE.CREATE_SERVICE is not aware of the actual database role. This functionality is only available with the SRVCTL interface for both Real Application Clusters and Oracle Restart. In the examples shown below, the service name is being used to imply the database role, which could be inaccurate after role transitions. The CREATE_SERVICE procedure creates a service name in the data dictionary.

DBMS_SERVICE.CREATE_SERVICE( -
  SERVICE_NAME => 'DG_PROD', -
  NETWORK_NAME => 'DG_PROD', -
  FAILOVER_METHOD => 'BASIC', -
  FAILOVER_TYPE => 'SELECT', -
  FAILOVER_RETRIES => 180, -
  FAILOVER_DELAY => 1);

DBMS_SERVICE.CREATE_SERVICE( -
 SERVICE_NAME => 'DG_RTQ', -
 NETWORK_NAME => 'DG_RTQ');

DBMS_SERVICE.CREATE_SERVICE('DG_LSBY','DG_LSBY');

DBMS_SERVICE.CREATE_SERVICE('DG_SNAP','DG_SNAP');

You should create services for the physical standby database when it is opened in read-only mode (using real-time query) and when it is converted into a snapshot standby database. The DBMS SERVICE CREATE SERVICE DBMS_SERVICE.CREATE_SERVICE will fail to execute on a physical standby database even will fail to execute on a physical standby database even if it is open read-only. The service must be created on the primary and allowed to propagate to the physical standby. In addition, create a service for logical standby databases in your configuration.

Connecting Clients to the Correct Database

Ensure that clients connect to the database instance that is in the correct state and role in the Data Guard configuration by using a database event trigger. The AFTER STARTUP trigger starts the appropriate service on the database. If no database is in the correct state and role, gg the trigger ensures that clients do not connect to a database.

Creating the AFTER STARTUP Trigger for RoleBased Services

CREATE TRIGGER MANAGE_SERVICES AFTER STARTUP ON DATABASE
DECLARE
   ROLE VARCHAR2(30);
   OMODE VARCHAR2(30);
BEGIN
   SELECT DATABASE_ROLE INTO ROLE FROM V$DATABASE;
   SELECT OPEN_MODE INTO OMODE FROM V$DATABASE;
   IF ROLE = 'PRIMARY' THEN
         DBMS_SERVICE.START_SERVICE ('DG_PROD');
  ELSIF ROLE = 'PHYSICAL STANDBY' THEN
        IF OMODE LIKE 'READ ONLY%' THEN
          DBMS_SERVICE.START_SERVICE ('DG_RTQ');
        END IF;
  ELSIF ROLE 'LOGICAL STANDBY' THEN ELSIF ROLE = 'LOGICAL STANDBY' THEN
        DBMS_SERVICE.START_SERVICE ('DG_LSBY');
  ELSIF ROLE = 'SNAPSHOT STANDBY' THEN
       DBMS_SERVICE.START_SERVICE ('DG_SNAP');
  END IF;
END;
/

Use the trigger to start database services that have already been created.

• DG_PROD: Primary database
• DG RTQ _ : Physical standby database opened in : Physical standby database opened in READ ONLY mode (real-time query)
• DG_SNAP: Physical standby database converted to a snapshot standby database
• DG_LSBY: Logical standby database

The AFTER STARTUP trigger is invoked when the database is opened. The trigger checks the database role and the open mode of the database and, based on the values, invokes the DBMS_SERVICE.START_SERVICE procedure to start the appropriate service. The CREATE TRIGGER SQL command needs to be run only in the primary database. It will be replicated to all standby databases.

Starting with Oracle Database 12c Release 1 (12.1), there is a new role value for the DATABASE_ROLE column of the V$DATABASE view. The value FAR SYNC has been added. No additional services should be started for this role type.

Configuring Service Names in the tnsnames ora File

To ensure that clients connect to a database in the correct state and role for a particular service, configure a Net service name for each service in the tnsnames.ora file. For each entry, both the listening endpoint on the primary database and the listening endpoint on the standby database are listed. This is needed if role-reversal events such as failover and switchover occur. Remember that the ADDRESS_LIST syntax causes the FAILOVER=on mode to be enabled even if not specified.

PROD = (DESCRIPTION = (ADDRESS_LIST =
   (ADDRESS=(PROTOCOL = TCP)(HOST = host01)(PORT = 1521))
   (ADDRESS=(PROTOCOL = TCP)(HOST = host03)(PORT = 1521)))
   (CONNECT_DATA = (SERVICE_NAME = DG_PROD)))

RTQ (DESCRIPTION (ADDRESS LIST RTQ = (DESCRIPTION = (ADDRESS_LIST =
  (ADDRESS=(PROTOCOL = TCP)(HOST = host01)(PORT = 1521))
  (ADDRESS=(PROTOCOL = TCP)(HOST = host03)(PORT = 1521)))
  (CONNECT_DATA = (SERVICE_NAME = DG_RTQ)))

SNAP = (DESCRIPTION = (ADDRESS_LIST =
  (ADDRESS=(PROTOCOL = TCP)(HOST = host01)(PORT = 1521))
  (ADDRESS=(PROTOCOL = TCP)(HOST = host03)(PORT = 1521))) (ADDRESS=(PROTOCOL = TCP)(HOST = host03)(PORT = 1521)))
  (CONNECT_DATA = (SERVICE_NAME = DG_SNAP)))

LSBY = (DESCRIPTION = (ADDRESS_LIST =
  (ADDRESS=(PROTOCOL = TCP)(HOST = host01)(PORT = 1521))
  (ADDRESS=(PROTOCOL = TCP)(HOST = host03)(PORT = 1521)))
  (CONNECT_DATA = (SERVICE_NAME = DG_LSBY)))

Configuring Role-Based Services Using Oracle Clusterware

You can configure database services with a database role on an Oracle RAC database or a single-instance database registered with Oracle Restart. The Data Guard broker interacts with Oracle Clusterware or Oracle Restart to ensure that the correct database services are active after a role change. This feature is available only if the Data Guard configuration is broker managed.

$ srvctl add service -db [db_unique_name] -service 
[service_name] -role "[PRIMARY] [,PHYSICAL_STANDBY] 
[,LOGICAL_STANDBY][,SNAPSHOT_STANDBY]" [-policy 
[AUTOMATIC | MANUAL]]

The SRVCTL ADD SERVICE and MODIFY SERVICE commands support the following attributes:

• -role: ROLE attribute with values of PRIMARY, PHYSICAL_STANDBY, LOGICAL_STANDBY, and SNAPSHOT_STANDBY. Default is PRIMARY. This attribute is used to specify the database role for which a given database service should be active used to specify the database role for which a given database service should be active.
• -policy: MANAGEMENT POLICY attribute with values of AUTOMATIC and MANUAL. Default is AUTOMATIC.

When the database instance starts, the service is started automatically if the MANAGEMENT POLICY attribute is set to AUTOMATIC and the value of the ROLE attribute matches the database role. Users can also start a service manually if the database instance is started.

Note: If the service is created on a database instance that has already been started, then it will be necessary to manually start the service the first time. The following syntax illustrates using SRVTL to manually start a service:

$ srvctl start service –db DG_PRMY –service DG_PRMY

For stand-alone servers not using Real Application Clusters (RAC), the following options to the SRVCTL ADD SERVICE command are applicable in Oracle Data Guard environments only:

• Failover type ( -failovertype ) with values { NONE | SESSION | SELECT | TRANSACTION }
• Failover method ( -failovermethod ) with values { NONE | BASIC }
• Failover delay ( -failoverdelay ) integer value measured in seconds
• Failover retries ( -failoverretry ) integer value indicating a count

Adding Standby Databases to Oracle Restart Configuration

When Enterprise Manager Cloud Control is used to create standby databases with the wizarddriven approach, the newly created standby databases are not automatically registered with the Oracle Restart configuration. Use SRVCTL to create the database object in the Oracle Restart Configuration before database services can be associated with the standby databases. The syntax for SRVCTL is displayed below to create a new database object.

$ srvctl add database -d [db_unique_name] -o [oracle_home]
[-m [domain_name]] [-p [spfile]] [-r [PRIMARY | PHYSICAL_STANDBY 
| LOGICAL_STANDBY | SNAPSHOT_STANDBY ]] [-s [start_options]] 
[-t [stop_options]] [-n [db_name]] [-y [AUTOMATIC | MANUAL]] 
[-a "[diskgroup_list]"]

The values for start_options include open, mount, or nomount. The values for stop_options include normal, transactional, immediate, or abort.

Example:

$ srvctl add database -d london –o
/u01/app/oracle/product/11.2.0/dbhome_1 –m example.com –p
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilelondon.ora
-r PHYSICAL_STANDBY –n boston –a "SBDAT,SBFRA"

The example shown above is using a reference to an Oracle Database 11 2 in which Oracle Restart was a suggested best practice.

Example: Configuring Role-Based Services

$ srvctl add service -d boston -s payroll -l PRIMARY –m BASIC –e SELECT –w 1 –z 180
$ srvctl add service –d dallas –s payroll –l PRIMARY –m BASIC –e SELECT –w 1 –z 180
$ srvctl add service -d dallas -s orderstatus -l PHYSICAL_STANDBY

The example above shows the configuration of two role-specific services:

• PAYROLL is a read-write service that always runs on the database with the primary role.
• ORDERSTATUS is a read-only service that always runs on an Active Data Guard standby database, which is currently defined as the Dallas database while it is in the PHYSICAL_STANDBY role. A role transition can stop this service.

Automatic Failover of Applications to a New Primary Database

Without the use of the Oracle Data Guard broker, user-written database triggers are required to implement automatic failover as follows:

• A startup trigger is used to start database services on the new primary database.
• A role-change trigger is used to publish a FAN ONS event to break JDBC clients still connected to the original primary database out of a TCP timeout.

You can automate fast failover of applications to a new primary database without the need for user-written triggers. You must use the Data Guard broker to use this feature.

Automatic fast failover of application clients to a new primary database following failover requires:

1. Fast database failover
2. Restarting database services on the new primary database
3. Notifying clients that a failure has occurred in order to break them out of TCP timeout and redirect them to the new primary database

Data Guard Broker and Fast Application Notification (FAN)

When a failover operation is complete, the Data Guard broker publishes a Fast Application Notification (FAN) event to notify applications that the old primary database is down, and that services on the old primary database are down. This enables applications to transparently fail over to the new primary database.

FAN notification is sent after failover for databases configured with Cluster Ready Services (CRS) and for single-instance databases registered with Oracle Restart.

Applications using the following Oracle integrated database clients can be configured for Fast Connection Failover (FCF) to automatically connect to a new primary database after a failover:

• Oracle Database JDBC
• Oracle Database Oracle Call Interface (OCI)
• Oracle Database ODP.NET

These clients can use FAN without programmatic changes. Applications can use FAN programmatically by using the Oracle Notification Service (ONS) Application Programming Interface to subscribe to FAN events and to execute event-handling actions upon the receipt of an event.

Automating Client Failover in a Data Guard Configuration

Automating client failover in a Data Guard configuration includes:

• Relocating database services to the new primary database as part of a Data Guard failover
• Notifying clients that the failure occurred in order to break them out of TCP timeout
• Redirecting clients to the primary database that is established during the failover operation

Client Failover: Components

The following features are used to implement client failover and minimize the impact of planned and unplanned outages:

• Connect time failover: Redirects failed connection requests to a secondary listener.
• Transparent Application Failover (TAF): Enables Oracle Call Interface (OCI) client applications to automatically reconnect to a database if the original connection fails. TAF fails over only the session and SELECT statements. SELECT statements are automatically restarted in the new session when TAF is configured for SELECT failover. INSERT, UPDATE, and DELETE statements must be rolled back by the application. In addition, any session customizations (for example, ALTER SESSION statements) must be re-executed by the application. Process state variables (such as PL/SQL session-level variables) are not reestablished but can be reestablished by using a TAF callback.
• Fast Application Notification (FAN): Provides quick notification when a resource (such as an instance, service, node, or database) fails. FAN is available to all applications by using either Fast Connection Failover with a FAN-integrated Oracle client (clients using JDBC, OCI, or OLE DB) or by using the FAN API to read FAN events directly
• Fast Connection Failover: Provides fast failover of database connections by enabling you to configure FAN-integrated JDBC clients to automatically subscribe to FAN high availability event and react to service, instance, and database UP and DOWN events.
• DB_ROLE_CHANGE system event: Is fired when any database is first opened after a Data Guard role transition occurs. Using this system event, you can write a trigger to perform post-role change actions.

Automating Failover for OCI Clients

To automate failover for OCI clients, perform the following steps to configure your database:

1. Ensure that your configuration is managed by the Data Guard broker.
2. Execute the DBMS SERVICE CREATE SERVICE procedure to create the database service, enable high-availability notification, and configure server-side TAF settings.
3. Create a trigger that fires on the system startup event. This trigger relocates the database to the service (created in step 2) to a Data Guard standby database after a role transition.

To automate failover for OCI clients, perform the following steps to configure your OCI clients to receive notification of FAN high-availability g events and avoid reconnecting to a failed instance:

1. Create an Oracle Net service name that includes an ADDRESS entry for the primary database host and all standby databases hosts.
2. Use the OCI_EVENTS parameter to initialize the environment so that OCI clients receive FAN notifications: OCIEnvCreate(…OCI_EVENTS…)
3. Link the OCI client application to the libthread or libpthread thread library
4. Set the SQLNET.OUTBOUND_CONNECT_TIMEOUT parameter in the sqlnet.ora file to a value of 3 seconds. This parameter enables clients to quickly traverse an address list in the event of a failure. If a client attempts to connect to a host that is unavailable, the connection attempt is bounded to the time specified by the SQLNET.OUTBOUND_CONNECT_TIMEOUT parameter, after which the client attempts to connect to the next host in the address list. This behavior continues for each host in the address list until a successful connection is made.
5. Register a callback that is invoked when a high-availability event occurs.

Automating Failover for OLE DB Clients

To automate failover for OLE DB clients, perform the following steps to configure your database:

1. Ensure that your configuration is managed by the Data Guard broker.
2. Execute the DBMS_SERVICE.CREATE_SERVICE procedure or SRVCTL to create the database service, enable high-availability notification, and configure server-side TAF settings.
3. Create a trigger that fires on the system startup event if needed. This trigger relocates the database after a role transition to the service that was created in step 2.

Configuring OLE DB Clients for Failover

To configure OLE DB clients to receive notification of FAN high-availability events:

1. Set the following OraOLEDB connection string attributes:
   • DBNotifications = true
   • DBNotificationPort = [unsigned integer]
   • Setting the DBNotificationPort attribute allows the port to be specified. If this attribute is not set, the port is randomly selected.
2. Set the SQLNET.OUTBOUND_CONNECT_TIMEOUT parameter in the sqlnet.ora file to a value of 3 seconds. This parameter enables clients to quickly traverse an address list if a failure occurs. If a client attempts to connect to a host that is unavailable, the connection attempt is bounded to the time specified by the SQLNET.OUTBOUND_CONNECT_TIMEOUT parameter, after which the client attempts to connect to the next host in the address list. This behavior continues for each host in the address list until a successful connection is made.

Automating Failover for JDBC Clients

To automate failover for JDBC clients, perform the following steps to configure your database:

1. Execute the DBMS_SERVICE.CREATE_SERVICE procedure or SRVCTL to create the database service for JDBC clients. Because JDBC clients use FCF rather than TAF, database services for JDBC clients are not configured for AQ HA events. Instead, a trigger is required to notify JDBC clients when a Data Guard failover occurs.
2. Configure and start ONS daemons on all hosts that may contain a primary database. Configure ONS in the $ORACLE_HOME/opmn/conf directory (similar to the example in the slide). See the Oracle Database JDBC Developer’s Guide and Reference for details.
3. Start the ONS daemon Start the ONS daemon.
4. Create a trigger on the system startup event to relocate the database service after a role transition if needed. The trigger is not needed when using Oracle Restart.
5. Create a trigger enabled for the DB_ROLE_CHANGE system event that calls a C program named the FAN ONS Publisher. This trigger is required because the primary host where the ONS daemons reside are no longer available. By calling the FAN ONS Publisher program based on a trigger enabled on the DB_ROLE_CHANGE system event, JDBC clients are notified of the primary site failure and instructed to reconnect to the new primary database.

Configuring JDBC Clients for Failover

To configure JDBC clients for failover:

1. Set the FastConnectionFailoverEnabled DataSource property to True so that the client application uses implicit JDBC connection cache on its data source.
2. Set the oracle.net.ns.SQLnetDef.TCP_CONNTIMEOUT_STR property to a value of 3 seconds on the data source. This property enables the JDBC client to quickly traverse an address list in the event of a failure. If the client attempts to connect to a host that is unavailable, the connection attempt is bounded to the time specified by the SQLnetDef.TCP_CONNTIMEOUT_STR property, after which the client attempts to connect to the next host in the address list. The behavior continues for each host in the address list until a successful connection is The behavior continues for each host in the address list until a successful connection is made.
3. Create an Oracle Net service name that includes an ADDRESS entry for the primary database host and all standby database hosts.
4. Configure a remote ONS subscription on the JDBC client so that an ONS daemon is not required on the client. The remote ONS subscription should contain all hosts that have the potential to become a primary database.
5. Enable SSL for communications.SSL should be used for all ONS communications.