On client machines, Net8 uses a configuration file named tnsnames.ora to translate a database service name into a specific hostname, port number, and instance name. When you connect to a service, Net8 reads this file to find out where that service resides. For any given service, you can define alternate database instances in order to implement connect-time failover. When you do this, any new connections to a service are first attempted using the first database instance in the list. If that attempt fails, Net8 automatically tries again using the second connection in the list. This process continues until a connection is made or until Net8 runs out of instances to try.

The following example shows a database service name definition from a tnsnames.ora file. The service name HADB contains three DESCRIPTION entries in its definition. One points to the database instance named HADB1 on NODE1. The other two point to the instance HADB2 on NODE2 and to the instance HADB3 on NODE3. When a client connects to the HADB service, Net8 will attempt to connect to the three instances in the order in which they are listed. HADB1 will be tried first. If that connection cannot be made for any reason, Net8 will try HADB2. If the connection attempt to HADB2 also fails, Net8 will move on to HADB3:

HADB =
  (DESCRIPTION_LIST =
    (DESCRIPTION = 
      (ADDRESS=
        (PROTOCOL=TCP)(HOST=NODE1)(PORT = 1521)
      )
      (CONNECT_DATA = (SID = HADB1))
    )
    (DESCRIPTION = 
      (ADDRESS=
        (PROTOCOL=TCP)(HOST=NODE2)(PORT = 1521)
      )
      (CONNECT_DATA = (SID = HADB2))
    )
(DESCRIPTION = 
      (ADDRESS=
        (PROTOCOL=TCP)(HOST=NODE3)(PORT = 1521)
      )
      (CONNECT_DATA = (SID = HADB3))
    )

  )

Connect-time failover happens transparently without requiring any changes to the client application. Note that tnsnames.ora allows the failover configuration to be different for each client machine. Thus, it is possible to configure a different order for failover instances for different client machines. For example, in the tnsnames.ora file of some client machines, the order of failover configuration for service name HADB can be set as HADB1, HADB2, and HADB3. On some other client machines, the order could be set as HADB1, HADB3, and HADB2. This permits the failover load to be balanced between HADB2 and HADB3 when HADB1 fails.

Net8 release 8.1.5 uses the service name rather than the SID to identify a database. When using the service name, it is not necessary to specify connect data for every SID. Using service names, our sample tnsnames.ora configuration looks like this:

HADB =
  (DESCRIPTION = 
    (ADDRESS=
      (PROTOCOL=TCP)(HOST=NODE1)(PORT = 1521)
    )
    (ADDRESS=
      (PROTOCOL=TCP)(HOST=NODE2)(PORT = 1521)
    )
    (ADDRESS=
      (PROTOCOL=TCP)(HOST=NODE3)(PORT = 1521)
    )
    (CONNECT_DATA= (SERVICE_NAME= HADB))
  )

Refer to Oracle Corporation’s Net8 Administrators Guide, Release 8.1 for detailed information on tnsnames.ora configuration, and for information about Net8 features that facilitate load balancing in an OPS environment.

Sometimes, Oracle Names is used to resolve database service names. This is very helpful when you have a large number of clients that connect to a database. Oracle Names allows you to centralize the management of service name definitions, so that whenever there is a change to be made, you can do it in one central location instead of having to go out and edit each tnsnames.ora file on each individual client machine. For purposes of failover, alternate databases instances are registered in Oracle Names in a manner similar to that used for tnsnames.ora.

The TYPE parameter is used in a tnsnames.ora connect string to specify the type of failover functionality that you want for the connection. There are three possible selections:

The METHOD parameter is used in a tnsnames.ora connect string to determine when the connection to the backup database occurs. It has two possible values:

Using the PRECONNECT setting saves time when a failure occurs, because you will already be connected to the backup database. The tradeoff, however, is the overhead caused by the normally unused connections to the backup database.

TAF has several restrictions. When a runtime failover occurs using TAF, the effects of any ALTER SESSION statements that were executed in the primary database are lost. They are not carried over to the backup database. The state of any PL/SQL package variables is also lost. TAF is helpful with query-only transactions when the value of the TYPE parameter is set to SELECT. In that case, the OCI8 library keeps track of the number of rows fetched by the SELECT statements being executed in an instance. When a failover occurs, OCI8 automatically reexecutes the queries in the backup database. The rows that were already fetched before are ignored, and the remaining rows are visible to the user. However, because the query has to be processed twice, the query response time will be slower than normal.

DML transactions represent the most difficult case to handle in a failover situation. Currently, failover-aware products such as SQL*Plus cannot failover transactions composed of DML statements such as INSERT, UPDATE, and DELETE. Remember that when a primary instance fails, one of the surviving instances will recover any lost transactions. All committed changes are reapplied, and all uncommitted changes are rolled back. To take care of any DML transactions interrupted by a failover, your application will need to have failover-specific code. This failover code will have to re-create the session environment and resubmit the failed transaction to the backup instance. Also, if the application is a batch application, the failover code needs to be sophisticated enough to skip over transactions that were already committed prior to the failover, allowing the application to resume processing from the point at which the failure occurred.

The following excerpt from a tnsnames.ora file shows an example of two net service names configured for TAF. The two services back each other up. HADB2 serves as the backup for HADB1 and vice versa. In this configuration, runtime failover is indicated by the FAILOVER_MODE parameter:

HADB1 =
  (DESCRIPTION = 
    (ADDRESS = (PROTOCOL = TCP)(HOST = NODE1)(PORT = 1521))
    (CONNECT_DATA = 
      (SID = HADB1)
      (FAILOVER_MODE = (TYPE = SESSION)(METHOD = BASIC)
                       (BACKUP = HADB2))
    )
  )

HADB2 =
  (DESCRIPTION = 
    (ADDRESS= (PROTOCOL = TCP)(HOST = NODE2)(PORT = 1521))
    (CONNECT_DATA = 
      (SID = HADB2)
      (FAILOVER_MODE = (TYPE = SESSION)(METHOD = BASIC)
                       (BACKUP = HADB1))
    )
  )

Using SQL*Plus, you can easily test the TAF configuration illustrated in the sample tnsnames.ora file. You can test failover by shutting down the primary database. You also can use the ALTER SYSTEM DISCONNECT SESSION command to trigger a failover even though the primary instance is alive. Note that failover does not occur when the user process (the Oracle shadow process) is killed when the primary instance is alive. In other words, using a “kill -9” command to kill a SQL*Plus session won’t result in failover. For a user session, you can verify the TAF failover configuration and check to see if the session has failed over, using the following query against the V$SESSION view:

SELECT sid, username, failover_type, 
            failover_method, failed_over 
FROM V$SESSION 
WHERE sid = sid;

The example in this chapter uses a simple tnsnames.ora configuration to explain how the TAF mechanism works. TAF also may be used in conjunction with other Net8 options such as the Oracle Names Server. For information on how to configure TAF in different Net8 environments, refer to Oracle Corporation’s Oracle8i Net8 Administrator’s Guide.