DBMS_APP

20 DBMS_APP_CONT

The DBMS_APP_CONT package provides an interface to determine if the in-flight transaction on a now unavailable session committed or not, and if the last call on that session completed or not.

20.1 DBMS_APP_CONT Overview

The DBMS_APP_CONT package can be used to solve this example issue.

Problem Description

One of the fundamental problems for recovering applications after an outage is that the commit message that is sent back to the client is not durable. If there is a break between the client and the server, the client sees an error message indicating that the communication failed. This error does not inform the application whether the submission executed any commit operations or if a procedural call, ran to completion executing all expected commits and session state changes or failed part way through or yet worse, is still running disconnected from the client.

GET_LTXID_OUTCOME

The purpose of the GET_LTXID_OUTCOME Procedure is to determine if the in-flight transaction on a now unavailable session completed or not. It is used when the original session returned an error due to unavailability. Situations that can cause such session unavailability may occur at the session, instance, server, or network, and result from planned or unplanned outages. When such an outage occurs, the application receives a disconnection error. Such an error provides no insight as to whether the transaction committed. It also does not reveal what the application might have been expecting from that commit if it had returned.

20.2 DBMS_APP_CONT Security Model

Applications must have the EXECUTE privilege on the DBMS_APP_CONT package.

To grant this privilege, ask your database administrator to run the following SQL statement:

GRANT execute on DBMS_APP_CONT to application user ;

20.3 Summary of DBMS_APP_CONT Subprograms

The DBMS_APP_CONT package contains the GET_LTXID_OUTCOME Procedure.

Table 20-1 DBMS_APP_CONT Package Subprograms

Subprogram	Description
ADD_SQL_CONNECTION_TEST Procedure	This procedure adds a new connection test that is used during draining sessions before planned maintenance begins. Use this procedure when the SQL connection test is not covered by standard tests. The test is enabled when added.
APPLY_REPLAY_RULE Procedure	This procedure activates a new replay rule that is inherited by any sub-routine unless it is overwritten by a new rule applied to that sub-routine.
DELETE_SQL_CONNECTION_TEST Procedure	This procedure deletes a connection test that is no longer needed for planned draining. Removing a test applies immediately to all RAC instances where the PDB is open.
DISABLE_CONNECTION_TEST Procedure	This procedure disables usage of a connection test during draining of sessions. Disabling a test applies immediately to all RAC instances where the PDB is open.
ENABLE_CONNECTION_TEST Procedure	This procedure enables usage of a connection test for draining database sessions before planned maintenance. Enabling a test applies immediately to all RAC instances where the PDB is open.
GET_LTXID_OUTCOME Procedure	Lets customer applications and third party application servers determine the transactional status of the last session when that session becomes unavailable.
GET_REPLAY_RULES Function	This function returns a list of the replay rules. From the results, you can test if any rule target is replayable with bit and function.
RESET_REPLAY_RULES Procedure	This procedure resets all existing replay rules. When you call this procedure, it clears all locally defined rules in the specified scope and restores them to the original state when they were inherited from their parent scope, or the service if parent scope is not specified.

20.3.1 ADD_SQL_CONNECTION_TEST Procedure

This procedure adds a new connection test that is used during draining sessions before planned maintenance begins. Use this procedure when the SQL connection test is not covered by standard tests. The test is enabled when added. If the optional service name qualifier is provided, the test only applies only to that service name.

Syntax

DBMS_SESSION.ADD_SQL_CONNECTION_TEST (
   connection_test          IN VARCHAR2
   service_name             IN VARCHAR2   DEFAULT NULL);

Parameters

Table 20-2 ADD_SQL_CONNECTION_TEST Procedure Parameters

Parameter	Description
`CONNECTION_TEST`	The SQL text used to test and drain connections.
`SERVICE_NAME`	Optional service name qualifier.

Usage Notes

The ADD_SQL_CONNECTION_TEST Procedure adds a connection test for the purpose of draining sessions before planned maintenance begins. The connection test is used by the application to test connections that are marked for draining. Sessions are set for draining at stop and relocate operations for services or PDBs. When set the RDBMS closes the connection while draining so the application sees no errors during planned maintenance. You can enter as many CONNECTION TESTs as needed. They are used only during planned maintenance. The tests apply to all RAC instances.

Check online documentation for latest updates on service qualifier availability.

Added connection can be viewed by querying the view DBA_CONNECTION_TESTS.

This procedure is owned by SYS and is granted to users for execution at CDB$ROOT or PDB levels, or when not multitenant, at dictionary level.

20.3.2 APPLY_REPLAY_RULE Procedure

This procedure activates a new replay rule that is inherited by any sub-routine unless it is overwritten by a new rule applied to that sub-routine.

A replay rule activated in a PL/SQL block loses its effect when reset_replay_rules is called or the block returns.

Note:

Calling this procedure in a sub-block of a procedure, function, or anonymous block applies the rule to its parent scope. When the sub-block returns, the rule will not lose its effect. Thus, you are not recommended to call this procedure in a sub-block.

Syntax

DBMS_APP_CONT.APPLY_REPLAY_RULE (
 replayable IN  BOOLEAN, 
 targets    IN  BINARY_INTEGER, 
 scope      IN  BINARY_INTEGER DEFAULT DBMS_APP_CONT.SCOPE_CURRENT);

Parameters

Table 20-3 APPLY_REPLAY_RULE Procedure Parameters

Parameter	Description
`replayable`	`TRUE` or `FALSE`, depending on weather the rule is replayable or not.
`targets`	Target effects on which this rule is applied. If multiple effects are desired, pass them as bit or as individual effects, such as: `dbms_app_cont.side_effects + dbms_app_cont.autonomous_transactions`
`scope`	Scope of the rule, either `dbms_app_cont.scope_current` (default) or `dbms_app_cont.scope_parent`.

20.3.3 DELETE_SQL_CONNECTION_TEST Procedure

This procedure deletes a connection test that is no longer needed for planned draining. Removing a test applies immediately to all RAC instances where the PDB is open.

Syntax

DBMS_SESSION.DELETE_SQL_CONNECTION_TEST (
   connection_test          IN VARCHAR2
   service_name             IN VARCHAR2   DEFAULT NULL);

Parameters

Table 20-4 DELETE_SQL_CONNECTION_TEST Procedure Parameters

Parameter Description

CONNECTION_TEST

The SQL text used to test and drain connections.

SERVICE_NAME

Optional service name qualifier.

If the optional SERVICE_NAME qualifier is provided, only the test for that service name is deleted.

Usage Notes

If you are not certain if a test should be deleted, you can disable the test using DISABLE_CONNECTION_TEST Procedure. Only custom SQL tests can be deleted. Predefined tests cannot be deleted. Check for latest updates on service qualifier availability.

This procedure is owned by SYS at CDB$ROOT or PDB level, or SYS for when not multitenant.

Connection tests and their status can be checked by querying the view DBA_CONNECTION_TESTS.

20.3.4 DISABLE_CONNECTION_TEST Procedure

This procedure disables usage of a connection test during draining of sessions. Disabling a test applies immediately to all RAC instances where the PDB is open.

Syntax

DBMS_SESSION.DISABLE_CONNECTION_TEST (
   connection_test_type     IN VARCHAR2,
   connection_test          IN VARCHAR2,
   service_name             IN VARCHAR2   DEFAULT NULL);

Parameters

Table 20-5 DISABLE_CONNECTION_TEST Procedure Parameters

Parameter Description

Parameter	Description
`CONNECTION_TEST_TYPE`	The permitted values are: `DBMS_SESSION.SQL_TEST` `DBMS_SESSION.PING_TEST` `DBMS_SESSION.ENDREQUEST_TEST`
`CONNECTION_TEST`	The SQL text used to test and drain connections. This parameter is allowed only if the value of `CONNECTION_TEST_TYPE` is `SQL_TEST`.
`SERVICE_NAME`	Optional service name qualifier. If the optional service name qualifier is provided, only the test for that service name is enabled. A disable at service name level takes precedence over an enable at `PDB` level. That is the `PDB` can be enabled, and the service disabled.

CONNECTION_TEST_TYPE

The permitted values are:

DBMS_SESSION.SQL_TEST
DBMS_SESSION.PING_TEST
DBMS_SESSION.ENDREQUEST_TEST

CONNECTION_TEST

The SQL text used to test and drain connections.

This parameter is allowed only if the value of CONNECTION_TEST_TYPE is SQL_TEST.

SERVICE_NAME

Optional service name qualifier. If the optional service name qualifier is provided, only the test for that service name is enabled. A disable at service name level takes precedence over an enable at PDB level. That is the PDB can be enabled, and the service disabled.

Usage Notes

This procedure is owned by SYS and is granted to users for execution at CDB$ROOT or PDB levels, or when not multitenant, at dictionary level.

Connection tests and their status can be checked by querying the view DBA_CONNECTION_TESTS.

20.3.5 ENABLE_CONNECTION_TEST Procedure

This procedure enables usage of a connection test for draining database sessions before planned maintenance. Enabling a test applies immediately to all RAC instances where the PDB is open.

Syntax

DBMS_SESSION.ENABLE_CONNECTION_TEST (
   connection_test_type     IN VARCHAR2,
   connection_test          IN VARCHAR2,
   service_name             IN VARCHAR2   DEFAULT NULL);

Parameters

Table 20-6 ENABLE_CONNECTION_TEST Procedure Parameters

Parameter Description

Parameter	Description
`CONNECTION_TEST_TYPE`	The connection type used when managing connection tests for draining before planned maintenance. See ADD, DELETE, ENABLE, DISABLE procedures for connection tests. The permitted values are: `DBMS_SESSION.SQL_TEST` `DBMS_SESSION.PING_TEST` `DBMS_SESSION.ENDREQUEST_TEST`
`CONNECTION_TEST`	The SQL text used to test and drain connections at the RDBMS before planned maintenance starts. This parameter is allowed only if the value of `CONNECTION_TEST_TYPE` is `SQL_TEST`.
`SERVICE_NAME`	Optional service name qualifier. If the optional service name qualifier is provided, only the test for that service name is enabled. An enable at service name level overrides any higher-level disables. That is, the PDB can be disabled, and the service enabled.

CONNECTION_TEST_TYPE

The connection type used when managing connection tests for draining before planned maintenance. See ADD, DELETE, ENABLE, DISABLE procedures for connection tests.

The permitted values are:

DBMS_SESSION.SQL_TEST
DBMS_SESSION.PING_TEST
DBMS_SESSION.ENDREQUEST_TEST

CONNECTION_TEST

The SQL text used to test and drain connections at the RDBMS before planned maintenance starts.

This parameter is allowed only if the value of CONNECTION_TEST_TYPE is SQL_TEST.

SERVICE_NAME

Optional service name qualifier. If the optional service name qualifier is provided, only the test for that service name is enabled. An enable at service name level overrides any higher-level disables. That is, the PDB can be disabled, and the service enabled.

Usage Notes

This procedure is owned by SYS and is granted to users for execution at CDB$ROOT or PDB levels, or when not multitenant, at dictionary level
ENABLE_CONNECTION_TEST enables a connection test for draining sessions during planned maintenance. The enable operation applies to all RAC instances where the PDB is open. It persists across database restarts.
This procedure is owned by SYS and is granted to users for execution at CDB$ROOT or PDB levels, or when not multitenant, at dictionary level.

20.3.6 GET_LTXID_OUTCOME Procedure

This procedure lets customer applications and third party application servers determine the transactional status of the last session when that session becomes unavailable.

Syntax

DBMS_APP_CONT.GET_LTXID_OUTCOME (
 client_ltxid          IN    RAW, 
 committed             OUT   BOOLEAN, 
 user_call_completed   OUT   BOOLEAN)

Parameters

Table 20-7 GET_LTXID_OUTCOME Procedure Parameters

Parameter	Description
`client_ltxid`	Client-side logical transaction ID. Obtain the `LTXID` from the previous failed session using the client driver provided APIs - `getLTXID` for JDBC, and `LogicalTransactionId` for `ODP`.`net`., and `OCI_ATTR_GET` with `LTXID` for OCI.
`committed`	Returns `TRUE` if the transaction with the named logical `LTXID` has `COMMITTED`. Returns `FALSE` if the logical `LTXID` has not `COMMITTED`. When returning `FALSE`, the procedure blocks the `LTXID` from further use so that there is no possibility of previous in-flight work committing this `LTXID`.
`user_call_completed`	Whether all information has been returned to the client. Examples of such messages are the number of rows processed when using autocommit or commit on success, parameter and function results when calling PL/SQL, or PL/SQL with more work to do after the `COMMIT`. Applications that expect to use data returned from the commit in order to function correctly must look at this second parameter.

Exceptions

Table 20-8 GET_LTXID_OUTCOME Procedure Exceptions

Exception	Description
`ORA-14950` - `SERVER_AHEAD`	The server is ahead so the transaction is both an old transaction and one which has already committed. This is an error as the application is passing an older `LTXID` that is the not the last used for that session. The purpose of `GET_LTXID_OUTCOME` is to return the current transaction outcome for that session after a recoverable outage.
`ORA-14951` - `CLIENT_AHEAD`	The client is ahead of the server. This can happen if the server has been flashed backed, recovered using media recovery, or is a standby that has opened earlier with data loss.
`ORA-14906` - `SAME_SESSION`	Executing `GET_LTXID_OUTCOME` is not supported on the session owning the `LTXID` as it blocks further processing on that session after a recoverable outage.
`ORA-14909` - `COMMIT_BLOCKED`	Your session has been blocked from committing by another user with the same username using `GET_LTXID_OUTCOME`. `GET_LTXID_OUTCOME` should only be called on dead sessions. Please check with your application administrator.
`ORA-14952` - `ERROR`	The outcome cannot be determined. During processing an error happened. The error stack shows the error detail.

20.3.7 GET_REPLAY_RULES Function

This function returns a list of the replay rules. From the results, you can test if any rule target is replayable with bit and function.

Syntax

DBMS_APP_CONT.GET_REPLAY_RULES (
 replayable BOOLEAN, 
 scope      IN  BINARY_INTEGER DEFAULT DBMS_APP_CONT.SCOPE_CURRENT)
 RETURN     BINARY_INTEGER;

Parameters

Table 20-9 GET_REPLAY_RULES Procedure Parameters

Parameter	Description
`replayable`	`TRUE` or `FALSE`, depending on weather the rule is replayable or not.
`scope`	Scope of the rules, either `dbms_app_cont.scope_current` (default) or `dbms_app_cont.scope_parent`.

20.3.8 RESET_REPLAY_RULES Procedure

This procedure resets all existing replay rules. When you call this procedure, it clears all locally defined rules in the specified scope and restores them to the original state when they were inherited from their parent scope, or the service if parent scope is not specified.

Syntax

DBMS_APP_CONT.RESET_REPLAY_RULES (
 targets  IN  BINARY_INTEGER DEFAULT NULL, 
 scope    IN  BINARY_INTEGER DEFAULT DBMS_APP_CONT.SCOPE_CURRENT);

Parameters

Table 20-10 RESET_REPLAY_RULES Procedure Parameters

Parameter	Description
`targets`	Target effects on which you want to reset the rules.
`scope`	Scope of the rule, either `dbms_app_cont.scope_current` (default) or `dbms_app_cont.scope_parent`.