VisiTransact Guide : XA Session Manager for Oracle OCI, version 9i Client

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”.
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:
Server requirements
The following Oracle server components must be installed and configured on each database server machine:
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:
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.
See the Oracle9i Server Administrator's Guide for more information on setting this parameter.
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.
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
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:
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:
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.
OCIStmtExecute in OCI_COMMIT_ON_
SUCCESS
mode
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:
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:
The process limit for file descriptors (ulimit) on Solaris is set too low for multithreaded applications.
Check the tnsnames.ora file for matching service names entry.
Check that you have set your File Descriptor limit (ulimit), on Solaris, high enough to assure that you can open connections.
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.