How to troubleshoot MSDTC transaction failure issue with linked Oracle Server on Win2003
In enterprise environments, customers sometimes need to set MSDTC transaction to work with remote Oracle target server. Because the cross-product setup is complicated, sometimes we may face Transaction Failure issue when updating data to a linked Oracle database, for example:
Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8000d048].
Below are general steps to help us narrow down and fix similar issues (before starting this, try general select statements from SQL to Oracle first, ensure the server name resolution and network between them are Okay):
1. On the Windows Server 2003, check the “Add/Remove Program” console, “Enable network dtc access” in Windows Components must be enabled. Enable it under Windows Components\Application Server\Enable Network dtc access. Refer to:
817064 How to enable network DTC access in Windows Server 2003
https://support.microsoft.com/default.aspx?scid=kb;EN-US;817064
2. Open DCOMCNFG, ensure the MSDTC settings are correct in the Security tab:
3. Open Registry table, in the Security key, ensure the Registry values matches the UI settings:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\Security
4. The Oracle modules configuration is correct in registry table:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI
OracleXaLib=oraclient9.dll
OracleSqlLib=orasql9.dll
OracleOciLib=oci.dll
Although here we use Oracle 9i client as the sample, we should know from Microsoft Data Access Components (MDAC) version 2.5 and later versions, both the Microsoft ODBC Driver and OLE DB Provider support ONLY Oracle 7 and Oracle 8i: https://support.microsoft.com/Default.aspx?id=244661
A new Microsoft .NET Managed Oracle Provider that supports the Oracle 9i client is now available. To download this provider, visit the following Microsoft Web site:https://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=4F55D429-17DC-45EA-BFB3-076D1C052524 (https://www.microsoft.com/downloads/details.aspx?displaylang=en\&FamilyID=4F55D429-17DC-45EA-BFB3-076D1C052524)
For other versions Oracle client (7/8) registry key settings, refer to:
193893 Information about using Oracle with Microsoft Transaction Server and COM+ components
https://support.microsoft.com/default.aspx?scid=kb;EN-US;193893
5. Find the service name of the linked Oracle server from SQL management console, if you are not sure how to find the Oracle service name, please check with local Oracle admin team.
6. Create an ODBC data source name (DSN) that refers to your Oracle database. Make sure that your DSN uses the Microsoft Oracle ODBC driver.
a. Open Data Source dialog box from Administration Tools:
b. Click the Add button in the User DSN tab, choose Microsoft ODBC for Oracle, and then complete the Wizard:
Run this command, we should get the correct result, which means pure Oracle transaction working fine:
Msdtcora.exe –U<username to oracle> -P<password to oracle> -S<service name we found at step 5>
The Msdtcora.exe can be got from:
https://download.microsoft.com/download/transaction/Utility/1/NT5/EN-US/DTCOra.exe
This tool will skip MSDTC and use Oracle Transaction directly. If the Oracle Test program can connect to your Oracle database server without error, it is very likely that COM+/MTS will also work with Oracle. If this step failed, should contact Oracle Support for more information.
7. Make sure Network Service account (this is new account for MSDTC in Win2003) has been added on c:\program files\oracle and c:\oracle (if you have other Oracle home folder, should do the same), force all sub objects inherit the same settings from parent folders. Otherwise the MSDTC service may fail to talk with Oracle libraries.
8. You must install Oracle Services for Microsoft Transaction Server (OraMTS) from Oracle Client Installation package. After this, an Oracle MTS Recovery Service is also automatically installed on the same computer.
If the transaction to Oracle still failed even step 6 returns successful information by Msdtcora, we should check if the pure MSDTC service working fine. You may raise MSDTC distributed transaction to other linked SQL server (the link SQL server should also follow steps 1,2,3) to see how things are going. Contact MSDTC support team to get further troubleshooting ideas if any difficulties happen.
More Information
==============
280106 How to set up and troubleshoot a linked server to an Oracle database in SQL Server
https://support.microsoft.com/default.aspx?scid=kb;EN-US;280106
Regards,
Freist Li