Oracle has provided support for failing over database connections and, through Transparent Application Failover (TAF), doing so in a way that allows connected applications to continue functioning relatively uninterrupted during and after the failover. The connected application has to work with the Oracle client layer to participate in the failover, rather than just erroring out when the original connection is lost.

Failover is first configured in the Oracle client layer through the client TNSNAMES.ORA file. For example, if you have two Oracle instances, an ORACLEA instance, which is the primary instance, and ORACLEB instance, which is the failover instance, the TNSNAMES.ORA file might look like the following in part:

ORACLEA =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = SERVERA)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oraclea)
(FAILOVER_MODE =
(TYPE = SESSION)
(METHOD=BASIC)
(BACKUP=ORACLEB)
)
)
)

ORACLEB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = SERVERB)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oracleb)
)
)

The FAILOVER_MODE has several options in addition to BACKUP, which points to the instance that will handle the connections if the primary server goes down. TYPE can be SESSION or SELECT. SESSION will failover the SESSION, but active queries will be lost and will have to be resubmitted manually. When set to SELECT, active queries are cached on the client and will automatically be resubmitted should a failover occur. Regardless of the setting, uncommitted updates to the failed instance are automatically rolled back and must be resubmitted manually. METHOD determines whether a connection to the failover instance is initiated at the same time as the initial connection to the primary database (PRECONNECT), or only attempted in the event of a failover (BASIC). Other options include RETRIES and DELAY, which determine how many times the client should attempt to connect to the failover instance before giving up and declaring the connection dead, and DELAY determines how much time the client should wait between attempts.

Beginning with Oracle 10g, the FAILOVER_MODE settings can be set on the server, and they will (for the most part) override any settings in the client TNSNAMES.ORA file. The client TNSNAMES.ORA file still needs the entry for failover instance though so that the client can find it if the primary server does fail.

PowerBuilder initially provided support in version 8. Several new DBParm parameters were added that determined whether and how failover would be supported:

• SvrFailover: Determines whether failover support is enabled
• FoDialog: Indicates whether a dialog should appear to the user while the failover is occurring
• FoRetryCount: Allows you to specify a value for RETRIES different than what was defined in the TNSNAMES.ORA file
• FoDelay: Allows you to specify a value for DELAY different than what was defined in the TNSNAMES.ORA file

There were a couple limitations that weren't addressed until the release of PowerBuilder 11. In particular:

1. If you used the FoDialog option to display a dialog to the user during the failover, they would end up seeing the information shown in Figure 1 and Figure 2.

There's a convention in Windows applications that any dialogs brought up by the application (particularly warning messages or errors) should have the name of the application in the title bar. It is particularly helpful if the user task switched away from the application, as they then might not realize where the message is coming from. Not all applications do this, but they generally provide something else that is meaningful to the end user if not the application title. How many of your end users know what PowerBuilder is? Would they automatically associate it with your application?

2. If you attended any of my session on using Oracle, or followed some of my articles or book articles on it, you'll know I'm a big proponent of using Oracle ROLES to assign rights to users, and only enabling those roles through a SET ROLE command when the application starts. If the role is non-default, it ensures that the user can't access the database directly through programs such as SQL*PLUS or Access and end up corrupting data. That's because a right granted through a non-default ROLE wouldn't apply to any connection where the role hasn't been enabled. So the user might be able to connect to the database still, but they wouldn't be able to do much else. I'm also a big proponent of adding a password to the ROLE, so that a savvy user couldn't enable it on their own if they learned the name of the ROLE.

The problem with this approach when it comes to failover is that session level information, such as enabled roles, isn't automatically carried over to the failover instance when the failover occurs. What happens in that case, if you are using a ROLE enabled from within the application, is that when the failover occurs, the connection is transferred automatically to the failover instance, but the user has no rights to database objects once they get there.

Both of those issues became fairly easy to address in PowerBuilder 11 when support for High Availability notification events was provided. It's only an option for the Oracle 10g driver (O10) and is enabled when the new HANotification DPParm parameter is set. A new DBNotification event has been added to the transaction object that will receive messages when the failover starts and ends. All we need to do now is script that event.

The first thing we need to do is create a new standard user object of type transaction, so we can edit the script for that new event. Once we've done that, we can add a new method call of_setrole to set the application role at login and after the failover:

EXECUTE IMMEDIATE "SET ROLE ROLENAME IDENTIFIED BY PASSWORD" ;

We could use that method to set the role after the initial connection to the database. Then, in the new dbnotification event, we add the following:

IF notification = DBFailover!  THEN
MessageBox ( GetApplication().displayname, dbmessage )
IF dbmessage = ‘Failover ended...resuming services.' THEN
this.post of_setrole()
END IF
END IF

The dbnotification event has three arguments:

• notification: An enumerated value of DBServerDown!, DBFailover! Or DBDataTruncate!
• command: The statement that had been sent to the database when the notification occurred
• dbmessage: The reason we received the message

The notification we're concerned about is DBFailover!, which we'll receive both when the failover starts and when it ends. The only way we can distinguish between them is to look at the text that was sent back in the dbmessage argument. What we are doing here in either case is displaying the message to the user (essentially what the FoDialog parameter did, so we won't be setting it) and then, if it is a resume from the failover, posting the function that performs the SET ROLE so that the users connection will have proper rights in the failed-over instance.

We've addressed our two concerns with the previous implementation. The dialog that appears to the user now displays our application name (or whatever else we want to display) and the user has their role properly enabled when they resume operations against the failover instance. However, there are still a couple of issues. The first is that this only works with PowerBuilder 11 and only when the Oracle 10g driver is being used. The second, and an issue that is more general to any use of embedded SQL to perform the SET ROLE, is that if database tracing is enable, the SET ROLE will appear in the database trace file:

(3880c10): LOGIN: (2639.186 MS / 2639.186 MS)
(3880c10): CONNECT TO TRA O10:
(3880c10): LOGID=scott
(3880c10): SERVER=oracle
(3880c10): DBPARM=HANotification='Yes',SvrFailover='Yes' (0.002 MS / 2639.188 MS)
(3880c10): PREPARE: (0.000 MS / 2639.188 MS)
(3880c10): BEGIN TRANSACTION: (0.000 MS / 2639.188 MS)
(3880c10): EXECUTE:
(3880c10): SET ROLENAME IDENTIFIED BY PASSWORD (300.764 MS / 2939.952 MS)

Obviously, that's less than ideal if we really intended to prevent the user from entering that information in a SQL*PLUS or Access session.

Well, there is another approach we can use to overcome these two issues as well. PowerBuilder's DBHandle function can be used on a transaction object to obtain the handle to the underlying database connection. That handle can then in turn be passed off to a C++ DLL to perform low-level database operations. What we're going to do is set up our own failover event notification and role-enabling functionality.

What we'll need to do is create a function in our DLL that accepts four arguments, the handle to the transaction from the DBHandle function, the text we want to display in the title of the dialogs (e.g., the application name) and the role name and password we want to set during the initial connection and after any failovers (see Listing 1). (Download Listings 1-4 here.)

m_appname, m_svchp and setrole are all member variables, the latter being of type std::string. The DoSetRole and RegisterCallback methods are explained below. The routine that performs the set role command is shown in Listing 2.

All that just to issue a single SQL statement; makes you appreciate 4GLs. The other thing we need to do is let Oracle know that we want to be notified about any failovers.  We do that by enabling the OCI_ATTR_FOCBK attribute on the connection.  To do that though, we'll need a callback event in our DLL (see Listing 3).

The callback event simply displays the messageboxes to let the user know what is going on and, when a failover ends, calls the set role function for the failed over instance.  Now that we have our callback event, we can let Oracle know we want to be notified of failovers and pass the event as the means to let us know when they occur (see Listing 4).

Now to call that from PowerBuilder. Once again we need to create a standard user object of type transaction, this time so we can add a local external function declaration to it that points to our new DLL:

FUNCTION ulong SetRole ( ulong handle, string appname, string role, string password ) LIBRARY setrole.dll" ALIAS FOR "SetRole;ANSI"

We're going to create an of_setrole function again to do the set role on the initial connection, but this time it calls the DLL instead:

dbhandle = DBHandle ( this )
ll_rc = SetRole ( dbhandle, &
GetApplication().displayname, &
is_role, is_password )

And we're done. This one will work from any version of PowerBuilder and for any of the native Oracle drivers. Once more, because the set role is being issued from within the DLL rather than from PowerBuilder, the set role command doesn't show up in the database trace.

Much of the error handling and other ancillary code was left out of the code listings for the C++ DLL. The entire code sample is available on Sybase's CodeXchange site.