Platform SDK: Transaction Server

Setting Up MTS to Access Oracle

[This product will work only on Windows NT 4.0 versions and earlier. For Windows 2000 and later, see COM+ (Component Services).]

Microsoft Transaction Server components may access:

Oracle 7 and Oracle 8 databases
Oracle databases on both Windows NT and Unix systems
Oracle Workgroup Servers
Oracle Enterprise Servers
Oracle Parallel Servers.

This section includes the following topics:

Required Software

Setting Up Oracle Support

Testing Installation and Configuration of MTS Support for Oracle

Validating Oracle Installation and Configuration Using the Sample Bank Application

Administering Oracle and Microsoft Distributed Transaction Coordinator

Known Limitations of MTS Support for Oracle

Required Software

The following software is required to access an Oracle database from Microsoft Transaction Server components.

Component	Version
Oracle Database on Windows NT	7.3.4 or Oracle 8
Oracle Database on Unix	(with patches) or Oracle 8
Microsoft Transaction Server 2.0	2.0
Microsoft Oracle ODBC Driver 2.0	02.73.7283.1 or later
ActiveX Data Objects (ADO)	1.5 or later

Important Note: Earlier versions of the software will not work properly. Please ensure you install these or later versions of the software. Failing to do this is by far the most common source of problems when trying to use Microsoft Transaction Server with Oracle.

Oracle 7.3 Database for Windows NT

Your transactional Microsoft Transaction Server components may access an Oracle 7.3 database on Windows NT. You may use either the Oracle 7.3.4 Workgroup Server release for Windows NT or the Oracle 7.3.4 Enterprise Server release for Windows NT. Earlier releases of Oracle for Windows NT will not work in conjunction with MTS transactions.

Oracle 8 Database on Windows NT

Your transactional Microsoft Transaction Server components may access an Oracle 8 database on Windows NT.

You must install Oracle 7.3.4 client software on the system containing your Microsoft Transaction Server components. The Microsoft Oracle ODBC Driver 2.0 only works with Oracle’s 7.3 client interfaces. It does not work with Oracle 8 client interfaces.

The Oracle 8 documentation discusses how to install and configure Oracle 8 for coexistence with Oracle 7 client software. For more information consult appendix F in the following Oracle publication:

Oracle 8™
Oracle Networking Products
Release 8.0 for Windows Platforms
Getting Started

Oracle 7 Database for UNIX

Your transactional Microsoft Transaction Server components may access an Oracle 7.3 database on Unix.

You must install the Oracle 7.3.3 release (or later) for that UNIX platform. In most cases, you will also be required to install an Oracle 7.3.3 patch release for Oracle on UNIX.

You must check with Oracle Customer Support to determine if an Oracle 7.3.3 patch release is required for your UNIX platform. Explain that you are going to access your Oracle database on UNIX using the XA transaction support that is included in the Oracle 7.3.4 release on Windows NT.

Oracle 8 Database on UNIX

Your transactional Microsoft Transaction Server components may access an Oracle 8 database on Unix.

You must install Oracle 7.3.4 client software on the Windows NT system containing your Microsoft Transaction Server components. The Microsoft Oracle ODBC Driver 2.0 only works with Oracle’s 7.3 client interfaces. It does not work with Oracle 8 client interfaces.

Oracle Client Software

You must install Oracle 7.3.4 client software on the Windows NT or Windows 98 system containing your Microsoft Transaction Server components. The Microsoft Oracle ODBC Driver 2.0 only works with Oracle’s 7.3 client interfaces. It does not work with Oracle 8 client interfaces.

Oracle OCIW32.DLL

You must install the specified version of Oracle OCIW32.DLL on your You must install the Oracle OCIW32.DLL that is designed to work with the Oracle 7.3 release. This DLL is contained in the “WIN32\V7\RSF73” directory of the Oracle 7.3 CDROM.

Oracle also includes an older version of the OCIW32.DLL on the Oracle 7.3 CDROM that is designed to work with the Oracle 7.2 release. This DLL is contained in the “WIN32\V7\RSF72” directory. This DLL will not work with Microsoft Transaction Server.

The following version of the Oracle OCIW32.DLL is provided in the “WIN32\V7\RSF72” director and is known to fail when used with Microsoft Transaction Server:

Version 7.x
Thursday, February 01, 1996 12:50:06 AM
Size 36 KB

Oracle XA73.DLL

You must install the specified version of Oracle XA73.DLL on your Microsoft Transaction Server system.

Microsoft Transaction Server 2.0

You must install Microsoft Transaction Server 2.0 if you wish to access an Oracle database using transactional Microsoft Transaction Server components.

Microsoft ODBC Driver for Oracle

The Microsoft ODBC Driver 2.0 for Oracle (MSORCL32.DLL) version 02.73.7283.1 or later is required if you wish to use Microsoft Transaction Server transactions. The Windows NT 4.0 Option Pack program automatically installs this DLL.

If you wish to access an Oracle database, we strongly suggest that you use the new Microsoft Oracle ODBC Driver 2.0 even if you do not require transaction support. This new driver offers better performance than the Oracle 1.0 driver it replaced The Oracle 1.0 driver serialized all activity at the driver level; requests were single-threaded through the driver. The Oracle 2.0 driver serializes all activities at the connection level. This allows different database connections to be used in parallel.

ActiveX Data Objects (ADO)

If your application uses ADO, you must install ADO version 1.5. Earlier ADO releases will not work with the new ODBC 3.5 Driver Manager. ADO 1.5 is included in the Windows NT 4.0 Option Pack.

Setting Up Oracle Support

To set up Oracle support for MTS transactional components

Install Oracle Database Server Software.

Install the appropriate Oracle software on your database server system as described above.

Install Oracle Client Software.

Install Oracle 7.3.4 client software on your Microsoft Transaction Server system.

Ensure that the correct version of the Oracle OCIW32.DLL is installed as described in the Required Software section.

Install Microsoft Transaction Server 2.0

When you install Microsoft Transaction Server 2.0, the following software will be installed.

Microsoft Transaction Server 2.0, including the Microsoft OCI Interface
Microsoft ODBC 3.5 Driver Manager
Microsoft ODBC Driver for Oracle 2.0
ADO 1.5

Delete the DTCXATM.LOG.

If you have never installed the Microsoft Transaction Server 2.0 Beta release, skip this step.

If you previously installed the Microsoft Transaction Server 2.0 Beta release, use the Windows Explorer to determine if the DTCXATM.LOG file is present on your system. If so, stop the Microsoft DTC service and delete the DTCXATM.LOG file.

You should only delete the DTCXATM.LOG file once when you first upgrade from the Microsoft Transaction Server 2.0 beta release. You should never delete the DTCXATM.LOG file thereafter, because it may contain vital recovery information.

Enabling Oracle XA Transaction Support

Perform the following steps to enable Oracle XA transaction support:

The Oracle system administrator must create views known as V$XATRANS$. To do this, the administrator must run an Oracle-supplied script, named "xaview.sql". This file can be found in C:\ORANT\RDBMS73\ADMIN.
The Oracle system administrator must grant SELECT access to the public on these views.
```
Grant Select on V$XATRANS$ to public.
```
In the Oracle Instance Manager, click “Advanced Mode” on the View menu and select “Initialization Parameters” in the left pane. In the right pane, select “Advanced Tuning” and increase the "distributed_transactions" parameter to allow more concurrent MTS transactions to update the database at a single time.

Consult your Oracle Server documentation for more information about configuring Oracle XA transaction support.

Configuring Integrated Security

Integrated security allows an Oracle database to rely upon Windows NT authentication to validate database users. This permits a user to log in to Oracle without supplying a separate login ID or password. Users can maintain one login ID and password for both Windows NT and Oracle.

If your Microsoft Transaction Server components always supply a login ID and password when connecting to Oracle databases, then you are not using integrated security. This is true whether your applications specify the login ID and password directly or indirectly through a DSN. In either event, you are not using integrated security and can ignore this step.

If you use integrated security, you must configure MS DTC to run under a login ID and password authorized to connect to your Oracle database. This is required because during database recovery, MS DTC opens your Oracle database to tell it the outcome of in doubt transactions.

You can configure the login ID for the MS DTC as follows. From the “Start” menu select “Settings” and then “Control Panel”. Start the “Services” applet on the control panel. Double click on “MSDTC”. Select “Log On As” and specify a login ID and password. Use the Oracle security administration tools to ensure that the login ID you specify is authorized to open your Oracle database.

For more information on Oracle’s integrated Windows NT security facilities consult your Oracle documentation.

Configuring Oracle To Support More Connections

If you want to create more than a few dozen connections to an Oracle database, you must configure the Oracle server to support additional database connections. See “Configuring Oracle to Support a Large Number of Connections” below for more information.

Testing Installation and Configuration of MTS Support for Oracle

After installing and configuring Oracle support, you must validate your Oracle installation using the Oracle test program installed with MTS. The Oracle test program uses Oracle's OCI XA interfaces in much the same way that MTS uses them.

The Oracle test program determines whether you can connect to an Oracle database using Oracle's XA facility. The Oracle test program uses standard Oracle interfaces and transaction facilities. It makes no use of Microsoft Transaction Server or Microsoft Distributed Transaction Coordinator. Therefore, failure of the test program indicates that your Oracle system is improperly installed or configured. If the Oracle test program fails, reinstall and reconfigure Oracle, or contact the Oracle Support Organization for assistance

To run the Oracle test program

Verify that you have installed all of the correct versions of the software as described in Required Software.
Create an ODBC DSN that refers to your Oracle database. Ensure that your DSN uses the new Microsoft Oracle ODBC 2.0 driver.
Ensure that you have enabled Oracle XA support.
Delete all existing Oracle trace files from the machine containing the MTS components that access the Oracle database. The easiest way to do this is to use the Windows Explorer to locate and delete all *.TRC files.

If the Oracle test program fails, the trace files may help you determine the source of the problem. By deleting all obsolete trace files, you make it easier to find any newly created ones.
If you previously installed the Microsoft Transaction Server 2.0 beta release, use the Windows Explorer to determine if the DTCXATM.LOG file is present on your system. If so, stop the Microsoft DTC service and delete the DTCXATM.LOG file.

If you have never installed the Microsoft Transaction Server 2.0 beta release, you can skip this step.

You should only delete the DTCXATM.LOG file once when you first upgrade from the Microsoft Transaction Server 2.0 beta release. You should never delete the DTCXATM.LOG file thereafter, because it may contain vital recovery information.
From the MS-DOS Command prompt run the Oracle test program (TestOracleXaConfig.exe) and supply your Oracle server user ID, password, and service_name. For example:
```
c:>TestOracleXaConfig.exe  -U<user id> -P<Password>
-S<Service_Name as contained in the TNS file>.
```

If you run the test program with no parameters, it will display help information that describes the required parameters.

The test program will display information about each Oracle operation it performs and will indicate whether each operation was successful.

If the Oracle test program is able to connect to your Oracle database server without error, then it is very likely that MTS will work with Oracle also. If the Oracle test program reports any errors, follow these steps:
- Document the exact error message that the Oracle test program displays.
- Examine the Oracle trace file produced when running the Oracle test program. The Oracle trace information is located in the *.TRC file. The Oracle trace file contains extended error information that is extremely helpful in diagnosing problems.
- Contact your Oracle support representative for assistance.

Validating Oracle Installation and Configuration Using the Sample Bank Application

After you have validated your Oracle installation and configuration using the Oracle test program, you should use the Sample Bank Application supplied with Microsoft Transaction Server to ensure that Microsoft Transaction Server can access your Oracle database.

To validate Oracle support using Sample Bank

Verify that your Oracle system is installed and configured correctly using the Oracle test program provided by MTS. If the Oracle test programs reports any error, you must correct that problem before proceeding.

On the Oracle database server, create a table named "Account". The following example demonstrates how to set up the Account table.

Owner                        scott
Name of Table            Account
Column 1 Name        AccountNo    of type  NUMBER
Column 2 Name        Balance        of type  NUMBER

Populate the "Account” table with at least two rows. The following table illustrates how to populate the table.
```
AccountNo        Balance
1                        1000
2                        1000
```

On the Oracle database server, create a table named "Receipt". The following example demonstrates how to set up the Receipt table.

Owner                        scott
Name of Table            Receipt
Column 1 Name        NextReceipt    of type  NUMBER

Populate the "Receipt" table with at least one row. The following table illustrates how to populate the table.
```
NextReceipt
1000    
```
Create a file DSN using the ODBC configuration utility. Name the file DSN "MTSSamples". Then manually update the DSN file to add the user’s password. The following example demonstrates how to add the user’s password to a file DSN.
```
[ODBC]
DRIVER=Microsoft ODBC for Oracle
UID=scott
PWD=mypassword
ConnectString=myserver
SERVER=myserver
```
Save the file DSN and run the Sample Bank client.

Administering Oracle and Microsoft Distributed Transaction Coordinator

DLL Name Changes in Future Releases of Oracle

Oracle sometimes changes DLL names when they release new versions of their product. Microsoft Transaction Server relies upon knowing the names of some Oracle DLLs. MTS currently looks for the DLL names provided with the Oracle 7.3 release. If Oracle renames these DLLs, you must modify the values of the following registry keys:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Transaction Server\Local Computer\My Computer

Under this key there are two string-named values.

OracleXaLib "xa73.dll"
OracleSqlLib "sqllib18.dll"

Changing the Userid Under Which Microsoft Distributed Transaction Coordinator Runs

Microsoft Distributed Transaction Coordinator maintains information in the secure portion of the NT registry. It uses this information when performing recovery with XA compliant databases including Oracle. If you change the userid under which MS DTC executes, you must be careful to ensure that MS DTC can still access the information it has previously stored in the secure portion of the registry.

If you change MS DTC’s userid, and it is unable to access the information in the secure registry, you will see the following message in the Windows NT event log.

XATM log object failed to set log encryption key

You can correct this problem by following these steps:

Stop MS DTC.
Change the userid for MS DTC back to its previous value. Alternatively you can assign a userid that is a member of the administrative group. To change the userid and password, run the Services applet in the Control Panel, double click on the entry for “MSDTC”, and change the “log on as” value.
Restart MS DTC.

Configuring Oracle to Support a Large Number of Connections

If you want to create more than a few dozen connections to an Oracle database, you must configure the Oracle server to support additional database connections.

You may experience one or more of the following errors if you fail to do this:

Failures on SQLConnect calls.
Failures to enlist on the calling object’s transaction, which may result in any of the following errors in the Oracle trace file:
- Too many sessions
- TNS server failed to locate the server name
- Too many distributed transactions
Timeouts while waiting for database locks. This is likely to occur if the configured number of locks is insufficient for the number of concurrently active transactions.
Record collision due to locks held by in-doubt transactions.

If you experience any of these problems, consider increasing the following Oracle server configuration parameters:

Oracle Configuation Parameter
sessions
distributed_lock_timeout
distributed_transactions
dml_locks
max_transaction_branches
open_cursors
processes
queuesize

Sessions

The value for sessions should typically be three times the total number of database connections that you expect your applications to make.

Queuesize

If the queue size for the Oracle Listener process is inadequate, the Listener sometimes falls behind and rejects database open requests. This happens if the Oracle Listener receives too many connection requests and its queue overflows. A client that encounters this error reportsthe error message ‘ORA-12541: No Listener’ and the client log or trace fileswill show the ECONREFUSED message.

To correct the connection request problem:

Stop the Oracle listener on the system containing the Oracle database server.
Increase the QUEUESIZE parameter in the LISTENER.ORA, TNSNET.ORA, or NAMES.ORA files on the Oracle database server system. Choose the queue size based on the number of simultaneous, or nearly simultaneous, connection requests you anticipate.

We suggest that you select a generous value for QUEUESIZE because the Oracle system typically opens more than one database connection for each transactional database connection that your application opens. As a result, the Oracle listener queue may become full and overflow.

To accommodate 100 requests, change the LISTENER.ORA file as follows:

QUEUESIZE = 100
Restart the Oracle listener.

Known Limitations of MTS Support for Oracle

ADO 1.5 Release Is Required When Using ODBC 3.5

If your applications use ADO, make certain that you install the ADO 1.5. Refer to the Required Software section for more information.

No Oracle Support on Digital Alpha Platform

Oracle database connectivity is not supported for Microsoft Transaction Server components running on the Digital Alpha platform. Support for Oracle on the Digital Alpha platform will be added in the Visual Studio 6 release.

Oracle OCIW32.DLL Version Problem

It is important that the correct version of the OCIW32.DLL is installed on your computer. You should check the version of the DLL any time you reinstall Oracle or Microsoft Transaction Server.

DLL Name Changes in Future Releases of Oracle

Oracle sometimes changes DLL names when they release new versions of their product. Microsoft Transaction Server relies upon knowing the names of some Oracle DLLs. Currently, MTS looks for the DLL names provided with Oracle version 7.3.3. As MTS cannot predict future names of these DLLs, you may need to modify the values in the following registry key when you upgrade your Oracle installation:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Transaction Server\Local Computer\My Computer

Under this key there are two string-named values.

OracleXaLib "xa73.dll"
OracleSqlLib "sqllib18.dll"