Frederick Tang Weblog

– Edit: I have updated the title of this post to include 9i. New failover features are available in 10g, so this article is a little out-of-date.

Over the week, my colleagues and I discussed application failover on a Oracle9i RAC database. I don’t know much about how RAC works, so I decided to do some research and post some notes here. Hopefully, this information can add to someone else’s researching into understanding the technology.

Disclaimer: What I am posting here isn’t something new, the information is freely available in Oracle documentation. If you find the information I post here is wrong, please kindly let me know. By all means, test the technology for yourselves. If you would like to ask a question, please feel free and I will try research a bit more to give an educated answer when I have time.

To start this off, I would imagine I have a 2-node Oracle9i RAC database and the simplest client tnsnames entry would look like this:

BLOGRAC =
  (DESCRIPTION =
     (LOAD_BALANCE = ON)
     (ADDRESS_LIST =
        (ADDRESS=(PROTOCOL=TCP)(HOST=blograc1)(PORT=1521))
        (ADDRESS=(PROTOCOL=TCP)(HOST=blograc2)(PORT=1521))
     )
     (CONNECT_DATA = (SERVICE_NAME=BLOGRAC))
   )    

The first failover feature is connect-time failover, where “a client connect request is forwarded to another listener if a listener is not responding”[source][source]. To enable this, the client tnsnames entry might look like this:

BLOGRAC =
  (DESCRIPTION =
     (LOAD_BALANCE = ON)
     (FAILOVER = ON)
     (ADDRESS_LIST =
        (ADDRESS=(PROTOCOL=TCP)(HOST=blograc1)(PORT=1521))
        (ADDRESS=(PROTOCOL=TCP)(HOST=blograc2)(PORT=1521))
     )
     (CONNECT_DATA = (SERVICE_NAME=BLOGRAC))
   )

The next failover feature is Transparent Application Failover (TAF), where “the failover and reestablishment of application-to-service connections. It enables client applications to automatically reconnect to the database if the connection fails, and optionally resume a SELECT statement that was in progress. This reconnect happens automatically from within the Oracle Call Interface (OCI) library.” [source].

TAF has several restrictions [source]:

• All PL/SQL package states on the server are lost at failover
• ALTER SESSION statements are lost
• If failover occurs when a transaction is in progress, then each subsequent call causes an error message until the user issues an OCITransRollback call. Then Oracle issues an Oracle Call Interface (OCI) success message. Be sure to check this message to see if you must perform additional operations.
• Oracle fails over the database connection and if TYPE=SELECT in the FAILOVER_MODE section of the service name description, Oracle also attempts to fail over the query
• Continuing work on failed-over cursors can result in an error message

To enable this feature, the client tnsnames entry might look like this:

BLOGRAC =
  (DESCRIPTION =
     (LOAD_BALANCE = ON)
     (FAILOVER = ON)
     (ADDRESS_LIST =
        (ADDRESS=(PROTOCOL=TCP)(HOST=blograc1)(PORT=1521))
        (ADDRESS=(PROTOCOL=TCP)(HOST=blograc2)(PORT=1521))
     )
     (CONNECT_DATA =
        (SERVICE_NAME=BLOGRAC)
        (FAILOVER_MODE=
           (TYPE=select)
           (METHOD=basic))
     )
   )

The TYPE are session| select | none, and METHOD are basic | preconnect [source].

As stated above, TAF is only supported on JDBC OCI drivers and not on JDBC Thin drivers. I found a couple of Oracle Metalink Notes that re-iterates this, although they are updated for Oracle10g, they still apply to Oracle9i. I also found a note which explains how to write Java code to enable OCI TAF callbacks:

Note:297490.1 Is TAF (Transparent Application Failover) Supported On The Latest JDBC Thin Driver (10g)?

Note:465423.1 Can the JDBC Thin Driver Do Failover by Specifying FAILOVER_MODE?

Note:276225.1 JDBC Sample on How to Implement OCI TAF callbacks with DML and PL/SQL

This is an aside… I wanted to know what error will occur to a Java application [source] when there’s instance shutdown or failure. I have written a very simple Java program to test this, and it will query results from a big table I created on a Oracle9i RAC database, using the Oracle9i JDBC Thin Driver:

1. Oracle instance shutdown

Vendor Error Code: 1089
java.sql.SQLException: ORA-01089: immediate shutdown in progress – no operations are permitted
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
    at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
    at oracle.jdbc.ttc7.Oclose.receive(Oclose.java:122)
    at oracle.jdbc.ttc7.TTC7Protocol.close(TTC7Protocol.java:683)
    at oracle.jdbc.driver.OracleStatement.close(OracleStatement.java:644)
    at TestDBOracle.main(TestDBOracle.java:49)

2. Oracle instance failure (by killing the pmon and smon processes) – yes, it came out with funny characters…

Vendor Error Code: 17410
java.sql.SQLException: �S���q��yŪ����
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
    at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:1160)
    at oracle.jdbc.ttc7.MAREngine.unmarshalUB1(MAREngine.java:963)
    at oracle.jdbc.ttc7.MAREngine.unmarshalSB1(MAREngine.java:893)
    at oracle.jdbc.ttc7.Oclose.receive(Oclose.java:101)
    at oracle.jdbc.ttc7.TTC7Protocol.close(TTC7Protocol.java:683)
    at oracle.jdbc.driver.OracleStatement.close(OracleStatement.java:644)
    at TestDBOracle.main(TestDBOracle.java:49)

[Please note: If your application is getting a 17410 error, it doesn't always mean the database / instance died.]