I am working as a SQL developer. I have a setup where I use MS SQL Server with a linked Oracle server. Our database has views on the Oracle database. I have in-house written software (I'm the lead developer) which uses this setup.
I've had this setup for 5 years and it's working fine in production, on a beta server and all our development boxes, except mine.
I recently rebuilt my machine using Windows 10 (I'm the first using Windows 10 for this). I'm using the Oracle Database 12c Release 1 (12.1.0.2.0) client (we previously used 11g, but Windows 10 appears to require 12c).
When I try to perform an update against a view on the Oracle server, within a transaction, it fails. This works fine on any of our other servers, so I'm sure it's either a Windows 10 issue, Oracle 12c client issue, or I've somehow messed up the setup.
I've done all the setups in the past (production, beta, and all developer machine setups), so I have quite a bit of experience doing this over the years. The problem is only with a transaction. Non-transactional queries and updates work just fine. Here's an example that will fail.
begin tran
SET XACT_ABORT ON;
update CLIENT set SUPERVISOR_FLAG = 'Y' where CLIENT_CODE = 'XYZ123'
commit tran
The error I get is:
OLE DB provider "OraOLEDB.Oracle" for linked server "REMOTESERVER" returned message "Unable to enlist in the transaction.".
Msg 7391, Level 16, State 2, Line 2
The operation could not be performed because of OLE DB provider "OraOLEDB.Oracle" for linked server "REMOTESERVER" was unable to begin a distributed transaction.
Multiple SQL Servers link to the same back-end Oracle server and they have no problem with this, so I can rule out a configuration issue on the Oracle server.
These are my DTC settings and these are the same as other machines that are working:
For my Oracle Provider for Ole DB (OraOLEDB.Oracle) settings, I have "Dynamic parameters", "Nested queries", "Allow inprocess" and "Supports 'Like' operator" enabled. This is also the same as other machines.
Under Server Options the settings are:
- Collation Compatible - False
- Data Access - True
- RPC- True
- RPC Out - True
- Use Remote Collation - True
- Collation name - [blank]
- Connection Timeout - 0
- Query Timeout - 0
- Distributor - False
- Publisher - False
- Subscriber - False
- Lazy Schema Validation - False
- Enable Promotion of Distributed Transactions - True
Again, these match the settings on other working servers.
I'm stumped. I'd really appreciate any help anyone can offer on this.
Update:
Not sure if this helps, but this is the DTC trace:
pid=10580 ;tid=10624 ;time=07/19/2018-13:02:06.767 ;seq=1 ;eventid=TRANSACTION_MANAGER_STARTED_2 ;;"TM Identifier='(null) '" ;"MS DTC started with the following settings: Security Configuration (OFF = 0 and ON = 1): Network Administration of Transactions = 1, Network Clients = 1, Inbound Transactions = 1, Outbound Transactions = 1, Transaction Internet Protocol (TIP) = 0, XA Transactions = 1, MSDTC RPC Security = Mutual Authentication Required, Account = NT AUTHORITY\NetworkService, Firewall Exclusion = 0, Transaction Bridge Installed = 0, Filtering duplicate events = 1."
pid=10580 ;tid=10624 ;time=07/19/2018-13:02:06.767 ;seq=2 ;eventid=TRACE_SETTINGS ;;"TM Identifier='(null) '" ;"Trace Configuration (OFF = 0 and ON = 1): Tracing Of DTC = 1, Tracing Of Transactions = 1, Tracing Of Aborted Transactions = 1, Tracing Of Long-Lived Transactions = 1, Tracing Of All Transactions = 0, Max Limit on Memory Buffers = 0."
pid=10580 ;tid=17072 ;time=07/19/2018-13:02:35.182 ;seq=3 ;eventid=TRANSACTION_BEGUN ;tx_guid=98d30080-f9d4-46cb-a35c-af47629b377c ;"TM Identifier='(null) '" ;"transaction has begun, description :'user_transaction'"
pid=10580 ;tid=17072 ;time=07/19/2018-13:02:35.182 ;seq=4 ;eventid=RM_ENLISTED_IN_TRANSACTION ;tx_guid=98d30080-f9d4-46cb-a35c-af47629b377c ;"TM Identifier='(null) '" ;"resource manager #1003 enlisted as transaction enlistment #1. RM guid = '452dc8f8-8bbd-4da4-b3e2-e1e29d417a15'"
pid=10580 ;tid=17072 ;time=07/19/2018-13:02:36.104 ;seq=5 ;eventid=RECEIVED_ABORT_REQUEST_FROM_BEGINNER ;tx_guid=98d30080-f9d4-46cb-a35c-af47629b377c ;"TM Identifier='(null) '" ;"received request to abort the transaction from beginner"
pid=10580 ;tid=17072 ;time=07/19/2018-13:02:36.104 ;seq=6 ;eventid=TRANSACTION_ABORTING ;tx_guid=98d30080-f9d4-46cb-a35c-af47629b377c ;"TM Identifier='(null) '" ;"transaction is aborting"
pid=10580 ;tid=17072 ;time=07/19/2018-13:02:36.104 ;seq=7 ;eventid=RM_ISSUED_ABORT ;tx_guid=98d30080-f9d4-46cb-a35c-af47629b377c ;"TM Identifier='(null) '" ;"abort request issued to resource manager #1003 for transaction enlistment #1"
pid=10580 ;tid=17072 ;time=07/19/2018-13:02:36.104 ;seq=8 ;eventid=RM_ACKNOWLEDGED_ABORT ;tx_guid=98d30080-f9d4-46cb-a35c-af47629b377c ;"TM Identifier='(null) '" ;"received acknowledgement of abort request from the resource manager #1003 for transaction enlistment #1"
pid=10580 ;tid=17072 ;time=07/19/2018-13:02:36.104 ;seq=9 ;eventid=TRANSACTION_ABORTED ;tx_guid=98d30080-f9d4-46cb-a35c-af47629b377c ;"TM Identifier='(null) '" ;"transaction has been aborted"
pid=10580 ;tid=18812 ;time=07/19/2018-13:02:57.188 ;seq=10 ;eventid=CHECKPOINTING_STOPPED ;;"TM Identifier='(null) '" ;"MSDTC is suspending the checkpointing of transactions due to lack of activity"
pid=10580 ;tid=18812 ;time=07/19/2018-13:02:57.188 ;seq=11 ;eventid=TRACING_STOPPED ;;"TM Identifier='(null) '" ;"MSDTC is suspending the tracing of long - lived transactions due to lack of activity"
pid=10580 ;tid=17072 ;time=07/19/2018-13:04:46.678 ;seq=12 ;eventid=TRACING_STARTED ;;"TM Identifier='(null) '" ;"MSDTC is resuming the tracing of long - lived transactions"
Thanks & Regards
Camillelola