Information about using Oracle with Microsoft Transaction Server and COM+ components (193893)

MORE INFORMATION

The following sections list the recommended order of installation of the software for COM+ and MTS components to work with Oracle databases. If you do not perform the installations in this exact order, the components may not work as expected because of configuration problems.

Windows 2000

To make sure that transactional COM+ components work with Oracle, follow these steps:

1. Install Windows 2000.
2. Install Oracle Client 8.1.7 with update 8.1.7.1.5. For information about how to install the latest update, contact Oracle.
3. Install Oracle Net8 with the latest updates.
4. Install Microsoft Data Access Components (MDAC) 2.6 Service Pack 1 (SP1) or later.
5. Update the client software registry keys. For more information, see "Step 7: Update Oracle Client software registry keys."

Windows NT 4.0

To make sure that transactional MTS components work with Oracle, follow these steps:

1. Install Windows NT with SP6a or later.
2. Install Oracle Client 8.1.7 with update 8.1.7.1.5. For information about how to install the latest update, contact Oracle.
3. Install Oracle Net8 with the latest updates.
4. Install the Windows NT Option Pack.
5. Install Windows NT SP6a.
6. Install MDAC 2.6 SP1 or later.
7. Update the client software registry keys. For more information, see "Step 7: Update Oracle Client software registry keys."

For more information about these steps, see the "Set up Oracle support" section.

Note All the version numbers that are listed in this article were current at the time that the article was written. We strongly recommend that you use the most current versions and updates of all Oracle software and Microsoft software. Earlier versions of some of the components that are listed in this article may work adequately in some circumstances but not in others.

Set up Oracle support

To set up Oracle to work with transactional COM+ components and MTS components, follow these steps.

Step 1: Install Oracle database server software

Install Oracle database server software on your database server system. If your COM+/MTS application accesses an Oracle database on Windows NT, Windows 2000, or UNIX, make sure that the latest Oracle update is installed on that system. To obtain the latest Oracle updates for Windows NT or Windows 2000, visit the following Oracle Web site: Log on to the site, and then select patches on the left navigation bar. To search for the latest updates, click Oracle Server as the Product family, and then select MS Windows NT/2000 as the operating system. For information about how to install the latest update, contact Oracle.

Note We recommend that you install Oracle Server and COM+/MTS on separate computers. Enable Oracle XA transaction supportIf you are using Oracle 8 or Oracle 8i, consider the following:

• Make sure that the V$XATRANS$ view and the DBA_PENDING_TRANSACTIONS view have been created in your Oracle 8 or Oracle 8i database. If this view does not exist, the Oracle system administrator must create it by running the Oracle-supplied script named XAVIEW.SQL. This file can be found in the Installation folder\ADMIN folder. This SQL script must be executed as the Oracle user "SYS."
• The Oracle system administrator must grant SELECT access to the public for the DBA_PENDING_TRANSACTIONS view.

Configure sufficient concurrent distributed transactionsIn the Oracle Instance Manager, follow these steps:

1. On the View menu, point to Advanced Mode, and then click Initialization Parameters in the left pane.
2. In the right pane, select Advanced Tuning, and then increase the distributed_transactions parameter to allow more concurrent MTS transactions to update the database at the same time.

For more information, see the "Configure Oracle to support many connections" section. Configure integrated securityNote If your COM+/MTS components always supply a login ID and a password when they connect to Oracle databases, you are not using integrated security. This is true whether your applications specify the login ID and the password directly or indirectly through a DSN. In either case, you do not use integrated security, and you can skip this step.

If you use integrated security, you must configure Microsoft Distributed Transaction Coordinator (MSDTC) to run under a login ID and a password that is authorized to connect to your Oracle database. This is required because during database recovery, MSDTC opens your Oracle database to tell it the outcome of in-doubt transactions.

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

Note For integrated security, the user ID in the connection string must to be a slash (/). Otherwise, all logins fail:

cn.Open "PROVIDER=MSDAORA.1;DATA SOURCE=TESTORA;USER ID=/;PASSWORD=;"

If you run the MSDTC/COM+ system on a Microsoft Cluster Server cluster, the account where MSDTC runs must also be a member of the Cluster Administrator Group for Microsoft Cluster Server.

To configure the login ID for MSDTC, follow these steps:

1. Click Start, point to Settings, and then click Control Panel.
2. Start the Services utility.
3. Double-click MSDTC.
4. Click Log On As, and then specify a login ID and a password.
   
   Note Use the Oracle security administration tools to make sure that the login ID that you specify is authorized to open your Oracle database.

For more information about Oracle integrated Windows NT and Windows 2000 security facilities, see your Oracle documentation. Configure Oracle Multi-Threaded Server support You must configure the Oracle Multi-Threaded Server feature if you want to open a database link to a remote Oracle database. You must complete this step because the Oracle database uses XA transaction support. The Oracle database must be able to move the XA transaction between processes (in the general case), so that it cannot have any operating system file descriptors open. Instead, it must use a virtual circuit to connect to the remote database. Virtual circuits are only supported with Oracle Multi-Threaded Server.

If the Oracle Multi-Threaded Server feature is not configured correctly, Oracle reports the following error message: Note Oracle refers to the Multi-Threaded Server feature with the acronym MTS. Microsoft documentation sometimes refers to Microsoft MTS with the acronym MTS. Configure Oracle to support more connectionsIf you want to create more than several dozen connections to an Oracle database, you must configure the Oracle server to support additional database connections. For more information, see "Configure Oracle to support many connections" in the "Administer Oracle and MSDTC" section.

Step 2: Install Oracle client software with the latest updates

Install Oracle client software with the latest updates on your COM+/MTS system. Oracle Client 8.1.7, with the latest update, 8.1.7.1.5, works correctly with transactional COM+/MTS components.

Important Make sure that you install Oracle Net8 when you install Oracle Client 8.1.7. Additionally, when you use Oracle 8.1.7, do not install Oracle Services for MTS if you are using Microsoft OLEDB/ODBC drivers. Oracle Services for MTS is not required for COM+/MTS components to work correctly with Oracle Database.

Customers frequently upgrade these Oracle update releases on the system that contains their Oracle database, but they cannot install the Oracle update release on the system that contains their COM+/MTS components. Oracle has corrected several bugs that affect XA transactional client support. You must install the latest Oracle update releases on your COM+/MTS system. To obtain the latest Oracle updates for Windows NT, visit the following Oracle Web site: Log on to the site, and then select patches on the left navigation bar. To search for the latest updates, click Oracle Server as the Product family, and then select MS Windows NT/2000 as the operating system. For information about how to install the latest update, contact Oracle.

Step 3: Install MTS 2.0

Install MTS 2.0. To do this, install Windows NT 4 Option Pack. When you install MTS 2.0, the following software is installed:

• MTS 2.0, including the Microsoft Oracle Call Interface (OCI) .dll file (Mtxoci.dll)
• Microsoft ODBC 3.5 Driver Manager
• Microsoft ODBC Driver for Oracle
• ActiveX Data Objects (ADO)

Note Ignore this step for Windows 2000.

Step 4: Install Windows NT 4.0 SP 6a

Install the Windows NT 4.0 SP 6a release that includes corrections for several XA-related MSDTC problems.

Important You must install Windows NT 4.0 Option Pack before you install Windows NT 4.0 SP 6a because the Window NT 4.0 Service Pack Setup program only updates MTS and MSDTC if Windows NT 4.0 Option Pack has already been installed.

Note Ignore this step for Windows 2000.

Step 5: Install MDAC 2.6 SP1 or later

Install MDAC 2.6 SP1 or later for Windows 2000-based computers. To obtain MDAC 2.6 SP1 or later, visit the following Microsoft Web site:

Step 6: Use Component Checker to see if there are any installation problems

After you install MDAC, you can use Component Checker to see if there are any installation problems. Component Checker is designed to help you determine version information and diagnose installation issues with MDAC. Component Checker runs on the following operating systems:

• Windows 95
• Windows 98
• Windows NT 4.0
• Windows 2000

Only 32-bit and 64-bit operating systems are supported. Component Checker has been updated to include MDAC 2.6 data. To download this tool, visit the following Microsoft Web site:

Step 7: Update Oracle Client software registry keys

Warning Serious problems might occur if you modify the registry incorrectly by using Registry Editor or by using another method. These problems might require that you reinstall your operating system. Microsoft cannot guarantee that these problems can be solved. Modify the registry at your own risk.
For information about how to edit the registry, see the following topics in Registry Editor Help:

• Changing keys and values
• Add and delete information in the registry
• Edit registry data

Note You must back up the registry before you edit it. If you are running Windows NT or Windows 2000, you must also update your Emergency Repair Disk (ERD).

If you use Oracle 8.1.7 Client software, modify the values of the following registry keys to make sure they match those that are specified as follows:

Oracle   Windows NT or Windows 9x           Windows 2000 
Client

8.1.7    [HKEY_LOCAL_MACHINE\SOFTWARE       [HKEY_LOCAL_MACHINE\SOFTWARE
         \Microsoft\Transaction Server      \Microsoft\MSDTC\MTxOCI]
         \Local Computer\My Computer]       "OracleXaLib"="oraclient8.dll" 
         "OracleXaLib"="oraclient8.dll"     "OracleSqlLib"="orasql8.dll"  
         "OracleSqlLib"="orasql8.dll"       "OracleOciLib"="oci.dll"
         "OracleOciLib"="oci.dll" 

Oracle   Windows Server 2003 
Client

9i       [HKEY_LOCAL_MACHINE\SOFTWARE
         \Microsoft\MSDTC\MTxOCI] 
         "OracleXaLib"="oraclient9.dll" 
         "OracleSqlLib"="orasql9.dll" 
         "OracleOciLib"="oci.dll"

Test the installation and the configuration of COM+/MTS support for Oracle

After you install and configure Oracle support, you must validate your Oracle installation. You can use the following tools to validate the configuration:

• Oracle Stress tool
• Oracle Test program

Both use Oracle OCI XA interfaces like the way that COM+/MTS uses them. The Oracle Test program determines whether you can use an Oracle XA facility to connect to an Oracle database. These programs use standard Oracle interfaces and transaction facilities. They do not use COM+/MTS or MSDTC. Therefore, failure of any one of the test programs indicates that your Oracle system is incorrectly installed or configured. If the test programs fail, you must reinstall and reconfigure Oracle or contact Oracle Support Services for more information.

After you have successfully completed these tests, you can use the Sample Bank application to test the setup (see the "Use the Sample Bank application to validate your Oracle installation and configuration" section). You can also write your own sample application to test the setup.

Oracle Stress tool

You can use the Oracle Stress tool to do the following:

• Check the Oracle client and server configuration.
• Stress test Oracle Server.

Obtain the Oracle Stress tool The following file is available for download from the Microsoft Download Center:
For more information about how to download Microsoft support files, click the following article number to view the article in the Microsoft Knowledge Base: Microsoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help prevent any unauthorized changes to the file.

You can use the C++ compiler to compile the source code. If you recompile the Oracle Stress tool, we recommend that you name the resulting program OracleStress.exe. Run the Oracle Stress toolTo run the Oracle Stress tool, follow these steps.

Note The following steps assume that the program is named OracleStress.exe.

1. Make sure that you have installed all the recommended software.
2. Open a command prompt.
3. For more information about how to use the tool, change the current working folder to the folder where OracleStress.exe is located, type OracleStress.exe at the command prompt, and then press ENTER.
   
   The following information appears on the screen:
   
   Syntax to use with the OracleStress tool:

   OracleStress.exe -S<server> -Q<SQL> [-U<username>] [-P<password>]
   [-T<threads>] [-I<iterations>] -[V<version>] [-N] [-W] [-O]
   [-F<Log File Name>]
   
   -S - Name of oracle server to connect to.
   -Q - SQL statement that each thread will execute.
   -U - Optional. Username. Defaults to "scott".
   -P - Optional. Password. Defaults to "tiger".
   -T - Optional. Number of threads to apply stress with. Defaults to "1".
   -I - Optional. Number of iterations per thread. Defaults to "100".
   -V - Optional. Version. Specifies Oracle version number. Can be one of: 7, 8, 8i, or 9i. Defaults to 8.
   -N - Optional. If specified, disables the use of XA transactions.
   -W - Optional. If specified, disables the display of warning level XA error messages.
   -O - Optional. If specified, open and close connection at each iteration.
   -F - Optional. Log file name. Defaults to "Ostress.txt". 

   Example:

   C:>OracleStress -SMyOracleServer -Q"Insert into Order Values (1, 'My order')" -UMyUserName  
   -PMySpecialPassword -T10 -V8i

   Note MyOracleServer is the name of the server that you are using. MyUserName is the user name, and MySpecialPassword is the password for the server.
4. The generated output file defaults to Ostress.txt. You can open it with Microsoft Notepad and see if it successfully executed.
5. If you receive any trace files that include Oracle errors, contact Oracle Support Services to resolve issues before using the COM+/MTS application.

Tips to use the Oracle Stress tool for stress-testing Oracle ServerConsider the following tips when you use the Oracle Stress tool:

• The SQL statement must be the same as or similar to the most complex queries in your application.
• The number of threads (-T) must be three times the estimated maximum number of concurrent connections that are open in your application. For example, if the maximum number of concurrent open connections in your application is 10, the number of threads must be 30.
• Specify a high number of iterations to keep the number of threads busy to test the reliability and performance of the server.

Note If the Oracle Stress tool can connect to your Oracle database server without error, it is very likely that COM+/MTS will also work with Oracle. If the Oracle Stress tool reports any errors, you must follow these steps:

1. Document the exact error message that is displayed by the Oracle Stress tool.
2. Examine the Oracle trace file that was produced when you ran the Oracle Stress tool. 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.
3. Contact Oracle Support Services for more information.

Oracle Test program

After you install and configure Oracle support, you must use the Oracle Test program to validate your Oracle installation. The Oracle Test program is available in two versions, one for Windows NT named TestOracleXAConfig.exe, and another for Windows 2000 named Msdtcora.exe. Obtain the Oracle Test program for COM+ on Windows 2000You can obtain the source code and the object code from the Platform SDK or from the Microsoft Download Center.

The following file is available for download from the Microsoft Download Center:
For more information about how to download Microsoft support files, click the following article number to view the article in the Microsoft Knowledge Base: Microsoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help prevent any unauthorized changes to the file.

Note You can use the TestOracleXAConfig.exe tool to verify the Oracle configuration and to troubleshoot problems that are related to Oracle if you are running an operating system that is earlier than Windows 2000. If you are running Windows 2000, use the Msdtcora.exe tool. The Msdtcora.exe tool uses the registry keys that are specified in the Update Oracle Client software registry keys step for Windows 2000. Obtain the Oracle Test program for MTS on Windows NTTo obtain both the source code and the object code for the Oracle Test program for Oracle 8 and Oracle Test program for Oracle 7.3, visit the following Microsoft Download Center Web site:Note The Oracle Test program for Oracle 7 is installed automatically when you install MTS. You can use the C++ compiler to compile the source code for Oracle 8. If you recompile the Oracle Test program, we recommend that you name the resulting program TestOracleXaConfig.exe. Run the Oracle Test programNote The following steps assume that the program is named TestOracleXaConfig.exe.

1. Make sure that you have installed all the recommended software.
2. Create an ODBC data source name (DSN) that refers to your Oracle database. Make sure that your DSN uses the Microsoft Oracle ODBC driver.
3. Make sure that you have enabled Oracle XA support.
4. Delete all existing Oracle trace files from the computer that contains the COM+/MTS components that access the Oracle database. To do this, use Windows Explorer to locate and delete all *.trc files. If the Oracle Test program fails, the trace files can help you determine the source of the problem. If you delete all obsolete trace files, you make it easier to find any newly created trace files.
5. At a command prompt, run the Oracle Test program (Msdtcora.exe or TestOracleXaConfig.exe), and then enter your Oracle server user ID, password, and service_name. For example, enter the following information if you use the Msdtcora.exe file:

   C:>msdtcora.exe -U<user id> -P<Password> -S<Service_Name as contained in the TNS file>

   You can enter the following information if you use the TestOracleXaConfig.exe file:

   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, the program provides help information that describes the required parameters. The test program provides information about each Oracle operation that it performs and indicates whether each operation was successful.

Note If the Oracle Test program can connect to your Oracle database server without error, it is very likely that COM+/MTS will also work with Oracle. If the Oracle Test program reports any errors, follow these steps:

1. Document the exact error message that is displayed by the Oracle Test program.
2. Examine the Oracle trace file that was created when you ran 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.
3. Contact Oracle Support Services for more information.

Use the Sample Bank application to validate your Oracle installation and configuration

After you use the Oracle Test program to validate your Oracle installation and configuration, you must use the Sample Bank application that is supplied with MTS to make sure that MTS can access your Oracle database.

Use the Sample Bank application to validate Oracle support

1. Use the Oracle Test program provided by MTS to verify that your Oracle system is installed and configured correctly. If the Oracle Test program reports any errors, you must correct the problem before you continue.
2. On the Oracle database server, create a table. Name the table Account. The following example shows 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 Account table with at least two rows. The following example shows how to populate the table:

   AccountNo                  Balance
   
        1                      1000
        2                      1000 

4. On the Oracle database server, create a table. Name the table Receipt. The following example shows how to set up the Receipt table:

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

5. Populate the Receipt table with at least one row. The following example shows how to populate the table:

   NextReceipt
   1000 

6. Use the ODBC configuration utility to create a file DSN. Name the file DSN MTSSamples, and then manually update the DSN file to add the user's password. The following example shows 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

7. Save the file DSN, and then run the Sample Bank client.

Administer Oracle and MSDTC

Change the MSDTC user ID

Microsoft Distributed Transaction Coordinator (MSDTC) maintains information in the security-enhanced part of the Windows NT registry or the Windows 2000 registry. MSDTC uses this information when it performs recovery with XA-compliant databases, including Oracle. If you change the user ID under which MSDTC runs, you must make sure that MSDTC can still access the information that it previously stored in the security-enhanced part of the registry. If you change the MSDTC user ID, and MSDTC cannot access the information in the security-enhanced registry, you receive the following error message in the Windows NT event log: XATM log object failed to set log encryption key To correct this problem, follow these steps:

1. Stop MSDTC.
2. Change the MSDTC user ID back to its previous value. Alternatively, you can assign a user ID that is a member of the administrative group. To change the user ID and password, follow these steps:
   1. In Control Panel, start the Services program.
   2. Double-click the entry for MSDTC, and then change the log on as value.
3. Restart MSDTC.

Configure Oracle to support many connections

If you want to create more than several dozen connections to an Oracle database, you must configure the Oracle server to support additional database connections. If you do not do this, one or more of the following problems may occur:

• Failures on SQLConnect calls.
• Failures to enlist on the calling object's transaction that may cause one or more 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 may occur while waiting for database locks. This problem is likely to occur if the configured number of locks is not sufficient for the number of active transactions.
• Record collision because of locks that are held by in-doubt transactions.

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

Oracle Configuration Parameter                        Recommended

Processes                                                Three times the maximum number                                                   
                                                         of connections you expect to make
                                                         to the database

Sessions                                                 (1.1 * PROCESSES) + 5

Transactions                                             (1.1 * SESSIONS)

Distributed_transactions                                 Transactions

Dml_locks                                                (4 * TRANSACTIONS)

MAX_TRANSACTION_BRANCHES                                 32 (OBSOLETE in Oracle 8i)

Open_cursors                                             1000

distributed_lock_timeout                                 300 (OBSOLETE in Oracle 8i)

Queuesize                                                Read Queuesize section 

Set the session values

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

Configure the QUEUESIZE parameter

If the QUEUESIZE parameter for the Oracle Listener process is not sufficient, the Listener process sometimes falls behind and rejects database open requests. This problem occurs if the Listener process receives too many connection requests and its queue overflows. A client that encounters this error reports the following error message: The client log files or trace files display the following error message: ECONREFUSED To correct this problem, follow these steps:

1. Stop the Oracle Listener process on the system that contains the Oracle database server.
2. Increase the QUEUESIZE parameter in the Listener.ora file, the Tnsnet.ora file, or the Names.ora file on the Oracle database server system.
   
   Note Set the value of QUEUESIZE parameter based on the number of simultaneous, or almost simultaneous, connections that you anticipate. We recommend that you select a generous value for the QUEUESIZE parameter because the Oracle 7.3 system typically opens three database sessions and Oracle 8 or Oracle 8i opens two database sessions for each transactional database connection that your application opens. Therefore, the Oracle Listener queue can become full and then overflow. For example, to handle 100 requests, change the Listener.ora file as follows:

   QUEUESIZE = 100 
   
   

3. Restart the Oracle Listener process.

Known limitations of COM+/MTS support for Oracle

COM+/MTS and Oracle Services for MTS

Oracle Services for MTS is not required for COM+/MTS applications if you are using the Microsoft OLEDB/ODBC provider. Do not install Oracle Services for MTS while you install the Oracle 8i client. We do not support this configuration and the applications may not work correctly if you install Oracle Services for MTS with the Microsoft ODBC/OLE DB provider. Contact Oracle Support for any problems that are related to using Oracle Services for MTS.

Oracle 8i and OLE transactions

Oracle 8i supports OLE transactions. This feature requires the use of the Oracle ODBC driver. Contact Oracle for information about the recommended configuration to use this feature, the required updates, and any related issues.

Microsoft Oracle ODBC Driver and Oracle 8

Currently, the Microsoft Oracle ODBC driver (Msorcl32.dll) does not work with the Compaq Alpha Oracle 8 client software. This limitation may be changed in a future release.

Oracle Ociw32.dll corrupts MSDTC during startup

This problem occurs because both MTS and MSDTC must load the Oracle Ociw32.dll file on their main thread before they perform any operation that might access an Oracle database. MTS must load the Mtxoci.dll file because your application may have to open a connection to an Oracle database. MSDTC must load the Mtxoci.dll file because it may have to perform Oracle database recovery. Because MTS cannot predict whether your application may want to use an Oracle database, it always loads the Ociw32.dll file. The Ociw32.dll file must load unconditionally at initialization because of the way that Oracle is implemented on Windows NT. Oracle implementation on Windows NT monitors all thread attaches. Oracle insists on seeing all the thread that are attached and only accepts database open requests from threads whose initial thread attach it has seen.

To work around this problem, you can rename or delete the Microsoft Oracle database support .dll file (Mtxoci.dll) if you never use Oracle with MTS or MSDTC. When MTS and MSDTC are initiated, they look for and then load the Mtxoci.dll file. The Mtxoci.dll file then loads the Oracle Ociw32.dll file. MTS and MSDTC continue to work correctly if they cannot locate and load the Mtxoci.dll file. However, they cannot access or recover an Oracle database if this .dll file is not present.

Note Check to make sure that you do not have multiple copies of the Ociw32.dll file on your COM+/MTS system. If you have multiple copies, an incorrect version of the .dll file may be loaded and cause unexpected behavior.

Microsoft provides third-party contact information to help you find technical support. This contact information may change without notice. Microsoft does not guarantee the accuracy of this third-party contact information.

The third-party products that this article discusses are manufactured by companies that are independent of Microsoft. Microsoft makes no warranty, implied or otherwise, regarding the performance or reliability of these products.