How to Make Application Continuity Most Effective in Oracle Database 23c
Contributors: Veronica Dumitriu & Nancy Ikeda
High availability allows applications to immediately react to instance, node, or database failures, and quickly establish new connections to surviving database instances.
Oracle Database and client drivers provide out-of-the-box features for Application High Availability. We encourage you to review the high-availability levels before proceeding reading further:
- Level 1: Basic Application High Availability — the downtime is minimized for unplanned and planned outages
- Level 2: Prepare Applications for Planned Maintenance — adds session draining configuration for minimal application impact during planned maintenance
- Level 3: Mask Unplanned and Planned Failovers from the applications and handle timeouts and outages
This article will focus on the most comprehensive high-availability support available in Oracle Database 23c.
Managing Planned and Unplanned Outages
For planned maintenance, Oracle recommends that you drain database sessions by moving a connection from one instance to another available instance over a controlled period, this being the safest way to migrate work without interrupting applications.
To support recovery from unplanned outages, Oracle introduced application continuity.
Application Continuity (AC)
Application Continuity maintains the continuity of database sessions during planned or unplanned outages, starting with Oracle Database 12.1 for JDBC thin applications, and Oracle Database 12.2.0.1 for OCI and ODP.NET applications with support for open-source drivers, such as Node.js, and Python, beginning with Oracle Database 19c.
Application Continuity rebuilds and recovers the session from a known point, which includes session states and transactional states, and then it replays all interrupted in-flight work (if it is already committed, it is not replayed). When the replay is complete, the results are returned to the application as if no interruption occurred.
Application Continuity is recommended for OLTP applications using an Oracle connection pool. It is enabled on the database service through which the application is connecting to the database.
Transparent Application Continuity (TAC)
Starting with Oracle Database19c, TAC automatically tracks and records the session and transactional states, so the database session can be recovered and rebuilt following recoverable outages. This is done with no reliance on application knowledge or application code changes, allowing you to enable TAC for your applications.
Application transparency and failover are achieved by consuming the state-tracking information that captures and categorizes the session state usage, as the application issues calls to the database. Set FAILOVERTYPE to AUTO on your service.
If you are not using an Oracle connection pool (as with SQL*Plus), or you do not have knowledge about the application, then enable TAC on your database service.
TAC works well for typical OLTP applications comprised of short database requests with little to no reliance on the state persisting after a request is completed.
Planned Failover with AC and TAC
Planned failover is failover that is invoked by the Oracle Database at points where the database decides that a session is replayable and is expected not to drain.
Planned failover is enabled by default when using AC or TAC. It improves situations where other draining methods are not active, for example, because FAN or connection tests are not configured.
Planned failover expedites maintenance by failing over early when replay is enabled.
For example, planned failover with TAC is the maintenance solution used with SQL*Plus.
Database Request
A database request is a unit of work submitted from the application. It typically corresponds to the SQL and PL/SQL, database RPC calls, and local client-side calls of a single web request executed sequentially on a single database connection. It is generally demarcated by the calls made to check out and check in the database connection from a connection pool. After a database request is completed, the session is considered stateless and session history may be discarded.
AC and TAC use database requests as units of replay and may ignore earlier work on the same session. This improves coverage by allowing a purge of committed transactions, long sequences of calls, and recreateable non-transactional states. Following a recoverable error, AC re-establishes the database session and repeats an uncommitted database request safely.
Request Boundaries
Request boundaries are tags that mark the beginning and end of a database request and may be explicit (achieved by a check-out and check-in from an Oracle pool) or implicit (inferred by the client driver based on its knowledge of the session recoverability at a given point in time).
Implicit boundaries are a key feature of TAC because these extend protection to applications that need support for planned and unplanned outages, but do not use an Oracle pool or cannot be modified to use an Oracle-provided connection health check. This higher level of protection is particularly important when accessing services provided in our public cloud.
As of Oracle Database 21c, the OCI client drivers may inject an implicit boundary for a session. From the earliest support for AC in the Oracle Call Interface (OCI), applications have been required to use the OCI Session Pool to establish the explicit request boundaries needed for the highest level of protection by AC. To increase the level of protection TAC affords, we extended the benefits of implicit boundaries to cover application programs that may have one or more cursors open outside of transactional work, a characteristic of many batch processes.
In Oracle Database 21c, applications connected to a TAC service cannot rely on an implicit request boundary to purge the transactional work because the outer cursor remains open even after the transactional work is committed. Without an implicit request boundary, the commit disables replay until the next explicit request boundary.
Use Cases
- Applications with no request boundaries connect to a TAC service and rely on the built-in session state classification layer along with the driver’s knowledge of the application state to find implicit request boundaries to provide better protection should an outage occur.
- Applications with custom connection pools: OCI currently relies on the use of the OCI Session Pool to provide explicit request boundaries. By decoupling explicit request boundaries from the OCI Session Pool, our drivers can leverage Application Continuity to provide high levels of protection from planned and unplanned outages while utilizing their native pools.
A new feature in Oracle Database 23c was to decouple the outer cursors from implicit request boundaries. As such, client drivers are free to purge their replay queue once the inner transaction commits and to continue fetching from the outer cursor. This allows the outer cursor to be failed over, following an outage. In addition to the benefits for long-running cursors, OCI-based applications will facilitate the adoption of Application Continuity to establish explicit request boundaries without the use of the OCI Session Pool by using two new calls: OCIRequestBegin() and OCIRequestEnd(). These new calls are used to demarcate the beginning and end of a database request.
When Is the Application Continuity Most Effective
Application Continuity in OCI is most effective under the following conditions:
- An application can mark the beginning and end of an application request, either explicitly (calling
OCIRequestBegin()andOCIRequestEnd()) or implicitly through the use of an OCI session pool. - The database service specifies the
COMMIT_OUTCOMEattribute and failover type asTRANSACTION.
These OCIRequest calls can be used in an application framework, for example, a custom connection pool that has its interface for connection check out and check in. At connection check out, make a call to OCIRequestBegin() to note the start of a request. OCIRequestBegin() is sensitive to planned maintenance notifications and will failover to a surviving instance if required.
OCIRequestBegin()
The OCIRequestBegin() performs the following two tasks:
- Validate the connection and session embedded in the service context. If validation fails, this call will create a brand-new connection and session using the same handles.
To validate the connection, OCIRequestBegin()internally checks the attribute OCI_ATTR_SERVER_STATUS, a lightweight connection health check that is sensitive to inband notifications and Fast Application Notification (FAN) event notifications.
- Inband notifications are sent by the server before the start of planned maintenance directing clients to start proactively draining connections when it will be least disruptive.
- FAN notifications are sent by Oracle Clusterware for both planned and unplanned outages of databases, networks, and nodes.
The validation fails if the connection has received either type of notification. The session is validated by calling OCIPing() based on the setting of OCI_ATTR_PING_INTERVAL.
2. Establish the start of a database request and an explicit request boundary.
The explicit request boundary is communicated to non-client side pools such as DRCP and PRCP, if configured. In turn, these pools may check out a new session to be used for the duration of the request.
When the connection is to a TAC or AC service, OCIRequestBegin()is an alternative to OCISessionGet(), for applications that cannot use the OCI session pool. The OCI calls made between OCIRequestBegin()and OCIRequestEnd() are captured at runtime for possible replay by AC, if a recoverable error occurs.
The call has the following syntax:
sword OCIRequestBegin (OCISvcCtx *svchp, OCIError *errhp, ub4 mode)where:
- svchp (IN/OUT) is the OCI service context handle.
- errhp (IN/OUT) is an error handle that you can pass to
OCIErrorGet()for retrieving diagnostic information if an error occurs. - mode (IN) is the
OCI_DEFAULTmode in which OCI will perform an implicit connection health check by checkingOCI_ATTR_SERVER_STATUSfor the server handle embedded in the service context. Based on the setting ofOCI_ATTR_PING_INTERVAL, this call may internally invokeOCIPing()to validate the session embedded in the service context. OCI will attempt to create a brand-new connection and session if either fails validation.
Note: to check if OCIRequestBegin() created a brand new connection and session, an application can check the service context attribute OCI_ATTR_SESSGET_FLAGS for a value of OCI_SESSGET_FLAGS_NEW.
OCIRequestBegin() may return an error in the following situations:
- If called when a database request is already active
- If called with a pooled service context obtained by
OCISessionGet() - If a connection and/or session fails validation and cannot be replaced with a brand-new connection and session.
OCIRequestEnd()
The OCIRequestEnd() indicates that a database request completed and it should be used only when the active database request was started with a call to OCIRequestBegin().
Calls that had been captured for possible replay are purged. State not permitted to span explicit request boundaries including open cursors and temporary LOBs that have been returned to the client are canceled or freed. If an open transaction is present, it is rolled back.
Upon the completion of the call:
- The connection must not be used in OCI calls until the next
OCIRequestBegin()call is completed. - States such as temp tables and the sequence
CURRVALsshould not be used in any future database requests. - Any DRCP and PRCP sessions used by the request may be released back to their respective pools.
The call has the following syntax:
sword OCIRequestEnd (OCISvcCtx *svchp, OCIError *errhp, ub4 mode)where:
- svchp (IN/OUT) is the OCI service context handle.
- errhp (IN/OUT) is the error handle that you can pass to
OCIErrorGet()for retrieving diagnostic information if an error occurs. - mode (IN) is the mode
OCI_DEFAULT. If an open transaction is present, it is rolled back. This implicit rollback may result in failover if the connection and/or session are not healthy.
OCIRequestEnd() will return an error in the following scenarios:
- If there is no active request initiated by
OCIRequestBegin(). - If connected to an AC or TAC service and the presence of an open transaction triggers a failover that is not successful.
- If called with a pooled service context obtained by
OCISessionGet()
Notes:
- A call to
OCIRequestEnd()can incur a round trip if pending state changes exist that have not yet been sent to the server. - After
OCIRequestEnd()completes, it is an error to reference the connection in OCI calls until afterOCIRequestBegin()is called to initiate the next database request.
New Service Parameter Value for SESSION_STATE_CONSISTENCY
The service parameter SESSION_STATE_CONSISTENCY currently supports values of DYNAMIC and AUTO. Currently, when FAILOVER_TYPE is AUTO, SESSION_STATE_CONSISTENCY must also be AUTO, meaning the session state is completely managed by the client driver and the database server. Unexpected state changes may be flagged as errors if an outage occurs.
A new value HYBRID was introduced in Oracle Database 23c which is valid only if the FAILOVER_TYPE is AUTO. When SESSION_STATE_CONSISTENCY is set to HYBRID at the service level (or override by service context handle in client side to HYBRID), all cursors (which are select and not select for update) executed when no transaction is in progress and when session state is client restorable or template restorable (no server restorable and no unrestorable) are considered resumable, and no internal operator check is done.
Application developers are encouraged to use ACCHK to analyze cursors used by the application before using this new value.
Example
Let’s walk through an example that integrates a resumable cursor with the new OCIRequest calls.
- If the service context has been idle for 2 minutes or more,
OCIRequestBegin()validates the session before starting a database request. - The weekly order_id cursor is opened before any transactional work is done and therefore remains open across the transactional work done for each order.
- The reference to
SYS_CONTEXTin the order_id cursor prevents identifying the cursor as resumable by default. TheSESSION_USERis known to be stable for the life of the cursor, so the service context attributeOCI_ATTR_SESSSTATE_CONSISTENCYis set toOCI_SESSSTATECONS_HYBRIDbefore the SELECT statement is parsed. - Because the order_id cursor is identified as resumable, an implicit request boundary is possible after each COMMIT so that replay does not disable.
/* Get connection (service context) from a custom pool */
/* Update the ping interval to 2 minutes */
sb2 pingInterval = 120;
OCIAttrSet(. . svchp, &pingInterval, (sb2 *)0, OCI_ATTR_PING_INTERVAL, . . .);
OCIRequestBegin(svchp, errhp, OCI_DEFAULT);
OCIAttrGet(. . . OCI_ATTR_SESSGET_FLAGS, . . .);
if (flagsValue == OCI_SESSGET_FLAGS_NEW)
applySessionUpdates(svchp, errhp);
OCIAttrSet(. . . OCI_ATTR_MODULE, . . .); /* set module to “Weekly order job” */
/* parse and execute a resumable cursor;
which gets the orders from last week updated by the logged on user */
OCIStmtPrepare2(svchp, &stmhp,
SELECT order_id FROM orders WHERE order_date > sysdate–7 AND
updated_by = SYS_CONTEXT(‘USERENV’,’SESSION_USER’)
ORDER BY order_id);
OCIStmtExecute(svchp, stmhp, . . .)
while (TRUE)
{
status = OCIStmtFetch(stmhp, . . .); /* order_id cursor stays open after COMMIT */
if (status == OCI_NO_DATA || status == OCI_ERROR)
break;
OCIAttrSet(. . . OCI_ATTR_ACTION, . . .); /* set action for the order txn */
OCIStmtExecute(. . ., “ALTER SESSION SET CURRENT_SCHEMA= “, . . .);
[Perform transactional work for one order]
COMMIT; /* this commit does not disable replay */
}
OCIRequestEnd(svchp, errhp, OCI_DEFAULT);
/* Return connection (service context) to custom pool */High Availability Best Practices
The introduction of OCI calls to mark the start and end of a request allows application developers to enable a custom connection pool to behave like an Oracle pool. At check out, make a call to OCIRequestBegin() and at check-in, make a call to OCIRequestEnd().
Recommended Option: Use an Oracle Connection Pool
Click here to read about the benefits of using an Oracle Connection Pool.
Using a FAN-aware Oracle connection pool is the recommended solution for managing planned maintenance.
Oracle pools provide full lifecycle management: draining, reconnecting, and rebalancing across nodes and sites. As the maintenance progresses and is completed (for each instance or node), sessions are moved and rebalanced across instances. There is no impact on users when your application uses an Oracle Pool with FAN and returns connections to the pool between requests.
Supported Oracle Pools include:
- Universal Connection Pool (UCP)
- WebLogic Active GridLink
- Tuxedo
- OCI Session Pool
- ODP.NET Managed and Unmanaged providers
- Oracle Session Pool for Python
When using these pools, no application changes are needed other than ensuring that your connections are returned to the pool between requests.
Learning Resources
- If you’d like to learn more about the use of
OCIRequestBegin()andOCIRequestEnd()calls, refer to the OCI Developer’s Guide and the RAC Admin Guide. - If you’d like to learn more about how to prepare applications for planned maintenance, refer to Prepare Applications for Planned Maintenance.
