Managing Planned Maintenance

To minimize service disruption to application users, Oracle Real Application Clusters (Oracle RAC) provides interfaces that relocate, disable, and enable services.

About Planned Maintenance Management

For repairs, upgrades, and changes, drain your sessions before stopping or relocating the services.

When you relocate a service, you indicate the service should run on another instance. When a service is stopped or relocated, FAN is published with a planned reason code, typically reason=user. Once you complete the operation, you can return the service to normal operation or enable the service and then restart it. When a service restarts, FAN is published with UP status codes.

Due to dependencies, if you manually shut down your database, then all of your services for that database automatically stop. If you want your services to automatically start when you manually restart the database, then you must set the management policy of the service to automatic. If you want to shut down only one instance of the database and you want the service to remain offered, then you can either relocate the service using srvctl relocate service or stop the instance using srvctl stop instance with the -failover option, which enables the service to automatically failover according to its failover policy.

In either case, Oracle recommends that work running under the service drain at request boundaries. The drain interval is specified as an attribute of the service or you can provide a drain interval on the SRVCTL command line.

Monitoring Draining and Failover Readiness with the MAA Client Dashboard

Use the MAA Client Dashboard and advisories to determine whether application sessions are ready to drain, reconnect, or use planned failover during planned maintenance, load balancing, and other high availability operations.

The Oracle Maximum Availability Architecture (MAA) Client Dashboard uses client high availability data collected at runtime to show whether sessions are ready for draining or failover. The dashboard helps DBAs identify sessions that can safely drain, sessions that can use planned failover, and sessions that require corrective action before maintenance proceeds. This information helps reduce application disruption during maintenance and load balancing operations.

The dashboard collects high availability information at runtime from supported clients. Client drivers send drain-point information and high availability key-value pairs to the server. A drain point is a point in the application workflow where a connection can be released without interrupting application processing. Request boundaries and connection tests are examples of drain points. Oracle RAC can make better decisions about draining, planned failover, load balancing, live migration, and related operations when the server has visibility into drain points.

The server exposes client high availability information through dynamic performance views, advisories, and Automatic Workload Repository (AWR) Client High Availability Health information. The following views provide client high availability readiness and request-duration information:

GV$CLIENT_HA_READINESS : Use this view to determine whether sessions are ready to drain, reconnect, or use planned failover.
GV$CLIENT_REQUEST_HISTOGRAM : Use this view to evaluate request-duration distributions when planning maintenance operations and load-balancing activities.
GV$SERVICE_DRAIN_TIMEOUT_ADVICE : Use this view to determine whether the configured drain timeout is appropriate for the workload.

Use the MAA Client Dashboard and advisories to identify:

Sessions that are ready to drain or have drain points.
Sessions that are ready for planned failover.
Clients that are not using a user-defined service.
Clients that must update drivers, enable FAN, use a high availability connect string, or return connections to the pool.

At the service level and in AWR, health advisories show how many sessions comply with each Client High Availability MAA level. At the session level, health advisories use row data for each MAA level and report the results as advice codes or decoded advice.

The MAA Client Dashboard also supports server-side features that require draining and failover readiness information, including planned maintenance, automatic load balancing, planned failover, live migration, load dispersion, failback, and unplanned failover.

Example: Viewing Client High Availability Readiness by Session

In this example, the query shows client high availability readiness information by service, session, client version, client driver, connection pool, FAN status, and in-band notification status. Use this information to review request activity, average elapsed time, and protected request time before planned maintenance.

SQL> SELECT
c.SERVICE_NAME,
s.SID, s.SERIAL#,
s.CLIENT_VERSION,
s.CLIENT_DRIVER,
c.CONNECTION_POOL,
c.FAN_READINESS,
c.INBAND_NOTIFICATION,
SUM(c.CLIENT_REQUESTS_COMPLETED_COUNT) AS TOTAL_REQUESTS,
SUM(c.CLIENT_REQUESTS_ELAPSED_TIME) / 1000 /
NULLIF(SUM(c.CLIENT_REQUESTS_COMPLETED_COUNT), 0) AS AVG_ELAPSED_TIME,
AVG(c.PERCENT_PROTECTED_TIME_IN_REQS) AS PROTECTED_RATIO
FROM GV$CLIENT_HA_READINESS c
JOIN GV$SESSION_CONNECT_INFO s
ON c.INST_ID = s.INST_ID
AND c.SID = s.SID
GROUP BY
c.SERVICE_NAME,
s.SID,
s.SERIAL#,
s.CLIENT_VERSION,
s.CLIENT_DRIVER,
c.CONNECTION_POOL,
c.FAN_READINESS,
c.INBAND_NOTIFICATION ORDER BY c.SERVICE_NAME,
s.SID;

The following sample output shows sessions that use UCP, receive FAN and in-band notifications, and have high protected request time.

Avg Elapsed  Protected
Service      SID      Serial#  Client Version   Client Driver           Conn Pool     FAN Ready   Inband Notif   Requests       (ms)         %
-----------  -------  -------  ---------------  ----------------------  ------------  ----------  ------------   ---------  ------------  ---------
acperf23svc  15       57026    26.1.0.24.0      jdbcthin : 26.1.0.24.0  UCP           READY       READY          2,117      6.772         99.60
acperf23svc  18       40374    26.1.0.24.0      jdbcthin : 26.1.0.24.0  UCP           READY       READY          1,805      8.162         99.68
acperf23svc  1247     8172     26.1.0.24.0      jdbcthin : 26.1.0.24.0  UCP           READY       READY          1,797      5.993         99.57
acperf23svc  1249     57167    26.1.0.24.0      jdbcthin : 26.1.0.24.0  UCP           READY       READY          1,667      8.971         99.72
acperf23svc  1250     60181    26.1.0.24.0      jdbcthin : 26.1.0.24.0  UCP           READY       READY          2,107      6.148         99.60
acperf23svc  2487     15612    26.1.0.24.0      jdbcthin : 26.1.0.24.0  UCP           READY       READY          1,520      8.137         99.69
acperf23svc  2489     9372     26.1.0.24.0      jdbcthin : 26.1.0.24.0  UCP           READY       READY          1,473      10.192        99.64
acperf23svc  3714     53782    26.1.0.24.0      jdbcthin : 26.1.0.24.0  UCP           READY       READY          2,243      6.189         99.61
acperf23svc  3715     62404    26.1.0.24.0      jdbcthin : 26.1.0.24.0  UCP           READY       READY          1,730      7.977         99.62
acperf23svc  3717     7568     26.1.0.24.0      jdbcthin : 26.1.0.24.0  UCP           READY       READY          2,168      6.810         99.60

In this output, you can compare request activity, average elapsed time, connection-pool usage, FAN readiness, in-band notification status, and protected time across services and client sessions. Sessions with high request activity, ready FAN and in-band notification status, and high protected time are stronger candidates for planned maintenance operations that require protected failover behavior. Sessions with lower protected time or incomplete readiness may require further review before maintenance proceeds.

Drain Timeout Recommendations for Planned Maintenance

Intelligent Drain Timing predicts when application work is likely to complete and helps guide service-draining decisions during planned maintenance.

Intelligent Drain Timing has two distinct purposes:

It provides advisory information that helps DBAs evaluate whether the configured drain timeout is appropriate for a service.
It automatically determines whether a protected session should continue draining or initiate planned failover when the session cannot complete within the available drain window.

Oracle RAC supports service draining through the DRAIN_TIMEOUT service attribute and the -drain_timeout option of SRVCTL commands. Intelligent Drain Timing complements these settings by evaluating current and historical request-duration information to determine whether active work is likely to complete before the drain window expires.

Oracle RAC records request-duration information for services in the Client Request Histogram. Intelligent Drain Timing uses this information, together with the duration of active requests, to estimate whether a session is likely to drain within the available time. DBAs can use these estimates to validate an existing drain timeout or choose a more appropriate value for a service.

Use Intelligent Drain Timing to answer questions such as:

What drain window is appropriate for this service?
Are active sessions likely to drain within the configured drain timeout?
Is the current workload likely to complete before the drain window expires?
Should maintenance be scheduled during periods with shorter request durations?

Note:

Intelligent Drain Timing provides both advisory information and automatic decision making. Use request-duration statistics, histograms, and drain-timeout recommendations to evaluate and configure an appropriate drain timeout for a service. When a protected session cannot drain within the available drain window, Oracle RAC automatically determines whether to initiate planned failover. No additional user action is required for this decision.

For sessions protected by Application Continuity, Transparent Application Continuity, or Transparent Application Failover, Oracle RAC automatically decides whether to continue draining the session or to initiate planned failover. This decision is based on the remaining drain window and the failover readiness of the session. The customer does not need to take any additional action for this automatic behavior.

If active work is likely to complete within the drain window, continue with normal service draining. If active work is unlikely to complete within the drain window, review the drain-timeout recommendations and planned failover readiness before proceeding with maintenance.

Example: Reviewing Request-Duration Patterns by Service

The GV$CLIENT_REQUEST_HISTOGRAM view shows the distribution of completed request durations for each service. Use this information to understand whether most requests complete within the expected drain window.

For example:

Service    <1s     <5s     <10s    <30s    <60s    <120s   <180s   <300s   <600s   <1800s  <3600s  <7200s  <14400s  >14400s
---------  ------  ------  ------  ------  ------  ------  ------  ------  ------  ------  ------  ------  -------  -------
TPURGENT   11,000   2,343   9,898   4,900   9,090      50     250      45       3     500      10       1        0        0
TP          9,878  30,000  11,000   1,900   1,200     200     900      50      20       2       0       0        0        0
HIGH           30      50      45     333      70     700      23      33      43       8       2       1        0        0
MEDIUM         13      23      33      43      53     900   1,200     200     900      45      27      35        0        0
LOW            14      24      34      44      54      40      50      89      79       9       8       2        2        0

In this example, the TP and TPURGENT services have many requests that complete within shorter duration buckets, so they may be better candidates for shorter drain windows. Services with more requests in longer duration buckets may require longer drain windows, closer scheduling review, or planned failover for protected sessions that cannot drain within the available window.

Automatic Planned Failover During Intelligent Drain Timing

If a protected session is unlikely to drain before the configured drain timeout expires, Oracle RAC evaluates the session for planned failover. When the session is protected by Application Continuity, Transparent Application Continuity, or Transparent Application Failover, and the failover readiness requirements are met, Oracle RAC initiates planned failover instead of waiting for the drain timeout to expire.

This automatic decision helps bound maintenance windows, reduce forced session termination when the drain timeout expires, and minimize replay time for protected applications.

Intelligent Drain Timing helps determine whether an active request is likely to complete within the remaining drain window. If the request is unlikely to complete and failover readiness requirements are met, Oracle RAC initiates planned failover automatically.

Planned Maintenance Without User Interruption

Follow the planned maintenance best practices to avoid the need to restart your application servers.

For planned maintenance, the recommended approach is to provide time for current work to complete before maintenance is started. You do this by draining the current work. Oracle provides several methods for draining. Choose the one that best suits your application:

Oracle Connection Pools
Standard driver-side connection tests
Server-side connection tests and more
Planned failover with Transparent Application Continuity

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

The service attributes -drain_timeout and -stopoption control the drain time period, and then how the service manages sessions that have not completed once this time period expires. Requests that complete and then check back in to the pool or close, can be directed to a new location that is not affected by the planned maintenance.

Application Continuity provides additional cover, giving continuous service for those requests that do not complete within the allotted drain time. Using any FAN-aware pool allows sessions to drain at request boundaries after receipt of the FAN planned DOWN event.

Because not every application uses an Oracle connection pool and not every application is FAN-aware, the database inspects sessions during planned maintenance seeking safe places to stop a session so that the application is not disrupted. After stopping the service, the database looks for safe places where the connection can be closed. When the connection is closed, the database cleans up the session.

Stopping a session at a safe place enables the application to open a new connection with the states that it needs. Draining sessions may take a period of work to flow through each session. There is no requirement that closing a session is immediate, rather the close must occur at a safe place that exposes no errors to the application, and, preferably, before the drain timeout period has expired.

Requests are far more important than transactions because they enable the issued work to complete. For draining requests, the Oracle Universal Connection Pool uses the drain timeout to gradually drain, which prevents an overload of logins on the instances drained, by slowly releasing the original sessions across the time period rather than all at once. Gradual draining has the benefit of not disturbing the other work ongoing at the target instances.

Both DRAIN_TIMEOUT and STOP_OPTION are service attributes that you can define when you add the service or modify it after creation. You can also specify these attributes using SRVCTL, which will take precedence over what is defined on the service. You can specify the -drain_timeout and -stopoption parameters when using the following SRVCTL commands:

srvctl add service
srvctl modify service
srvctl relocate service
srvctl stop service
srvctl stop database
srvctl stop instance
srvctl stop pdb
srvctl relocate pdb

To manage planned maintenance without user interruption:

Use SRVCTL to relocate a singleton service or a service not running on all nodes. Use the -force flag with the previously listed SRVCTL commands, except add and modify. You must use the -force flag if you specify the -stopoption parameter on the command line when you run either srvctl relocate service or srvctl stop service. For example:
```
$ srvctl relocate service -db mycdb01 -service myservice -drain_timeout 120
  -stopoption IMMEDIATE -oldinst mycdb01_01 -force
```
The preceding command relocates the service named myservice01 from the instance named mycdb01_01 to any instance on which it is configured to run. Oracle Clusterware chooses the instance if you do not specify a target on the command line, and waits two minutes (in this example) for any active sessions to drain, after which any sessions remaining on mycdb01_01 are forcibly disconnected. The connection pool automatically releases a connection at a request boundary.

Note:

If the service you want to relocate is a uniform service that is currently running on all nodes, then the preceding command returns an error, unless the service is not up on all instances, in which case the preceding command example would succeed for a uniform service.
The FAN planned DOWN event clears idle sessions from the connection pool immediately and marks active sessions to be released at the next check-in. These FAN actions drain the sessions from the instance without disrupting the users.
Existing connections on other instances remain usable, and new connections can be opened to these instances if needed. The database also marks the sessions to drain. The database looks for connection tests and, in Oracle Database 19c and later, for safe places to failover. An implicit connection boundary with Transparent Application Continuity is such a place.
Not all sessions, in all cases, will check their connections into the pool. Oracle recommends, as a best practice, to have a timeout period (by setting the -drain_timeout parameter), after which the instance is forcibly shut down or the service stopped, evicting any remaining client connections.
After the drain interval expires, the -stopoption parameter is implemented, which you can define against a service or a database, as follows:
- When stopping a service (srvctl stop service), you can specify one of the following stop options using the -stopoption parameter: TRANSACTIONAL or IMMEDIATE
- When stopping a database (srvctl stop database), you can specify one of the following stop options using the -stopoption parameter: NORMAL, TRANSACTIONAL, IMMEDIATE, or ABORT
The database stop options correlate to the service stop options, as follows:
- NORMAL=NONE
- TRANSACTIONAL/TRANSACTIONAL LOCAL=TRANSACTIONAL
- IMMEDIATE/ABORT=IMMEDIATE
For those services that are configured to use Application Continuity, an attempt is made to recover these remaining sessions, after they are terminated, masking the outage from users and applications.
Once maintenance is complete, restart the instance and the services on the original node.
The FAN UP event for the service informs the connection pool that a new instance is available for use, allowing sessions to be created on this instance at next request boundaries.

Managing a Group of Services for Maintenance

With Oracle Real Application Clusters (Oracle RAC), you can use SRVCTL to manage groups of services in your cluster.

Stopping a Group of Services Example

See how you can use SRVCTL to stop services by node name, database name, Pluggable Database name, or instance name.

Many enterprises run a large number of services, whether it be many services offered by a single database or instance, or many databases offering a few services running on the same node. You no longer need to run SRVCTL commands for each individual service but need only specify the node name, database name, Pluggable Database name, or the instance name for all affected services.

For example, if you want to stop all of the services running on a node called racnode01, then you can use the following command:

$ srvctl stop service -node racnode01 -drain_timeout 60 -stopoption IMMEDIATE

The command stops all services running on racnode01, allowing a drain interval of 60 seconds. After 60 seconds, any remaining sessions are stopped immediately. The 60-second drain timeout interval overrides any attribute setting on any of the services.

The command can also be qualified to stop the databases on a node, as in the following example:

$ srvctl stop instance 	-node racnode01 -drain_timeout 60 -stopoption TRANSACTIONAL LOCAL -failover -force

When you specify the -failover parameter:

All services are relocated, if possible, respecting the drain timeout interval and the stop option specified.
Any services that cannot be failed over are stopped, using the stop option specified.
Wait for the length of the drain timeout interval or until all sessions for targeted services are removed, whichever is sooner.
All instances stop according to the stop option specified.

When you specify the -stopoption TRANSACTIONAL LOCAL parameter:

Remaining services stop according to the drain timeout interval and stop option specified.
Wait for the length of the drain timeout interval or until all sessions for targeted services are removed, whichever is sooner.
The instance stops using the TRANSACTIONAL LOCAL stop option.

Starting Services

You can use the srvctl start service command to start all services on a node, all services offered by a database, or all services offered by a Pluggable Database.

To start services, you can also supply a list of services (a subset of all services) to the srvctl start service command that you want to start. Additionally, you can provide a node restriction, used in conjunction with the database option, for all services that can be started on a particular node. You can restrict the srvctl start service command to start only the parallel query service by specifying the -pq parameter.

The following examples illustrate how you can start services:

To start all of the services offered by a single Pluggable Database:

$ srvctl start service -db myRACCDB01 -pdb myPDB01 -startoption OPEN

To start all services on a given database and any of its Pluggable Databases:

$ srvctl start service -db myRACDB

To start a list of services on a given database, regardless of any Pluggable Database with which they are associated:

$ srvctl start service -db myRACDB -service "myFirstService,mySecondService,myThirdService"

To start all services for a database that can run on a given node:

$ srvctl start service -d myRACDB -node racnode01

Pluggable Database-Level Operations

You can use SRVCTL to manage services on Pluggable Databases.

To start all services for a Pluggable Database, for all instances or a single instance:

$ srvctl start service -db db_name -pdb pdb_name [-instance instance_name]

To stop all services for a Pluggable Database, for all instances or a single instance:

$ srvctl stop service -db db_name -pdb pdb_name [-node node_name | -instance 
   inst_name] [-stopoption stop_option] [-drain_timeout timeout]
   [-force [-noreplay]]

Note:

The -pdb pdb_name parameter is optional. If you omit the Pluggable Database name, then the operation occurs for the entire Container Database (all Pluggable Databases within this container).

Relocating Services

You can use the srvctl relocate service command to relocate services to a target destination, which can be an instance, a node, or a database.

In the following command examples, all services are relocated from the named database, Pluggable Database, instance, or node. The services will only relocate if the target can support that service, as defined by the service configuration. Any services that cannot be relocated remain at the original location. A placement error is recorded against any services that could not be relocated, or were already running at the new target. Services that fail to relocate remain running at their original location, and any sessions remain active.

$ srvctl relocate service -db myRACCDB -oldinst RACCDB_01 -newinst RACCDB_03
  -drain_timeout 30 -stopoption immediate

$ srvctl relocate service -db myRACCDB -pdb myPDB01 -currentnode racnode01
  -targetnode racnode02 -drain_timeout 30 -stopoption immediate

The relocate operation starts the service in the new location before stopping the service in its existing location.

If you do not specify a target destination, then Oracle Clusterware relocates all services or specific services from the specified database, Pluggable Database, instance, or node, as in the following examples:

$ srvctl relocate service -db myRACCDB -service "myService01,myService02"
  -drain_timeout 30 -stopoption immediate

$ srvctl relocate service -db myRACCDB -pdb myPDB01 -drain_timeout 30
  -stopoption transactional

If there is no valid target available, then the service remains at the original location and the sessions remain active. You must examine the services and stop them if that is what you want to do.

When you relocate a service, it starts at the new location before it stops at the original location. Oracle Clusterware can start that new instance or Pluggable Database as a dependency. When specified, the -drain_timeout and -stopoption parameters override the service attributes.

Stopping Services

You can use the srvctl stop service command to stop all services on a node, all services offered by a database, or all services offered by a Pluggable Database.

When you want to stop a subset of services, you can also supply a list of services (a subset of all services) that you want to stop to the srvctl stop service command. You can also restrict the srvctl stop service command to stop only the parallel query service by specifying the -pq parameter.

To stop all of the services offered by a single Pluggable Database:

$ srvctl stop service -db myRACCDB01 -pdb myPDB01 -drain_timeout 15 -stopoption TRANSACTIONAL

To stop all services on a given database and any of its Pluggable Databases:

$ srvctl stop service -db myRACDB -drain_timeout 15 -stopoption IMMEDIATE

To stop only a subset of the services offered by a database:

$ srvctl stop service -db myRACDB -service "myFirstService,mySecondService,
  myThirdService" -drain_timeout 60 -stopoption IMMEDIATE

Note:

If you use the -wait YES SRVCTL command line parameter, then the -stopoption parameter is not enforced until the entire drain timeout interval has expired, even if all of the sessions have exited prior to this interval completing.

Server Draining Ahead of Planned Maintenance

Before planned maintenance, drain or failover database sessions at the database instance so application work is not interrupted.

When you prepare for planned maintenance, you must stop or relocate the services that are using the server infrastructure. Relocating services is done over a period of time prior to the planned outage and is based on the nature of work associated with each service.

The procedure for rolling planned maintenance moves services in advance of maintenance to another database instance, and notifies the client-side drivers, connections pools, the database instance itself, and other subscribers that maintenance is pending, and what needs to be drained (either connections or sessions using this service). Once notified of draining, a Fast Application Notification (FAN) event is sent and the client pools behave as described elsewhere, in addition, the database begins to search for safe places to release connections and, if needed, to migrate the connections.

Moving or stopping a service triggers a FAN notification that is received by the subscribing Oracle drivers and Oracle connection pools. The FAN notification also triggers session draining at the server. Immediately, new work to that service is directed to another functioning instance of that service. Existing sessions are marked for release after their work completes. As work completes and the connections are returned to the connection pool, either the Oracle driver or the connection pool terminates these sessions.

Draining Sessions at the Database

For OLTP applications, application servers, and custom applications, which all have their own connection pools that borrow and return database sessions, it is safe to drain a database session when it is no longer borrowed. The optimal point for the Oracle server infrastructure to close a session is when the application server tests the validity of that connection. No error is returned to the application when the connection pool manager tests the validity of connections when borrowing and releasing, and also finds that the connection is no longer valid.

A safe place is a point where an application is not disturbed. In the case of connection pools, that means connections that are not borrowed (checked-in), and, in the case of applications, the same applies at the point of borrowing or returning a connection. At this time, all work is either complete or not started. The database can also fail over connections when all states can be restored transparent to the application.

Oracle AI Database uses an extensible set of rules and heuristics to detect when to take the database session away. When draining starts, the database session persists at the database until a rule is satisfied. The rules include the following:

Standard application server tests for validity
Custom SQL tests for validity
Request boundaries are in effect and no request is active
Request boundaries are in effect and the current request has ended
The session has one or more session states that are recoverable, and can be recreated at failover

In the case of connection tests, for example, it is standard practice for application servers, pooled applications, job schedulers, and others, to test connections when borrowed from connection pools, when returned to the pool, and at batch commits. When draining, the database intercepts the connection test, closes the connection and returns a failed status for the test. The application layer issuing the connection test is ready to handle a failed return status and, typically, issues a further request, to obtain a different connection. The application is not disturbed.

Not all sessions can be drained, such as when a connection is not returned to the pool or when FAN is not in use. If Transparent Application Continuity or Application Continuity is enabled, then the server detects request boundaries where Application Continuity can recover the session fast. The server can interrupt the session, which Application Continuity recovers elsewhere (such as, to another server in the Oracle RAC cluster) with no interruption.

For database sessions that do not drain, the database must finds a break point when the session can be replaced. At a break point, a connection can be failed over transparently when states are known and recoverable. Break points can be transaction boundaries, a request starting (beginRequest), before calls are processed in that request, and patterns, such as an audit call that signals that a request is starting or ending. Break points apply only when states are known to be restorable. Oracle AI Database software determines where to failover the session and invokes Application Continuity to failover the session.

Failing over connections requires that you enable Application Continuity, Transparent Application Continuity, or transparent application failover (TAF), depending on your application.

Draining Sessions Using XA Transactions

When using XA, enable XA_START_NEW in DBA_CONNECTION_TESTS. You can enable this connection test for services with applications that are using XA transactions.

When a session is marked to drain and the first branch of an XA transaction is received at the database instance, then the following actions take place:

The database server closes the session.
The database server returns XAER_RMFAIL and places the connection back in R0 state.
The client needs to perform xa_open() again to continue, which is the expected behavior.

These steps allow XA transactions that are underway to complete and do not allow new XA transactions at the instance.

Note:

When using the XA_START_NEW drain method for XA, do not mix this method with other drain methods, such as client side connection tests or FAN on sessions that are using XA.

Draining XA and Sessionless Transactions

As described in the previous section, the connections are failed over at safe points like when they are not borrowed (checked-in) during the draining period. You cannot do that for XA and sessionless transactions. These type of transactions are not supported by the Application Continuity or Transparent Application Continuity and they do not failover when they are active in the sessions. Such transactions are aborted after the drain timeout.

Starting with Oracle AI Database 26ai, the extensible set of rules and heuristics to detect when to take the database session away are further enhanced by adding two new rules, one for XA and the other for the sessionless transactions.

When you enable the new rules, the borrowed (checked-in) connections are allowed to resume the existing XA and sessionless transactions during the drain period. But, you cannot start new transactions. This increases the transaction success rate by allowing the current transactions to successfully end while disallowing the new transactions to start.

Enabling or Disabling Predefined Connection Tests

By default, predefined connection tests PING_TEST, ENDREQUEST_TEST, XA_START_TEST, and SESSIONLESS_START_TEST are disabled and all other predefined connection tests are enabled. To enable predefined connection test rules, which are disabled by default for a particular service, follow these steps.

To enable the connection test for a CDB or PDB, use a SQL statement
similar to the
following:

SQL> execute dbms_app_cont_admin.enable_connection_test(connection_test_type => dbms_app_cont_admin.xa_start_test);

To selectively disable the connection test for services where it is not
needed, use a SQL statement similar to the
following:

SQL> execute dbms_app_cont_admin.disable_connection_test(connection_test_type => dbms_app_cont_admin.xa_start_test, service_name => 'tkfojpm2');

Adding, Disabling, Enabling, and Removing Connection Tests for Draining at the Server

You can add a SQL connection test to a service, a Pluggable Database. To add a new connection test to your PDB, use ALTER SESSION SET CONTAINER to switch to your PDB.

There are four SQL connection tests added for every database service and Pluggable Database service, by default, so, if an application uses these following SQL connection tests on the connection, then you do not to need to add them:

SELECT 1 FROM DUAL;
SELECT COUNT(*) FROM DUAL;
SELECT 1;
BEGIN NULL;END

Note:

When you use a connection test, the outcome of the connection test applies to that session only. Do not use connection tests to make general decisions about the instance and to make a determination to stop more than the session to which the test applies. Disable connection pool properties for flushing and destroying the pool on connection test failure. This is important for Oracle WebLogic Server data sources.
For external monitors, you are recommended to prefix the SQL used in that monitor with a hint /*+ MONITOR */ for SQL used inside the monitors, so that the monitor does not drain.

To add a server-side SQL connection test for a service, use a SQL statement similar to the following:
```
SQL> execute dbms_app_cont_admin.add_sql_connection_test('select dummy from dual','sw_orcl');
```
To add a server-side SQL connection test for a Pluggable Database, use a SQL statement similar to the following:
```
SQL> execute dbms_app_cont_admin.add_sql_connection_test('begin null;end;');
```
After you add the SQL connection test, it will be enabled by default.
You can disable a SQL connection test if you do not need it or it is not in use by logging on to a Pluggable Database and using a SQL statement similar to the following:
```
SQL> execute dbms_app_cont_admin.disable_connection_test(dbms_app_cont_admin.sql_test,'select dummy from dual');
```
By default, the ping test and end request test are disabled but, if you want to disable them after enabling them, then you can use either of the following SQL statements:

If you want to disable the ping test, then use a SQL statement similar to the following:
```
SQL> execute dbms_app_cont_admin.disable_connection_test(dbms_app_cont_admin.ping_test);
```
If you want to disable the end request test, then use a SQL statement similar to the following:
```
SQL> execute dbms_app_cont_admin.disable_connection_test(dbms_app_cont_admin.endrequest_test);
```
You can enable a SQL connection test after you disable it by logging on to the Pluggable Database and using a SQL statement similar to the following:
```
SQL> execute dbms_app_cont_admin.enable_connection_test(dbms_app_cont_admin.sql_test,'select dummy from dual');
```
You can also enable the ping test and end request test if they are disabled by using either of the following SQL statements:

If you want to run any test that uses ping such as isValid, isUsable, OCIping, or connection.status, then use a SQL statement similar to the following:
```
SQL> execute dbms_app_cont_admin.enable_connection_test(dbms_app_cont_admin.ping_test);
```
If you want to enable draining at the end of a request, then use a SQL statement similar to the following:
```
SQL> execute dbms_app_cont_admin.enable_connection_test(dbms_app_cont_admin.endrequest_test);
```
If you want to disable draining on the end of a request, then use a SQL statement similar to the following:
```
SQL> execute dbms_app_cont_admin.disable_connection_test(dbms_app_cont_admin.endrequest_test);
```

You can remove a SQL connection test if it is not needed by logging on to the Pluggable Database and running SQL statements similar to the following:

SQL> execute dbms_app_cont_admin.delete_sql_connection_test('select dummy from dual','sw_orcl');
SQL> execute dbms_app_cont_admin.delete_sql_connection_test('begin null;end;');

If you want to modify rules for a service that belongs to a specific PDB, then switch to that PDB and modify the rules. For example, for eBusiness Suite:

SQL> alter session set container='VISPRD';
SQL> execute dbms_app_cont_admin.add_sql_connection_test('Begin null; End ;');
SQL> execute dbms_app_cont_admin.add_sql_connection_test('Begin null; End ;', 'VISPRD');

If you want to enable XA transactions start test, then use a SQL statement similar to the following:
```
SQL> execute dbms_app_cont_admin.enable_connection_test(dbms_app_cont_admin.xa_start_test);
```
If you want to disable XA transactions start test, then use a SQL statement similar to the following:
```
SQL> execute dbms_app_cont_admin.disable_connection_test(dbms_app_cont_admin.xa_start_test);
```
If you want to enable sessionless transactions start test, then use a SQL statement similar to the following:
```
SQL> execute dbms_app_cont_admin.enable_connection_test(dbms_app_cont_admin.sessionless_start_test);
```
If you want to disable sessionless transactions start test, then use a SQL statement similar to the following:
```
SQL> execute dbms_app_cont_admin.disable_connection_test(dbms_app_cont_admin.sessionless_start_test);
```

Every application server has a feature to test the validity of the connections in their respective connection pools, which is set either by a configuration property or at the administrative console. The purpose of the test is to prevent vending an unusable connection to an application, and when an unusable connection is detected, to remove it when released to the pool.

Across the various application servers, the tests have similar names. The tests offered use various approaches, the most common being a SQL statement. Oracle recommends that Java application servers use the standard Java call connection.isValid. Beginning with Oracle Database 18c, these tests are used to drain the database. Also beginning with Oracle Database 18c, the database drains sessions without using FAN by inspecting sessions for safe draining points.

The following table describes the standard connection tests available for several of the more common application servers:

Table: Standard Connection Tests for Some Common Application Servers

Application Server	Connection Test to Database
Oracle WebLogic Server	The tests offered include: STEP_LIST_PLACEHOLDER_5
Oracle WebLogic Server Active Gridlink	The test is embedded: `isUsable`
IBM WebSphere	`dbms_app_cont_admin.enable_connection_test(dbms_app_cont_admin.sql_test,'select 1 from dual');` Pretest connections (SQL syntax) for server draining: `Select 1 from dual;`
RedHat JBoss	`check-valid-connection-sql` (SQL syntax): `dbms_app_cont_admin.enable_connection_test(dbms_app_cont_admin.sql_test,'select 1 from dual');`
Apache Tomcat	There are two tests available-`testOnBorrow` and `testOnReturn`-and they both use SQL syntax to test the connection to the database: `dbms_app_cont_admin.enable_connection_test(dbms_app_cont_admin.sql_test,'select 1 from dual');` Application server uses: `Select 1 from dual;`

Oracle recommends that you use the following format for supporting automatic configuration of Oracle Notification Services (ONS), so that you can receive FAN events (over ONS):

Example: Automatic Configuration of FAN

alias =(DESCRIPTION =
 (CONNECT_TIMEOUT=90)(RETRY_COUNT=20)(RETRY_DELAY=3)(TRANSPORT_CONNECT_TIMEOUT=3)
   (ADDRESS_LIST =
   (LOAD_BALANCE=on)
   ( ADDRESS = (PROTOCOL = TCP)(HOST=primary-scan)(PORT=1521)))
   (ADDRESS_LIST =
   (LOAD_BALANCE=on)
   ( ADDRESS = (PROTOCOL = TCP)(HOST=secondary-scan)(PORT=1521)))
  (CONNECT_DATA=(SERVICE_NAME = gold-cloud)))

Planned Failover with Application Continuity

Planned Failover is a failover that is invoked by Oracle AI Database at points where the database knows that the session is replayable using Application Continuity (AC) and that the session is expected not to drain.

Planned failover is an automatic solution that is used for relocating sessions during planned maintenance for batch and long running operations that are not expected to complete in the specified drain timeout period. Session migration is also used with ALTER SYSTEM DISCONNECT SESSION statement.

Planned failover is activated when draining starts. A rules engine decides when to invoke a failover.

The database maintains statistics regarding the rate and size of requests, and when a failover feature such as Application Continuity is enabled, then the database also maintains statistics for the level of protection for calls for replay, and the session state that needs to be recovered.
The database knows when Transparent Application Continuity (TAC) or Application Continuity are enabled on a session and whether session state is tracked and recoverable, and when failover is enabled, if that failover is likely to be disabled before the drain timeout expires.
The database knows when a session is not expected to drain, and that a session is likely to recover, and how much replay it would need to run if it needs to replay.
The database knows if Fast Application Notification is enabled.
The database knows when request boundaries are discovered for Transparent Application Continuity.

A session failed over by the database is marked in the alert log so that you can find more information about the failed over session.

To use planned failover, follow these steps:

Enable Application Continuity or Transparent Application Continuity.
Set the service attributes -drain_timeout and -stopoption on your services.
During maintenance, drain your services by relocating or stopping them. For Data Guard, you can use switchover wait with Data Guard Broker.

Intelligent Drain Timing can help determine whether a session is likely to drain before the configured drain timeout expires. If request-duration information shows that the session is unlikely to drain within the available drain window and the session is configured with Application Continuity, Transparent Application Continuity, or Transparent Application Failover, then Oracle RAC automatically considers planned failover instead of waiting for the drain timeout to expire.