Setting Up MTS to Access Oracle

You can enable transactional MTS components to access an Oracle 7.3.3 database through ODBC. MTS works with Oracle 7 Workgroup Server for Windows NT, Oracle 7 Enterprise Server for Windows NT, Oracle 7 Enterprise Servers on UNIX, and Oracle Parallel Server on UNIX.

Your MTS component may access an Oracle 8 database on either Windows NT or Unix provided your Microsoft Transaction Server component uses the Oracle 7 client software. MTS does not support Oracle 8 client software.

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

Known Limitations of MTS Support for Oracle

Required Software

Refer to the following table for a list of the software required to access an Oracle database from MTS components running on either the Windows NT or UNIX platform.

Important Earlier versions of the software will not work properly. Please ensure you install the correct versions of the software. Failing to do this is by far the most common source of problems when trying to use MTS with Oracle.

Oracle for Windows NT

You must install either the Oracle 7.3.3 Workgroup Server release for Windows NT or the Oracle 7.3.3 Enterprise Server release for Windows NT. The Oracle 7.3.2 and earlier releases of Oracle for Windows NT are not supported and will not work in conjunction with MTS transactions.

You must install Oracle 7.3.3 patch release 2 or later. This patch is required for all Oracle 7.3.3 clients accessing an Oracle 7.3.3 or Oracle 8 database. Oracle patch release 2 contains fixes that are required to make Oracle XA transaction support work properly on Windows NT. The Oracle 7.3.3 release will not work with MTS unless Oracle 7.3.3 patch release 2 or later is installed.

Note If you encounter problems setting up Oracle patch release 2 on Windows 95, contact Oracle for support.

To obtain Oracle 7.3.3 patch releases from the Oracle customer support organization you must submit a problem report to the Oracle Customer Support Organization. These patch releases were not available from the Oracle public web site at the time this note was written.

Oracle SQL*Net

You must install the Oracle SQL*Net 2.3.3 release for Windows NT. You can obtain this release from Oracle. Earlier versions of Oracle SQL*Net may not work.

Oracle OCIW32.DLL

You must ensure that the correct version of the Oracle OCIW32.DLL is installed. Be very careful to check the version installed on your computer.

The correct version of the Oracle OCIW32.DLL is:

Version 1, 0, 0, 5
Tuesday, March 18, 1997 2:47:52 PM
Size 18KB. 

The improper version of the Oracle OCIW32.DLL is:

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

You can obtain the correct version of this DLL from the Oracle 7.3.3 installation CD from the \WIN32\V7\RSF73 directory.

Oracle for UNIX

In order for transactional MTS components to access an Oracle 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 new XA transaction support that is now included in the Oracle 7.3.3 release on Windows NT.

The following patch releases are known to work:

Microsoft Transaction Server 2.0

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

Microsoft ODBC Driver for Oracle

The Microsoft ODBC 2.0 Driver for Oracle (MSORCL32.DLL) is required. 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 ODBC Driver for Oracle 2.0 even if you do not require transaction support. This new driver offers better performance than the ODBC 1.0 driver it replaces. The ODBC 1.0 driver serialized all activity at the driver level; requests were single-threaded through the driver. The ODBC 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 applications use ADO, you must install the 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 setup program.

Setting Up Oracle Support

To set up Oracle support for MTS transactional components

1. Install the Oracle 7.3.3 release on Windows NT.

If your Oracle database is located on a UNIX system, install the Oracle 7.3.3 release on that system.

1. Install the Oracle 7.3.3 patch 2 or later on Windows NT. The resulting Oracle version will be Oracle 7.3.3.2 or later depending upon which Oracle patch you install. You must install Oracle 7.3.3 patch 2 or later if you wish to access any Oracle 7 or Oracle 8 database on either Windows NT or Unix. These Oracle patches correct problems that affect Oracle clients.

If you are using UNIX, install any Oracle 7.3.3 patch releases that are required for your UNIX system. I

1. Ensure that the correct version of the Oracle OCIW32.DLL is installed as described in the Required Software section.
2. Install the Microsoft Transaction Server 2.0 version 3.0, which automatically installs the following:
   • Microsoft Transaction Server 2.0, including the Microsoft OCI Interface
   • Microsoft ODBC 3.5
   • Microsoft ODBC 2.0 driver for Oracle
   • ADO 1.5
3. Delete the DTCXATM.LOG. Use the Explorer to locate and delete this file if found. Note that the Microsoft Distributed Transaction Coordinator service must be stopped before the DTCXATM.LOG file can be deleted.
4. Enable Oracle XA Support

To enable an Oracle database to work with MTS transactions

1. The 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 usually be found in C:\ORANT\RDBMS73\ADMIN.
2. The system administrator must grant SELECT access to the public on these views.

   Grant Select on V$XATRANS$ to public.

3. In the Oracle Instance Manager, click Advanced Mode on the View menu and select Initialization Parameters in the left pane.
4. In the right pane, select Advanced Tuning and increase the "distributed_transactions" parameter to allow for more concurrent MTS transactions to update the database at a single time.

See your Oracle Server documentation for more information about configuring Oracle support for XA transactions.

Testing Installation and Configuration of MTS Support for Oracle

After installing and configuring Oracle support, you should 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 is installed or configured improperly. Reinstall and reconfigure Oracle, or contact your Oracle representative.

To run the Oracle test program

1. Verify that you have installed all of the correct versions of the software as described in Required Software.
2. Create an ODBC DSN that refers to your Oracle database. Ensure that your DSN uses the new Microsoft Oracle ODBC 2.0 driver.
3. Ensure that you have enabled Oracle XA support.
4. 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.
5. Delete the DTCXATM.LOG file, if found, from the computer hosting the MTS components that access the Oracle database. Use the Windows Explorer to locate and delete the DTCXATM.LOG file (if it is located on your computer).
6. From the MS-DOS Command Prompt run the Oracle test program (TestOracleXaConfig.exe) and supply your Oracle server user ID, password, and server name. For example:

   c:>TestOracleXaConfig.exe  -U<user id> -P<Password>
   -S<Server name as in the TNS file>.

If you run the test program with no parameters, the program will display help information that describes the required parameters. The test program will display information about each Oracle operation performed and will indicate whether each operation was successful.

1. 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 can 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

1. Ensure that you have verified that your Oracle support is installed and configured correctly using the Oracle test program provided by MTS.
2. On the 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

3. Populate the table with at least two rows. The following table illustrates how to populate the table.

   AccountNo        Balance
   1            1000
   2            1000

4. Create a file DSN using the ODBC configuration utility. Name the file DSN "MTSSamples". Then manually update the DSN file to add the password of the user. The following example demonstrates how to add the user password to a file DSN.

   [ODBC]
   DRIVER=Microsoft ODBC for Oracle
   UID=scott
   PWD=mypassword
   ConnectString=myserver
   SERVER=myserver

5. Save the file DSN and run the Sample Bank client.

Known Limitations of MTS Support for Oracle

ADO 1.5 Beta Release Is Required When Using ADO with 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 from Digital Alpha platforms running Microsoft Transaction Server.

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"

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 objects 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, increase the following Oracle server configuration parameters:

• Session count (This is typically three times the number of expected connections)
• Distributed transaction count
• Process count
• Lock count