XA Session Manager for Oracle OCI, version 9i Client

This chapter covers issues relating to using the Oracle9i version of the Oracle Call Interface (OCI) database with the XA Session Manager implementation.

This chapter provides you with information on the specific database issues and requirements for using the Oracle9i version of the Oracle Call Interfaces (OCI) and the Oracle9i database with the VisiTransact using XA transaction coordination. This includes software requirements, installation and configuration information, Session Manager and XA Resource Director configuration attributes, and programming restrictions.

VisiTransact transactional data access occurs through the use of the Session Manager for OCI and the Oracle XA libraries. A connection to the database is established when the application requests a Connection object from the Session Manager. The application can then obtain a native handle, which can be used for making normal OCI calls.

The information covered in this chapter focuses on the specific requirements for accessing an Oracle DBMS with VisiTransact using the standard XA commit protocol. To properly install and configure Oracle, you need to follow the instructions in the documentation shipped with your Oracle database.

Who should read this chapter

System administrators and database administrators responsible for administering this database should read this chapter before installing and configuring the DBMS if it will be used for transaction processing. In particular, refer to the sections identified below. Application developers building applications with VisiTransact should review the information in “Programming restrictions”.

Sections	System administrators	Database administrators	Application developers
Oracle9i Software Requirements	X	X
Oracle9i Installation and Configuration Issues	X	X
Required Environment Variables, Troubleshooting	X	X	X
Session Manager Connection Profile Attributes	X	X
Programming Restrictions		X	X
Troubleshooting	X	X	X

Oracle9i software requirements

You must install the Oracle9i client libraries on every machine on which you will run the XA Resource Director or an application built using the Session Manager. The XA Resource Director and the Session Manager are components of VisiTransact.

The following sections list database client and server requirements by platform.

Client requirements

The following Oracle client components for Oracle OCI must be installed and configured on each node which runs the XA Resource Director or an application built using the Session Manager:

•	Oracle OCI, version 9i must be installed on Solaris

•	Oracle XA Libraries

Server requirements

The following Oracle server components must be installed and configured on each database server machine:

•	Oracle Server, version 9i

•	Oracle Distributed Database option

Oracle9i installation and configuration issues

The following sections identify Oracle installation and configuration software issues.

Installation requirements

To install Oracle, you will need the following:

•	Oracle installation and configuration guide

•	Corresponding release bulletins

Database configuration

Use the init.ora parameters, described in the following table, to help configure your database for use with the XA Session Manager for Oracle OCI.

init.ora parameter	Description
transactions	The number of distributed transactions in which the database can concurrently participate.
sessions	The total number of user and system sessions.
processes	See the Oracle9i Server Administrator's Guide for more information on setting this parameter.
distributed_lock_timeout	The amount of time in seconds for distributed transactions to wait for locked resources.

With VisiTransact, the number of distributed transactions is limited by the database init.ora parameter transactions. Transactions remain active from the time of the first getConnection() or getConnectionWithCoordinator() call until the commit or rollback is complete. The default setting for transactions is generally set too low for use with the Session Manager. The default is system dependent.

With Oracle OCI, each distributed transaction, as opposed to a connection, consumes a database session. Make sure that init.ora parameters, sessions and processes, are set high enough to accommodate the distributed transactions as well as other applications' sessions.

The use of distributed transactions like XA may restrict the use of other Oracle features on some platforms. For instance, the use of the Oracle Parallel Server option may be restricted on some platforms.

Note

See Oracle documentation for information on how to set init.ora parameters and for information about the interaction of Oracle XA with other Oracle features, including Oracle Parallel Server and Oracle Replication.

DBA_PENDING_TRANSACTIONS view

The view, DBA_PENDING_TRANSACTION is used during recovery processing by the XA Resource Director to synchronize transaction information between the database and the VisiTransact Transaction Service. All users specified as Oracle userids in Oracle9i Session Manager profiles must be granted the SELECT privilege on this view.

To make sure that the permissions to the view are correct and that recovery processing can take place, log into Oracle using SQL*Plus as the userid for the XA Resource Director and perform the following query:

select count (*) from SYS.DBA_PENDING_TRANSACTIONS;

If you receive Oracle error “ORA-00942: the table or view does not exist,” then the XA Resource Director will not be able to access this view. The user can either logon as user sys or system or connect internally from the server manager to grant the select privilege on this view to the appropriate user.

Required environment variables

The PATH environment variable needs to include the path to the Oracle client directory where the database client libraries are installed, as well as the path to the Session Manager libraries.

LD_LIBRARY_PATH
PATH

Add $ORACLE_HOME/bin to your PATH and $ORACLE_HOME/lib32 (or $ORACLE_HOME/lib for 64 bit applications) to your LD_LIBRARY_PATH. For example, with the Borne shell:

LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:${ORACLE_HOME}/lib32
PATH=${ORACLE_HOME}/bin:${PATH}

Session Manager connection profile attributes

The following table contains the configuration profile attributes which are specific to the XA Session Manager for Oracle OCI.

Attribute	UI label	Description	Range
heartbeat_retry_period	Heartbeat Retry Period	The number of seconds between heartbeats to the VisiTransact Transaction Service instance after a missed heartbeat. Used to detect the reactivation of an instance of the VisiTransact Transaction Service in order to begin recovery. Used in the Resource Director only.	> 0
heartbeat_watch_period	Heartbeat Watch Period	The number of seconds between heartbeats to the VisiTransact Transaction Service instance. Used to automatically detect VisiTransact Transaction Service instance failures. Used in the Resource Director only.	> 0
oracle_txn_idle_timeout	Transaction Idle Timeout	The timeout (in seconds) that an unprepared transaction can be idle before Oracle rolls back the transaction. When using this attribute, consider the timeout period set for the VisiTransact Transaction Service.	> 0
oracle_xa_logdir	Log Directory Path	Path to the directory where the Oracle XA log files are to be written.	0 to 256 characters long
resource_director_name	Resource Director	The name of the Resource Director to be used.	1 to 128 characters long
native_handle_type	Native Handle Type	The type of the native connection handle requested by the application.	Valid values are Lda_Def and ITSoracle9i_ handles

Using the Session Manager with the OCI 9i API

In Oracle9i, the OCI interface has been completely rewritten. With this new interface, several handles are needed in order to executed SQL statements. In order to use this API with the Session Manager, perform the following steps:

1	In the connection profile, set the attribute native_handle_type to ITSoracle9i_handles.

2	Include the file, ora9i_sessmgr.h, in the application source to define the object ITSoracle9i_handles.

3	Cast the return value of Connection::getNativeConnectionHandle() to the type ITSoracle9i_handles * (a pointer to an object of type ITSoracle9i_handles).

4	Use accessor methods provided by the class ITSoracle9i_handles to obtain the various handles needed. These methods are:

•	OCISvcCtx *getSvcCtx();

•	OCIEnv *getEnv();

•	OCIError *getError();

Do not attempt to deallocate the objects obtained through the ITSoracle9i_handles object; these object instances are managed by the Session Manager.

Programming restrictions

The following restrictions apply when programming an application for transaction processing:

•

You must use a Connection object in the thread in which it was created. This is a restriction of the Oracle9i XA implementation that means that you can only use the native connection handle obtained from a Connection object instance in the thread that obtained the object. Using this connection handle on any other threads will cause unexpected results.

•

Do not use DDL statements in your application. This restriction means that DDL SQL statements will not be supported in the Oracle XA application. This is because a DDL SQL statement such as CREATE TABLE performs an implicit commit. Any required DDL statements must be performed by a process which does not use the XA protocol.

The following operations, shown in the following table, cannot be used on connections obtained through the Session Manager.

Operations	Disallowed SQL commands	9i API disallowed OCI calls
Connection Operations	CONNECT	OCISvcCtxLogon OCISvcCtxLogoff
Transaction Operations	COMMIT ROLLBACK SAVEPOINT SET TRANSCTION (READ ONLY|READWRITE|USE ROLLBACK SEGMENT)	OCITransCommit OCITransRollback OCIStmtExecute in OCI_COMMIT_ON_ SUCCESS mode
Implicit Operations	DDL SQL statements (for example, CREATE TABLE, CREATE INDEX)

Troubleshooting

This section identifies problems that could occur when using the XA Session Manager for Oracle OCI with the Oracle database and provides you with suggestions for troubleshooting the problem.

VisiTransact message log

The VisiTransact message log may contain Session Manager and native Oracle error messages when a connection or transaction error happens.

Using the xa_trc files

If errors occur that indicate problems with the XA code, more information on any Oracle errors can be found in the xa_*.trc files. These files will be placed in the log directory specified in the defined connection profile. If a log directory is not specified in the Session Manager connection profile, the xa_*.trc files will be placed in the $ORACLE_HOME/rdbms/log directory if $ORACLE_HOME is available, or in the current directory if $ORACLE_HOME is not available, when the process is started.

Note

If a directory is specified but does not exist, there will be no log file and you will not receive a warning.

Distributed update problems

A network or system failure can cause the following types of problems:

•	A prepare or commit being processed may not be completed at all nodes of the session when a failure occurs.

•	If a failure persists (for example, if the network is down for a extended period), the data exclusively locked by in-doubt transactions (prepared, but not committed or rolled back) is not available for statements of other transactions.

Note

See Oracle documentation for more information on the behavior of distributed updates where one Oracle node serves as a subcoordinator for another Oracle database.

Data access failures

When a user issues a SQL statement, Oracle9i attempts to lock the required data to successfully execute the statement. However, if the requested data is being handled by statements of other uncommitted transactions and continues to remain locked for long periods of time, a timeout occurs.

Lock from in-doubt transaction

A query or DML statement that requires locks on a local database may be blocked indefinitely due to the locked resources of an in-doubt distributed transaction. In this case, the following error message is returned to the user:

ORA-01591: lock held by in-doubt distributed transaction <IDt>

In this case, the SQL statement is rolled back immediately. The rollback of the SQL statement does not automatically force a rollback of the transaction. The application that executed the statement can try to re-execute the statement later. If the lock persists, the user should contact an Administrator to report the problem, including the ID of the in-doubt distributed transaction.

An in-doubt transaction is a transaction in the prepared state that has not been committed or rolled back.

Transaction timeout

A DML statement that requires locks on a remote database may be blocked if another transaction currently has locks on the requested data. If these locks continue to block the requesting SQL statement, a timeout occurs, the statement is rolled back and the following error message is returned to the user.

ORA-02049: timeout: distributed transaction waiting for lock

In this case, the SQL statement is rolled back immediately. The rollback of the SQL statement does not automatically force a rollback of the transaction. The application should proceed as if a deadlock has been encountered. The application that executed the statement can try to re-execute the statement at a later time. If the lock persists, the user should contact an administrator to report the problem.

The timeout interval described in the above situation can be controlled with the initialization parameter distributed_lock_timeout. This interval is in seconds. For example, to set the timeout interval for an instance to 30 seconds, include the following line in the associated parameter file:

DISTRIBUTED_LOCK_TIMEOUT=30

With the above timeout interval, the timeout errors discussed in the previous section occur if a transaction cannot proceed after 30 seconds of waiting for unavailable resources.

See “Database configuration” for a description of the distributed_lock_timeout parameter.

Oracle error messages

The VisiTransact Message Log contains Oracle error messages which could help you troubleshoot connections and transaction errors including the following:

Error message	Description	Solution
ORA-12154	The process limit for file descriptors (ulimit) on Solaris is set too low for multithreaded applications.	Check profile name for correct database name. Check the tnsnames.ora file for matching service names entry. Check that the database and Oracle listener process are up. Check that you have set your File Descriptor limit (ulimit), on Solaris, high enough to assure that you can open connections. See the Solaris Operating System documentation for information on setting the ulimit command.

Forcing heuristic completion

Use COMMIT FORCE <local transaction id> or ROLLBACK FORCE <local transaction id>—where <local transaction id> comes from the dba_2pc_pending table—to force completion of a heuristic transaction. Refer to Oracle9i Distributed Database Systems documentation for more information.