Oracle Cloud Infrastructure Documentation

Client Configuration for Continuous Availability on Autonomous Database

You do not need to restart applications for planned maintenance activities when you enable Application Continuity and you follow the coding best practices.

Parent topic: Use Application Continuity on Autonomous Database

Connect Using Database Services with Application Continuity Enabled

Oracle database services provide transparency for the underlying Autonomous Database infrastructure.

The high availability and application continuity operations are predicated on the use of Autonomous Database connection services. To obtain application continuity, use a database service when you connect to your database.

The names of the predefined database services on Autonomous Database are different, depending on your workload, as described in Database Service Names for Autonomous Database.

Use Recommended Practices That Support Draining

On Autonomous Database there is never a need to restart application servers when planned maintenance follows best practice.

For planned maintenance, the recommended approach is to provide time for current work to complete before maintenance is started. On Autonomous Database this happens automatically and work is drained before starting maintenance activities when you follow these guidelines:

  • FAN with Oracle Connection Pools or Oracle Drivers
  • Connection tests

Use draining in combination with your chosen failover solution for those requests that do not complete within the allocated time for draining. Your failover solution will try to recover sessions that did not drain in the allocated time.

Return Connections to the Connection Pool

The application should return the connection to the connection pool on each request. It is best practice that an application checks-out a connection only for the time that it needs it. Holding a connection instead of returning it to the pool does not perform. An application should therefore check-out a connection and then check-in that connection immediately the work is complete. The connections are then available for later use by other threads, or your thread when needed again. Returning connections to a connection pool is a general recommendation regardless of whether you use FAN to drain, or connection tests to drain.

Use an Oracle Connection Pool

Using a FAN-aware, Oracle connection pool is the recommended solution for hiding planned maintenance. As the maintenance progresses and completes, sessions are moved and rebalanced. There is no impact to users when your application uses an Oracle Pool with FAN and returns connections to the pool between requests. Supported Oracle Pools include UCP, WebLogic GridLink, Tuxedo, OCI Session Pool, and ODP.NET Managed and Unmanaged providers. No application changes whatsoever are needed to use FAN other than making sure that your connections are returned to pool between requests.

Use UCP with a Third-Party Connection Pool

If you are using a third party, Java-based application server, the most effective method to achieve draining and failover is to replace the pooled data source with UCP. This approach is supported by many application servers including Oracle WebLogic Server, IBM WebSphere, IBM Liberty, Apache Tomcat, Red Hat WildFly (JBoss), Spring, and Hibernate, and others.

Use Connection Tests

If you cannot use an Oracle Pool with FAN, then the Autonomous Database or provided client drivers will drain the session. When services are relocated or stopped during maintenance, or there is a switchover to a standby site using Autonomous Data Guard, the Oracle Database and Oracle client drivers look for safe places to release connections according to the following rules:

  • Standard connection tests for connection validity at borrow or return from a connection pool
  • Custom SQL tests for connection validity
  • Request boundaries are in effect and the current request has ended

Use Connection Tests with Autonomous Database

You can add, delete, enable or disable connection tests for Autonomous Database.

Use the view DBA_CONNECTION_TESTS to show the available connection tests.

For example:

SQL> EXECUTE 
   dbms_app_cont_admin.add_sql_connection_test('SELECT COUNT(1) FROM DUAL');
SQL> EXECUTE 
   dbms_app_cont_admin.enable_connection_test(dbms_app_cont_admin.sql_test, 
                                             'SELECT COUNT(1) FROM DUAL');
SQL> SELECT * FROM DBA_CONNECTION_TESTS;

Configure the same connection test that is enabled in your database at your connection pool or application server. Also configure flushing and destroying the pool on connection test failure to at least two times the maximum pool size or MAXINT.

Use Connection Tests with Thin Java Driver

If you would like to use connection tests that are local to the driver and cannot use UCP’s full FAN support:

  • Enable validate-on-borrow=true
  • Set the Java system properties:
    • -Doracle.jdbc.fanEnabled=true
    • -Doracle.jdbc.defaultConnectionValidation=SOCKET

And then use one of the following tests:

  • java.sql.Connection.isValid(int timeout)
  • oracle.jdbc.OracleConnection.pingDatabase()
  • oracle.jdbc.OracleConnection.pingDatabase(int timeout)
  • A HINT at the start of your test SQL:
    • /*+ CLIENT_CONNECTION_VALIDATION */

Use Connection Tests with OCI Driver

If you would like to use the OCI driver directly, use OCI_ATTR_SERVER_STATUS. This is the only method that is a code change. In your code, check the server handle when borrowing and returning connections to see if the session is disconnected. During maintenance, the value of OCI_ATTR_SERVER_STATUS is set to OCI_SERVER_NOT_CONNECTED. When using OCI session pool, this connection check is done for you.

The following code sample shows how to use OCI_ATTR_SERVER_STATUS: 

ub4 serverStatus = 0OCIAttrGet((dvoid *)srvhp,
          OCI_HTYPE_SERVER,             
        (dvoid *)&serverStatus, (ub4 *)0, OCI_ATTR_SERVER_STATUS,
      errhp);if (serverStatus ==
        OCI_SERVER_NORMAL)printf("Connection is
        up.\n");else if (serverStatus ==
        OCI_SERVER_NOT_CONNECTED) printf("Connection is down.\n");

Steps for Using Application Continuity

Perform these steps to use Application Continuity:

  • As a prerequisite, enable and configure Application Continuity or Transparent Application Continuity (TAC) for your database service on Autonomous Database. See Configure Application Continuity on Autonomous Database for more information.

  • Oracle strongly recommends that you use the latest client drivers. Oracle Database 19c client drivers and later provide full support for Application Continuity (AC) and for Transparent Application Continuity (TAC). Use one of the following supported clients drivers:

    • Oracle JDBC Replay Driver 19c or later. This is a JDBC driver feature provided with Oracle Database 19c for Application Continuity

    • Oracle Universal Connection Pool (UCP) 19c or later with Oracle JDBC Replay Driver 19c or later

    • Oracle Weblogic Server 12c with Active GridLink, or third-party JDBC application servers using UCP with Oracle JDBC Replay Driver 19c or later

    • Java connection pools or standalone Java applications using Oracle JDBC Replay Driver 19c or later

    • Oracle Call Interface Session Pool 19c or later.SQL*Plus 19c (19.8) or later

    • ODP.NET pooled, Unmanaged Driver 19c or later ("Pooling=true" default in 12.2 and later)

    • Oracle Call Interface based applications using 19c OCI driver or later

Return Connections to the Connection Pool

The application should return the connection to the Oracle connection pool on each request. Best practice for application usage is to check-out (borrow) connections for only the time that they are needed, and then check-in to the pool when complete for the current actions. This is important for best application performance at runtime, for rebalancing work at runtime and during maintenance and failover events. This practice is also important for draining.

When using an Oracle connection pool, such as Universal Connection Pool (UCP) or OCI Session Pool, or ODP.Net Unmanaged Provider or when using WebLogic Active GridLink, following this practice embeds request boundaries that Application Continuity uses to identify safe places to resume and end capture. This is required for Application Continuity and is recommended for Transparent Application Continuity.

Transparent Application Continuity, in addition, will discover request boundaries if a pool is not in use or when replay is disabled. The conditions for discovering a boundary are:

  • No open transaction
  • Cursors are returned to the statement cache or cancelled
  • No un-restorable session state exists (refer to Clean Session State between Requests in this paper)

Enable Mutables Used in the Application

Mutable functions are functions that can return a new value each time they are executed. Support for keeping the original results of mutable functions is provided for SYSDATE, SYSTIMESTAMP, SYS_GUID, and sequence.NEXTVAL. If the original values are not kept and different values are returned to the application at replay, replay is rejected.

If you need mutables for PL/SQL, issue GRANT KEEP as required.

For example:

SQL> GRANT KEEP DATE TIME to adb_user;
SQL> GRANT KEEP SYSGUID to adb_user;
SQL> GRANT KEEP SEQUENCE mySequence to adb_user on mysequence.myobject;

Side Effects

When a database request includes an external call such as sending MAIL or transferring a file then this is termed a side effect.

Side effects are external actions, they do not roll back. When replay occurs, there is a choice as to whether side effects should be replayed. Many applications choose to repeat side effects such as journal entries and sending mail as duplicate executions cause no problem. For Application Continuity side effects are replayed unless the request or user call is explicitly disabled for replay. Conversely, as Transparent Application Continuity is on by default, TAC does not replay side effects. The capture is disabled, and re-enables at the next implicit boundary created by TAC.

Developer Best Practices for Continuous Availability

Follow these best practices to code for continuous availability on Autonomous Database.

Return Connections to the Connection Pool

The most important developer practice is to return connections to the connection pool at the end of each request. This is important for best application performance at runtime, for draining work and for rebalancing work at runtime and during maintenance, and for handing failover events. Some applications have a false idea that holding onto connections improves performance. Holding a connection neither performs nor scales.

Clean Session State between Requests

It is best practice to clean session state between database requests.

When an application returns a connection to the connection pool, cursors in FETCH status, and session state set on that session remain in place unless an action is taken to clear them. If your application is setting state, it is best practice to return your cursors to the statement cache and to clear application related session state to prevent leakage to later re-uses of that database session. Cleaning your session state ensures that TAC can discover boundaries.

To automatically clean your state between requests with Oracle Database 23ai, set the service attribute RESET_STATE=LEVEL1. Doing this will avoid state leakage and fetching from cursors by later usage of the connection pool.

If you are using Oracle Database 19c, use DBMS_SESSION.RESET_PACKAGE to clear PL/SQL global variables, use TRUNCATE to clear temporary tables, SYS_CONTEXT.CLEAR_CONTEXT to clear context and cancel your cursors by returning them to the statement cache.

If your application is stateless, such as REST, APEX, Microservice, and most web applications, it is best practice to use RESET_STATE.

Do not embed COMMIT in PL/SQL and Avoid Commit on Success and Autocommit

It is recommended practice to use a top-level commit, (OCOMMIT or COMMIT() or OCITransCommit). If your application is using COMMIT embedded in PL/SQL or AUTOCOMMIT or COMMIT ON SUCCESS, it may not be possible to recover following an outage or timeout. PL/SQL is not reentrant. Once a commit in PL/SQL has executed, that PL/SQL block cannot be resubmitted. Applications either need to unpick the commit which is not sound as that data may have been read, or for batch use a checkpoint and restart technique. When using AUTOCOMMIT or COMMIT ON SUCCESS, the output is lost.

If your application is using a top-level commit, then there is full support for Transparent Application Continuity (TAC), Application Continuity (AC), and TAF Select Plus. If your application is using COMMIT embedded in PLSQL or AUTOCOMMIT or COMMIT ON SUCCESS, it may not be possible to replay for cases where that the call including the COMMIT did not run to completion.

Use ORDER BY or GROUP BY in Queries

Application Continuity ensures that the application sees the same data at replay. If the same data cannot be restored, Application Continuity will not accept the replay. When a SELECT uses ORDER BY or GROUP BY order is preserved. In Autonomous Database the query optimizer most often uses the same access path, which can help in the same ordering of the results. Application Continuity also uses an AS OF clause to return the same query results where AS OF is allowed.

Considerations for SQL*Plus

SQL*Plus is often our go to tool for trying things out. SQL*Plus of course does not reflect our actual application that will be used in production, so it is always better to use the real application test suite to test your failover plan and to measure your protection. SQL*Plus is not a pooled application so does not have explicit request boundaries. Some applications do use SQL*Plus for example for reports. To use SQL*Plus with failover check the following:

  1. FAN is always enabled for SQL*Plus. Use the recommended connect string that auto-configures ONS end points for you.

  2. When using SQL*plus the key is to minimize round trips to the database: https://blogs.oracle.com/opal/sqlplus-12201-adds-new-performance-features

  3. SQL*Plus is supported for TAC starting with Oracle Database 19c. For best results set a large arraysize. For example (set arraysize 1000). Avoid enabling serveroutput as this creates unrestorable session state.