ORA-01591 Fault Handling

In the morning to the office to listen to colleagues said the table was locked, a try, found that a table in a field of 1111111 of the row are locked, select None. Report Error ORA-01591, open toad knowledge EXpert, described very little, just say because of the distributed transaction error caused by the lock. Ask your colleague, yesterday a stored procedure called another stored procedure was an error, and the latter inserts some data into the SQL Server database through a transparent gateway.
Immediately thought of opening the OEM, who knew the disappointment, entered the lock, and did not find that the relevant object was locked, and began a little depressed. Instead of checking the session, the user has 5 sessions, all inactive, no matter 3,721, all killed. The result remains the same, and the lock does not appear. Remote landing on the host, found that the CPU and process are normal, and did not find a transparent gateway process hanging dead (previously found tg4sql in the absence of business volume will also appear about 25% of the CPU, hang dead).
Suddenly thought to see Alert.log, after careful search, finally found:

Wed Nov 17 00:00:04 2004
Errors in file d:\oracle\admin\xdcj\udump\xdcj_j006_3020.trc:
ORA-12012: Auto-Perform job 82 error
ORA-01591: Lock has been handled by a problematic assignment transaction 6.5.887985 pending
ORA-06512: On line 6

This is where the mistake goes, back:

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:6.5.887985 problem with transaction processing
ORA-28500: Returns this information when connecting Oracle to a non-ORACLE system:
[Transparent gateway for MSSQL]
ORA-02063: Shortly thereafter 2 lines (from ZSMOS_CRM)
Tue Nov 16 17:35:04 2004
Distrib 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 2004
Errors 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 ' login failed. (SQL state:28000; SQL code:18456)
Ora-02063:preceding 2 lines from ZSMOS_CRM

Tue Nov 16 17:35:12 2004
Errors 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 ' login failed. (SQL state:28000; SQL code:18456)
Ora-02063:preceding 2 lines from ZSMOS_CRM

This is the location of the incident. It looks like yesterday afternoon the remote transaction failed, but no return caused the distributed transaction to die, locking the row. Finally found a detailed error ORA-02054, into Toad a check, said to wait or submit the transaction, but how to operate it. or open the official document search related content, in Adminstrator Guide found the following:
Discovering Problems with a two-phase Commit
The user application that commits a distributed transaction are informed of a problem by one of the following error message S

Ora-02050:transaction ID rolled back,
Some remote DBS may IN-DOUBT
Ora-02051:transaction ID Committed,
Some remote DBS may IN-DOUBT
Ora-02054:transaction ID In-doubt


A robust application should save information about a transaction if it receives any of the above. This information can to used later if manual distributed transaction recovery is desired.

No action is required by the administrator of any node so has one or more in-doubt distributed transactions due to a net Work or system failure. The automatic recovery features of Oracle transparently complete any in-doubt transaction so this same outcome On "All nodes of" a session tree (which is, all commits or all roll back) after the network or system failure is resolved.

In extended outages, however, 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 Override
Override a specific IN-DOUBT transaction manually only one of the following situations:

The IN-DOUBT transaction locks the data is required by other transactions. This situation occurs is the ORA-01591 error message interferes with user transactions.
A IN-DOUBT transaction prevents the extents of a rollback segment from being to other used. The portion of a in-doubt distributed transaction ' s local transaction ID corresponds to the ID of the rollback SEGM ENT, as listed by the data dictionary views dba_2pc_pending and Dba_rollback_segs.
The failure preventing the two-phase commit phases to complete cannot is corrected in a acceptable time period. Examples of such cases include a telecommunication network that has been damaged or a damaged database that requires a Lon G recovery time.
Normally, you should make a decision to locally force a IN-DOUBT distributed transaction into consultation with Administrat ORS at the other locations. A wrong decision can leads to the database inconsistencies that can is difficult to trace and so you must manually correct.

If the conditions above does not apply, always allow the automatic recovery the features of Oracle to complete the transaction. If the above criteria are met, however, consider a local override of the IN-DOUBT transaction.

It looks like a suggestion, and Oracle always tries to log in to SQL Server to recover automatically, but it's not always successful. Look at the view dba_2pc_pending did find traces of the transaction. How do we do that?

Manually committing an in-doubt Transaction
Before attempting to commit the transaction, ensure this you have the proper privileges. Note the following requirements:

If the transaction is committed by ... Then You must have this privilege ...
You
FORCE TRANSACTION

Another User
FORCE any TRANSACTION


Committing Using only the Transaction ID
The following SQL statement commits an IN-DOUBT transaction:

COMMIT FORCE ' transaction_id ';


The variable transaction_id is the identifier of the transaction as specified in either the local_tran_id or Global_tran_i D columns of the dba_2pc_pending data dictionary view.

For example, assume so you query dba_2pc_pending and determine this local_tran_id for a distributed transaction is 1:45. 13.

You are then issue the following SQL statement to force the commit of this IN-DOUBT transaction:

COMMIT FORCE ' 1.45.13 ';

Committing Using an SCN
Optionally, you can specify the "SCN for" transaction when forcing a transaction to commit. This is feature allows you to commit a IN-DOUBT transaction with the SCN assigned when it is committed at other nodes.

Consequently, you maintain the synchronized commit to the distributed transaction even if there is a failure. Specify an SCN can be determine the SCN of the same transaction already committed at another node.

For example, assume your want to manually commit a transaction with the following global transaction ID:

sales.acme.com.55d1c563.1.93.29

The dba_2pc_pending view of a remote database also involved with the transaction in question. The SCN used for the commit of the "Transaction at" node. Specify the SCN when committing the transaction in the local node. For example, if the SCN is 829381993, issue:

COMMIT FORCE ' sales.acme.com.55d1c563.1.93.29 ', 829381993;

Also:
Oracle9i SQL Reference For more information about using the COMMIT statement


Manually rolling back a in-doubt Transaction
Before attempting to roll back the IN-DOUBT distributed transaction, ensure, have the proper privileges. Note the following requirements:

If the transaction is committed by ... Then You must have this privilege ...
You
FORCE TRANSACTION

Another User
FORCE any TRANSACTION


The following SQL statement rolls back a IN-DOUBT transaction:

ROLLBACK FORCE ' transaction_id ';


The variable transaction_id is the identifier of the transaction as specified in either the local_tran_id or Global_tran_i D columns of the dba_2pc_pending data dictionary view.

For example, to roll back the IN-DOUBT transaction with the local transaction ID of 2.9.4, use the following statement:

ROLLBACK FORCE ' 2.9.4 ';

So log in to the database
COMMIT FORCE ' 6.5.887985 ';
Then view the dba_2pc_pending discovery status has been changed to ' COMMIT FORCE ', select the table related rows, all normal. At this point, the problem is resolved.
Overall, direct insert ... Tablename@sqldblk is still very dangerous, in the event of a normal return on the problem. Oracle's documentation is recommended for use with packages or stored procedures, and it is recommended that colleagues switch to this method, which is now being tested.