How to troubleshoot msdtc transaction failure issue with linked Oracle server on Windows 2008
We introduced how to make MSDTC transaction function work with linked Oracle server on Windows 2003 before. For Windows 2008/Windows 7, to make the settings work, we need to consider more factors, I'd like to share as below:
a. Install Application Server Role on Windows 2008, for details please check:
b. Install Oracle Services for Microsoft Transaction Server (OraMTS) from Oracle Client Installation package. Restart Win2008.
c. Follow this blog to make sure required registry keys in [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI] are correct:
If this is Oracle client 11g, the settings will be:
d. Open DCOMCNFG, ensure the MSDTC settings are correct in the Security tab:
Open Registry table, in the Security key, ensure the Registry values matches the UI settings:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\Security
* Again, 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
You can consider using Oracle client Provider directly.
e. 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.
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 e>
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.
* This tool only has 32bit version. If your Oracle client is 64bit, we cannot use the Msdtcora.exe. Please skip this step.
f. Some important points to check:
* The Oracle home client folder on the linked server must give "Network Service" account enough permission to read&execute
* Oracle home client 10g (as I know, before 10.2.0.4) has known issues to work with MSDTC on some Windows System, such as Win2008/Win7. Please install Oracle Client 11g R2+ OraMTS on the linked server. I have experienced this in test environment and another customer environment. Some discussion thread from internet for your reference:
https://kr.forums.oracle.com/forums/thread.jspa?threadID=723002
g. Please use the ISOLATION LEVEL that Oracle server recognized, for example, should be "Read Committed" Instead of "Read Uncommitted". (Oracle Transaction Level supports Read Committed, Serializable, and Read-Only). Otherwise, this error will occur:
"Neither the isolation level nor a strengthening of it is supported.".
Regards,
Freist from APGC DSI Team