Oracle Transactions Support with MS DTC

  • Article

Microsoft Distributed Transaction Coordinator (MS DTC) can be used to manage Oracle transactions in the following common scenarios: SQL/Oracle linked server, COM+ transactional components or the System.Transactions transaction using Oracle as a data store.

The Microsoft ODBC driver and OLEDB provider for Oracle are built by using Oracle Call Interface (OCI) version 7 and provide full support for Oracle 7. Oracle no longer supports applications that use OCI version 7 calls for Oracle 8 and above. These technologies are deprecated. If you are using Oracle data sources, you should migrate to Oracle-supplied driver and provider (see https://msdn.microsoft.com/en-us/library/ms810810.aspx).

When using the Oracle ODBC driver or OLEDB provider, some people follow the instructions in this MSDN article: How To Configure MSDTC to Support Oracle Transactions

It is incorrect. In fact, this article only applies to the Microsoft ODBC driver or OLEDB provider. The MDAC components that link asks you to install will actually install the Microsoft Oracle OLEDB provider. It is a different programming model when using the Oracle OLEDB provider to interface between MSDTC and Oracle. You don’t need to follow any such guidelines in the article when using the Oracle provider. You don’t need to enable XA transactions in MSDTC. They are only needed when using the Microsoft ODBC driver or OLEDB provider. You also do not need to set the Oci, Xa or Sql dlls since you aren’t programming to those models.

Instead, you will need to follow any Oracle documentation on the client library installation and configuration. You will need to install the Oracle Services for Microsoft Transaction Server, which is part of the Oracle Client setup. This component is the interface between DTC and Oracle.

One thing to note in Oracle documentation: MTS == Microsoft Transaction Server == DTC.

When using the Oracle driver or provider, DTC sees OLE protocol transactions. When using the Microsoft driver/provider, DTC sees XA protocol transactions.

There is an independent document that explains how to use the Oracle OLEDB providers with DTC: https://www.devx.com/vb2themax/Article/19860

In general, customers should follow Oracle guidelines and contact Oracle support if Oracle transaction is not working. They can try Process Monitor to catch any Oracle file access problems which might show what components are missing during Oracle client installation.