Oracle® Services for Microsoft Transaction Server

Developer's Guide

11g Release 1 (11.1) for Microsoft Windows

B28377-02

July 2007

Oracle Services for Microsoft Transaction Server Developer's Guide 11g Release 1 (11.1) for Microsoft Windows

B28377-02

Copyright © 1996, 2007, Oracle. All rights reserved.

Contributing Authors:  Patricia Huey, Mark Kennedy, Roza Leyderman, Janelle Simmons

Contributors:  Alex Keh, Valarie Moore, Vivek Raja, Eric Wang, Hu Yong

The Programs (which include both the software and documentation) contain proprietary information; they are provided under a license agreement containing restrictions on use and disclosure and are also protected by copyright, patent, and other intellectual and industrial property laws. Reverse engineering, disassembly, or decompilation of the Programs, except to the extent required to obtain interoperability with other independently created software or as specified by law, is prohibited.

The information contained in this document is subject to change without notice. If you find any problems in the documentation, please report them to us in writing. This document is not warranted to be error-free. Except as may be expressly permitted in your license agreement for these Programs, no part of these Programs may be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose.

If the Programs are delivered to the United States Government or anyone licensing or using the Programs on behalf of the United States Government, the following notice is applicable:

U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data delivered to U.S. Government customers are "commercial computer software" or "commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the Programs, including documentation and technical data, shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement, and, to the extent applicable, the additional rights set forth in FAR 52.227-19, Commercial Computer Software--Restricted Rights (June 1987). Oracle USA, Inc., 500 Oracle Parkway, Redwood City, CA 94065.

The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently dangerous applications. It shall be the licensee's responsibility to take all appropriate fail-safe, backup, redundancy and other measures to ensure the safe use of such applications if the Programs are used for such purposes, and we disclaim liability for any damages caused by such use of the Programs.

Oracle, JD Edwards, PeopleSoft, and Siebel are registered trademarks of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

The Programs may provide links to Web sites and access to content, products, and services from third parties. Oracle is not responsible for the availability of, or any content provided on, third-party Web sites. You bear all risks associated with the use of such content. If you choose to purchase any products or services from a third party, the relationship is directly between you and the third party. Oracle is not responsible for: (a) the quality of third-party products or services; or (b) fulfilling any of the terms of the agreement with the third party, including delivery of products or services and warranty obligations related to purchased products or services. Oracle is not responsible for any loss or damage of any sort that you may incur from dealing with any third party.

Contents

List of Examples

List of Figures

Preface

- Audience

- Documentation Accessibility

- Related Documents

- Conventions

1 Using Microsoft Transaction Server with Oracle Database

- 1.1 Microsoft Transaction Server Overview

- 1.1.1 Oracle Services for Microsoft Transaction Server Support for Serializable Transactions

- 1.1.2 Oracle Services for Microsoft Transaction Server Support for DTC

- 1.2 Microsoft Transaction Server and Oracle Integration Overview

- 1.3 Getting Started with Microsoft Transaction Server and Oracle

2 Installing and Migrating Oracle Products

- 2.1 Installing Oracle Services for Microsoft Transaction Server

- 2.1.1 Installation Requirements for Microsoft Transaction Server

- 2.1.1.1 Oracle Products

- 2.1.1.2 Non-Oracle Products

- 2.1.2 Installation Requirements for Oracle Database

- 2.1.2.1 Oracle Products

- 2.1.2.2 Non-Oracle Products

- 2.2 Upgrading Oracle Services for Microsoft Transaction Server

- 2.2.1 Deleting OraMTS with Oracle Manager for MTS Services

- 2.2.1.1 Stopping OraMTS

- 2.2.1.2 Stopping and Restarting the Database

- 2.2.1.3 Restarting the OraMTS

- 2.2.1.4 Monitoring the OraMTS Trace Files

- 2.2.1.5 Deleting OraMTS Table Information

- 2.2.1.6 Deleting the OraMTS

- 2.3 Deleting Roles and Privileges of an Inactive OraMTS User

- 2.4 Deleting Oracle Services for Microsoft Transaction Server Manually

- 2.4.1 Manually Deleting Oracle Services for Microsoft Transaction Server with the Registry

- 2.4.2 Deleting the OracleMTSServicen Service

3 Managing Recovery Scenarios

- 3.1 Microsoft Transaction Server Configuration Requirements

- 3.2 Microsoft Transaction Server Transaction Recovery Overview

- 3.3 Scheduling Automatic Microsoft Transaction Server Transaction Recovery

- 3.3.1 Setting and Starting Up Database Job-Queue Processes

- 3.3.2 Creating and Scheduling Automatic Transaction Recovery

- 3.3.2.1 utl_oramts.show_indoubt Procedure

- 3.3.2.2 utl_oramts.recover_automatic Procedure

- 3.3.2.3 utl_oramts.forget_RMs Procedure

- 3.3.2.4 oramts_2pc_pending View

- 3.4 Viewing Microsoft Transaction Server In-Doubt Transactions

- 3.5 Modifying Registry Values for Oracle Fail Safe Configurations

4 Running the Microsoft Application Demo

- 4.1 Configuring Oracle Call Interface with the Microsoft Application Demo

- 4.1.1 Microsoft Application Demo Overview

- 4.1.2 Ensuring the Database Includes the Proper Microsoft Application Demo Tables

- 4.1.3 Running the Microsoft Application Demo

- 4.2 Configuring Oracle ODBC Driver with the Microsoft Application Demo

- 4.3 Configuring Oracle Provider for OLE DB with the Microsoft Application Demo

5 Programming with Microsoft Transaction Server and an Oracle Database

- 5.1 COM Component Integration in a Transaction

- 5.2 Microsoft Transaction Server Application Development

- 5.2.1 Microsoft Transaction Server Component Registration

- 5.2.1.1 Types of Registration Components

- 5.2.1.2 Registration of Components

- 5.2.2 Microsoft Transaction Server-Coordinated Component Transaction

- 5.2.3 Microsoft DTC-Coordinated Component Transaction

- 5.3 OCI Integration with Microsoft Transaction Server

- 5.3.1 Integrating COM Components

- 5.3.1.1 COM Components Running in an MTS-Coordinated Transaction

- 5.3.1.2 Non-Transactional COM Components Running with OCI Connection Pooling

- 5.3.1.3 COM Components Using MS DTC and OCI Connection Pooling

- 5.3.1.4 COM Components Using MS DTC and Nonpooling OCI Connection

- 5.3.2 Using OCI Functions

- 5.3.3 OraMTSSvcGet()

- 5.3.4 OraMTSSvcRel()

- 5.3.5 OraMTSSvcEnlist()

- 5.3.6 OraMTSSvcEnlistEx()

- 5.3.7 OraMTSEnlCtxGet()

- 5.3.8 OraMTSEnlCtxRel()

- 5.3.9 OraMTSJoinTxn()

- 5.3.10 OraMTSTransTest()

- 5.3.11 OraMTSOCIErrGet()

- 5.4 ODBC Integration with Microsoft Transaction Server Overview

- 5.4.1 Setting the Connection Attribute

- 5.4.2 Using Oracle ODBC Driver

- 5.4.3 Using Microsoft Oracle ODBC Driver

6 Tuning Microsoft Transaction Server Performance

- 6.1 Improving Microsoft Transaction Server Application Performance

- 6.2 Managing Microsoft Transaction Server Connections

- 6.2.1 Connection Pooling Registry Parameters

- 6.3 Increasing the Transaction Timeout Parameter

- 6.4 Changing Initialization Parameter Settings

- 6.5 Starting MSDTC

7 Troubleshooting Oracle Microsoft Transaction Server

- 7.1 Tracking Oracle Services for Microsoft Transaction Server Performance

- 7.2 Correcting Windows Explorer Problems

- 7.3 Correcting Oracle Net Changes that Impact Connection Pooling

- 7.4 Designing an Application that Uses Multiple Databases

- 7.5 Working with Different Types of Connection Pooling

- 7.6 Working with In-Doubt Transactions

- 7.7 Dropping the Microsoft Transaction Server Administrative User Account

Glossary

Index

List of Examples

- 5-1 Integration of MTS and OCI

- 5-2 Retrieving the OCI Error Code and Message Text

List of Figures

- 2-1 Microsoft Management Console

- 5-1 Component Integration in a Transaction

- 7-1 Distributed DML Statements from MTS Applications

Preface

This manual explains how to install, configure, use, and administer Oracle Services for Microsoft Transaction Server that apply to operating systems. It covers the features of Oracle Database software that apply to the Windows 2000, Windows XP, and Windows Server 2003 operating systems.

This preface contains these topics:

- Audience

- Documentation Accessibility

- Related Documents

- Conventions

Audience

This guide is intended for anyone who performs the following tasks:

- Uses component object model (COM) components with Microsoft Transaction Server

- Registers COM components as transactional and has Microsoft Transaction Server control the transaction

- Uses client-side connection pooling in Microsoft Transaction Server

- Uses .NET applications with Oracle Services for Microsoft Transaction Server to access Oracle Database instances.

Documentation Accessibility

Our goal is to make Oracle products, services, and supporting documentation accessible, with good usability, to the disabled community. To that end, our documentation includes features that make information available to users of assistive technology. This documentation is available in HTML format, and contains markup to facilitate access by the disabled community. Accessibility standards will continue to evolve over time, and Oracle is actively engaged with other market-leading technology vendors to address technical obstacles so that our documentation can be accessible to all of our customers. For more information, visit the Oracle Accessibility Program Web site at

http://www.oracle.com/accessibility/

Accessibility of Code Examples in Documentation

Screen readers may not always correctly read the code examples in this document. The conventions for writing code require that closing braces should appear on an otherwise empty line; however, some screen readers may not always read a line of text that consists solely of a bracket or brace.

Accessibility of Links to External Web Sites in Documentation

This documentation may contain links to Web sites of other companies or organizations that Oracle does not own or control. Oracle neither evaluates nor makes any representations regarding the accessibility of these Web sites.

TTY Access to Oracle Support Services

Oracle provides dedicated Text Telephone (TTY) access to Oracle Support Services within the United States of America 24 hours a day, seven days a week. For TTY support, call 800.446.2398.

Related Documents

For more information, see these Oracle resources:

- Oracle Database Reference

- Oracle Provider for OLE DB Developer's Guide

- Oracle Objects for OLE Developer's Guide

- Oracle Data Provider for .NET Developer's Guide

- Oracle Net Services Administrator's Guide

- Oracle Database Platform Guide for Windows

For information about Oracle error messages, see Oracle Database Error Messages. Oracle error message documentation is available only in HTML. If you only have access to the Oracle Documentation CD, you can browse the error messages by range. Once you find the specific range, use your browser's "find in page" feature to locate the specific message. When connected to the Internet, you can search for a specific error message using the error message search feature of the Oracle online documentation.

Many of the examples in this book use the sample schemas of the seed database, which is installed by default when you install Oracle. Refer to Oracle Database Sample Schemas for information on how these schemas were created and how you can use them yourself.

Printed documentation is available for sale in the Oracle Store at

http://oraclestore.oracle.com/

To download free release notes, installation documentation, white papers, or other collateral, please visit the Oracle Technology Network (OTN). You must register online before using OTN; registration is free and can be done at

http://www.oracle.com/technology/membership/

If you already have a username and password for OTN, then you can go directly to the documentation section of the OTN Web site at

http://www.oracle.com/technology/documentation/

Conventions

The following text conventions are used in this document:

Convention

Meaning

boldface

Boldface type indicates graphical user interface elements associated with an action, or terms defined in text or the glossary.

italic

Italic type indicates book titles, emphasis, or placeholder variables for which you supply particular values.

monospace

Monospace type indicates commands within a paragraph, URLs, code in examples, text that appears on the screen, or text that you enter.

1 Using Microsoft Transaction Server with Oracle Database

This chapter describes Microsoft Transaction Server and Oracle Database integration.

This chapter contains these topics:

- Microsoft Transaction Server Overview

- Microsoft Transaction Server and Oracle Integration Overview

- Getting Startedwith Microsoft Transaction Server and Oracle

1.1 Microsoft Transaction Server Overview

Microsoft Transaction Server is a proprietary component object model (COM) transaction processing system that runs on an Internet or network server. Microsoft Transaction Server deploys and manages application and database transaction requests on behalf of a client computer. Microsoft Transaction Server provides:

- ActiveX/distributed component object model (DCOM) programming model to develop distributed applications and a runtime environment in which to deploy these applications.

- Atomicity, Consistency, Isolation, and Durability (ACID) properties for components in transactions.

- Access to performance-enhancing features such as component caching and database connection pooling.

Microsoft Transaction Server is a component of the three-tiered, server-centric architecture model. This model lets you separate the presentation, business logic, and data elements of applications onto different computers connected in a network. Microsoft Transaction Server functionality is also implemented in COM+ and Enterprise Services. Oracle Services for Microsoft Transaction Server, or OraMTS, support Microsoft Transaction Server, COM+, and Enterprise Services.

See Also:
Microsoft documentation for additional information about Microsoft Transaction Server

1.1.1 Oracle Services for Microsoft Transaction Server Support for Serializable Transactions

Oracle Services for Microsoft Transaction Server supports distributed transactions set to a serializable and read-committed isolation level.

1.1.2 Oracle Services for Microsoft Transaction Server Support for DTC

Oracle Services for Microsoft Transaction Server works with Microsoft Distributed Transaction Coordinator (DTC), which is part of the Enterprise Services component of .NET. DTC implements a two-phase commit protocol that makes sure that the transaction outcome is consistent across all data resources involved in a transaction.

See Also:
Oracle Database Oracle Real Application Clusters Administration and Deployment Guide for information on how Oracle Services for Microsoft Transaction Server affects distributed transactions in Real Application Clusters.

1.2 Microsoft Transaction Server and Oracle Integration Overview

Without any special integration, you can deploy applications that were created using Win32, Win64, COM, or Microsoft .NET in a Microsoft Transaction Server that connects to an Oracle Database. To use either of the following features, however, you must install Oracle Services for Microsoft Transaction Server (OraMTS):

- Register the Win32, Win64, COM or .NET application as transactional and have Microsoft Transaction Server control the transaction. You can do this by using the Properties dialog box of the component in the Microsoft Management Console Explorer.

- Use client-side connection pooling in Microsoft Transaction Server.

After you have installed Oracle Services for Microsoft Transaction Server, an Oracle MTS Recovery Service is also automatically installed on the same computer. The Oracle MTS Recovery Service helps in the recovery of in-doubt transactions left in Oracle Database instances that originated from this computer. On each connected database:

- Create the Microsoft Transaction Server administrator user account.

- Schedule a database-level transaction recovery job.

This enables the database to participate in Microsoft Transaction Server-started transactions.

Create the COM component with any of the following Oracle products:

- Oracle Call Interface (OCI)

- Oracle Objects for OLE (OO4O)

- Oracle Open Database Connectivity (ODBC) Driver

- Oracle Provider for OLE DB

- Access the Oracle Database using any .NET application using Oracle Data Provider for .NET (ODP.NET).

1.3 Getting Started with Microsoft Transaction Server and Oracle

You are now ready to use Microsoft Transaction Server with a database. To get started quickly, follow these steps:

1. Install the Oracle and Microsoft products required for Microsoft Transaction Server and database integration.
See Chapter 2, "Installing and Migrating Oracle Products".

2. Migrate from a previous release of Oracle Services for Microsoft Transaction Server.
See Chapter 2, "Installing and Migrating Oracle Products".

3. Create the Microsoft Transaction Server administrator user account.
See Chapter 3, "Managing Recovery Scenarios".

4. Schedule a Microsoft Transaction Server transaction recovery job.
See Chapter 3, "Managing Recovery Scenarios".

5. Run the Microsoft application demo.
See Chapter 4, "Running the Microsoft Application Demo" for information about running an application demo that:

- Uses transactional components hosted by Microsoft Transaction Server

- Accesses a database in a transaction controlled by Microsoft Transaction Server

6. Create Microsoft Transaction Server-hosted applications.
See Chapter 5, "Programming with Microsoft Transaction Server and an Oracle Database" for instructions on using OCI, OO4O, Oracle ODBC Driver, or Oracle Provider for OLE DB with COM-based applications.

7. Learn about using Microsoft Transaction Server on the different Windows operating systems.

2 Installing and Migrating Oracle Products

This chapter describes installation and migration requirements for the Microsoft Transaction Server and Oracle Database environment.

This chapter contains these topics:

- Installing Oracle Services for Microsoft Transaction Server

- Upgrading Oracle Services for Microsoft Transaction Server

- Deleting Roles and Privileges of an Inactive OraMTS User

- Deleting Oracle Services for Microsoft Transaction Server Manually

2.1 Installing Oracle Services for Microsoft Transaction Server

You can install OraMTS by choosing the Custom installation type when you install Oracle Database.

This section describes the Oracle and non-Oracle products you must install for OraMTS. Additional installation requirements include:

- Sufficient RAM if Oracle Database, Microsoft Transaction Server, and OraMTS are installed on the same computer.

Note:
Refer to Oracle Database Installation Guide for Windows or the minimum RAM requirements.

- Only one Oracle MTS Recovery Service exists on each computer.

- For Oracle Data Provider for .NET (ODP.NET) cluster configurations (or any failover configuration), install Microsoft Transaction Server on the node running the Microsoft Distributed Transaction Coordinator (MS DTC) component. This ensures that the Oracle MTS Recovery Service migrates with the client application during failover. You can configure this when scheduling recovery transactions.

2.1.1 Installation Requirements for Microsoft Transaction Server

The Windows computer where Microsoft Transaction Server is installed has the following product requirements:

2.1.1.1 Oracle Products

- OraMTS

- Oracle Net Services for the client; automatically installed with OraMTS

- Oracle Objects for OLE (OO4O)

- Oracle Open Database Connectivity (ODBC) Driver

- Oracle Provider for OLE DB

- Oracle Call Interface (OCI)

- Oracle Data Provider for .NET (ODP.NET)

- Oracle Net Manager

- ORACLE_LOADER (data access driver)

- SQL*Plus

Notes:

- OO4O, Oracle ODBC Driver, ODP.NET, Oracle Provider for OLE DB, and OCI are only required if you are building or using components with which they are required.

- Depending on the installation, you are prompted to enter the port number on which the Oracle MTS Recovery Service will listen for requests to resolve in-doubt transactions

2.1.1.2 Non-Oracle Products

- Microsoft Transaction Server version 2.0 or later versions

- Windows operating system

- Fully-functioning networking protocol software

- COM+ or Enterprise Services; these are later versions of Microsoft Transaction Server

2.1.2 Installation Requirements for Oracle Database

The computer where Oracle Database is installed has the following product requirements:

2.1.2.1 Oracle Products

- Oracle Server (the Oracle Database) Oracle Net Services for the server

- SQL*Plus

2.1.2.2 Non-Oracle Products

- Fully enabled networking protocol

See Also:
Oracle Database Installation Guide for Windows

2.2 Upgrading Oracle Services for Microsoft Transaction Server

Before uninstalling Oracle Services For Microsoft Transaction Server, you must use the Oracle Manager for MTS Services snap-in in the Microsoft Management Console Explorer to delete the existing OraMTS. The entire process is outlined in the following steps:

1. Delete OraMTS using the Oracle Manager for MTS Services snap-in.
Alternatively, if you already deleted the database, delete the service from the registry.

2. Delete roles and privileges of the user associated with the deleted OraMTS.

3. Uninstall Oracle Services For Microsoft Transaction Server from the Windows computer where Microsoft Transaction Server is installed.

4. Install release 11.1 of OO4O, Oracle Provider for OLE DB, Oracle ODBC Driver, or OCI, if you plan to build component object model (COM) components with these products.

5. Install Oracle Services For Microsoft Transaction Server release 11.1 into a single Oracle home. The Oracle MTS Recovery Service is installed automatically.

6. Create the Microsoft Transaction Server administrator user account.

7. Schedule Microsoft Transaction Server transaction recovery jobs for all databases that participate in Microsoft Transaction Server transactions.

Steps 1 and 2 are discussed in detail in this section. For Steps 3 through 5, see Oracle Database Installation Guide for Windows. For Steps 6 and 7, refer to Chapter 3, "Managing Recovery Scenarios".

2.2.1 Deleting OraMTS with Oracle Manager for MTS Services

You must use Oracle Manager for MTS Services snap-in in the Microsoft Management Console Explorer to delete the OraMTS. Deleting the OraMTS in any other way (such as with the Delete button on the keyboard) causes data inconsistencies in the database. These inconsistencies require the database administrator to manually commit or terminate transactions that did not successfully complete or recover. Before deleting the OraMTS, ensure that all transactions are resolved by completing these steps:

- Stopping OraMTS

- Stopping and Restarting the Database

- Restarting the OraMTS

- Monitoring the OraMTS Trace Files

- Deleting OraMTS Table Information

- Deleting the OraMTS

2.2.1.1 Stopping OraMTS

1. Go to the computer from which to delete an OraMTS. You must modify an OraMTS before deleting it. The OraMTS can be running on this computer or on a remote computer that you can access from this computer.

2. From the Start menu, select Programs, then Oracle - HOME_NAME, then Application Development, then Oracle Manager for Microsoft Transaction Server.
The Microsoft Management Console appears.

3. Find the OraMTS to modify in the Explorer window.

4. Right-click the OraMTS icon to modify; MTSDEMO in this example.

Figure 2-1 Microsoft Management Console

A menu with several options appears.

5. Choose Stop Service.
A message indicates that the OraMTS has stopped.

6. Click OK.

2.2.1.2 Stopping and Restarting the Database

To stop and restart the database:

1. Go to the computer on which the Oracle Database is running.

2. Start SQL*Plus:

C:\> sqlplus /NOLOG

3. Connect to the database as SYSDBA:

SQL> CONNECT / AS SYSDBA

4. Shut down the Oracle Database:

SQL> SHUTDOWN

5. Restart the Oracle Database:

SQL> STARTUP

6. Exit SQL*Plus:

SQL> EXIT

2.2.1.3 Restarting the OraMTS

To restart the OraMTS:

1. On the computer where OraMTS is modified, from the Start menu, select Programs, then Oracle - HOME_NAME, then Application Development, then Oracle Manager for Microsoft Transaction Server.
The Microsoft Management Console appears.

2. Find the OraMTS to start in the Explorer window.

3. Right-click the OraMTS icon.
A menu with several options appears.

4. Choose Start Service.
A message indicates that the OraMTS started.

5. Click OK.

2.2.1.4 Monitoring the OraMTS Trace Files

To monitor the OraMTS trace files:

1. Do not enable any new transactions to use the OraMTS.

2. Monitor the OraMTS trace file, located at ORACLE_BASE\ORACLE_HOME\oramts\trace, for a message indicating that the recovery completed successfully:

2515156: [2096] OracleMTSService - Accepting new enlistment requests.

Right-click the OraMTS icon in the Microsoft Management Console once message 2515156 appears.

3. Choose Stop Service.

A message indicates that the OraMTS stopped.

4. Click OK.

2.2.1.5 Deleting OraMTS Table Information

To delete OraMTS table information:

1. Go to the computer on which the Oracle Database is running.

2. Start SQL*Plus:

C:\> sqlplus /NOLOG

3. Connect to the database as SYSDBA:

SQL> CONNECT / AS SYSDBA

4. Delete this information from the mts_proxy_info table:

SQL> DROP TABLE mtsadmin_username.mts_proxy_info;

where mtsadmin_username is the OraMTS user, such as mtssys.

SQL> COMMIT;

2.2.1.6 Deleting the OraMTS

To delete the OraMTS:

1. On the computer from which the OraMTS should be deleted, from the Start menu, select Programs, then Oracle - HOME_NAME, then Application Development, then Oracle Manager for Microsoft Transaction Server.
The Microsoft Management Console appears.

2. Find the OraMTS to delete in the Explorer window.

3. Right-click the OraMTS icon.
A menu with several options appears.

4. Choose Delete.

5. If you receive a message indicating that the OraMTS was successfully deleted, see "Deleting Roles and Privileges of an Inactive OraMTS User".
If OraMTS was not deleted successfully, see "Deleting Oracle Services for Microsoft Transaction Server Manually".

2.3 Deleting Roles and Privileges of an Inactive OraMTS User

Ensure that you delete the roles and privileges assigned to an OraMTS user that you no longer use, or whose service you have deleted.

To delete roles and privileges of an inactive OraMTS user:

1. Go to ORACLE_BASE\ORACLE_HOME\oramts\admin.

2. Open the file revokeuser.sql with a text editor.

3. Replace mts_user with the username from which to revoke roles and privileges.
This script uses the username mtssys and the password mtssys. If you have changed the password or are using an OraMTS username other than mtssys, you must substitute the correct username and password.

4. Save the changes and exit revokeuser.sql.

5. Start SQL*Plus:

C:\> sqlplus /NOLOG

6. Connect to the database as SYSDBA:

SQL> CONNECT / AS SYSDBA

7. Run the modified script:

SQL> @ORACLE_BASE\ORACLE_HOME\oramts\admin\revokeuser.sql;

The roles and privileges for the user are deleted.

8. Exit SQL*Plus:

SQL> EXIT

See Also:
Oracle Database Installation Guide for Windows for instructions on installing the latest Oracle Services For Microsoft Transaction Server release

2.4 Deleting Oracle Services for Microsoft Transaction Server Manually

Before deleting OraMTS, it must be cleanly disassociated from the Oracle Database to which it connects. Sometimes this disassociation fails. Follow the instructions in this section only if:

- The deletion procedures in Deleting OraMTS with Oracle Manager for MTS Services were unsuccessful.

- You have already deleted the Oracle Database and cannot use the Oracle Manager for MTS Services snap-in.

Oracle Manager for MTS Services snap-in of the Microsoft Management Console Explorer can fail to delete or modify the OraMTS under three distinct conditions:

1. The Oracle Manager for MTS Services snap-in cannot connect to the Oracle Database using the information in the registry.
Ensure that the Oracle Database and its listener are started. Use SQL*Plus or a different tool to verify that the Oracle Database accepts new connections.

2. The information in the Oracle Database does not match the information in the registry.
The Oracle Manager for MTS Services snap-in is connecting to a different Oracle Database than the one to which the OraMTS connects. If the Oracle Manager for MTS Services snap-in and the OraMTS run on the same computer, they may be using tnsnames.ora files from different Oracle homes. If they run on different computers (for example, the Oracle Manager for MTS Services snap-in is configuring a service on a remote computer), the entry in their tnsnames.ora file is pointing to different databases. Whether it is a local or remote problem, resolve it by ensuring that the entry in the tnsnames.ora file for both the Oracle Manager for MTS Services snap-in and the OraMTS points to the same database instance.

3. Oracle Manager for MTS Services snap-in cannot delete the service information stored in Oracle Database.
Oracle Database is unstable or is not working properly. Check if any database trace files are being created that indicate a database process failure. Trace files are located in ORACLE_BASE\ORACLE_HOME\oramts\trace.

2.4.1 Manually Deleting Oracle Services for Microsoft Transaction Server with the Registry

To manually delete OraMTS with the registry:

1. Start the registry from the command prompt:

C:\> regedt32

The Registry Editor window appears.

2. Select the HKEY_LOCAL_MACHINE.
Go to System\CurrentControlSet\Services\OracleMTSServicen,

where n is the number of the OraMTS.

The right-hand side of the window shows various parameters and values associated with OracleMTSServicen:

- ORAMTS_SUNAME is the OraMTS username.

- ORAMTS_SUPWD is the password for the OraMTS username, encrypted in the registry.

- ORAMTS_OCI_OBJ_MODE Initializes OCI in object mode or threaded mode.

- ORAMTS_ORADB is the net service name for the OraMTS to use in connecting to the Oracle Database.

3. Start SQL*Plus:

C:\> sqlplus /NOLOG

4. Connect to the Oracle Database; use the same username and net service name with which the OraMTS connects:

CONNECT as username/password@net_service_name

where net_service_name is the net service name for connecting to the database. The password is stored in the registry in encrypted form. Use plain text passwords when connecting with SQL*Plus.

5. Verify that the Oracle Database is the same one to which the OraMTS connects by checking the following database information:

SQL> SELECT NAME, DBID FROM V$DATABASE;

6. Check that the NAME value matches the registry ORAMTS_DBNAME value, and that the DBID value matches the ORAMTS_DBID value.

7. Check service information:

SQL> SELECT RMGUID FROM mts_proxy_info;

8. Check that the RMGUID value matches the registry ORAMTS_RMGUID value.

9. Proceed only if all values match.
If all values do not match, the Oracle Database is not the same one to which the OraMTS connects. If you continue, OraMTS installation on the database fails. This can leave the database in an inconsistent state that requires database administrator intervention to correct. Because of mismatched tnsnames.ora files, SQL*Plus and OraMTS did not connect to the same database.

10. Delete the service information stored in the database:

SQL> DELETE FROM mts_proxy_info;
SQL> COMMIT;

11. Exit from SQL*Plus.

SQL> EXIT

2.4.2 Deleting the OracleMTSServicen Service

To delete the OracleMTSServicen service:

1. Restart the computer.

2. From the Start menu, select Programs, then Oracle - HOME_NAME, then Application Development, then Oracle Manager for Microsoft Transaction Server.
The Microsoft Management Console appears.

3. Find the OraMTS to delete in the Explorer window.

4. Right-click OraMTS.
A menu with several options appears.

5. Click Delete.
If successful, a message indicates that the OraMTS was deleted.

If unsuccessful, a message indicates that the OraMTS was not deleted. In this case, use the registry to delete the service's registry entry. In the HKEY_LOCAL_MACHINE, delete the following key:

\System\CurrentControlSet\Services\OracleMTSServicen,

where n is the number of the OraMTS.

Go to "Deleting Roles and Privileges of an Inactive OraMTS User".

3 Managing Recovery Scenarios

This chapter describes how to create and schedule Microsoft Transaction Server-related Oracle transaction recovery.

This chapter contains these topics:

- Microsoft Transaction Server Configuration Requirements

- Microsoft Transaction Server Transaction Recovery Overview

- Scheduling Automatic Microsoft Transaction Server Transaction Recovery

- Viewing Microsoft Transaction Server In-Doubt Transactions

- Modifying Registry Values for Oracle Fail Safe Configurations

3.1 Microsoft Transaction Server Configuration Requirements

You must configure the Microsoft Transaction Server and Oracle Database environments after installing or migrating Oracle Services for Microsoft Transaction Server (OraMTS).

Configuration is not required on the Windows computer if a Microsoft Transaction Server is installed on a computer.

To configure the Microsoft Transaction Server, perform the following tasks on the computer where the Oracle Database is installed:

1. Run the oramtsadmin.sql script against the database to create the Microsoft Transaction Server administrative user account (the default username is mtssys).

2. Schedule automatic transaction recovery.
See "Scheduling Automatic Microsoft Transaction Server Transaction Recovery"

3. If you have an Oracle Fail Safe configuration, modify the registry values before or after running the oramtsadmin.sql script.
See "Modifying Registry Values for Oracle Fail Safe Configurations".

3.2 Microsoft Transaction Server Transaction Recovery Overview

Distributed transaction capabilities are required to use Microsoft Transaction Server with Oracle. Microsoft Transaction Server-related Oracle transactions become in-doubt transactions when any of the following fail:

- Microsoft Transaction Server application

- Network

- Microsoft Distributed Transaction Coordinator (MS DTC)

An Oracle MTS Recovery Service resolves in-doubt transactions on the computer that started the failed transaction. An Oracle MTS Recovery Service is automatically installed with Oracle Services For Microsoft Transaction Server. Only one Oracle MTS Recovery Service can be installed for each computer. A scheduled recovery job on each Microsoft Transaction Server-enabled database permits the Oracle MTS Recovery Service to resolve in-doubt transactions.

The Oracle MTS Recovery Service resolves an in-doubt Microsoft Transaction Server transaction in the following order:

1. The DBMS recovery job detects an in-doubt MTS-related transaction.

2. The DBMS recovery job extracts the recovery service's endpoint address from the XID of the in-doubt transaction and requests the recovery service for the outcome of the MTS/MS DTC transaction.

3. The recovery service requests its MS DTC for transaction outcome.

4. The recovery service reports transaction outcome to the DBMS job process.

5. The DBMS recovery job commits or terminates the in-doubt transaction.

3.3 Scheduling Automatic Microsoft Transaction Server Transaction Recovery

Automatic transaction recovery is performed by scheduling a database job. A database job for in-doubt transactions must be scheduled for each database participating in Microsoft Transaction Server transactions.

Transaction recovery is configured by running the oramtsadmin.sql script, which triggers utl_oramts.sql and prvtoramts.plb scripts to create the PL/SQL package utl_oramts. The database view oramts_2pc_pending is also created to show in-doubt transactions related to Microsoft Transaction Server transactions.

The oramtsadmin.sql script:

- Creates the Microsoft Transaction Server administrator user account.

- Automatically schedules database jobs for transaction recovery every one minute.
When the database job is run, it checks for unresolved global transactions in the database that are related to Microsoft Transaction Server. Information in the transaction identifiers (XIDs) of the in-doubt transactions identifies the computer on which the transaction was started. The Oracle MTS Recovery Service on that computer resolves the transaction.

- Schedules post-recovery cleanup every half hour.

Schedule automatic transaction recovery in the database by performing these tasks:

- Setting and Starting Up Database Job-Queue Processes

- Creating and Scheduling Automatic Transaction Recovery

3.3.1 Setting and Starting Up Database Job-Queue Processes

The JOB_QUEUE_PROCESSES initialization parameter specifies the number of job queue processes started in an instance.

To set and start up job-queue processes:

1. Ensure that you have SYSDBA privileges.

2. Go to the computer on which the Oracle Database is installed.

3. Start SQL*Plus:

C:\> sqlplus /NOLOG

4. Connect to the database as SYSDBA:

SQL> CONNECT / AS SYSDBA

5. Set the JOB_QUEUE_PROCESSES initialization parameter:

JOB_QUEUE_PROCESSES = 1

The default value for this parameter is 0. Set this parameter to a value greater than 1 if there are many destinations to which to propagate the messages.

6. Shut down the Oracle Database:

SQL> SHUTDOWN

7. Restart the Oracle Database:

SQL> STARTUP

8. Exit SQL*Plus:

SQL> EXIT

3.3.2 Creating and Scheduling Automatic Transaction Recovery

The oramtsadmin.sql script creates the Microsoft Transaction Server administrator user account with the default username mtssys. The Microsoft Transaction Server transaction recovery jobs run under the administrator user account.

The oramtsadmin.sql script runs the utl_oramts.sql script to grant the following privileges and roles to the administrator user account:

- CREATE SESSION role

- SELECT_CATALOG_ROLE role

- FORCE_ANY_TRANSACTION privilege

- DBMS_JOBS package, on which EXECUTE privileges are granted

- DBMS_TRANSACTION package, on which EXECUTE privileges are granted

To create and schedule automatic transaction recovery:

1. Ensure that you have SYSDBA privileges.

2. Log on to the computer where the Oracle Database is installed.

3. Start SQL*Plus:

C:\> sqlplus /NOLOG

4. Connect to the database as SYSDBA:

SQL> CONNECT / AS SYSDBA

5. Run the oramtsadmin.sql script:

SQL> @ORACLE_BASE\ORACLE_HOME\oramts\admin\oramtsadmin.sql;

You are prompted for the Microsoft Transaction Server administrator username and password. You can accept the default username of mtssys and password of mtssys, or change them.

6. If you did change the password in step 5, you can change it using this script:

SQL> ALTER USER USERNAME IDENTIFIED BY new_password;

To change the username after completing this task, drop the user, rerun the oramtsadmin.sql script, and specify a different username when prompted.

7. Exit SQL*Plus:

SQL> EXIT

A single PL/SQL package, utl_oramts, is created in the Microsoft Transaction Server administrator's schema. utl_oramts exposes these public procedures and creates this view:

- utl_oramts.show_indoubt Procedure

- utl_oramts.recover_automatic Procedure

- utl_oramts.forget_RMs Procedure

- oramts_2pc_pending View

3.3.2.1 utl_oramts.show_indoubt Procedure

Use this procedure to view Microsoft Transaction Server in-doubt transactions in the database. This procedure uses the dbms_output package to display results.

Description This procedure requires SERVEROUTPUT set to ON.

SQL> SET SERVEROUTPUT ON

SQL> EXECUTE utl_oramts.show_indoubt;

The following information appears:

=========================================================
currently indoubt transactions
=========================================================
formatid        : 21255235
gtrid                   : C2229A505904974D81FB7316B147325900000000
bqual           : 5BAB6A6B55CD294AA20335839110829C0100000000901944700050
local txid      : 142.11.202
tx state                   : prepared
protocol        : HTTP
endpoint        : middletier-1@foo.com:2030
=========================================================
formatid        : 21255235
gtrid                   : 259DF9C8DFC5574F8876F0DF4E15CCAD00000000
bqual           : 2C8DCED5B9816244BA2B73CC013EEB870100000000901944700050
local txid      : 2.18.185
tx state        : prepared
protocol        : HTTP
endpoint        : middletier-2@foo.com:2030

3.3.2.2 utl_oramts.recover_automatic Procedure

This procedure is run by the transaction recovery job. An automatic database job is scheduled for utl_oramts.recover_automatic. When the job is run, it checks for unresolved global transactions in the database that are related to Microsoft Transaction Server. Information in the XIDs of the in-doubt transactions identifies the computer on which the transaction started. The Oracle MTS Recovery Service is contacted and resolves the transactions.

3.3.2.3 utl_oramts.forget_RMs Procedure

Use this procedure to request the transaction manager (MS DTC) to forget resolved transactions. This procedure is run by the post-recovery cleanup job.

3.3.2.4 oramts_2pc_pending View

The view oramts_2pc_pending is created by executing oramtsadmin.sql. oramts_2pc_pending shows in-doubt transactions in the database. This view consists of the following columns:

Formatid  This is the formatid of the global transaction in the database.

global_transaction_id  This is the transaction identifier of the Oracle global transaction corresponding to the Microsoft Transaction Server transaction. In fact, this is the globally unique identifier (GUID) of the Microsoft Transaction Server transaction.

branch_id This shows the branch identifier of the Oracle transaction. A single Microsoft Transaction Server transaction can have multiple Oracle global transactions. This depends on the number of Microsoft Transaction Server/COM+ components that span the same Microsoft Transaction Server transaction. All these transactions have the small global transaction identifier, but different branch identifiers.

local_tx_id A local Oracle transaction corresponds to each Microsoft Transaction Server transaction. This column shows the identifier corresponding to this local transaction.

state This shows the state of the transaction: pending, heuristically committed, heuristically terminated, and so on.

protocol This is the protocol that the transaction recovery job in the database uses to communicate with the Oracle MTS Recovery Service.

endpoint  This is the endpoint of the Windows computer on which the Microsoft Transaction Server transaction originated. For HTTP connections, this translates to a hostname and port number.

3.4 Viewing Microsoft Transaction Server In-Doubt Transactions

To view Microsoft Transaction Server–related in-doubt transactions in the database, use SQL*Plus to query the view oramts_2pc_pending:

1. Start SQL*Plus with the Microsoft Transaction Server administrator user account:

C:\> sqlplus mtsadmin_user/ mtsadmin_password

2. Enter the following command:

SQL> SELECT * FROM oramts_2pc_pending;

This displays the computer on which the in-doubt transaction originated.

3.5 Modifying Registry Values for Oracle Fail Safe Configurations

In typical configurations, the MS DTC and Oracle MTS Recovery Service run on the same computer. This ensures that the required information for transaction recovery is available to the Oracle-Microsoft Transaction Server integration layer.

In configurations where the Microsoft Transaction Server application is part of a Windows cluster (for example, the application can fail over to another node or host in the cluster), the MS DTC runs as a cluster-wide resource. All cluster nodes use a single instance of the MS DTC running on any cluster node.

If you have an Oracle Fail Safe configuration, make sure the following registry information is replicated on all nodes in the cluster participating in Microsoft Transaction Server transactions:

To modify registry values for Oracle Fail Safe configurations:

1. Go to the computer on which the MS DTC and Oracle MTS Recovery Service are installed.

2. Start the registry from the command prompt:

C:\> regedt32

The Registry Editor window appears.

3. Go to HKEY_LOCAL_MACHINE\Software\Oracle\OracleMTSRecoveryService.

4. Copy the registry information appearing here to all nodes in the cluster.

5. Reboot the computer on which you added the key.

4 Running the Microsoft Application Demo

This chapter describes how to use the sample Microsoft component object model (COM) application demo that is integrated with Microsoft Transaction Server.

This chapter contains these topics:

- Configuring Oracle Call Interface with the Microsoft Application Demo

- Configuring Oracle ODBC Driver with the Microsoft Application Demo

- Configuring Oracle Provider for OLE DB with the Microsoft Application Demo

Note:
The Microsoft application demo is not included with Microsoft Transaction Server on Windows 2000, Windows XP, or Windows Server 2003.

4.1 Configuring Oracle Call Interface with the Microsoft Application Demo

You can use the Oracle Call Interface (OCI) with the sample banking application demo that Microsoft provides with Microsoft Transaction Server. In most cases, OCI is automatically integrated with the Microsoft application demo. Determine if OCI and the Microsoft application demo are integrated, and what you can do if they have not been integrated.

If the Microsoft Transaction Server is already installed when you install OraMTS, then the Oracle Universal Installer automatically backs up and substitutes several Visual C++ files of the banking demo with files that integrate the oci.dll and oramts.dll files. This enables you to use OCI with the banking demo.

If the Microsoft Transaction Server is not installed when you install OraMTS, then do the following:

1. Install Microsoft Transaction Server.

2. Back up the ROOTDRIVE:\program files\mts\ samples\packages\vcacct.dll file to a different location.

3. Copy the vcacct.dll file in ORACLE_BASE\ORACLE_HOME\oramts\samples\account.vc\release to the directory listed in Step 2.

4.1.1 Microsoft Application Demo Overview

The Microsoft application demo is installed in the ORACLE_BASE\ORACLE_HOME\oramts\samples\account.vc directory and is an OCI implementation of the Visual C++ Sample Bank package that ships with Microsoft Transaction Server. The demo component uses the user account scott and password tiger to connect to a database whose net service name is mtsdemo. You can change this information in the oramisc.h file. The demo also uses account and receipt tables.

These tables are part of the user scott schema in the default Oracle Database created during installation.

See Also:
Unlocking the Sample Tables in SQL*Plus User's Guide and Reference for more information on unlocking the sample schemas

4.1.2 Ensuring the Database Includes the Proper Microsoft Application Demo Tables

If the default database is not being used, or the database does not include the user scott, create the tables required to run the sample banking Microsoft application demo in the relevant user's schema.

To ensure the database includes the proper tables, determine if the database includes the proper tables:

- If you create the database automatically (using the General Purpose, Transaction Processing, or Data Warehouse database configuration type of Oracle Universal Installer or Database Configuration Assistant) or manually (using a custom SQL script, and then explicitly running the scott.sql script followed by the omtssamp.sql script), then the database includes the proper tables.
Go to "Running the Microsoft Application Demo".

- If you create the database by manually using a custom SQL script and do not run omtssamp.sql against the database, using the CREATE DATABASE syntax in SQL*Plus line mode, or in any available method on Solaris or any other operating system, then the database does not include the proper tables. Perform steps 0 through 6 in this section before proceeding to "Running the Microsoft Application Demo".

If the database does not include the proper tables, you must create them manually.

To manually create the proper tables:

1. Start SQL*Plus:

C:\> sqlplus /NOLOG

2. Connect to the database:

SQL> CONNECT username
Enter password: password@net_service_name

where net_service_name is the net service name that connects to the database. If you connect to the database with a username other than scott, you must change the username and password in oramisc.h and rebuild vcacct.dll.

3. Create the user mtsdemousr:

SQL> CREATE USER mtsdemousr IDENTIFIED BY mtsdemousr_password;

This creates the user mtsdemousr, which runs the Microsoft sample application.

4. Assign the following roles to user mtsdemousr:

SQL> GRANT CREATE SESSION,RESOURCE TO mtsdemousr;

5. Connect with user mtsdemousr:

SQL> CONNECT mtsdemousr
Enter password: mtsdemousr_password@net_service_name

6. Run the following SQL script:

SQL> @ORACLE_BASE\ORACLE_HOME\oramts\samples\sql\omtssamp.sql

This creates the account and receipt tables in the schema of user mtsdemousr.

4.1.3 Running the Microsoft Application Demo

This section describes how to run the Microsoft application demo:

1. Ensure that you have installed the Sample Bank Client application shipped with Microsoft Transaction Server. The sample component DLLs are typically installed under ROOTDRIVE:\program files\mts\samples\packages.

2. Open the project account.dsp in the ORACLE_BASE\ORACLE_HOME\oramts\SAMPLES\account.vc\Release directory using Microsoft Developer Studio.

3. Build vcacct.dll.

4. Back up the file vcacct.dll installed under ROOTDRIVE:\program files\mts\samples\packages.

5. Overwrite vcacct.dll with the one you built in step 3.

6. From the Start menu, choose Programs, then Windows Option Pack, then Microsoft Transaction Server, then Bank Client.
This starts the Sample Visual Basic bank application and runs vbbank.exe to test the component.

7. Click the Visual C++ radio button of the Language field.

8. Click the Account or the MoveMoney radio boxes under the Component field.

9. Enter the account numbers and the amount for the operation.

10. Click Submit.

11. Start SQL*Plus:

C:\> sqlplus /NOLOG

12. Connect with the username scott and the password tiger (unless you changed it):

SQL> CONNECT scott
Enter password: tiger

13. Verify the success of the operation:

SQL> SELECT * FROM account;
SQL> SELECT * FROM receipt;

4.2 Configuring Oracle ODBC Driver with the Microsoft Application Demo

You can use Oracle Open Database Connectivity (ODBC) Driver 11g Release 1 (11.1) with the Microsoft sample application.

See Also:
"Programming with Microsoft Transaction Server and an Oracle Database" for instructions on integrating the Oracle ODBC Driver with the sample application

4.3 Configuring Oracle Provider for OLE DB with the Microsoft Application Demo

You can use Oracle Provider for OLE DB 11g Release 1 (11.1)with the Microsoft sample application.

See Also:
Oracle Provider for OLE DB Developer's Guide for information about using Oracle Provider for OLE DB with Microsoft Transaction Server

5 Programming with Microsoft Transaction Server and an Oracle Database

This chapter describes how to program with Microsoft Transaction Server and an Oracle Database.

This chapter contains these topics:

- COM Component Integration in a Transaction

- Microsoft Transaction Server Application Development

- OCIIntegration with Microsoft Transaction Server

- ODBC Integration with Microsoft Transaction Server Overview

OraMTS also provides integration with OO4O, Oracle Provider for OLE DB, and Oracle Data Provider for .NET.

See Also:

- Oracle Objects for OLE Developer's Guide for information on using OO4O with MTS

- Oracle Provider for OLE DB Developer's Guide for information on using Oracle Provider for OLE DB with MTS

- Oracle Data Provider for .NET Developer's Guide for information on using Oracle Data Provider for .NET with MTS

5.1 COM Component Integration in a Transaction

The focal point of the transaction process is a component of Microsoft Transaction Server called Microsoft Distributed Transaction Coordinator (MS DTC). When a client computer starts a business method on a transactional component, Microsoft Transaction Server begins a transaction coordinated by the MS DTC. The Oracle connection pooling layer enables the database to act as a resource manager (RM) in the MS DTC-coordinated transaction. Figure 5-1 illustrates this transactional model.

Figure 5-1 Component Integration in a Transaction

Client Computer The client computer activates the application components on the MTS Application Server through a Web browser or through the component object model (COM) /distributed component object model (DCOM).

MTS Application Server The MTS application server is composed of the transaction client computer activates the application components through a Web browser or through the component object model (COM) /distributed component object model (DCOM).

Transactional Application Logic COM Components Three primary responsibilities:

- Embed the business logic. If a component is transactional, Microsoft Transaction Server starts a transaction for every method invocation on that component.

- Acquire pooled connections to a Oracle Database through the Oracle resource dispenser and Oracle Call Interface (OCI), Oracle Open Database Connectivity (ODBC) Driver, Oracle Provider for OLE DB, or Oracle Objects for OLE (OO4O).

- Decide the outcome of the operation by notifying Microsoft Transaction Server of its decision to commit or terminate the changes to all RMs.

Oracle ODBC Driver, OO4O, Oracle Provider for OLE DB, and OCI Two primary responsibilities:

- Obtain a service context to the Oracle Database through the OCI connection pooling component.

- Provide connection pooling resources, if necessary (through Oracle Provider for OLE DB or Oracle ODBC Driver). The Oracle ODBC Driver provides pooled ODBC connections. Oracle Provider for OLE DB provides pooled data source objects. OO4O uses the OCI connection pool.

OCI Connection Pool Three primary responsibilities:

- Enlists the RM (Oracle Database) in the component's Microsoft Transaction Server transaction.

- Starts an Oracle global transaction corresponding to the Microsoft Transaction Server transaction of which the component is a part.

- Acts as a resource dispenser to perform client-side connection pooling.

Oracle Net Provides connectivity in distributed, heterogenous computing environments.

Oracle MTS Recovery Service Recovers in-doubt Oracle transactions that originated from the host computer and are related to the Microsoft Transaction Server.

Database Recovery Job Detects in-doubt DTC transactions. This job extracts the recovery service's endpoint address inthe in-doubt transaction's XID and then requests the outcome of the Microsoft DTC transaction from the recovery service. Ultimately, the job will commit or terminate the in-doubt transaction when it receives the transaction's outcome.

Microsoft DTC Microsoft Distributed Transaction Coordinator is part of Microsoft Transaction Server and has two primary responsibilities:

- Commits and terminates transactions using the two-phase commit protocol.

- Monitors transactions that require recovery. Multiple MS DTCs can be involved in a single transaction. When a transactional Microsoft Transaction Server component on computer A invokes another transactional Microsoft Transaction Server component on computer B, a connection is opened between the MS DTC on computer A and the MS DTC on computer B. When the root MS DTC commits or terminates a transaction, it sends the request through all involved MS DTCs. The transaction request is then passed to the OCI connection pooling/Microsoft Transaction Server integration, which sends it to the database.

Oracle Database Acts as an RM for Microsoft Transaction Server. This is the database on which the client transaction request is performed.

5.2 Microsoft Transaction Server Application Development

OCI connection pooling is used to coordinate a transaction in nearly all application programming interfaces. This sections describes how transactions are registered and how OCI connection pooling coordinates them.

5.2.1 Microsoft Transaction Server Component Registration

Application components that run in the Microsoft Transaction Server environment are created as dynamic link libraries (DLLs). Application components are registered with Microsoft Transaction Server using the Microsoft Transaction Server Explorer graphical user interface (GUI) tool.

5.2.1.1 Types of Registration Components

When you register the application component, you mark it as one of the following types:

- Requires a Transaction The component must run in a transaction. If the transaction does not currently exist, Microsoft Transaction Server automatically creates a new transaction for each method invocation on the component.

- Supports a Transaction The component can run within the client's transaction. When a new component is created, its context inherits the transaction from the context of the invoking client. If the client does not have a transaction, the new context is also created without one.

- Requires a New Transaction The component must run within its own transaction. Microsoft Transaction Server automatically creates a new transaction for each method invocation on the component.

- Does Not Support Transactions The component does not run within a transaction. Each method invocation on the component is performed without a surrounding transaction, regardless of whether the invoking client includes a transaction.

5.2.1.2 Registration of Components

How you register an application component determines if it runs in a Microsoft Transaction Server-coordinated transaction.

- If the application component runs in a Microsoft Transaction Server-coordinated transaction, the OCI connection pooling is always used and Microsoft Transaction Server and its MS DTC component coordinate the creation, startup, management, and commitment phases of the transaction. Microsoft Transaction Server ensures that all changes made by the component are committed if the transaction succeeds, or are terminated if the transaction fails.

- If the application component does not run in a Microsoft Transaction Server-coordinated transaction, the component runs in a Microsoft Transaction Server environment, but the databases that it accesses may or may not take part in MS DTC-coordinated transactions. If the transaction is not MS DTC-coordinated, the client application must create, start, manage, and commit the transaction. OCI connection pooling may be used, depending upon the interface accessing the database (such as Oracle Provider for OLE DB, Oracle ODBC Driver, OO4O, or others).

5.2.2 Microsoft Transaction Server-Coordinated Component Transaction

This section describes how OCI connection pooling, Microsoft Transaction Server, and MS DTC operate with application components in a Microsoft Transaction Server-coordinated transaction environment.

1. The client API (one of Oracle ODBC Driver, OCI, OO4O, ODP.NET or Oracle Provider for OLE DB) calls OCI function OraMTSSvcGet() to obtain a service context from the OCI connection pooling component.

2. The OCI connection pooling component enlists the transaction that will be coordinated by the MS DTC component of Microsoft Transaction Server.
The OCI service and environment handles are returned to client applications.

3. The client application:

- Performs the database operations.

- Calls OCI function OraMTSSvcRel() to release the OCI pooling connection obtained at the beginning of the transaction.

- Calls SetComplete (to commit database operations) or SetAbort (to terminate database operations) on the Microsoft Transaction Server context object associated with the component.

4. MS DTC performs the two-phase commit protocol to prepare and commit or to terminate the transaction. This notifies the OCI connection pooling component and ends the transaction.

5. OCI connection pooling is notified and performs the necessary steps to complete phase one, the prepare phase, and phase two, the commit or terminate phase.

5.2.3 Microsoft DTC-Coordinated Component Transaction

This section describes how OCI connection pooling, Microsoft Transaction Server, and MS DTC operate with application components not running in a Microsoft Transaction Server-coordinated transaction, but using MS DTC.

1. The client application starts an MS DTC transaction and connects to the Oracle Database. The connection protocol follows one of the following scenarios:

- Nonpooled OCI connections are obtained through OCI logon calls such as OCIServerAttach() and OCISessionBegin(). For these connections, the application calls OraMTSEnlCtxGet() to associate the OCI service context with a Microsoft Transaction Server enlistment context.

- A connection pool is obtained by calling OraMTSSvcGet(..,..,ORAMTS_CFLG_NOIMPLICIT).

2. The client handles the context in one of the following scenarios:

- For nonpooled connections, the client application passes in the enlistment context to OraMTSJoinTxn().

- For pooled connections, the client application passes the OCI service context into OraMTSSvcEnlist().

3. The OCI connection pooling component enlists the connection, either pooled or nonpooled, in the transaction coordinated by the MS DTC component of Microsoft Transaction Server.

4. The client application then:

- Performs database operations.

- Calls OraMTSSvcEnlist() with a NULL transaction reference to de-enlist from an MS DTC coordinated transaction.
For nonpooled connections, OraMTSTxnJoin() is invoked with a NULL transaction reference to perform the de-enlistment.

- Calls OraMTSSvcRel() to release a pooled connection back to the pool.
For nonpooled connections, the client calls OraMTSEnlCtxRel() to release the enlistment context and then logs off the database.

- Calls the commit or terminate method on the MS DTC transaction object, such as pTransaction->Commit() or pTransaction->Abort().

5. MS DTC performs the two-phase commit protocol to commit the transaction.

6. OCI connection pooling is notified and performs the necessary steps to complete phase one, the prepare phase, and phase two, the commit or terminate phase.

5.3 OCI Integration with Microsoft Transaction Server

Example 5-1 illustrates how you can integrate the MTS sever with OCI. The only change in code you must make involves obtaining and releasing the OCI service context handle. Both OCI service context handle and environment handle are acquired when you obtain a pooled OCI connection to the database by calling OraMTSSvcGet(). Include the oramts.h header and link with the oramts.lib library. When you are finished, call OCI function OraMTSSvcRel() to release the service context handle and environment handle. Using OraMTSSvcGet() enables you to receive connection pooling and implicit transaction support if you registered the application component to run in a Microsoft Transaction Server transaction.

Ensure that for each process, you call OCIInitialize at least once before executing any other OCI calls. This initializes the OCI process environment. In addition, you must pass it the OCI_THREADED flag. If you are using Microsoft Internet Information Server (IIS) and the components are being called as in-process libraries, then OCIInitialize is already called for you. The registry key ORAMTS_OCI_OBJ_MODE has been added. Set the value to 1 to initialize OCI in Object mode; otherwise OCI will initialize in the threaded mode.

Example 5-1 Integration of MTS and OCI

#include <oci.h>
#include  <oramts.h>
#include  <xolehlp.h>
// other MTS relevant includes ...

// prototype for the error handler.
BOOL Chekerr(sword swOCIStat, OCIError *OCIErrh);

// MTS component method
HRESULT OCITestMethod()
{
IObjectContext *pObjectContext = NULL;
OCIEnv    *myenvh = NULL;
OCISvcCtx *mysvch = NULL;
OCIError  *myerrh = NULL;
OCIStnt   *mystmh = NULL;
DWORD      dwStat;
HRESULT    hRes = S_OK;
sword      swOCIStat;
BOOL       bCommit = FALSE;
char      *lpzStmt = "UPDATE EMP SET SAL = SAL + 1000";

// Initialize the OCI environment first -- request OCI_THREADED
OCIInitialize(OCI_THREADED, (dvoid*)NULL,NULL,NULL,NULL);
// attempt to get a connection to the database through the resource dispenser
OraMTSSvcGet(
"hr","hr_password","finprod_db",&mysvch, &myenvh, ORAMTS_CFLG_ALLDEFAULT);
// validate return status
if(dwStat != ORAMTS_ERR_NOERROR)
{
printf("error: failed to obtain a connection to the database - %ld",
dwStat);
goto cleanup;
}
// successful logon and enlistment in the MTS transaction. allocate statement
// handles and other handles using the OCI environment handle myenvh ....
swOCIStat = OCIHandleAlloc(myenvh, (void *)&myerrh,OCI_HTYPE_ERROR, 0 , NULL);
if (Checkerr(swOCIStat, myerrh)) goto cleanup;
swOCIStat = OCIHandleAlloc(myenvh, (dvoid *)&mystmh,OCI_HTYPE_STMT, 0,NULL);
if (Checkerr(swOCIStat, myerrh)) goto cleanup;
// prepare a DML statement
OCIStmtPrepare(mystmh, myerrh, lpzStmt, lstrlen(lpzStmt), OCI_NTV_SYNTAX,
OCI_DEFAULT)
Checkerr(swOCIStat, myerrh);
// execute the statement -- ensure that AUTOCOMMIT is not requested.
OCIStmtExecute(mysvch, mystmh, myerrh, 1, 0, NULL, NULL, OCI_DEFAULT);
if (Checkerr(swOCIStat, myerrh)) goto cleanup;
// all's well so far choose to go for a commit
bCommit = TRUE;
cleanup:
if (mystmh) OCIHandleFree((void*)mystmh, OCI_HTYPE_STMT);
if (myerrh  OCIHandleFree((void*)myerrh, OCI_HTYPE_ERROR);
if (mysvch) OraMTSSvcRel(mysvch);
if (bCommit)
pObjectContext->SetComplete();
else
pObjectContext->Abort();
return(bCommit ? S_OK : E_FAIL);
}

5.3.1 Integrating COM Components

There are several scenarios for integrating COM components. COM applications that are not hosted by the Microsoft Transaction Server environment, also known as standalone applications, cannot use declarative transactions through the Microsoft Transaction Server Explorer Microsoft Management Console, but they can use the last three of the scenario described.

5.3.1.1 COM Components Running in an MTS-Coordinated Transaction

COM components that are running in an MTS-coordinated transactions use OCI connection pooling to implicitly enlist the database in a transaction. The following pseudo-code listing illustrates the use of OCI functions:

OCIInitialize(OCI_THREADED, ...)
OraMTSSvcGet(..., &OCISvc, ..., ORAMTS_CFLAG_ALLDEFAULT)
...
OraMTSSvcRel(OCISvc)

5.3.1.2 Non-Transactional COM Components Running with OCI Connection Pooling

COM components that are marked as non-transactional and running in an MTS-coordinated transaction use OCI connection pooling do not enlist the database in a transaction. The following pseudo-code listing illustrates the use of OCI functions:

OCIInitialize(OCI_THREADED)
OraMTSSvcGet(..., &OCISvc, ..., ORAMTS_CFLAG_NOIMPLICIT)
...
OraMTSSvcRel(OCISvc)

5.3.1.3 COM Components Using MS DTC and OCI Connection Pooling

COM components that are not running in an MTS-coordinated transaction use MS DTC with OCI connection pooling to explicitly enlist the database in a transaction. The following pseudo-code listing illustrates the use of OCI functions:

OCIInitialize(OCI_THREADED, ...)
DTCGetTransactionManager(...)
BeginTransaction(..., &transaction)
OraMTSSvcGet(..., &OCISvc, ..., ORAMTS_CFLAG_NOIMPLICIT)
OraMTSSvcEnlist(OCISvc, ..., transaction, ...)
...
OraMTSvcEnlist(OCISvc, ..., NULL, ...)
OraMTSSvcRel(OCISvc)

5.3.1.4 COM Components Using MS DTC and Nonpooling OCI Connection

COM components that are not running in an MTS-coordinated transaction use MS DTC with a non-pooling OCI connection to explicitly enlist the database in a transaction. The following pseudo-code listing illustrates the use of OCI functions:

OCIInitialize(OCI_THREADED, ...)
OCI toget connected
OraMTSEnlCtxGET
DTCGetTransactionManager(...)
BeginTransaction(..., &transaction)
OraMTSJoinTxn (OCISvc, ..., transaction, ...)
...
OraMTSJoinTxn
...
OraMTSEnlCtxRel()
OCI to logoff

5.3.2 Using OCI Functions

This section details the OCI functions discussed earlier in this section. Table 5-1 summarizes these functions.

Table 5-1 Summary of OCI Functions for Integrating MTS and Oracle Database

OCI Function

Summary

OraMTSSvcGet()

Obtains a pooled connection from the OCI connection pool.

OraMTSSvcRel()

Releases a pooled OCI connection, OCI service context, back to the connection pool.

OraMTSSvcEnlist()

Enlists or de-enlists an OCI connection in a transaction coordinated by MS DTC.

OraMTSSvcEnlistEx()

Enlists an OCI connection or service context in an MS DTC transaction.

OraMTSEnlCtxGet()

Creates an enlistment context for a nonpooled OCI connection.

OraMTSEnlCtxRel()

Eliminates a previously set up enlistment context for a nonpooled OCI connection.

OraMTSJoinTxn()

Enlists a nonpooled OCI connection in an MS DTC transaction.

OraMTSTransTest()

Tests if you are running inside a Microsoft Transaction Server-started transaction.

OraMTSOCIErrGet()

Retrieves the OCI error code and message text.

5.3.3 OraMTSSvcGet()

Obtains a pooled connection, also known as an OCI service context, from the OCI connection pool. The pooled connection includes an OCI service context handle and an OCI environment handle.

Syntax

DWORD  OraMTSSvcGet(

text*       lpUname,
text*       lpPsswd,
text*       lpDbnam,
OCISvcCtx** pOCISvc,
OCIEnv**    pOCIEnv,
ub4         dwConFlgs);

Parameters

Table 5-2 OraMTSSvcGet() Parameters

Parameter

IN/OUT

Description

lpUname

IN

Username for connecting to the Oracle Database

lpPsswd

IN

Password for the username

lpDbnam

IN

The net service name for connecting to the database (created with Oracle Net Manager or Oracle Net Configuration Assistant)

pOCISvc

OUT

Pointer to the OCI service context handle

pOCIEnv

OUT

Pointer to the OCI environment handle

dwConFlgs

IN

Connection flags. Possible values are:

- ORAMTS_CFLG_ALLDEFAULT
Obtains a pooled connection and enlists the connection in any Microsoft Transaction Server transaction, if one exists. If the component is nontransactional, no enlistment request is dispensed.

- ORAMTS_CFLG_NOIMPLICIT
Obtains a pooled connection, but does not enlist the resource in any Microsoft Transaction Server transaction even if the component is transactional. Use this flag if the component enlists the connection resource later using OraMTSSvcEnlist(). Prior to releasing a connection obtained in this fashion, the client must de-enlist the resource if enlisted.

- ORAMTS_CFLG_UNIQUESRVR
Requests a single OCI session for each OCI Server. In this release, multiplexing is not supported. Therefore, this option is always used.

- ORAMTS_CFLG_SYSDBALOGN
Use this flag if connecting as SYSDBA.

- ORAMTS_CFLG_SYSOPRLOGN
Use this flag if connecting as SYSOPER.

- ORAMTS_CFLG_PRELIMAUTH
Use this flag if connecting as the user INTERNAL to pre-Oracle9i databases. The INTERNAL account is no longer valid as of Oracle9i. Instead, log on with a SYSDBA or SYSOPER account using the ORAMTS_CFLG_SYSOPRLOGN or ORAMTS_CFLG_SYSDBALOGN flag.

Returns

Returns ORAMTSERR_NOERROR upon successful acquisition of an OCI pooling connection (OCI service context).

Usage Notes

- OraMTSSvcGet() returns a pooled OCI connection to the caller, enabling a database transaction using OCI to begin. Use OraMTSSvcGet() to implicitly enlist the OCI connection in a transaction coordinated by Microsoft Transaction Server. In this type of transaction, Microsoft Transaction Server controls the creation, startup, management, and commitment phases of the transaction through its MS DTC component.

- OraMTSSvcGet() also provides connection pooling without enlisting the Oracle Database in a Microsoft Transaction Server transaction. This is done by setting OraMTSSvcGet() as follows:

OraMTSSvcGet(...,ORAMTS_CFLG_NOIMPLICIT)

- In all cases where OraMTSSvcGet() is used, you must always use OraMTSSvcRel() to release the connection when finished.

- Use the flags ORAMTS_CFLG_SYSDBALOGN and ORAMTS_CFLG_SYSOPRLOGN when connecting as SYSDBA and SYSOPER, respectively.

- To obtain a nonenlisted connection using the hr/hr_password account, call OraMTSSvcGet() as follows:

OraMTSSvcGet("hr", "hr_password", "oracle", &OCISvc, &OCIEnv, ORAMTS_CFLG_ALLDEFAULT | ORAMTS_CFLG_NOIMPLICIT);

- OraMTSSvcGet() does not support placing the username (lpUname), password (lpPsswd), and net service name syntax (lpDbname) together in the username argument (for example, hr/hr_password@prod_fin). Instead, the caller must fill in lpUname, lpPsswd, and lpDbname separately (as shown in the previous syntax example). Calling OraMTSSvcGet() with the username and password as NULL strings uses external authentication (operating system authentication) for the connection.

5.3.4 OraMTSSvcRel()

Releases a pooled OCI connection, OCI service context, back to the connection pool. Use this function to release connections that were acquired with OraMTSSvcGet().

Syntax

DWORD OraMTSSvcRel(OCISvcCtx* OCISvc);

Parameters

Table 5-3 OraMTSSvcRel() Parameters

Parameter

IN/OUT

Description

OCISvc

IN

OCI service context for a pooled connection

Returns

Returns ORAMTSERR_NOERROR upon successful release of a pooled OCI connection.

Usage Notes

- An OCI pooled connection obtained through a previous call to OraMTSSvcGet() is released back to the connection pool. Once released back to the connection pool, the OCI service context, its environment handle, and all child handles are invalid.

- A nontransactional client component must explicitly call OCITransCommit() or OCITransAbort() prior to releasing a connection obtained through OraMTSSvcGet(..., ...,ORAMTS_CFLG_ALLDEFAULT) back to the pool. Otherwise, all changes made in that session are rolled back. A transaction component uses the SetComplete or SetAbort methods on its Microsoft Transaction Server object context.

- Components that have called OraMTSSvcGet(..., ...,ORAMTS_CFLG_NOIMPLICIT) to obtain a connection resource must first de-enlist the resource if enlisted. If the connection was enlisted explicitly, pTransaction->Commit() or pTransaction->Abort() must be called. Otherwise, OCITransCommit() or OCITransAbort() must be called before releasing the connection back to the pool.

5.3.5 OraMTSSvcEnlist()

Enlists or de-enlists an OCI connection in a transaction coordinated by MS DTC. Use this call to explicitly enlist pooled connections. Nonpooled connections must enlist with OraMTSJoinTxn().

Syntax

DWORD OraMTSSvcEnlist(

OCISvcCtx*  OCISvc,
OCIError*   OCIErr,
void*       lpTrans,
unsigned    dwFlags);

Parameters

Table 5-4 OraMTSSvcEnlist() Parameters

Parameter

IN/OUT

Description

OCISvc

IN

OCI service context for pooled connections obtained by calling OraMTSSvcGet()

OCIErr

IN/OUT

OCI error handle (ignored)

lpTrans

IN

Pointer to the MS DTC-controlled transaction in which to enlist. If NULL, the OCI connection is de-enlisted from the MS DTC-controlled transaction.

dwFlags

IN

Flag used for enlisting in a transaction. Use the ORAMTS_ENFLG_DEFAULT value. If enlisting, then start a new Oracle global transaction. If de-enlisting, then detach from any global Oracle transaction and delete the context object if the OCI service context represents a nonpooled connection.

Returns

Returns ORAMTSERR_NOERROR on success.

Usage Notes

- Use this call to explicitly enlist or de-enlist a pooled connection. For enlisting and de-enlisting nonpooled connections, use OraMTSSvcRel().

- OraMTSSvcEnlist() enlists (or de-enlists) pooled OCI connections obtained previously through OraMTSSvcGet() with the ORAMTS_CFLG_NOIMPLICIT flag and not yet released with OraMTSSvcRel(). The pooled OCI connections must be explicitly enlistable. When the transaction is complete, you must de-enlist OraMTSSvcEnlist(), passing NULL as the transaction pointer as follows:

OraMTSSvcEnlist (OCISvc, OCIErr, NULL, ORAMTS_ENFLG_DEFAULT)

You must use OraMTSSvcRel() to release the connection when done.

- Callers must allocate a connection, enlist the connection, perform work, de-enlist the connection, release the connection, and then attempt to commit or terminate.

5.3.6 OraMTSSvcEnlistEx()

Enlists an OCI connection or service context in an MS DTC transaction. Use this call only to explicitly enlist pooled connections. Nonpooled connections must enlist with OraMTSJoinTxn().

Syntax

DWORD OraMTSSvcEnlistEx(
OCISvcCtx* OCISvc,

OCIError*  OCIErr,
void*      lpTrans,
unsigned   dwFlags,
char*      lpDBName);

Parameters

Table 5-5 OraMTSSvcEnlistEx() Parameters

Parameter

IN/OUT

Description

OCISvc

IN

OCI service context for pooled connections obtained by calling OraMTSSvcGet()

OCIErr

IN/OUT

OCI error handle (ignored)

lpTrans

IN

Pointer to the MS DTC-controlled transaction in which to enlist. If NULL, the OCI connection is de-enlisted from the MS DTC-controlled transaction.

dwFlags

IN

Flag used for enlisting in a transaction. Use the ORAMTS_ENFLG_DEFAULT value. If enlisting, then start a new Oracle global transaction. If de-enlisting, then detach from any global Oracle transaction and delete the context object if the OCI service context represents a nonpooled connection.

lpDBName

-

Net service name for connecting to the database (created with Oracle Net Manager or Oracle Net Configuration Assistant)

Returns

Returns ORAMTSERR_ILLEGAL_OPER.

Usage Notes

Use OraMTSSvcEnlistEx() for pooled connections or OraMTSJoinTxn() for nonpooled connections.

5.3.7 OraMTSEnlCtxGet()

Creates an enlistment context for a nonpooled OCI connection.

Syntax

DWORD OraMTSEnlCtxGet(

text*       lpUname,
text*       lpPsswd,
text*       lpDbnam,
OCISvcCtx*  pOCISvc,
OCIError*   pOCIErr,
ub4         dwFlags,
void**      pCtxt);

Parameters

Table 5-6 OraMTSEnlCtxGet() Parameters

Parameter

IN/OUT

Description

lpUname

IN

Username for connecting to the Oracle Database

lpPsswd

IN

Password for connecting to the Oracle Database

lpDbnam

IN

Net service name for connecting to a database

pOCISvc

IN

OCI service context for a nonpooled connection

pOCIErr

IN

OCI error handle

dwFlags

IN

Enlistment flags. The only value currently permitted is 0.

pCtxt

OUT

Enlistment context to be created

Returns

Returns ORAMTSERR_NOERROR on success.

Usage Notes

- This call sets up an enlistment context for a nonpooled connection. This call must be started just after the caller establishes the OCI connection to the database. Once created, this context can be passed into OraMTSJoinTxn() calls. Prior to deleting the OCI connection, OraMTSEnlCtxRel() must be called to delete the enlistment context.

- Callers must:

- Allocate a nonpooled connection through OCI.

- Create an enlistment context by calling OraMTSEnlCtxGet().

- Enlist the connection by calling OraMTSJoinTxn().

- Perform database work.

- De-enlist the connection by calling OraMTSJoinTxn() with a NULL transaction pointer.

- Attempt to commit or terminate work.

- Release the enlistment context by calling OraMTSEnlCtxRel().

- Release the nonpooled OCI connection and delete its associated OCI environment handle.

5.3.8 OraMTSEnlCtxRel()

Eliminates a previously set up enlistment context for a nonpooled OCI connection.

Syntax

DWORD OraMTSEnlCtxRel(void* pCtxt);

Parameters

Table 5-7 OraMTSEnlCtxRel() Parameters

Parameter

IN/OUT

Description

pCtxt

IN

Enlistment context to eliminate

Returns

Returns ORAMTSERR_NOERROR on success.

Usage Notes

- Before dropping a nonpooled OCI connection, a client must call OraMTSEnlCtxRel() to eliminate any enlistment context it may have created for that connection. The enlistment context can maintain OCI handles allocated off the connection's OCI environment handle. This makes it imperative that the environment handle is not deleted for the associated enlistment context.

5.3.9 OraMTSJoinTxn()

Enlists a nonpooled OCI connection in an MS DTC transaction.

Syntax

DWORD OraMTSJoinTxn(void*  pCtxt,
void*  pTrans);

Parameters

Table 5-8 OraMTSJoinTxn() Parameters

Parameter

IN

Description

pCtxt

IN

Enlistment context for the OCI connection

pTrans

IN

Reference to the MS DTC transaction object

Returns

Returns ORAMTSERR_NOERROR on success.

Usage Notes

- Clients use this call with nonpooled OCI connections to enlist connections in MS DTC-coordinated transactions. The client passes in the wide reference to the enlistment context representing the OCI connection, along with a reference to an MS DTC transaction object. If pTrans is NULL, the OCI connection is de-enlisted from any MS DTC transaction in which it is currently enlisted. You can enlist a previously-enlisted OCI connection in a different MS DTC transaction.

5.3.10 OraMTSTransTest()

Tests if you are running inside a Microsoft Transaction Server-started transaction.

Syntax

BOOL OraMTSTransTest();

Returns

Returns true if running inside a Microsoft Transaction Server transaction.

Usage Notes

Microsoft Transaction Server transactional components use OraMTSTransTest() to check if a component is running within the context of a Microsoft Transaction Server transaction. Note that this call can only test Microsoft Transaction Server-started transactions. Transactions started by directly calling the MS DTC are not detected.

5.3.11 OraMTSOCIErrGet()

Retrieves the OCI error code and message text, if any, from the last OraMTS function operation, typically OraMTSSvcGet() or OraMTSJoinTxn().

Syntax

BOOL OraMTSOCIErrGet(DWORD* dwErr,
LPCHAR lpcEMsg,
DWORD* lpdLen);

Parameters

Table 5-9 OraMTSOCIErrGet() Parameters

Parameter

IN/OUT

Description

dwErr

-

Error code

lpcEMsg

-

Buffer for the error message, if any

lpdLen

-

Set to the actual number of message bytes

Returns

Returns true if an OCI error is encountered. Otherwise, false is returned. If true is returned and lpcEMsg and lpdLen are valid, and there is a stashed error message, up to lpdLen bytes are copied into lpcEMsg. lpdLen is set to the actual number of message bytes.

Usage Notes

Example 5-2 illustrates how OraMTSOCIErrGet() retrieves the OCI error code and OCI error message text, if any, from the last OraMTSSvc() operation on this thread.

Example 5-2 Retrieving the OCI Error Code and Message Text

DWORD dwStat = OraMTSSvcGet("hr",
"invalid_password",
"fin_prod",
"db",
&mysvch,
&myenvh,
ORAMTS_CFLG_ALLDEFAULT);

if (dwStat != ORAMTS_ERR_NOERROR)
{
DWORD   dwOCIErr;
char    errBuf[MAX_PATH];
DWORD   errBufLen = sizeof(effBuf);

if (OraMTSOCIErrGet(&dwOCIErr, &errBuf, &errBufLen))
printf("OCIError %d: %s"\n);
}

5.4 ODBC Integration with Microsoft Transaction Server Overview

This section describes how to use Oracle ODBC Driver with Microsoft Transaction Server and a Oracle Database. No changes to OCI code are necessary for ODBC to operate successfully.

5.4.1 Setting the Connection Attribute

To use Microsoft Transaction Server with either Oracle ODBC Driver 11.1 or Microsoft Oracle ODBC driver, set the connection attribute using the SQLSetConnectAttr function to call the parameter SQL_ATTR_ENLIST_IN_DTC in the ODBC code. This enables you to receive connection pooling and implicit transaction support.

5.4.2 Using Oracle ODBC Driver

The ODBC Driver Manager distributed with ODBC 3.0 is a Resource Dispenser that supports connection pooling. Oracle ODBC Driver release 11.1 integrates with the ODBC 3.0 Driver Manager by supporting the SQLSetConnectAttr(...,..., SQL_ATTR_ENLIST_IN_DTC) call to enlist or de-enlist the ODBC connection used in MS DTC-coordinated transactions.

Use the Oracle ODBC Driver 11.1 with:

- Applications you develop

- The sample banking application that Microsoft provides with Microsoft Transaction Server.

To configure Oracle ODBC Driver, follow these steps:

1. Choose Start > Settings > Control Panel.
The Control Panel window appears.

2. Double-click ODBC.
The ODBC Data Source Administrator dialog box appears.

3. Choose the File DSN tab.

4. To make Oracle ODBC Driver work with Microsoft sample banking application demo, follow these steps. Otherwise, skip this step.

- Back up Microsoft mtssamples.dsn file. This file is located in ROOTDRIVE:\program files\common files\odbc\data sources.

- Select mtssamples.dsn and click Remove.

- Click Yes when prompted.

This deletes the configuration file that enables the Microsoft Transaction Server sample application demo to use the Microsoft ODBC driver.

If you don't intend to use the demo, click Add to create a new File data source name (DSN).

The Create New Data Source wizard appears.

5. Select Oracle in HOME_NAME.

6. Click Advanced.

7. Add the following information in the keywords and values field:

SERVER=database_alias
USERNAME=hr
PASSWORD=hr_password

where:

- SERVER is the The database alias used by the demo to access the database mtsdemo.

- USERNAME is the database username for this application, such as hr.

- PASSWORD is the database password for username hr.

Verify that the hr schema contains the account and receipt tables.

8. Click OK.

9. Click Next to continue with the Create New Data Source wizard.

10. For the Microsoft sample application, enter mtssamples.dsn (Microsoft ODBC name). This name must exactly match the name you removed in Step 4.
For applications you develop, enter the name of the DSN file that will be used.

11. Complete the remaining Create New Data Source wizard pages.

12. Click OK to exit the ODBC Data Source Administrator dialog box.

13. Exit the Control Panel window.

See Also:

- Microsoft Transaction Server SDK for information

- Chapter 4, "Running the Microsoft Application Demo" for more information

5.4.3 Using Microsoft Oracle ODBC Driver

As an alternative to the Oracle ODBC driver, you can use the Microsoft Oracle ODBC Driver. You should be aware that you would not be able to integrate with OO40, Oracle Provider for OLE DB, and Oracle Data Provider for .NET if using the Microsoft driver. Also, you will not receive the performance benefits of the Oracle ODBC driver, API support for integration, or Oracle client support services.

After enabling the Microsoft Oracle ODBC Driver, perform these additional steps to configure the Microsoft Oracle ODBC Driver:

To configure the Microsoft Oracle ODBC Driver:

1. Install Oracle Required Support Files (RSF) release 11.1 and SQL*Net 2.3 or later on the computer where the Microsoft Oracle ODBC Driver is operating.

2. Run the ORACLE_BASE\ORACLE_HOME\oramts\samples\ sql\omtssamp.sql script.

3. Use SQL*Net Easy Config to set up a database alias connection. This alias is used in the mtssamples.dsn file.

4. If you installed the release 11.1 RSFs in a home with Oracle Net installed, be sure to set the following registry parameter at HKEY_LOCAL_MACHINE\ SOFTWARE\ORACLE:

ORAOCI = ORA73.DLL

See Also:
"Setting Up MTS to Access Oracle" in the Microsoft Transaction Server online Help for instructions on enabling the Microsoft Oracle ODBC Driver

6 Tuning Microsoft Transaction Server Performance

This chapter provides Microsoft Transaction Server performance tuning information.

This chapter contains these topics:

- Improving Microsoft Transaction Server Application Performance

- Managing Microsoft Transaction Server Connections

- Increasing the Transaction Timeout Parameter

- Changing Initialization Parameter Settings

- Starting MSDTC

6.1 Improving Microsoft Transaction Server Application Performance

Optimizing the programming methods of your application improves its performance. For example, placing all code for a given transaction into one component object model (COM) component means you do not mark that component as transactional. This eliminates the overhead of going through Microsoft Transaction Server. You can subsequently use the Oracle commit or rollback functions to control that transaction in the component. If you are using the Oracle Call Interface (OCI), you can still use ORAMTSSvcGet(), but you can also use the ORAMTS_CFLG_NOIMPLICIT flag. If you are updating across two or more Oracle Database instances, use database links and connect to one database from the COM component.

See Also:
"OCIIntegration with Microsoft Transaction Server" for more information on using ORAMTSSvcGet()

6.2 Managing Microsoft Transaction Server Connections

When a .NET or COM component ends a session with the Oracle Database, the connection does not immediately terminate. Instead, it remains idle in a connection pool, where it is available for reuse by another component attempting a new connection to the Oracle Database.

6.2.1 Connection Pooling Registry Parameters

The idle period during which a connection is reusable reduces the resource costs associated with opening a new connection. The amount of time that the connection remains idle and available in the connection pool is determined by several registry parameter settings. You can modify these parameters on the computers on which the client Microsoft Transaction Server components are installed, in the file HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID:

ORAMTS_CONN_POOL_TIMEOUT The time, in seconds, that the connection remains idle and available for reuse in the client side connection pool, before timing out and being released. The default value of this parameter is 120 seconds.

ORAMTS_SESS_TXNTIMETOLIVE The time, in seconds, that the connection established using OraMTSSvcGet() remains alive in the client side connection pool after being released by an OraMTSSvcRel() call. The sum of the ORAMTS_CONN_POOL_TIMEOUT and ORAMTS_NET_CACHE_TIMEOUT values determines the actual time before a connection terminates completely. The default value of this parameter is 120 seconds.

ORAMTS_NET_CACHE_TIMEOUT The time, in seconds, that is used in session initialization and authentication, such as the username and password. ORAMTS_NET_CACHE_TIMEOUT is the time allocated to deal with communication issues such as sending and receiving data, the actual Oracle Net connection. Establishing a new Oracle Net connection requires more resources than using that connection to establish a logon session. Oracle recommends setting this parameter to a higher value than the with ORAMTS_CONN_POOL_TIMEOUT value. The sum of the ORAMTS_CONN_POOL_TIMEOUT and ORAMTS_NET_CACHE_TIMEOUT values determines the actual time before a connection terminates completely. The default value of this parameter is 120 seconds.

ORAMTS_NET_CACHE_MAXFREE The maximum number of free server connections that should be maintained in the client-side connection pool at a given time. The default value of this parameter is 5.

ORAMTS_OSCREDS_MATCH_LEVEL The level of Windows security checking implemented when the OS_ROLES initialization parameter in the init.ora file is true.

When a user establishes a connection to the Oracle Database using the CONNECT command, the Windows username is associated with specific database roles and privileges. When the user disconnects, this connection becomes idle and available in the pool. When another user enters the CONNECT command, if the Windows username is identical to the one used by the first user, the second user can receive the same database roles and privileges as the first user. This is a considerable security concern, especially if the second user possesses only the CREATE SESSION and RESOURCE database roles but receives the DBA privileges of the first user.

By default, the ORAMTS_OSCREDS_MATCH_LEVEL parameter value is OS_AUTH_LOGIN, and Windows security checking is performed only if the username and password are NULL.

The most secure setting for this parameter is ALWAYS, which ensures that Windows security checking is performed in all cases, and takes care of possible security breaches due to identical non-null Window usernames.

Because Windows security checking is a resource-intensive operation, you may wish to set the value of this parameter to NEVER. However, if you know that OS_ROLES is true, or if you use operating system-authenticated connections, you should avoid this option.

6.3 Increasing the Transaction Timeout Parameter

If transaction requests are timing out before completing, the transaction timeout parameter may be set too low. Increase the transaction timeout parameter to ensure that transactions have enough time to complete.

To increase the transaction timeout parameter:

1. Go to the Windows computer on which Microsoft Transaction Server is installed.

2. From the Start menu, select Programs, then Administrative Tools, then Component Services.
The Component Services window appears.

3. Double-click Console Root in the Component Services window so its tree structure expands.

4. Double-click Component Services.

5. Double-click Computers.

6. Right-click My Computer.
A menu appears with several options.

7. Choose Properties.
The My Computer Properties dialog box appears.

8. Choose the Options tab.

9. Enter a value in the Transaction Timeout field and click OK.

The transaction timeout value is increased. For most environments, 60 seconds may be enough. However, if the transaction is competing with numerous concurrent transactions, this value may be too low.

6.4 Changing Initialization Parameter Settings

You may need to modify several initialization parameters to use the Oracle Database with Microsoft Transaction Server. The values to which to set these parameters are based on the database workload environment.

To verify initialization parameter file values, follow these steps:

1. Ensure that you have SYSDBA privileges.

2. Go to the computer on which the Oracle Database is installed.

3. Start SQL*Plus:

C:\> sqlplus /NOLOG

4. Connect to the database as SYSDBA:

SQL> CONNECT / AS SYSDBA

5. Check the value for the SESSIONS parameter:

SQL> SHOW PARAMETER SESSIONS

6. Check the value for the PROCESSES parameter:

SQL> SHOW PARAMETER PROCESSES

The current settings for both SESSIONS and PROCESS parameters are typically appropriate for running the Microsoft application demo. For creating and deploying .NET or COM-based applications, the values for these parameters depend on the database environment's anticipated workload. For example, if you anticipate 100 concurrent connections to the Oracle Database, consider setting both values to 200 to accommodate a possible system overload. Ensure that you do not set these parameters too high, because they are resource-intensive.

See Also:
Oracle Database Reference for information about these parameters.

7. Set the following initialization parameters to at least these values:

- SESSIONS = 200 (or larger if anticipating heavier loads)

- PROCESSES = 200 (or larger if anticipating heavier loads)

8. Shut down the Oracle Database:

SQL> SHUTDOWN

9. Restart the Oracle Database:

SQL> STARTUP

10. Exit SQL*Plus:

SQL> EXIT

6.5 Starting MSDTC

The Microsoft Distributed Transaction Coordinator (MS DTC) must be running to enable communication with Oracle Services for Microsoft Transaction Server.

To start MS DTC, follow these steps:

1. On the computer where Microsoft Transaction Server is installed, from the Start menu, choose Programs, then Administrative Tools, then Services, then Microsoft Transacation Server.
The Microsoft Management Console appears.

2. Double-click Console Root in the Microsoft Management Console Explorer window.

3. Double-click Microsoft Transaction Server.

4. Double-click Computers.

5. Right-click My Computer.
A menu with several options appears.

6. Choose Start MSDTC.
MS DTC starts.

7 Troubleshooting Oracle Microsoft Transaction Server

This chapter provides information on troubleshooting Oracle Microsoft Transaction Server.

This chapter contains these topics:

- Tracking Oracle Services for Microsoft Transaction Server Performance

- Correcting Windows Explorer Problems

- Correcting Oracle Net Changes that Impact Connection Pooling

- Designing an Application that Uses Multiple Databases

- Working with Different Types of Connection Pooling

- Working with In-Doubt Transactions

- Dropping the Microsoft Transaction Server Administrative User Account

7.1 Tracking Oracle Services for Microsoft Transaction Server Performance

Trace files record information about Oracle Services for Microsoft Transaction Server performance. This information includes:

- Any errors

- Enlistment requests and outcomes

- Prepare, commit, and terminate requests and their outcomes

Registry parameters handle tracing within oramts.dll, which performs the following tasks:

- It implements the API for integrating the Oracle Database with Microsoft Transaction Server.

- It works as a resource dispenser to provide pooled Oracle Call Interface (OCI) connections.

- It enables clients with nonpooled OCI connections to enlist in transactions started by Microsoft Distributed Transaction Coordinator (MS DTC).

- It communicates with Oracle Services for Microsoft Transaction Server to enlist the Oracle Database in transactions started by MS DTC.

The MTS-based COM components can acquire connections to both dedicated and shared Oracle servers of a database. The components can then attempt to perform distributed updates, using data manipulation language, on another database using pre-existing database links between these databases. While the distributed updates from shared servers succeed, those from dedicated servers fail.

Registry parameters that handle tracing are automatically set in \\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID during the installation of Oracle Services for Microsoft Transaction Server. Use the registry parameters instead of setting them as environment variables; setting environment variables requires you to restart your computer for the changes to occur.

Table 7-1 shows the range of ORAMTS_CP_TRACE_LEVEL trace values.

Table 7-1 ORAMTS_CP_TRACE_LEVEL Trace Registry Parameter Values

Level

Description

0

Disables tracing. If the registry parameter is not set in the registry or as an environment variable, then tracing is disabled. This is equivalent to setting the level to 0.

1

Traces errors only

2

Traces important events in addition to errors

3

This level is not supported; if you set this parameter to 3, level 2 tracing is enabled.

4

Traces function entry/exit, important events, and errors

5

Traces reference counting function and constructor/destructor entry/exit

Note:
The Oracle MTS Recovery Service also generates trace file output in the ORACLE_BASE\ORACLE_HOME\oramts\trace directory.

7.2 Correcting Windows Explorer Problems

If you are using Windows and experience Windows Explorer crashes or other unexpected Windows problems when using Microsoft Transaction Server with a database, install the Windows 4.0 Service Pack 6a or greater (available from Microsoft).

7.3 Correcting Oracle Net Changes that Impact Connection Pooling

The connection pool provided by the OraMTS layer, oramts.dll, uses a connection's net service name to identify pooled connections for an application. If changes are made to the net service name, and pooled connections are available, the application using the connection pool must be stopped and restarted. These changes can include altering the host or the database system identifier (SID) for the net service name in the tnsnames.ora file.

These changes ensure that all currently pooled connections corresponding to the old net service name are destroyed and any new pooled connections use the changes made to the net service name. This includes any application hosting Microsoft Transaction Server components.

To empty connection pools, perform the following:

- If the application is an out-of-process Microsoft Transaction Server component (server package), run the following application:

C:\> mtxstop

This empties the connection pools.

- If the application is an in-process Microsoft Transaction Server component (library package), terminate the application. This also empties the connection pool.

7.4 Designing an Application that Uses Multiple Databases

Oracle clients can establish connections to a database in two ways:

- Typical Oracle clients establish connections to a database using a dedicated server configuration. In a dedicated server configuration, one client corresponds to one Oracle server process.

- For scalability under heavy loads, Oracle clients have the option of using a shared server configuration. In a shared server configuration, a single Oracle server process can be shared by more than one client connection.

Microsoft Transaction Server communicates with the database through distributed transactions. In a dedicated server configuration, you cannot use distributed updates (data manipulation language statements across database links) from other databases. However, if the original connection to the database is established using shared server configurations, the distributed updates from other databases succeed.

To use data manipulation language statements in shared server configurations, set the following parameter in the tnsnames.ora file:

SERVER=dedicated

This forces the Oracle Net listener to provide a dedicated connection. Figure 7-1 shows this process.

Figure 7-1 Distributed DML Statements from MTS Applications

See Also:
Oracle Net Services Administrator's Guide

7.5 Working with Different Types of Connection Pooling

Oracle Net Connection Pooling  Oracle Net connection pooling is a server-side feature that is implemented only if the Oracle Database is configured for shared server support. Oracle Net connection pooling enables you to minimize the number of physical network connections to a shared server. This is achieved by sharing a dispatcher's set of connections among multiple client processes.

Microsoft Transaction Server Connection Pooling Microsoft Transaction Server provides a resource pooling infrastructure that enables certain resources to be pooled, such as memory and database connections.

OCI Connection Pooling OCI connection pooling layer works with MTS resource pooling to provide pooled Oracle client/server sessions. The OCI connection pooling layer also caches Oracle Net connections to reduce client/server session setup time.

7.6 Working with In-Doubt Transactions

Oracle uses distributed transactions in the following configurations:

- Distributed database configurations, such as distributed updates using database links

- External transaction managers, such as Tuxedo and MS DTC, for coordinating transaction outcome

The two-phase commit protocol completes these transactions. During phase one, the transaction manager (TM) requests the various resource managers involved in the TM's transaction to prepare the underlying distributed transactions. In phase two, the TM determines whether it commits or terminates the transaction, and requests the resource managers to commit or terminate the underlying transaction. If a resource manager fails to receive the phase two notification, the underlying distributed transaction becomes in-doubt.

To integrate Oracle with Microsoft Transaction Server, distributed transactions are used in the database. Distributed transactions correspond to transactions coordinated by the MS DTC. A distributed transaction can become in-doubt when the transaction cannot commit or terminate (phase two of the two-phase commit). This occurs when the Microsoft Transaction Server application server process, database, or network fails.

See Also:
Chapter 3, "Managing Recovery Scenarios"

7.7 Dropping the Microsoft Transaction Server Administrative User Account

The Microsoft Transaction Server administrative user account is created by running the oramtsadmin.sql script. If you later change the database with which Microsoft Transaction Server is coordinating transactions, you can drop the administrative user account schema from the previous database.

To drop the Microsoft Transaction Server administrative user account:

1. Start SQL*Plus:

c:\> sqlplus /NOLOG

2. Connect to the database as SYSDBA:

SQL> CONNECT / AS SYSDBA

3. Enter the following command to drop administrative user account schema:

SQL> DROP USER mtsadmin_username CASCADE;

where mtsadmin_username is the Microsoft Transaction Server administrative user account (default is mtssys).

See Also:
See Chapter 3, "Managing Recovery Scenarios" for information on creating the Microsoft Transaction Server administrative user account for the new database

Glossary

Atomicity, Consistency, Isolation, and Durability (ACID)

ACID consists of the four primary attributes provided to any transaction by a transaction manager (also called a transaction manager).

component object model (COM)

A binary standard that enables objects to interact with other objects, regardless of the programming language in which each object was written.

distributed component object model (DCOM)

An extension of COM that enables objects to interact with other objects across a network.

data manipulation language

The category of SQL statements that query and update database data. Common DML statements are SELECT, INSERT, UPDATE, and DELETE.

JOB_QUEUE_PROCESSES

This initialization parameter specifies the number of job queue processes started in an instance. This parameter must be set to at least 1 to run job queue processes.

listener.ora

A listener configuration file that identifies the following for a listener:

- Unique name

- Protocol addresses on which it accepts connection requests

- Services for which it is listening

Microsoft .NET

Microsoft .NET is a set of Microsoft software technologies used to connect information, people, systems, and devices through web services to each other and to larger applications over the Internet.

Microsoft application demo

An Oracle Call Interface (OCI) implementation of the Visual C++ Sample Bank package that ships with Microsoft Transaction Server on Windows.

Microsoft Distributed Transaction Coordinator (MS DTC)

The focal point of the transaction process is a component of Microsoft Transaction Server called Microsoft Distributed Transaction Coordinator (MS DTC).

Microsoft Management Console

An application that serves as a host for administrative tools called snap-ins. By itself, Microsoft Management Console does not provide any functionality.

Microsoft Transaction Server

A COM-based transaction processing system that runs on an Internet or network server.

mtssys

The default Microsoft Transaction Server administrator username. In releases prior to Oracle9i Database release 1 (9.0.1), this was the username for the OraMTS.

net service name

The name used by clients to identify an Oracle Net server and the specific system identifier (SID) or database for the Oracle Net connection. A net service name is mapped to a port number and protocol. A net service name is also known as a connect string, database alias, host string, or service name.

This also identifies the specific SID or database to which the connection is attaching, and not just the Oracle Net server.

OraMTS

Abbreviation for "Oracle Services for Microsoft Transaction Server".

ORAMTS_ORADB

Database alias for connecting the OraMTS to the Oracle Database; no longer supported as of Oracle9i Database release 1 (9.0.1)).

Oracle Call Interface (OCI)

An application programming interface that enables you to manipulate data and schemas in a database. You compile and link an OCI program in the same way that you compile and link a nondatabase application. There is no requirement for a separate preprocessing or precompilation step.

Oracle Data Provider for .NET (ODP.NET)

Oracle Data Provider for .NET (ODP.NET) features optimized data access to the Oracle Database from a .NET environment. ODP.NET includes support for connection pooling, PL/SQL, LOBs, RefCursors, globalization/localization, proxy user authentication/ parameter array binding, named parameters, and safe type mapping between Oracle types and .NET types.

Oracle Fail Safe

Ensures that if a failure occurs on one cluster node, then the databases and applications running on that node fail over (move) automatically and quickly to a surviving node.

Oracle Manager for MTS Services snap-in

This Microsoft Management Console snap-in is no longer available or required with OraMTS, as of Oracle9i Database release 1 (9.0.1).

Oracle MTS Recovery Service

The Oracle MTS Recovery Service resolves in-doubt transactions on the computer that started the failed transaction. A scheduled recovery job for each Microsoft Transaction Server-enabled database lets the Oracle MTS Recovery Service resolve in-doubt transactions.

Oracle Objects for OLE (OO4O)

Oracle Objects for OLE (OO4O) is a COM-based database connectivity tool that combines seamless and optimized access to Oracle Database instances with easy to use interfaces.

Oracle Open Database Connectivity (ODBC) Driver

Oracle ODBC Driver provides a standard interface that allows one application to access many different data sources. The application's source code does not have to be recompiled for each data source. A database driver links the application to a specific data source. A database driver is a dynamic link library that an application can invoke on demand to gain access to a particular data source. Therefore, the application can access any data source for which a database driver exists.

Oracle Provider for OLE DB

Interfaces that offer high performance and efficient access to Oracle data by applications, compilers, and other database components.

Oracle Services for Microsoft Transaction Server (OraMTS)

A component that provides full integration of the Oracle Database with Microsoft Transaction Server. This component enables you to develop and deploy COM-based applications using Microsoft Transaction Server.

Optimal Flexible Architecture (OFA)

A set of file naming and placement guidelines for Oracle software and databases.

resource manager (RM)

Microsoft Transaction Server enlists the database to act as a resource manager (RM) in the transaction process.

SYSDBA

A special database administration role that contains all system privileges with the ADMIN OPTION and the SYSOPER system privilege. SYSDBA also permits CREATE DATABASE actions and time-based recovery.

SYSOPER

A special database administration role that permits a database administrator to perform STARTUP, SHUTDOWN, ALTER DATABASE OPEN/MOUNT, ALTER DATABASE BACKUP, ARCHIVE LOG, and RECOVER, and includes the RESTRICTED SESSION privilege.

tnsnames.ora

A file that contains connect descriptors mapped to net service names. The file can be maintained centrally or locally for use by all or individual clients.

transaction identifiers (XIDs)

Identifies the client computer from which a transaction originated.

Index
A  C  D  E  F  G  I  J  M  N  O  P  R  S  T  U  V  W

A

account table

using with Microsoft application demo, 4.1.1

administrator username

dropping, 7.7

Microsoft Transaction Server, 3.3.2

C

Component Object Model (COM)

marking components as transactional, 1.2

programming with Oracle Call Interface and Microsoft Transaction Server, 5.3

registering in a Microsoft Transaction Server environment, 5.2.1

running in a Microsoft Transaction Server

coordinated transaction, 5.2.2

running in an MS DTC-coordinated transaction, 5.2.3 using with Microsoft Oracle ODBC Driver, 5.4.3 using with the Oracle ODBC Driver and Microsoft Transaction Server, 5.4.2 computer on which Microsoft Transaction Server is installed

installation requirements, 2.1.1

computer on which Oracle Database is installed

installation requirements, 2.1.2

configuration requirements

modifying for Oracle Fail Safe, 3.5

on client computer, 3.1

on computer on which Microsoft Transaction Server is installed, 3.1

on computer on which Oracle Database is installed, 3.1

connection

managing connection pooling, 6.2

connection attribute

setting with ODBC, 5.4.1

connection pooling

client side registry parameters, 6.2

emptying connection pools, 7.3

managing connections, 6.2

obtaining service handles, 5.3.3

releasing connections, 5.3.4

using OraMTSSvcGet() function, 5.3.3

CREATE SESSION role, 3.3.2

D

Data Manipulation Language (DML)

using in shared server configurations, 7.4

database job-queue processes

starting, 3.3.1

DBMS_JOBS package, 3.3.2 DBMS_TRANSACTION package, 3.3.2 DCOM. See Distributed Component Object Model (DCOM) demos

Microsoft application, 4.3

Microsoft application demo, 4.1

running the Microsoft application demo, 4.1.3

running the omtssamp.sql script, 4.1.2

using the mtsdemousr username, 4.1.2

using Visual C++, 4.1.3

verifying Microsoft application demo installation, 4.1.3

verifying the Oracle Database includes the proper tables, 4.1.2

differences with OraMTSJoinTxn() function, 5.3.6 Distributed Component Object Model (DCOM), 1.1 distributed transactions

in-doubt, 7.6

support for serializable isolation level, 1.1.1

DML. See Data Manipulation Language (DML)

E

Enterprise Services, 1.1.2

F

FORCE_ANY_TRANSACTION privilege, 3.3.2

G

getting started

with Microsoft Transaction Server and an Oracle Database, 1.3

I

in-doubt transactions

JOB_QUEUE_PROCESSES initialization parameter, 3.3.1

resolving, 3.2

scheduling automatic recovery, 3.3

starting database job-queue processes, 3.3.1

viewing, 3.4

initialization parameters

JOB_QUEUE_PROCESSES, 3.3.1

PROCESSES, 6.4

SESSIONS, 6.4

installation

of Oracle MTS Recovery Service, 2.1

installation requirements

for computer on which Microsoft Transaction Server is installed, 2.1.1

for computer on which Oracle Database is installed, 2.1.2

for computer running Oracle Fail Safe, 2.1

Microsoft Transaction Server, 2.1, 2.1.1.2

Oracle Net Manager, 2.1.1.1

Oracle Net Services for the client, 2.1.1.1

Oracle Net Services for the server, 2.1.2.1

Oracle Objects for OLE, 2.1.1.1

Oracle ODBC Driver, 2.1.1.1

Oracle Services for Microsoft Transaction Server, 2.1.1.1

required RAM, 2.1

Service Pack 5.0 or greater, 2.1.1.2

SQL*Plus, 2.1.1.1, 2.1.2.1

J

JOB_QUEUE_PROCESSES initialization parameter, 3.3.1

M

Microsoft application demo

overview of database contents, 4.1.1

running, 4.1.3

running the omtssamp.sql script, 4.1.2

using Microsoft Developer Studio, 4.1.3

using Oracle Call Interface, 4.1

using Oracle Provider for OLE DB, 4.3

using the mtsdemousr username, 4.1.2

using the Oracle ODBC Driver, 4.2

using Visual C++, 4.1.3

verifying installation, 4.1.3

verifying the Oracle Database includes the proper tables, 4.1.2

Microsoft Developer Studio

using with the Microsoft application demo, 4.1.3

Microsoft Distributed Transaction Coordinator (DTC), 1.1.2 Microsoft Distributed Transaction Coordinator (MS DTC)

COM components running in an MS DTC-coordinated transaction, 5.2.3

in a cluster, 3.5

starting, 6.5

using with Oracle Service for MTS, 2.1

Microsoft Transaction Server

benefits, 1.1

changing the administrator username, 3.3.2

COM components running in a transaction, 5.2.2

components running in an MS DTC-coordinated transaction, 5.2.3

creating the administrator user account, 3.3.2

definition, 1.1

getting started with an Oracle Database, 1.3

installation requirements, 2.1, 2.1.1.2

integration with an Oracle Database, 1.2

programming with Microsoft Oracle ODBC Driver, 5.4

programming with Oracle Call Interface, 5.3

programming with Oracle ODBC Driver, 5.4

registering COM components, 5.2.1

scheduling transaction recovery, 3.3

starting MS DTC, 6.5

upgrade from a previous installation, 2.2

using Oracle Call Interface with the Microsoft application demo, 4.1

using with Microsoft Oracle ODBC Driver, 5.4.3

using with the Oracle ODBC Driver, 5.4.2

Microsoft Transaction Server demos

using Oracle Provider for OLE DB with the Microsoft application demo, 4.3

using the Oracle ODBC Driver with the Microsoft application demo, 4.2

migration. See upgrade mtsdemo username, 4.1.1

using the account and receipt tables, 4.1.1

using with Microsoft application demo, 4.1.1

mtsdemousr username

using the Microsoft application demo, 4.1.2

MTSSamples.dsn file

using with the Oracle ODBC Driver, 5.4.2

mtssys username

changing the password, 3.3.2

default administrator user account, 3.3.2

mtxstop.exe file

running, 7.3

N

net service name

changes that impact connection pool, 7.3

changes that impact connection pooling, 7.3

.NET, Enterprise Services, 1.1.2 nonpooled Oracle Call Interface connection

OraMTSJoinTxn function, 5.3.9

O

OCI_THREADED flag

passing, 5.3

OCIInitialize function

calling, 5.3

ODBC. See Open Database Connectivity (ODBC) omtssamp.sql script, 4.1.2, 5.4.3 Open Database Connectivity (ODBC)

configuring Microsoft Oracle ODBC Driver with Microsoft Transaction Server, 5.4.3

configuring the Oracle ODBC Driver with Microsoft Transaction Server, 5.4.2

Oracle ODBC Driver installation requirements, 2.1.1.1

programming with Microsoft Transaction Server, 5.4

setting the connection attribute, 5.4.1

using Microsoft Oracle ODBC Driver with Microsoft Transaction Server, 5.4.3

using the MTSSamples.dsn file with the Oracle ODBC Driver, 5.4.2

using the Oracle ODBC Driver with Microsoft Transaction Server, 5.4.2

using the Oracle ODBC Driver with the Microsoft application demo, 4.2

using the SQL_ATTR_ENLIST_IN_DTC parameter, 5.4.1

using the SQLSetConnectAttr function, 5.4.1

Oracle Call Interface (OCI), 4.1

enlisting an MS DTC-coordinated transaction, 5.3.5

obtaining pooled or standard Oracle Call Interface connections, 5.3.5

obtaining pooled Oracle Call Interface connections, 5.3.3

OraMTSEnlCtxGet() function, 5.3.7

OraMTSEnlCtxGet() function parameters, 5.3.7

OraMTSJoinTxn() function, 5.3.9

OraMTSOCIErrGet() function, 5.3.11

OraMTSOCIErrGet() function parameters, 5.3.11

OraMTSSvcEnlist() function, 5.3.5

OraMTSSvcEnlist() function parameters, 5.3.5

OraMTSSvcGet() function, 5.3.3

OraMTSSvcGet() function parameters, 5.3.3

OraMTSSvcRel() function, 5.3.4

programming with Microsoft Transaction Server, 5.3

releasing pooled Oracle Call Interface connections, 5.3.4

Oracle Database

changing init.ora file parameter settings, 6.4

integration with Microsoft Transaction server, 1.2

Oracle Fail Safe

installation requirements, 2.1

modifying registry parameters, 3.5

Oracle MTS Recovery Service

installation, 2.1

resolving in-doubt transactions, 3.2

trace file output, 7.1

Oracle Net Manager

installation requirements, 2.1.1.1

Oracle Net Services for the client

installation requirements, 2.1.1.1

Oracle Net Services for the server

installation requirements, 2.1.2.1

Oracle Objects for OLE (OO4O)

installation requirements, 2.1.1.1

Oracle Provider for OLE DB

using with the Microsoft application demo, 4.3

Oracle Service for MTS

deleting, 2.2.1

deleting roles and privileges of inactive users, 2.3

modifying Oracle Service for MTS in the registry, 2.4

preparing to delete, 2.2.1

using with MS DTC, 2.1

Oracle Services for Microsoft Transaction, 3.1 Oracle Services for Microsoft Transaction Server

installation requirements, 2.1.1.1

oramts_2pc_pending

views, 3.3.2.4

ORAMTS_CFLG_ALLDEFAULT flag

description, 5.3.3

ORAMTS_CFLG_SYSDBALOGN flag

description, 5.3.3

using, 5.3.3

ORAMTS_CFLG_SYSOPRLOGN flag

description, 5.3.3

using, 5.3.3

ORAMTS_CONN_POOL_TIMEOUT registry parameter, 6.2.1 ORAMTS_ENFLG_DEFAULT flag

description, 5.3.5, 5.3.6

ORAMTS_NET_CACHE_MAXFREE registry parameter, 6.2.1 ORAMTS_NET_CACHE_TIMEOUT registry parameter, 6.2.1, 6.2.1 ORAMTS_OCI_OBJ_MODE registry parameter, 2.4.1 ORAMTS_ORADB registry parameter, 2.4.1 ORAMTS_OSCREDS_MATCH_LEVEL registry parameter, 6.2.1 ORAMTS_SUNAME registry parameter, 2.4.1 ORAMTS_SUPWD registry parameter, 2.4.1 oramtsadmin.sql script

creating the Microsoft Transaction Server administrator user account, 3.3.2

creating the PL/SQL package, 3.3.2

oramts.dll file

definition, 7.1

OraMTSEnlCtxGet() function

Oracle Call Interface function, 5.3.7

OraMTSEnlCtxRel() function

destroying a previously set up enlistment context, 5.3.8

parameters, 5.3.8

returning ORAMTSERR_NOERROR, 5.3.8

syntax, 5.3.8

ORAMTSERR_ILLEGAL_OPER

returning upon acquiring a connection, 5.3.6

ORAMTSERR_ILLEGAL_OPER returning upon acquiring a connection, 5.3.6 ORAMTSERR_NOERROR

returning upon acquiring a connection, 5.3.5, 5.3.9

returning upon obtaining a connection, 5.3.3

returning upon releasing a connection, 5.3.4

OraMTSJoinTxn() function

enlisting a nonpooled Oracle Call Interface connection, 5.3.9

Oracle Call Interface function, 5.3.9

returning ORAMTSERR_NOERROR upon acquiring a connection, 5.3.9

syntax, 5.3.9

OraMTSOCIErrGet() function

parameters, 5.3.11

retrieving the Oracle Call Interface error code, 5.3.11

syntax, 5.3.11

OraMTSSvcEnlist() function

enlisting pooled or standard Oracle Call Interface connections, 5.3.5

Oracle Call Interface function, 5.3.5

ORAMTS_ENFLG_DEFAULT flag, 5.3.5, 5.3.6

parameters, 5.3.5

restrictions on use, 5.3.5

returning ORAMTSERR_NOERROR upon acquiring a connection, 5.3.5

syntax, 5.3.5

OraMTSSvcEnlistEx() function

restrictions on use, 5.3.6

returning ORAMTSERR_ILLEGAL_OPER upon acquiring a connection, 5.3.6

syntax, 5.3.6

OraMTSSvcGet() function

Oracle Call Interface function, 5.3.3

ORAMTS_CFLG_ALLDEFAULT flag, 5.3.3

ORAMTS_CFLG_SYSDBALOGN flag, 5.3.3

ORAMTS_CFLG_SYSOPRLOGN flag, 5.3.3

overview, 5.3

parameters, 5.3.3

responsibilities, 5.3.3

returning a pooled connection, 5.3.3

returning ORAMTSERR_NOERROR upon acquiring a connection, 5.3.3

syntax, 5.3.3

OraMTSSvcRel() function

Oracle Call Interface function, 5.3.4

overview, 5.3

releasing a pooled connection, 5.3.4

returning ORAMTSERR_NOERROR upon releasing a connection, 5.3.4

syntax, 5.3.4

OraMTSTransTest() function

syntax, 5.3.10

ORAOCI registry parameter

setting, 5.4.3

P

packages

DBMS_JOBS, 3.3.2

DBMS_TRANSACTION, 3.3.2

passwords

changing for mtssys username, 3.3.2

pooled connection

releasing, 5.3.4

privileges

deleting privileges of an inactive Oracle Service for MTS user, 2.3

FORCE_ANY_TRANSACTION, 3.3.2

of administrator user account, 3.3.2

utl_oramts.sql script, 3.3.2

PROCESSES initialization parameter

changing the value, 6.4

programming methods

optimizing to improve performance, 6.1

prvtoramts.plb file, 3.3 public procedures

exposing, 3.3.2

recover_automatic, 3.3.2.2

show_indoubt, 3.3.2.1

utl_oramts.forget_RMs, 3.3.2.3

R

Real Application Clusters, 1.1.2

receipt table

using with Microsoft, 4.1.1

recover_automatic

public procedure, 3.3.2.2

recovery

of in-doubt transactions, 3.2

registry

modifying the Oracle Service for MTS database connection, 2.4

modifying values for Oracle Fail Safe configurations, 3.5

trace file settings, 7.1

registry parameters

modifying for Oracle Fail Safe, 3.5

ORAMTS_CONN_POOL_TIMEOUT, 6.2.1

ORAMTS_NET_CACHE_MAXFREE, 6.2.1

ORAMTS_NET_CACHE_TIMEOUT, 6.2.1, 6.2.1

ORAMTS_OCI_OBJ_MODE, 2.4.1

ORAMTS_ORADB, 2.4.1

ORAMTS_OSCREDS_MATCH_LEVEL, 6.2.1

ORAMTS_SUNAME, 2.4.1

ORAMTS_SUPWD, 2.4.1

revokeuser.sql script

running, 2.3

roles

CREATE SESSION, 3.3.2

deleting roles of an inactive Oracle Service for MTS user, 2.3

of administrator user account, 3.3.2

SELECT_CATALOG_ROLE, 3.3.2

S

samples

Microsoft application demo, 4.1

SELECT_CATALOG_ROLE role, 3.3.2 serializable transactions, 1.1.1 service

deleting, 2.2.1

service handles, 5.3.3 Service Pack 5.0 or greater

correcting Windows NT Explorer crashes, 7.2

installation requirements, 2.1.1.2

SESSIONS initialization parameter

changing the value, 6.4

shared server configurations, 7.4 show_indoubt

public procedure, 3.3.2.1

SQL*Plus

installation requirements, 2.1.1.1, 2.1.2.1

T

three-tiered architecture, 1.1

tnsnames.ora file

ensuring that entries point to the correct database, 2.4

setting for shared server configurations, 7.4

trace files

filename conventions, 7.1

monitoring for successful recovery messages, 2.2.1.4

Oracle MTS Recovery Service, 7.1

oramts.dll, 7.1

registry settings, 7.1

using, 7.1

transaction recovery

JOB_QUEUE_PROCESSES initialization parameter, 3.3.1

Oracle Fail Safe environment, 3.2

overview, 3.2

scheduling, 3.3

starting database job-queue processes, 3.3.1

troubleshooting, 3.4

transactions

ensuring consistency across data resources, 1.1.2

troubleshooting

correcting Oracle Net changes that impact connection pooling, 7.3

correcting Windows Explorer crashes, 7.2

dropping the administrator user account, 7.7

starting MS DTC, 6.5

transaction recovery, 3.4

using trace files, 7.1

tuning

change, 6.4

managing connection pooling, 6.2

two-phase commit protocol, 7.6

U

upgrade

from a previous Oracle Services for Microsoft Transaction Server installation, 2.2

requirements, 2.2

usernames

using with Microsoft application demo, 4.1.3

utl_oramts PL/SQL package

exposing public procedures, 3.3.2

utl_oramts.forget_RMs

public procedure, 3.3.2.3

utl_oramts.sql script, 3.3

privileges and roles granted, 3.3.2

V

vcacct.dll file, 4.1

building, 4.1.3

views

oramts_2pc_pending, 3.3.2.4

Visual C++

using with the Microsoft application demo, 4.1.3

W

Windows 2000

Microsoft application demo not included, 4

Windows Explorer

correcting crashes, 7.2

Oracle Legal Notices

Copyright Notice

Copyright © 1994-2016, Oracle and/or its affiliates. All rights reserved.

License Restrictions Warranty/Consequential Damages Disclaimer

This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.

Warranty Disclaimer

The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.

Restricted Rights Notice

If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, then the following notice is applicable:

U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, delivered to U.S. Government end users are "commercial computer software" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, shall be subject to license terms and license restrictions applicable to the programs. No other rights are granted to the U.S. Government.

Hazardous Applications Notice

This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.

Trademark Notice

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.

Third-Party Content, Products, and Services Disclaimer

This software or hardware and documentation may provide access to or information about content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services unless otherwise set forth in an applicable agreement between you and Oracle. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services, except as set forth in an applicable agreement between you and Oracle.

Alpha and Beta Draft Documentation Notice

If this document is in preproduction status:

This documentation is in preproduction status and is intended for demonstration and preliminary use only. It may not be specific to the hardware on which you are using the software. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to this documentation and will not be responsible for any loss, costs, or damages incurred due to the use of this documentation.

Private Alpha and Beta Draft Documentation Notice

If this document is in private preproduction status:

The information contained in this document is for informational sharing purposes only and should be considered in your capacity as a customer advisory board member or pursuant to your beta trial agreement only. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described in this document remains at the sole discretion of Oracle.

This document in any form, software or printed matter, contains proprietary information that is the exclusive property of Oracle. Your access to and use of this confidential material is subject to the terms and conditions of your Oracle Master Agreement, Oracle License and Services Agreement, Oracle PartnerNetwork Agreement, Oracle distribution agreement, or other license agreement which has been executed by you and Oracle and with which you agree to comply. This document and information contained herein may not be disclosed, copied, reproduced, or distributed to anyone outside Oracle without prior written consent of Oracle. This document is not part of your license agreement nor can it be incorporated into any contractual agreement with Oracle or its subsidiaries or affiliates.

Documentation Accessibility

For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website at http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc.

Access to Oracle Support

Oracle customers that have purchased support have access to electronic support through My Oracle Support. For information, visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info or visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs if you are hearing impaired.