15 Developing Applications with Oracle XA

This chapter describes how to use the Oracle XA library. Typically, you use this library in applications that work with transaction monitors. The XA features are most useful in applications in which transactions interact with more than one database.

The chapter includes the following topics:

See Also:

  • X/Open CAE Specification - Distributed Transaction Processing: The XA Specification, X/Open Document Number XO/CAE/91/300, for an overview of XA, including basic architecture. Access at http://www.opengroup.org/pubs/catalog/c193.htm.

  • Oracle Call Interface Programmer's Guide for background and reference information about the Oracle XA library.

  • The Oracle Database platform-specific documentation for information on library linking filenames.

  • README for changes, bugs, and restrictions in the Oracle XA library for your platform.

X/Open Distributed Transaction Processing (DTP)

The X/Open Distributed Transaction Processing (DTP) architecture defines a standard architecture or interface that enables multiple application programs (APs) to share resources provided by multiple, and possibly different, resource managers (RMs). It coordinates the work between APs and RMs into global transactions.

The Oracle XA library conforms to the X/Open software architecture's XA interface specification. The Oracle XA library is an external interface that enables a non-Oracle, client-side transaction manager (TM) to coordinate global transactions, thereby allowing inclusion of non-Oracle Database RMs in distributed transactions. For example, a client application can manage an Oracle Database transaction and a transaction in an NTFS file system as a single, global transaction.

Figure 15-1 illustrates a possible X/Open DTP model.

Figure 15-1 Possible DTP Model

Description of Figure 15-1 follows
Description of "Figure 15-1 Possible DTP Model"

DTP Terminology

This section introduces you to key terminology in distributed transaction processing.

Resource Manager (RM)

A resource manager controls a shared, recoverable resource that can be returned to a consistent state after a failure. Examples are relational databases, transactional queues, and transactional file systems. Oracle Database is an RM and uses its online redo log and undo segments to return to a consistent state after a failure.

Distributed Transaction

A distributed transaction, also called a global transaction, is a client transaction that involves updates to multiple distributed resources and requires an "all-or-none" semantics across distributed RMs.


A branch is a unit of work contained within one RM. Multiple branches make up one global transaction. In the case of Oracle Database, each branch maps to a local transaction inside the database server.

Transaction Manager (TM)

A transaction manager provides an API for specifying the boundaries of the transaction and manages commit and recovery. The TM implements a two-phase commit engine to provide an "all-or-none" semantics across distributed RMs.

An external TM is a middle-tier component that resides outside of Oracle Database. Normally, the database is its own internal TM. Using a standards-based TM enables Oracle Database to cooperate with other heterogeneous RMs in a single transaction.

Transaction Processing Monitor (TPM)

A TM is usually provided by a transaction processing monitor (TPM) vendor. A TPM coordinates the flow of transaction requests between the client processes that issue requests and the back-end servers that process them. Basically, a TPM coordinates transactions that require the services of several different types of back-end processes, such as application servers and RMs distributed over a network.

The TPM synchronizes any commits or rollbacks required to complete a distributed transaction. The TM portion of the TPM is responsible for controlling when distributed commits and rollbacks take place. Thus, if a distributed application program takes advantage of a TPM, then the TM portion of the TPM is responsible for controlling the two-phase commit protocol. The RMs enable the TMs to perform this task.

Because the TM controls distributed commits or rollbacks, it must communicate directly with Oracle Database (or any other RM) through the XA interface. It uses Oracle XA library subroutines, which are described in "XA Library Subroutines", to tell Oracle Database how to process the transaction, based on its knowledge of all RMs in the transaction.

Two-Phase Commit Protocol

The Oracle XA library interface follows the two-phase commit protocol. The sequence of events is as follows:

  1. In the prepare phase, the TM asks each RM to guarantee that it can commit any part of the transaction. If this is possible, then the RM records its prepared state and replies affirmatively to the TM. If it is not possible, then the RM may roll back any work, reply negatively to the TM, and forget about the transaction. The protocol allows the application, or any RM, to roll back the transaction unilaterally until the prepare phase completes.

  2. In phase two, the TM records the commit decision and issues a commit or rollback to all RMs participating in the transaction. TM can issue a commit for an RM only if all RMs have replied affirmatively to phase one.

Application Program (AP)

An application program defines transaction boundaries and specifies actions that constitute a transaction. For example, an AP can be a precompiler or OCI program. The AP operates on the RM's resource through its native interface, for example, SQL.

TX Interface

An application program starts and completes all transaction control operations through the TM by means of an interface called TX. The AP does not directly use the XA interface. APs are not aware of branches that fork in the middle-tier: application threads do not explicitly join, leave, suspend, and resume branch work, instead the TM portion of the transaction processing monitor manages the branches of a global transaction for APs. Ultimately, APs call the TM to commit all-or-none.


The naming conventions for the TX interface and associated subroutines are vendor-specific. For example, the tx_open call may be referred to as tp_open on your system. In some cases, the calls may be implicit, for example, at the entry to a transactional RPC. Refer to the documentation supplied with the transaction processing monitor for details.

Tight and Loose Coupling

Application threads are tightly coupled if the RM considers them as a single entity for all isolation semantic purposes. Tightly coupled branches must see changes in each other. Furthermore, an external client must either see all changes of a tightly coupled set or none of the changes. If application threads are not tightly coupled, then they are loosely coupled.

Dynamic and Static Registration

Oracle Database supports both dynamic and static registration. In dynamic registration, the RM executes an application callback before starting any work. In static registration, you must call xa_start() for each RM before starting any work, even if some RMs are not involved.

Required Public Information

As a resource manager, Oracle Database is required to publish the information described in Table 15-1.

Table 15-1 Required XA Features Published by Oracle Database

XA Feature Oracle Database Details

xa_switch_t structures

The Oracle Database xa_switch_t structure name is xaosw for static registration and xaoswd for dynamic registration. These structures contain entry points and other information for the resource manager.

xa_switch_t resource manager

The Oracle Database resource manager name within the xa_switch_t structure is Oracle_XA.

Close string

The close string used by xa_close() is ignored and can be null.

Open string

The format of the open string used by xa_open() is described in detail in "Defining the xa_open() String".


Libraries needed to link applications using Oracle XA have platform-specific names. The procedure is similar to linking an ordinary precompiler or OCI program except that you may have to link any TPM-specific libraries.

If you are not using sqllib, then link with $ORACLE_HOME/rdbms/lib/xaonsl.o or $ORACLE_HOME/rdbms/lib32/xaonsl.o (for 32 bit application on 64 bit platforms).


None. The functionality to support XA is part of both Standard Edition and Enterprise Edition.

Oracle XA Library Interface Subroutines

The Oracle XA library subroutines enable a TM to instruct Oracle Database how to process transactions. Generally, the TM must open the resource by using xa_open(). Typically, the opening of the resource results from the AP's call to tx_open. Some TMs may call xa_open() implicitly when the application begins.

Similarly, there is a close (using xa_close()) that occurs when the application is finished with the resource. The close may occur when the AP calls tx_close or when the application terminates.

The TM instructs the RMs to perform several other tasks, which include the following:

  • Starting a new transaction and associating it with an ID

  • Rolling back a transaction

  • Preparing and committing a transaction

XA Library Subroutines

XA Library subroutines are described in Table 15-2.

Table 15-2 XA Library Subroutines

XA Subroutine Description


Connects to the RM.


Disconnects from the RM.


Starts a new transaction and associates it with the given transaction ID (XID), or associates the process with an existing transaction.


Disassociates the process from the given XID.


Rolls back the transaction associated with the given XID.


Prepares the transaction associated with the given XID. This is the first phase of the two-phase commit protocol.


Commits the transaction associated with the given XID. This is the second phase of the two-phase commit protocol.


Retrieves a list of prepared, heuristically committed, or heuristically rolled back transactions.


Forgets the heuristically completed transaction associated with the given XID.

In general, the AP does not need to worry about the subroutines in Table 15-2 except to understand the role played by the xa_open() string.

Extensions to the XA Interface

Oracle Database's XA interface includes some additional functions, which are described in Table 15-3.

Table 15-3 Additional Functions in the XA Interface for Oracle Database

Function Description

OCISvcCtx *xaoSvcCtx(text *dbname)

Returns the OCI service handle for a given XA connection. The dbname parameter must be the same as the DB parameter passed in the xa_open() string. OCI applications can use this routing instead of the sqlld2 calls to obtain the connection handle. Hence, OCI applications need not link with the sqllib library. The service handle can be converted to the Version 7 OCI logon data area (LDA) by using OCISvcCtxToLda() [Version 8 OCI]. Client applications must remember to convert the Version 7 LDA to a service handle by using OCILdaToSvcCtx() after completing the OCI calls.

OCIEnv *xaoEnv(text *dbname)

Returns the OCI environment handle for a given XA connection. The dbname parameter must be the same as the DB parameter passed in the xa_open() string.

int xaosterr(OCISvcCtx *SvcCtx,sb4 error)

Converts an Oracle Database error code to an XA error code (only applicable to dynamic registration). The first parameter is the service handle used to execute the work in the database. The second parameter is the error code that was returned from Oracle Database. Use this function to determine if the error returned from an OCI command was caused because the xa_start() failed. The function returns XA_OK if the error was not generated by the XA module or a valid XA error if the error was generated by the XA module.

Developing and Installing XA Applications

This section discusses developing and installing Oracle XA applications:

Responsibilities of the DBA or System Administrator

The responsibilities of the DBA or system administrator are as follows:

  1. Define the open string, with help from the application developer. This task is described in "Defining the xa_open() String".

  2. Make sure the DBA_PENDING_TRANSACTIONS view exists and grant the SELECT privilege to the view for all Oracle users specified in the xa_open() string.

    In Oracle Database version 7 client applications, all Oracle Database accounts used by Oracle XA library must have the SELECT privilege on the V$XATRANS$ view. This view should have been created during the XA library installation. If necessary, you can manually create the view by running the SQL script xaview.sql as Oracle Database user SYS.

    See Also:

    Your Oracle Database platform-specific documentation for the location of the catxpend.sql script
  3. Using the open string information, install the RM into the TPM configuration. Follow the TPM vendor instructions.

    The DBA or system administrator should be aware that a TPM system starts the process that connects to Oracle Database. Refer to your TPM documentation to determine what environment exists for the process and what user ID it will have. Be sure that correct values are set for $ORACLE_HOME and $ORACLE_SID in this environment.

  4. Grant the user ID write permission to the directory in which the system will write the XA trace file.

    See Also:

    "Defining the xa_open() String" for information on how to specify an Oracle System Identifier (SID) or a trace directory that is different from the defaults
  5. Start the relevant database instances to bring Oracle XA applications on-line. You should perform this task before starting any TPM servers.

Responsibilities of the Application Developer

The responsibilities of the application developer are as follows:

  1. Define the open string with help from the DBA or system administrator, as explained in "Defining the xa_open() String".

  2. Develop the applications.

    Observe special restrictions on transaction-oriented SQL statements for precompilers.

  3. Link the application according to TPM vendor instructions.

Defining the xa_open() String

The open string is used by the transaction monitor to open the database. The maximum number of characters in an open string is 256.

This section covers:

Syntax of the xa_open() String

You can define an open string with the syntax shown in Example 15-1.

Example 15-1 xa_open() String

ORACLE_XA{+required_fields...} [+optional_fields...]

The following strings shows sample parameter settings:


The following sections describe valid parameters for the required_fields and optional_fields placeholders.


  • You can enter the required fields and optional fields in any order when constructing the open string.

  • All field names are case insensitive. Their values may or may not be case-sensitive depending on the platform.

  • There is no way to use the plus character (+) as part of the actual information string.

Required Fields for the xa_open() String

The required_fields placeholder in Example 15-1 refers to any of the following name-value pairs described in Table 15-4.

Table 15-4 Required Fields for the xa_open() string

Syntax Element Description


Indicates that no explicit user or password information is provided and that the operating system authentication form will be used. For more information refer to Oracle Database Administrator's Guide.


Specifies the username and password for a valid Oracle Database account. For example, Acc=P/hr/hr indicates that the user is hr and the password is hr. As described in "Responsibilities of the DBA or System Administrator", make sure that hr has the SELECT privilege on the DBA_PENDING_TRANSACTIONS table.


Specifies the maximum number of seconds allowed in a transaction between one service and the next, or between a service and the commit or rollback of the transaction, before the system aborts the transaction. For example, SesTM=15 indicates that the session idle time limit is 15 seconds.

For example, if the TPM uses remote procedure calls between the client and the servers, then SesTM applies to the time between the completion of one RPC and the initiation of the next RPC, or the tx_commit, or the tx_rollback.

The value of 0 indicates no limit. Entering a value of 0 is strongly discouraged. It might tie up resources for a long time if something goes wrong. Also, if a child process has SesTM=0, then the SesTM setting is not effective after the parent process is terminated.

Optional Fields for the xa_open() String

The optional_fields placeholder in Example 15-1 refers to any of the following name-value pairs described in Table 15-5.

Table 15-5 Optional Fields in the xa_open() String

Syntax Element Description

NoLocal= true | false

Specifies whether local transactions are allowed. The default value is false. If the application needs to disallow local transactions, then set the value to true.


Indicates the name used by Oracle Database precompilers to identify the database. For example, DB=payroll indicates that the database name is payroll and that the application server program will use that name in AT clauses.

Application programs that use only the default database for the Oracle Database precompiler (that is, they do not use the AT clause in their SQL statements) should omit the DB=db_name clause in the open string. Applications that use explicitly named databases should indicate that database name in their DB=db_name field. Oracle Database Version 7 OCI programs need to call the sqlld2() function to obtain the correct context for logon data area (Lda_Def), which is the equivalent of an OCI service context. Version 8 and higher OCI programs need to call the xaoSvcCtx() function to get the OCISvcCtx service context.

The db_name is not the sid and is not used to locate the database to be opened. Rather, it correlates the database opened by this open string with the name used in the application program to execute SQL statements. The sid is set from either the environment variable ORACLE_SID of the TPM application server or the sid given in the Oracle Net clause in the open string. The Oracle Net clause is described later in this section.Some TPM vendors provide a way to name a group of servers that use the same open string. You may find it convenient to choose the same name both for that purpose and for db_name.


Specifies the path name on the local machine where the Oracle XA library error and tracing information should be logged. The default is $ORACLE_HOME/rdbms/log if ORACLE_HOME is set; otherwise, it specifies the current directory. For example, LogDir=/xa_trace indicates that the logging information is located under the /xa_trace directory. Ensure that the directory exists and the application server can write to it.

Objects= true | false

Specifies whether the application is initialized in object mode. The default value is false. If the application needs to use certain API calls that require object mode, such as OCIRawAssignBytes(), then set the value to true.


Specifies the number of cursors to be allocated when the database is opened. It serves the same purpose as the precompiler option maxopencursors. For example, MaxCur=5 indicates that the precompiler should try to keep five open cursors cached. Note that this parameter overrides the precompiler option maxopencursors that you might have specified in your source code or at compile time.


Specifies the Oracle Net database link to use to log on to the system. This string should be an entry in tnsnames.ora. For example, the string SqlNet=inst1_disp might connect to a shared server at instance 1 if so defined in tnsnames.ora.

You can use the SqlNet parameter to specify the ORACLE_SID in cases where you cannot control the server environment variable. You must also use it when the server needs to access more than one Oracle Database instance. To use the Oracle Net string without actually accessing a remote database, use the Pipe driver. For example, specify SqlNet=localsid1, where localsid1 is an alias defined in the tnsnames.ora file.

Loose_Coupling=true | false

Specifies whether locks are shared. Oracle Database transaction branches within the same global transaction can be coupled tightly or loosely. If branches are loosely coupled, then they do not share locks. Set the value to true for loosely coupled branches. If branches are tightly coupled, then they share locks. Set the value to false for tightly coupled branches. The default value is false. However, if branches are landed on different RAC instances when running Oracle Real Application Clusters, they are loosely coupled even if the value is set to false. Refer to "Managing Transaction Branches in XA Applications" for more information.


Specifies the number of seconds Oracle Database waits for a transaction branch that is being used by another session before XA_RETRY is returned. The default value is 60 seconds.

Threads=true | false

Specifies whether the application is multithreaded. The default value is false. If the application is multithreaded, then the setting is true.

Interfacing XA with Precompilers and OCI

This section describes how to use the Oracle XA library with precompilers and Oracle Call Interface (OCI). It contains the following subsections:

Using Precompilers with the Oracle XA Library

When used in an Oracle XA application, cursors are valid only for the duration of the transaction. Explicit cursors should be opened after the transaction begins, and closed before the commit or rollback.

You have the following options when interfacing with precompilers:

The following examples use the precompiler Pro*C/C++.

Using Precompilers with the Default Database

To interface to a precompiler with the default database, make certain that the DB=db_name field used in the open string is not present. The absence of this field indicates the default connection. Only one default connection is allowed for each process.

The following is an example of an open string identifying a default Pro*C/C++ connection.


Note that the DB=db_name is absent, indicating an empty database ID string.

The syntax of a SQL statement would be:

EXEC SQL UPDATE Emp_tab SET Sal = Sal*1.5;
Using Precompilers with a Named Database

To interface to a precompiler with a named database, include the DB=db_name field in the open string. Any database you refer to must reference the same db_name you specified in the corresponding open string.

An application may include the default database as well as one or more named databases.For example, suppose you want to update an employee's salary in one database, his department number (DEPTNO) in another, and his manager in a third database. You would configure the following open strings in the transaction manager:

Example 15-2 Sample Open String Configuration


Note that there is no DB=db_name field in the last open string in Example 15-2.

In the application server program, you would enter declarations such as:


Again, the default connection (corresponding to the third open string that does not contain the DB field) needs no declaration.

When doing the update, you would enter statements similar to the following:

EXEC SQL UPDATE Emp_Tab SET Deptno=30 WHERE Empno=7788;

There is no AT clause in the last statement because it is referring to the default database.

In Oracle Database precompilers release 1.5.3 or later, you can use a character host variable in the AT clause, as the following example shows:



Do not have XA applications create connections other than the ones created through xa_open(). Any work performed on non-XA connections would be outside the global transaction and would have to be committed separately.

Using OCI with the Oracle XA Library

Oracle Call Interface applications that use the Oracle XA library should not call OCISessionBegin() to log on to the resource manager. Rather, the logon should be done through the TPM. The applications can execute the function xaoSvcCtx() to obtain the service context structure when they need to access the resource manager.

In applications that need to pass the environment handle to OCI functions, you can also call xaoEnv() to find that handle.

Because an application server can have multiple concurrent open Oracle Database resource managers, it should call the function xaoSvcCtx() with the correct arguments to obtain the correct service context.

See Also:

Oracle Call Interface Programmer's Guide for more information about using the OCISvcCtx handle

Managing Transaction Control with XA

When you use the XA library, transactions are not controlled by the SQL statements that commit or roll back transactions. Rather, they are controlled by an API accepted by the TM that starts and stops transactions. You call the API that is provided by the transaction manager, including the TX interface listed in Table 15-6, but not the XA Library Subroutines listed in Table 15-2.

The TMs typically control the transactions through the XA interface. This interface includes the functions described in Table 15-2, "XA Library Subroutines".

Table 15-6 TX Interface Functions

TX Function Description


Logs into the resource manager(s)


Logs out of the resource manager(s)


Starts a new transaction


Commits a transaction


Rolls back the transaction

Most TPM applications use a client/server architecture in which an application client requests services and an application server provides them. The examples shown in "Examples of Precompiler Applications" use such a client/server model. A service is a logical unit of work, which in the case of Oracle Database as the resource manager, comprises a set of SQL statements that perform a related unit of work.

For example, when a service named "credit" receives an account number and the amount to be credited, it executes SQL statements to update information in certain tables in the database. In addition, a service might request other services. For example, a "transfer fund" service might request services from a "credit" and "debit" service.

Typically, application clients request services from the application servers to perform tasks within a transaction. For some TPM systems, however, the application client itself can offer its own local services. As shown in "Examples of Precompiler Applications", you can encode transaction control statements within either the client or the server.

To have more than one process participating in the same transaction, the TPM provides a communication API that enables transaction information to flow between the participating processes. Examples of communications APIs include RPC, pseudo-RPC functions, and send/receive functions.

Because the leading vendors support different communication functions, the examples that follow use the communication pseudo-function tpm_service to generalize the communications API.

X/Open includes several alternative methods for providing communication functions in their preliminary specification. At least one of these alternatives is supported by each of the leading TPM vendors.

Examples of Precompiler Applications

The following examples illustrate precompiler applications. Assume that the application server has already logged onto the RMs system, in a TPM-specific manner. Example 15-3 shows a transaction started by an application server.

Example 15-3 Transaction Started by an Application Server

/***** Client: *****/
tpm_service("ServiceName");              /*Request Service*/

/***** Server: *****/
  <get service specific data>
  tx_begin();                             /* Begin transaction boundary */

  /* This application server temporarily becomes */
  /* a client and requests another service. */

  tx_commit();                             /* Commit the transaction */
  <return service status back to the client>

Example 15-4 shows a transaction started by an application client.

Example 15-4 Transaction Started by an Application Client

/***** Client: *****/
tx_begin();                            /* Begin transaction boundary */
tx_commit();                           /* Commit the transaction */

/***** Server: *****/
  <get service specific data>
  <return service status back to the client>
  <get service specific data>
  <return service status back to client>

Migrating Precompiler or OCI Applications to TPM Applications

To migrate existing precompiler or OCI applications to a TPM application that uses the Oracle XA library, you must do the following:

  1. Reorganize the application into a framework of "services" so that application clients request services from application servers. Some TPMs require the application to use the tx_open and tx_close functions, whereas other TPMs do the logon and logoff implicitly.

    If you do not specify the SqlNet parameter in your open string, then the application uses the default Oracle Net driver. Thus, be sure that the application server is brought up with the ORACLE_HOME and ORACLE_SID environment variables properly defined. This is accomplished in a TPM-specific fashion. Refer to your TPM vendor documentation for instructions on how to accomplish this.

  2. Ensure that the application replaces the regular connect and disconnect statements. For example, replace the connect statements EXEC SQL CONNECT (for precompilers) or OCISessionBegin(), OCIServerAttach(), and OCIEnvCreate() (for OCI) with tx_open(). Replace the disconnect statements EXEC SQL COMMIT/ROLLBACK WORK RELEASE (for precompilers) or OCISessionEnd()/OCIServerDetach (for OCI) with tx_close().

  3. Ensure that the application replaces the regular commit or rollback statements for any global transactions and begins the transaction explicitly.

    For example, replace the COMMIT/ROLLBACK statements EXEC SQL COMMIT/ROLLBACK WORK (for precompilers), or OCITransCommit()/OCITransRollback() (for OCI) with tx_commit()/tx_rollback() and start the transaction by calling tx_begin().


    The preceding is only true for global rather than local transactions. You should commit or roll back local transactions with the Oracle API.
  4. Ensure that the application resets the fetch state before ending a transaction. In general, you should use release_cursor=no. Use release_cursor=yes only when you are certain that a statement will be executed only once.

Table 15-7 lists the TPM functions that replace regular Oracle Database commands when migrating precompiler or OCI applications to TPM applications.

Table 15-7 TPM Replacement Commands

Regular Oracle Database Commands TPM Functions

CONNECT user/password

tx_open (possibly implicit)

implicit start of transaction



Service that executes the SQL






tx_close (possibly implicit)

Managing XA Library Thread Safety

If you use a transaction monitor that supports threads, then the Oracle XA library enables you to write applications that are thread safe. Nevertheless, you should keep certain issues in mind.

A thread of control (or thread) refers to the set of connections to resource managers. In an nonthreaded system, each process could be considered a thread of control because each process has its own set of connections to RMs and maintains its own independent resource manager table. In a threaded system, each thread has an autonomous set of connections to RMs and each thread maintains a private RM table. This private table must be allocated for each new thread and de-allocated when the thread terminates, even if the termination is abnormal.


In Oracle Database, each thread that accesses the database must have its own connection.

Specifying Threading in the Open String

The xa_open() string provides the clause Threads=. You must specify this clause as true to enable the use of threads by the TM. The default is false. In most cases, the TM creates the threads; the application does not know when a new thread is created. Therefore, it is advisable to allocate a service context on the stack within each service that is written for a TM application. Before doing any Oracle Database-related calls in that service, you must call the xaoSvcCtx function to retrieve the initialized OCI service context. You can then use this context for OCI calls within the service.

Restrictions on Threading in XA

The following restrictions apply when using threads:

  • Any Pro* or OCI code that executes as part of the application server process on the transaction monitor cannot be threaded unless the transaction monitor is explicitly told when each new application thread is started. This is typically accomplished by using a special C compiler provided by the TM vendor.

  • The Pro* statements EXEC SQL ALLOCATE and EXEC SQL USE are not supported. Therefore, when threading is enabled, you cannot use embedded SQL statements across non-XA connections.

  • If one thread in a process connects to Oracle Database through XA, then all other threads in the process that connect to Oracle Database must also connect through XA. You cannot connect through EXEC SQL CONNECT in one thread and through xa_open() in another thread.

Troubleshooting XA Applications

This section discusses how to find information in case of problems or system failure. It also discusses trace files and recovery of pending transactions. This section contains the following topics:

Accessing XA Trace Files

The Oracle XA library logs any error and tracing information to its trace file. This information is useful in supplementing the XA error codes. For example, it can indicate whether an xa_open() failure is caused by an incorrect open string, failure to find the Oracle Database instance, or a logon authorization failure.

The name of the trace file is xa_db_namedate.trc, where db_name is the database name specified in the open string field DB=db_name, and date is the date when the information is logged to the trace file. If you do not specify DB=db_name in the open string, then it automatically defaults to the name NULL.

For example, xa_NULL06022005.trc indicates a trace file that was created on June 2, 2005. Its DB field was not specified in the open string when the resource manager was opened. The filename xa_Finance12152004.trc indicates a trace file was created on December 15, 2004. Its DB field was specified as "Finance" in the open string when the resource manager was opened.


Multiple Oracle XA library resource managers with the same DB field and LogDir field in their open strings log all trace information that occurs on the same day to the same trace file.

Suppose that a trace file contains the following contents:

1032.12345.2:  ORA-01017:  invalid username/password;  logon denied
1032.12345.2:  xaolgn:  XAER_INVAL;  logon denied

Table 15-8 explains the meaning of each element.

Table 15-8 Sample Trace File Contents

String Description


The time when the information is logged.


The process ID (PID).


The resource manager ID.


The name of the module.


The error returned as specified in the XA standard.


The Oracle Database information that was returned.

The xa_open() String DbgFl

Normally, the XA trace file is opened only if an error is detected. The xa_open() string DbgFl provides a tracing facility to record additional detail about the XA library. By default, its value is zero. You can set it to any combination of the following values:

  • 0x1, which enables you to trace the entry and exit to each procedure in the XA interface. This value can be useful in seeing exactly which XA calls the TP Monitor is making and which transaction identifier it is generating.

  • 0x2, which enables you to trace the entry to and exit from other non-public XA library routines. This is generally of use only to Oracle Database developers.

  • 0x4, which enables you to trace various other "interesting" calls made by the XA library, such as specific calls to the OCI. This is generally of use only to Oracle Database developers.


The flags are independent bits of an ub4, so to obtain printout from two or more flags, you must set a combined value of the flags.

Trace File Locations

The XA application determines a location for the trace file according to the following algorithm:

  1. The LogDir directory specified in the open string.

  2. If you do not specify LogDir in the open string, then the Oracle XA application attempts to create the trace file in the following directory (if the Oracle home is accessible):

    • %ORACLE_HOME%\rdbms\trace on Windows

    • $ORACLE_HOME/rdbms/log on UNIX

  3. If the Oracle XA application cannot determine where the Oracle home is located, then the application creates the trace file in the current working directory.

Managing In-Doubt or Pending Transactions

In-doubt or pending transactions are transactions that have been prepared but not yet committed to the database. In general, the TM provided by the TPM system should resolve any failure and recovery of in-doubt or pending transactions. The DBA may have to override an in-doubt transaction if the following situations occur:

  • It is locking data that is required by other transactions.

  • It is not resolved in a reasonable amount of time.

Refer to the TPM documentation for more information about overriding in-doubt transactions in such circumstances and about how to decide whether the in-doubt transaction should be committed or rolled back.

Using SYS Account Tables to Monitor XA Transactions

The following views under the Oracle Database SYS account contain transactions generated by regular Oracle Database applications and Oracle XA applications:





For transactions generated by Oracle XA applications, the following column information applies specifically to the DBA_2PC_NEIGHBORS table:

  • The DBID column is always xa_orcl

  • The DBUSER_OWNER column is always db_namexa.oracle.com

Remember that the db_name is always specified as DB=db_name in the open string. If you do not specify this field in the open string, then the value of this column is NULLxa.oracle.com for transactions generated by Oracle XA applications.

For example, you could use the following SQL statement to obtain more information about in-doubt transactions generated by Oracle XA applications.

AND n.DBID = 'xa_orcl';

Alternatively, if you know the format ID used by the transaction processing monitor, then you can use DBA_PENDING_TRANSACTIONS or V$GLOBAL_TRANSACTION. Whereas DBA_PENDING_TRANSACTIONS gives a list of prepared transactions, V$GLOBAL_TRANSACTION provides a list of all active global transactions.

XA Issues and Restrictions

This section contains the following topics:

Using Database Links in XA Applications

Oracle XA applications can access other Oracle Database instances through database links with the following restrictions:

  • They must use the shared server configuration.

    The transaction processing monitors (TPMs) use shared servers to open the connection to an Oracle Database A. Then the operating system network connection required for the database link is opened by the dispatcher instead of a dedicated server process. This allows different services or threads to operate on the transaction.

    If this restriction is not satisfied, then when you use database links within an XA transaction, it creates an operating system network connection between the dedicated server process and the other Oracle Database B. Because this network connection cannot be moved from one dedicated server process to another, you cannot detach from this dedicated server process of database A. Then when you access the database B through a database link, you receive an ORA-24777 error.

  • The other database being accessed should be another Oracle Database.

Assuming that these restrictions are satisfied, Oracle Database allows such links and propagates the transaction protocol (prepare, rollback, and commit) to the other Oracle Database instances.

If using the shared server configuration is not possible, then access the remote database through the Pro*C/C++ application by using EXEC SQL AT syntax.

The init.ora parameter OPEN_LINKS_PER_INSTANCE specifies the number of open database link connections that can be migrated. These dblink connections are used by XA transactions so that the connections are cached after a transaction is committed. Another transaction is free to use the database link connection provided the user that created the connection is the same as the user who created the transaction. This parameter is different from the init.ora parameter OPEN_LINKS, which specifies the maximum number of concurrent open connections (including database links) to remote databases in one session. The OPEN_LINKS parameter is not applicable to XA applications.

Managing Transaction Branches in XA Applications

Oracle Database transaction branches within the same global transaction can be coupled tightly or loosely. If the transaction branches are tightly coupled, then they share locks. Consequently, pre-COMMIT updates in one transaction branch are visible in other branches that belong to the same global transaction. In loosely coupled transaction branches, the branches do not share locks and do not see updates in other branches.

In a tightly coupled branch, Oracle Database obtains the DX lock before executing any statement. Because the system does not obtain a lock before executing the statement, loosely coupled transaction branches result in greater concurrency. The disadvantage is that all transaction branches must go through the two phases of commit, that is, the system cannot use XA one-phase optimization.

Table 15-9 summarizes the trade-offs between tightly coupled branches and loosely coupled branches.

Table 15-9 Tightly and Loosely Coupled Transaction Branches

Attribute Tightly Coupled Branches Loosely Coupled Branches

Two Phase Commit

Read-only optimization

[prepare for all branches, commit for last branch]

Two phases

[prepare and commit for all branches]


Database call


Using XA with Oracle Real Application Clusters

This section contains the following topics:

Managing Transaction Branches on Oracle Real Application Clusters (RAC)

Oracle Database permits different instances to operate on different transaction branches in RAC. For example, Node 1 can operate on branch A while Node 2 operates on branch B. If transaction branches are on different instances, then they are loosely coupled and do not share locks. In this case, Oracle Database treats different units of work in different application threads as separate entities that do not share resources.

A different case is when multiple instances operate on a single transaction branch. For example, assume that a single transaction lands on Node 1 and Node 2 as follows:

Node 1

  1. xa_start()

  2. SQL operations

  3. xa_end() (SUSPEND)

Node 2

  1. xa_start() (RESUME)

  2. xa_prepare()

  3. xa_commit()

  4. xa_end()

In the previous sequence, Oracle Database returns an error because Node 2 should not resume a branch that is physically located on a different node (Node 1).

The way to achieve tight coupling in RAC is to use DTP services, that is, services whose cardinality (one) ensures that all tightly-coupled branches land on the same instance—whether or not load balancing is enabled. Mid-tier components address Oracle Database by means of a common logical database service name that maps to a single RAC instance at any point in time. An intermediate name resolver for the database service hides the physical characteristics of the database instance. DTP services enable all participants of a tightly-coupled global transaction to create branches on one instance.

For example, when you use a DTP service, the following sequence of actions occurs on the same instance:

  1. xa_start()

  2. SQL operations

  3. xa_end() (SUSPEND)

  4. xa_start() (RESUME)

  5. SQL operations

  6. xa_prepare()

  7. xa_commit() or xa_rollback()

Moreover, multiple tightly-coupled branches land on the same instance if each addresses the Oracle RM with the same DTP service.

To leverage all instances in the cluster, create multiple DTP services, with one or more on each node that hosts distributed transactions. All branches of a global distributed transaction exist on the same instance. Thus, you can leverage all instances and nodes of a RAC cluster to balance the load of many distributed XA transactions, thereby maximizing application throughput.

See Also:

Oracle Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide to learn how to manage distributed transactions in a Real Application Clusters configuration.

Managing Instance Recovery in Real Application Clusters

Prior to Oracle Database 10g Release 2 (10.2), the onus of detecting failure and triggering failover and failback in RAC was on the TM. In order to ensure information about in-doubt transactions is propagated to DBA_2PC_PENDING, TM was required to invoke xa_recover() before TM can proceed to resolve the in-doubt transactions. If an instance failed, then the XA client library could not fail over to another instance until it had run the SYS.DBMS_SYSTEM.DIST_TXN_SYNC procedure to ensure that the undo segments of the failed instance were recovered. In the current release there is no such requirement to invoke xa_recover() in cases where the TM has enough information about in-flight transactions.


In releases subsequent to Oracle Database 9i Release 2, xa_recover() is required to wait for distributed DML to complete on remote sites.

Using DTP services in RAC has the following benefits:

  • Automates instance failure detection.

  • Automates instance failover and failback. When an instance fails, the DTP service hosted on this instance fails over to another instance. The failover forces clients to reconnect; nevertheless, the logical names for the service remain the same. Failover is automatic and does not require an administrator intervention. The administrator can induce failback by a service relocate command, but all failback-related recovery is automatically handled within the database server.

  • Enables Oracle Database rather than the client to drive instance recovery. The database does not require mid-tier TM involvement to determine the state of transactions prepared by other instances.

Global Uniqueness of XIDs in Real Application Clusters

The TM must maintain the global uniqueness of transaction IDs (XIDs). According to the XA specification, the RM must accept XIDs from the TM. XA on RAC cannot determine whether a given XID is unique throughout the cluster.

For example, suppose that there is an XID Fmt(x).Tx(1).Br(1) on RAC instance 1 and another XID Fmt(x).Tx(1).Br(1) on RAC instance 2. Each of these can start a branch and execute SQL even though the XID is not unique across RAC instances.

SQL-Based XA Restrictions

This section describes restrictions concerning the following SQL operations:

Rollbacks and Commits

Because the transaction manager is responsible for coordinating and monitoring the progress of the global transaction, the application should not contain any Oracle Database-specific statement that independently rolls back or commits a global transaction. However, you can use rollbacks and commits in a local transaction.

Do not use EXEC SQL ROLLBACK WORK for precompiler applications when you are in the middle of a global transaction. Similarly, an OCI application should not execute OCITransRollback(), or the Version 7 equivalent orol(). You can roll back a global transaction by calling tx_rollback().

Similarly, a precompiler application should not have the EXEC SQL COMMIT WORK statement in the middle of a global transaction. An OCI application should not execute OCITransCommit() or the Version 7 equivalent ocom(). For example, use tx_commit() or tx_rollback() to end a global transaction.

DDL Statements

Because a DDL SQL statement, such as CREATE TABLE, implies an implicit commit, the Oracle XA application cannot execute any DDL SQL statements.

Session State

Oracle Database does not guarantee that session state will be valid between TPM services. For example, if a TPM service updates a session variable (such as a global package variable), then another TPM service that executes as part of the same global transaction may not see the change. Use savepoints only within a TPM service. The application must not refer to a savepoint that was created in another TPM service. Similarly, an application must not attempt to fetch from a cursor that was executed in another TPM service.


Do not use the EXEC SQL command to connect or disconnect. That is, do not use EXEC SQL CONNECT, EXEC SQL COMMIT WORK RELEASE or EXEC SQL ROLLBACK WORK RELEASE.

Miscellaneous Restrictions

Note the following restrictions:

  • Oracle Database does not support association migration (a means whereby a transaction manager may resume a suspended branch association in another branch).

  • The optional XA feature asynchronous XA calls is not supported.

  • Set the TRANSACTIONS initialization parameter to the expected number of concurrent global transactions. The initialization parameter OPEN_LINKS_PER_INSTANCE specifies the number of open database link connections that can be migrated. These database link connections are used by XA transactions so that the connections are cached after a transaction is committed.

  • The maximum number of xa_open() calls for each thread is 32.

  • When building an XA application based on TP-monitor, ensure that the TP-monitors libraries (that define the symbols ax_reg and ax_unreg) are placed in the link line before Oracle Database's client shared library. If your platform does not support shared libraries or if your linker is not sensitive to ordering of libraries in the link line, use Oracle Database's non-shared client library. These link restrictions are applicable only when using XA's dynamic registration (Oracle XA switch xaoswd).