# 19. Using High Availability with python-oracledb

Applications can use 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

## 19.1. General HA Recommendations

General recommendations for creating highly available python-oracledb 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).

- Use 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.

## 19.2. 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 python-oracledb 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 = oracledb.connect("hr", userpwd, "dbhost.example.com/orclpdb?expire_time=2")

## 19.3. 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.

Note

FAN is only supported in the python-oracledb Thick mode. See Enabling python-oracledb Thick mode.

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

FAN allows python-oracledb 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 `ConnectionPool.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 more information on FAN, see the white paper on Fast Application Notification.

## 19.4. Application Continuity (AC)

Oracle Application Continuity (AC) and Transparent Application Continuity (TAC) 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.

Note

Oracle AC and TAC functionality is only supported in the python-oracledb Thick mode. See Enabling python-oracledb Thick mode.

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

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

See OCI and Application Continuity for more information.

## 19.5. Transaction Guard

Python-oracledb 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.

Note

The Transaction Guard feature is only supported in the python-oracledb Thick mode. See Enabling python-oracledb Thick mode.

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. Log in 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 that 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.