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.
The operating system TCP and Oracle Net configuration should be configured for performance and availability.
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
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
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.
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
Follow the steps below to use the Transaction Guard feature in Python:
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>;
Create a new service by executing the following PL/SQL block as SYSDBA. Replace the
<retention-value>values with suitable values. It is important that the
COMMIT_OUTCOMEparameter 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; /
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
ltxidto determine the logical transaction id.
- Call the
DBMS_APP_CONT.GET_LTXID_OUTCOMEPL/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.