Enhance Availability with Oracle Fast Application Notification and Role-based Services

With all this automation, we should be able to avoid all those dreaded late-night phone calls. However, although our job as DBA is covered by automating failovers, how does an application know that a database failover has occurred? With Oracle 11g Release 2, the Oracle Data Guard Broker had been enhanced to publish Fast Application Notification (FAN) events (via Oracle Notification Service, or ONS), allowing for clients to be notified in the event of a failover.

In addition, another new feature called Role-Based Services allows for applications to failover from a failed primary database to a designated standby with no user intervention and with little or no service interruption. We won’t go into the details of ONS and FAN here, rather we will describe how they can be used within an Oracle Data Guard Broker configuration to provide seamless failover of the application when a database failover has occurred.

When in an Oracle Data Guard Broker configuration, the broker will publish FAN events when a failover occurs. Oracle Java Database Connectivity (JDBC), Oracle Call Interface (OCI), or Oracle Data Provider for .NET (ODP.NET) clients can subscribe to these FAN events, allowing the application to handle the failover of the database gracefully (Fast Connection Failover). The overall end result is the ability for a failover to take place at the database level without user intervention (no need for application server reconfigurations and/or restarts). It is true that you will have to “code” this into your application for full functionality, but the extra effort will truly prove to be worth it for those who require those 5 9s.

To take this one step further, Role-Based Services lets database services run on a designated database when that database is in a specific role. For example, if we wanted a service called myoltp (serving JDBC connections) to run on our primary database, we would define a role-based service on our primary system and standby system, specifying the database role with the –l PRIMARY option.

NOTE

Since JDBC-based connections are serviced by the myoltp service, Transparent Application Failover (TAF) has not been configured (as it should not be).

Here’s the configuration on a primary cluster:

   # srvctl add service -d pitt -r ”maadb1,maadb2” -s reporting -l
   PHYSICAL_STANDBY -q TRUE -e SELECT -m SESSION -w 10 -z 150

Here’s the configuration on a standby cluster:

   # srvctl add service -d cosp -r ”maadb1,maadb2” -s myoltp -l PRIMARY -q
    FALSE -e NONE -m NONE -w 0 -z 0

NOTE

If the myoltp service is servicing ODP.NET or OCI client connections, we would want to enable subscription to AQ Notifications by specifying –q TRUE.

Assuming that cosp is our current standby database, this service will be enabled on that database only when it is in the primary role. The current database role is stored in the OCR and automatically managed by the broker in such a configuration.

For those making use of the Oracle Active Data Guard option, Role-Based Services can be used to provide service functionality of read-only applications directed toward Real-Time Query–enabled standby databases. For demonstration purposes, assume that we want to create a service called reporting with TAF enabled for a ODP.NET application. This service will run only on our Real-Time Query standby database. To achieve our goal, we would perform the following:

On the primary cluster:

   # srvctl add service -d pitt -r ”maadb1,maadb2” -s reporting -l
   PHYSICAL_STANDBY -q TRUE -e SELECT -m SESSION -w 10 -z 150

On the standby cluster:

   # srvctl add service -d cosp -r ”maadb1,maadb2” -s myoltp -l
   PHYSICAL_STANDBY -q TRUE -e SESSION -m BASIC -w 10 -z 150

In addition to creating the service on both the primary and standby clusters, we must create the service definition on the primary database (it will not be running on the primary) to allow for the service definition to be propagated to the standby through Redo Apply. To do so, we would execute the following SQL statement on our primary database:

   SQL> EXECUTE DBMS_SERVICE.CREATE_SERVICE('reporting', 'reporting',
   NULL, NULL,TRUE,'BASIC', 'SESSION', 150, 10, NULL);

Keep in mind that if this reporting service were servicing JDBC connections, we would have set AQ Notifications (–q) to FALSE and set the TAF-related entries to none. For an example of JDBC-based services, see the earlier example for the myoltp service.

Given the fact that these are application design considerations, this is about as far as we are going to take this subject. After all, we are DBAs! However, this topic is definitely one that should be discussed with your application developers or application vendor, because it opens a new world of possibilities, such as zero downtime patching, zero downtime upgrades, and uninterrupted application availability in the event of a failover. With this feature fully integrated with Fast-Start Failover, we might be able to turn off our on-call phones at night, and we can finally get the 99.999 percent availability management has been begging for.