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.
- Connect Using Database Services with Application Continuity Enabled
- Use Recommended Practices That Support Draining
On Autonomous Database there is never a need to restart application servers when planned maintenance follows best practice. - Steps for Using Application Continuity
Perform these steps to use Application Continuity: - Developer Best Practices for Continuous Availability
Follow these best practices to code for continuous availability on Autonomous Database.
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 Connection Tests with Thin Java Driver
- Use Connection Tests with OCI Driver
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
.
Parent topic: Use Recommended Practices That Support Draining
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 */
Parent topic: Use Recommended Practices That Support Draining
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");
Parent topic: Use Recommended Practices That Support Draining
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:
-
FAN is always enabled for SQL*Plus. Use the recommended connect string that auto-configures ONS end points for you.
-
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
-
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.