INFO: Using Oracle Databases with Microsoft Transaction Server

• Microsoft Transaction Server 2.0

SUMMARY

This article describes how to use Oracle databases with Microsoft Transaction Server (MTS).

This information replaces earlier documentation that described how to use Oracle with Microsoft Transaction Server (MTS) including the documentation that accompanied the Microsoft Transaction Server 2.0 release.

Microsoft Transaction Server components can access both Oracle 7 and Oracle 8 database servers. MTS components may access Oracle database servers on Windows NT, Unix, and other systems. MTS components can also access Oracle Workgroup Servers, Oracle Enterprise Servers, and Oracle Parallel Servers.

MORE INFORMATION

The following software is required to access an Oracle database from MTS components.

Oracle 7.3

Component                       Version
---------------------------------------------------------------

Oracle Server on Windows NT     7.3.3.5.2 or later
Oracle SQL*Net                  2.3.3.0.4 or later
Oracle Ociw32.dll               1.0.0.5 or later
Oracle Sqllib18.dll             1.8.3.0.4 or later
Oracle Xa73.dll                 7.3.3.5.2 or later
Oracle Server on UNIX           7.3.3 or later with patches 

Oracle 8

Component                           Version
---------------------------------------------------------------

Oracle Server on Windows NT         8.0.4.1.1c or later
Oracle Net8
Oracle Ociw32.dll                   8.0.4.0.0 or later
Oracle Sqllib80.dll                 8.0.4.1.0 or later
Oracle Xa80.dll                     8.0.4.1.0 or later
Oracle Server on UNIX               8.0 or later 

Microsoft

Microsoft Transaction Server 2.0    MTS 2.0 or later on Intel x86
                                    MTS 2.0 and Windows NT4 Service Pack 4
                                    or later on Compaq Alpha

Microsoft ODBC Driver for           02.73.7283.03 or later on Intel x86
Oracle (Msorcl32.dll)               2.573.2927 or later on Compaq Alpha

ActiveX Data Objects (ADO)*
(Msado15.dll)                       1.50.2.2404 (MDAC 1.5c) or later 

Oracle 7.3 Database Server on Windows NT

Oracle 8 Database Server on Windows NT

Oracle 7 Database Server on UNIX

Oracle 8 Database Server on UNIX

Oracle Client Software on Intel x86

Oracle Client Software on Compaq Alpha

Oracle Ociw32.dll

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

Microsoft Transaction Server 2.0

Microsoft ODBC Driver for Oracle

ActiveX Data Objects (ADO)

http://www.microsoft.com/data/

Oracle Access from MSCS (Wolfpack) Clusters

Setting up Oracle Support

1. Install Oracle Database Server software.
   
   Install the appropriate Oracle software on your database server system.
   
   If your Microsoft Transaction Server application is accessing an Oracle database on either Windows NT or Unix, ensure that the latest Oracle patch is installed on that system. You can obtain the latest Oracle patches for Windows NT from the Oracle FTP site. Go to ftp://oracle-ftp.oracle.com and select "server", "wgt-tech", "server", and "WindowsNT"



2. Install the Oracle Client software.
   
   Install the latest Oracle 7.3 or Oracle 8 client software on your Microsoft Transaction Server system.
   
   Make sure that the latest Oracle 7.3 or Oracle 8 Client software patch release is installed on the system containing your Microsoft Transaction Server components. Customers often upgrade these Oracle patch releases on the system containing their Oracle database but fail to install the Oracle patch release on the system containing their Microsoft Transaction Server components. Oracle has corrected several bugs that affect their XA transactional client support; therefore, installing the latest Oracle patch release on your Microsoft Transaction Server system is essential. You can obtain the latest Oracle patches Windows NT from the Oracle FTP site. Go to ftp://oracle-ftp.oracle.com and select "server", "wgt-tech", "server", and "windowsNT"
   
   If you are using Oracle 7.3 Client software, make sure that the correct version of the Oracle Ociw32.dll is installed as described in the Required Software section.



3. Install Microsoft Transaction Server 2.0.
   
   Install Microsoft Transaction Server 2.0 by installing the Windows NT 4.0 Option Pack release. When you install Microsoft Transaction Server 2.0, the following software will be installed:
   
   
   • Microsoft Transaction Server 2.0, including the Microsoft OCI Interface DLL (MTXOCI.DLL)
   
   
   
   • Microsoft ODBC 3.5 Driver Manager
   
   
   
   • Microsoft ODBC Driver for Oracle 2.0
   
   
   
   • ADO 1.5
   
   
   



4. Install Windows NT 4.0 Service Pack 4 or Windows NT 4.0 Service Pack 5
   
   Install the Windows NT 4.0 Service Pack 4 or the Windows NT 4.0 Service Pack 5 release. These releases include corrections for several XA related MS DTC problems.
   
   You must install the Windows NT 4.0 Service Pack 4 or Windows NT 4.0 Service Pack 5 releases after you install the Windows NT4 Option Pack release. This is essential because the NT 4.0 Service Pack setup program will only update MTS and MS DTC when the Windows NT4 Option Pack has previously been installed.



5. Install Microsoft Data Access Components version 2.0 or later.
   
   You can install the Microsoft Data Access Components 2.0 release from the following Web URL by following the "Download" link to the Universal Data Access Downloads section:

   http://www.microsoft.com/data/




6. Update the Oracle Client Software Registry Keys.
   
   NOTE: If you are using the Oracle 7.3 Client software, you can skip this step.
   
   If you are using the Oracle 8 Client software, you must modify the values of two registry keys. Under the following registry key are two string-named values that specify the names of the Oracle 7.3 Client software .dlls:

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

   String-Named Values:

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

   Change these values to specify the names of the Oracle 8 Client software .dlls:

   OracleXaLib "xa80.dll"
   OracleSqlLib "sqllib80.dll"




7. Delete the Dtcxatm.log file.
   
   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 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.



8. Enable Oracle XA Transaction Support.
   
   If you are using Oracle 7.3:
   
   1. Make sure that V$XATRANS$ exists. This view should have been created when the XA library was installed. If this view does not exist, your Oracle system administrator must create it by running the Oracle-supplied script named "XAVIEW.SQL". This file can be found in C:\ORANT\RDBMS73\ADMIN. This SQL script must be executed by the Oracle user "SYS".
   
   
   
   2. The Oracle system administrator must grant SELECT access to the public on the $XATRANS$ view. This can be done as follows:

      
                  Grant Select on V$XATRANS$ to public 

   
   
   
   If you are using Oracle8:
   
   1. Oracle8 should have created both the V$XATRANS$ and the DBA_PENDING_TRANSACTIONS views. You should not need to create either of these views.
   
   
   
   2. The Oracle system administrator must grant SELECT access to the public for the DBA_PENDING_TRANSACTIONS view. This can be done as follows:
      

      
      Grant Select on DBA_PENDING_TRANSACTIONS to public. 

   
   
   



9. Configure Enough Concurrent Distributed Transactions.
   
   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.



10. Configure 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 data source name (DSN). In either event, you are not using integrated security and can ignore this step.
    
    If you use integrated security, you must configure Microsoft Distributed Transaction Coordinator to run under a login ID and password authorized to connect to your Oracle database. This is required because during database recovery, Microsoft Distributed Transaction Coordinator opens your Oracle database to tell it the outcome of in doubt transactions.
    
    You can configure the login ID for the Microsoft Distributed Transaction Coordinator as follows. From the Start menu, choose Settings and then select Control Panel. Start the Services applet on the control panel. Select MSDTC. Select "Log On As" and specify a login ID and password. Use the Oracle security administration tools to make sure 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.



11. 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. This is essential because when using XA transaction support, the Oracle database must be able to move the XA transaction between processes (in the general case) so it cannot have any operating system file descriptors open but rather it must connect to the remote database using a virtual circuit. Support for Virtual circuits is available only with Oracle "Multi-Threaded Server".
    
    If the Oracle "Multi-Threaded Server" feature is not configured properly, Oracle reports the following error:

    ORA-24777: Cannot create migratable transaction

    NOTE: Oracle refers to the "Multi-Threaded Server" feature using the acronym "MTS". Microsoft refers to "Microsoft Transaction Server" using the acronym "MTS". The only relation between these two terms is the one described in the preceding section. Microsoft apologizes for the confusion.



12. Configure 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. Please see the "Configuring Oracle to Support a Large Number of Connections" section for more information.

Testing Installation and Configuration of MTS Support for Oracle

1. Verify that you have installed all of the correct versions of the software as described in the Required Software section.



2. Create an ODBC DSN that refers to your Oracle database. Make sure that your DSN uses the new Microsoft Oracle ODBC driver.



3. Ensure that you have enabled Oracle XA support as described in EnableOracleXATransactionSupport.



4. Delete all existing Oracle trace files from the computer 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.



5. If you never installed the Microsoft Transaction Server 2.0 beta release, you can skip this step.
   
   If you have 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.



6. 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>. 




7. If you run the test program without any parameters, it displays help information that describes the required parameters. The test program displays information about each Oracle operation it performs and indicates whether each operation was successful.



8. If the Oracle test program is able to connect to your Oracle database server without error, then it is very likely that MTS works 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

Validating Oracle Support Using the Sample Bank Application

1. Verify that your Oracle system installation and configuration is correct by using the Oracle test program provided by MTS. If the Oracle test program reports any error, you must correct that problem before proceeding.



2. 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 




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

   
         AccountNo       Balance
         --------------------------
   
         1                1000
         2                1000 




4. 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 




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

   
         NextReceipt
         1000 




6. Create a file DSN using the ODBC configuration utility. Name the file DSN "MTSSamples". Next, 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 




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

Administering Oracle and Microsoft Distributed Transaction Coordinator

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

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

   OracleXaLib     "xa80.dll"
   OracleSqlLib    "sqllib80.dll" 

XATM log object failed to set log encryption key.

1. Stop Microsoft Distributed Transaction Coordinator.



2. Change the userid for the Microsoft Distributed Transaction Coordinator back to its previous value. Alternately, you can assign a userid that is a member of the administrative group. To change the userid and pass word, run the Services applet in Control Panel, select the entry for "MSDTC", and change the "log on as" value.



3. Restart Microsoft Distributed Transaction Coordinator.

• 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.

  Oracle Configuration Parameter        Notes
   --------------------------------------------------------------------

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


   distributed_lock_timeout

   distributed_transactions

   dml_locks

   max_transaction_branches

   open_cursors

   processes

   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 reports the
                                          following error message and the
                                          client log or trace files show
                                          the ECONREFUSED message:

                                            ORA-12541: No Listener

                                          To correct this problem follow
                                          these steps:


                                         1. Stop the Oracle listener on the
                                            system containing the Oracle
                                            database server.

                                          2. 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.

                                             Microsoft 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

                                            3. Restart the Oracle listener. 

Known Limitations of MTS Support for Oracle

ADO 1.5 Release Is Required When Using ODBC 3.5

Microsoft Oracle ODBC Driver and Oracle 8

Oracle Ociw32.dll Version Problem

Oracle Ociw32.dll Corrupts Microsoft DTC During Startup

Method One

Second Method

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

REFERENCES

For the latest information on Microsoft Transaction Server, please see the following World Wide Web site:

http://www.microsoft.com/

Additional query words: kbMTS kbOracle KbClientServer

Keywords : kbMTS kbMTS200 kbOracle kbWinDNA kbGrpCom kbDSupport
Version : winnt:2.0
Platform : winnt
Issue type : kbinfo