In the morning, I listened to my colleagues and said that I was locked. I found that a group of 1111111 in the table was locked. SELECT can't. Report an error ORA-01591, open toad's Knowledge Expert, which is rare, but it is only caused by distributed transaction errors. Ask a colleague, call another stored procedure yesterday, and the latter has some data to the SQL Server database through the transparent gateway INSERT. Immediately think of opening OEM, who knows that the loss is expected, enter the lock, did not find the relevant objects to be locked, start a bit depressed. Turning and checking the session, the user has 5 sessions, all inactive, no matter what three seven twenty one, all killed. The result is still, and the lock has not appeared. Remote login on the host, found that the CPU and the process are normal, nor did it find that the transparent gateway process is mounted (before, TG4SQL will also have a 25% CPU, hang). Suddenly think of watching Alert.log, after careful search, finally discovered:
WED NOV 17 00:00:04 2004ERRORS IN FILE D: /Oracle/admin/xdcj/udump/XDCJ_J006_3020.TRC: ORA-12012: Automated Job 82 Error ORA-01591: Lock has been issued allocation transaction 6.5. 887985 hangs ORA-06512: in line 6
This is the place where it is wrong, trace it forward:
Tue NOV 16 17:35:04 2004 error 28500 trapped in 2pc on transaction 6.5.887985. Cleaning Up.Error Stack Returned to User: ORA-02054: Transaction 6.5.887985 There is a problem ORA-28500: Connect Oracle to non-Oracle system Returns this information: [Transparent Gateway for MSSQL] ORA-02063: Tight 2 Lines (Zsmos_CRM) Tue Nov 16 17:35:04 2004Distrib TRAN QDCJ.US.Oracle.com.5ae32328.6.5.887985 Is Local TRAN 6.5 .887985 (hex = 06.05.d8cb1) insert pending prepared tran, scn = 6606197672830 (hex = 602.2010cb7e) Tue Nov 16 17:35:07 2004Errors in file d: /oracle/admin/xdcj/bdump/xdcj_reco_3024.trc: ORA -28500: Connection from Oracle To a Non-Oracle System Returned This Message: [Transparent Gateway for MSSQL] [Microsoft] [ODBC SQL Server Driver] [SQL Server] User 'Recover' Failed. (SQL State: 28000; SQL Code: 18456) ORA-02063: Preceding 2 LINES from ZSMOS_CRM
Tue Nov 16 17:35:12 2004erro/xdcj/BDUMP/XDCJ_RECO_3024.TRC: ORA-28500: Connection from Oracle To a Non-Oracle System Returned this Message: [Transparent Gateway for MSSQL] [ Microsoft] [ODBC SQL Server Driver] [SQL Server] User 'Recover' failed. (SQL State: 28000; SQL Code: 18456) ORA-02063: Preceding 2 LINES from Zsmos_CRM This is the incident place. It seems that it failed yesterday afternoon, but did not return to a distributed business hang, thereby locked it. Finally found detailed error ORA-02054, entered the Toad, saying to wait or submit the transaction, how to do it. Or open official document search related content found in Adminstrator Guide in the following: Discovering Problems with a Two-Phase CommitThe user application that commits a distributed transaction is informed of a problem by one of the following error messages:
ORA-02050: Transaction ID Rolled Back, Some Remote DBS May Be in-DoubTora-02051: Transaction ID Committed, Some Remote DBS May Be in-DoubTora-02054: Transaction ID in-Doubt
.
No action is required by the administrator of any node that has one or more in-doubt distributed transactions due to a network or system failure. The automatic recovery features of Oracle transparently complete any in-doubt transaction so that the same outcome occurs on all nodes Of a session tree (That is, all commit or all roll back) after the network or system failure is resolved.
In Extended Outages, HoWever, You Can Force The Commit Or Rollback of a Transaction To Release Any Locked Data. Applications Must Account for Such Possibilities.
Determining Whether to Perform a Manual OverrideOverride a specific in-doubt transaction manually only when one of the following situations exists:. The in-doubt transaction locks data that is required by other transactions This situation occurs when the ORA-01591 error message interferes with user transactions. An in-doubt transaction prevents the extents of a rollback segment from being used by other transactions. The first portion of an in-doubt distributed transaction's local transaction ID corresponds to the ID of the rollback segment, as listed by the data dictionary views DBA_2PC_PENDING and DBA_ROLLBACK_SEGS. The failure preventing the two-phase commit phases to complete can not be corrected in an acceptable time period. Examples of such cases include a telecommunication network that has been damaged or a damaged database that requires a long recovery time. Normally, you SHOULD MAKE A DECISION TO LOCALLY Force An in-Doubt Distributed Transaction In Construction With Administrator .
If the conditions above do not apply, always allow the automatic recovery features of Oracle to complete the transaction. If any of the above criteria are met, however, consider a local override of the in-doubt transaction.
It seems that the suggestion is almost, and the back Oracle always tries to log in to SQL Server is to automatically recover, but it is not successful. The view DBA_2PC_PENDING does find the trace of the transaction. How do you do it?
Manually committing an in-doubt TransactionBefore attempting to commit the transaction, EnSure That You Have The Proper Privileges. Note The Following Requirements:
If the transaction was committed by ... Then you must have this privilege ... You FORCE TRANSACTION Another user FORCE ANY TRANSACTIONCommitting Using Only the Transaction IDThe following SQL statement commits an in-doubt transaction:
Commit force 'Transaction_ID';
.,,,,,,,,,,,,,,
For example, Assume That You Query DBA_2PC_PENDING AND DETERMINE THATE LOCAL_TRAN_ID FOR A Distributed Transaction IS 1: 45.13.
You damUe the Following Sql Statement to Force the commit of this in-Doubt Transaction:
Commit force '1.45.13';
Committing Using an SCNOptionally, you can specify the SCN for the transaction when forcing a transaction to commit. This feature allows you to commit an in-doubt transaction with the SCN assigned when it was committed at other nodes.
Consequently, you maintain the synchronized commit time of the distributed transaction even if there is a failure. Specify an SCN only when you can determine the SCN of the same transaction already committed at another node.
For Example, Assume You want to manually commit a Transaction with the folowing global transaction ID:
Sales.acme.com.55d1c563.1.93.29
First, query the DBA_2PC_PENDING view of a remote database also involved with the transaction in question. Note the SCN used for the commit of the transaction at that node. Specify the SCN when committing the transaction at the local node. For example, if the SCN IS 829381993, ISSUE:
Commit force 'sales.acme.com.55d1c563.1.93.29', 829381993;
See Also: Oracle9i SQL Reference for More Information About Using The Commit Statement
Manually Rolling Back an In-Doubt TransactionBefore attempting to roll back the in-doubt distributed transaction, ensure that you have the proper privileges Note the following requirements:. The transaction was committed by ... Then you must have this privilege If ... You force Transaction Another User Force Any Transaction
The Following SQL Statement Rolls Back An in-Doubt Transaction:
Rollback force 'Transaction_ID';
.,,,,,,,,,,,,,,
For Example, To Roll Back the in-doubt Transaction with the local transaction ID of 2.9.4, Use the Following Statement: 0000-00-00.- DOUBT THE
Rollback force '2.9.4';
So the database commit force '6.5.887985'; then view the DBA_2PC_PENDING discovery state has changed to 'commit force', select the table related rows, everything is normal. At this point, the fault is resolved. Overall, directly insert ... TableName @ Sqldblk is still very dangerous, and if you can't return it, you will have problems. Oracle's documentation is recommended to use a package or stored procedure to solve it. Subsequent colleagues to use this method, it has been tested.
