High Availability with cx_Oracle

Applications can utilize many features for high availability (HA) during planned and unplanned outages in order to:

  • Reduce application downtime

  • Eliminate compromises between high availability and performance

  • Increase operational productivity

General HA Recommendations

General recommendations for creating highly available cx_Oracle programs are:

  • Tune operating system and Oracle Network parameters to avoid long TCP timeouts, to prevent firewalls killing connections, and to avoid connection storms.

  • Implement application error handling and recovery.

  • Use the most recent version of the Oracle client libraries. New versions have improvements to features such as dead database server detection, and make it easier to set connection options.

  • Use the most recent version of Oracle Database. New database versions introduce, and enhance, features such as Application Continuity (AC) and Transparent Application Continuity (TAC).

  • Utilize Oracle Database technologies such as RAC or standby databases.

  • Configure database services to emit FAN events.

  • Use a connection pool, because pools can handle database events and take proactive and corrective action for draining, run time load balancing, and fail over. Set the minimum and maximum pool sizes to the same values to avoid connection storms. Remove resource manager or user profiles that prematurely close sessions.

  • Test all scenarios thoroughly.

Network Configuration

The operating system TCP and Oracle Net configuration should be configured for performance and availability.

Options such as SQLNET.OUTBOUND_CONNECT_TIMEOUT, SQLNET.RECV_TIMEOUT and SQLNET.SEND_TIMEOUT can be explored.

Oracle Net Services options may also be useful for high availability and performance tuning. For example the database’s listener.ora file can have RATE_LIMIT and QUEUESIZE parameters that can help handle connection storms.

With Oracle Client 19c, EXPIRE_TIME can be used in tnsnames.ora connect descriptors to prevent firewalls from terminating idle connections and to adjust keepalive timeouts. The general recommendation for EXPIRE_TIME is to use a value that is slightly less than half of the termination period. In older versions of Oracle Client, a tnsnames.ora connect descriptor option ENABLE=BROKEN can be used instead of EXPIRE_TIME. These settings can also aid detection of a terminated remote database server.

When cx_Oracle uses Oracle Client libraries 19c, then the Easy Connect Plus syntax syntax enables some options to be used without needing a sqlnet.ora file. For example, if your firewall times out every 4 minutes, and you cannot alter the firewall settings, then you may decide to use EXPIRE_TIME in your connect string to send a probe every 2 minutes to the database to keep connections ‘alive’:

connection = cx_Oracle.connect("hr", userpwd, "dbhost.example.com/orclpdb1?expire_time=2")

Fast Application Notification (FAN)

Users of Oracle Database FAN must connect to a FAN-enabled database service. The application should have events set to True when connecting. This value can also be changed via Oracle Client Configuration.

FAN support is useful for planned and unplanned outages. It provides immediate notification to cx_Oracle following outages related to the database, computers, and networks. Without FAN, cx_Oracle can hang until a TCP timeout occurs and an error is returned, which might be several minutes.

FAN allows cx_Oracle to provide high availability features without the application being aware of an outage. Unused, idle connections in a connection pool will be automatically cleaned up. A future SessionPool.acquire() call will establish a fresh connection to a surviving database instance without the application being aware of any service disruption.

To handle errors that affect active connections, you can add application logic to re-connect (this will connect to a surviving database instance) and replay application logic without having to return an error to the application user.

FAN benefits users of Oracle Database’s clustering technology Oracle RAC because connections to surviving database instances can be immediately made. Users of Oracle’s Data Guard with a broker will get FAN events generated when the standby database goes online. Standalone databases will send FAN events when the database restarts.

For a more information on FAN see the white paper on Fast Application Notification.

Application Continuity (AC)

Oracle Application Continuity and Transparent Application Continuity are Oracle Database technologies that record application interaction with the database and, in the event of a database instance outage, attempt to replay the interaction on a surviving database instance. If successful, users will be unaware of any database issue. AC and TAC are best suited for OLTP applications.

When AC or TAC are configured on the database service, they are transparently available to cx_Oracle applications.

You must thoroughly test your application because not all lower level calls in the cx_Oracle implementation can be replayed.

See OCI and Application Continuity for more information.

Transaction Guard

cx_Oracle supports Transaction Guard which enables Python application to verify the success or failure of the last transaction in the event of an unplanned outage. This feature is available when both client and database are 12.1 or higher.

Using Transaction Guard helps to:

  • Preserve the commit outcome

  • Ensure a known outcome for every transaction

See Oracle Database Development Guide for more information about using Transaction Guard.

When an error occurs during commit, the Python application can acquire the logical transaction id (ltxid) from the connection and then call a procedure to determine the outcome of the commit for this logical transaction id.

Follow the steps below to use the Transaction Guard feature in Python:

  1. Grant execute privileges to the database users who will be checking the outcome of the commit. Login as SYSDBA and run the following command:

    GRANT EXECUTE ON DBMS_APP_CONT TO <username>;
    
  2. Create a new service by executing the following PL/SQL block as SYSDBA. Replace the <service-name>, <network-name> and <retention-value> values with suitable values. It is important that the COMMIT_OUTCOME parameter be set to true for Transaction Guard to function properly.

    DECLARE
        t_Params dbms_service.svc_parameter_array;
    BEGIN
        t_Params('COMMIT_OUTCOME') := 'true';
        t_Params('RETENTION_TIMEOUT') := <retention-value>;
        DBMS_SERVICE.CREATE_SERVICE('<service-name>', '<network-name>', t_Params);
    END;
    /
    
  3. Start the service by executing the following PL/SQL block as SYSDBA:

    BEGIN
        DBMS_SERVICE.start_service('<service-name>');
    END;
    /
    

Ensure the service is running by examining the output of the following query:

SELECT name, network_name FROM V$ACTIVE_SERVICES ORDER BY 1;

Python Application code requirements to use Transaction Guard

In the Python application code:

  • Use the connection attribute ltxid to determine the logical transaction id.

  • Call the DBMS_APP_CONT.GET_LTXID_OUTCOME PL/SQL procedure with the logical transaction id acquired from the connection attribute. This returns a boolean value indicating if the last transaction was committed and whether the last call was completed successfully or not.

See the Transaction Guard Sample for further details.