Remove in-doubt transactions

We use Oracle version 7.3.4.
We have 4 in-doubt transactions connected to our database for over a year now. If I do force commit or force rollback, these transactions don't react.
How can I remove these transactions?
nielsvdcAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mathavraCommented:
The following is from Oracle manual:

Oracle8i Distributed Database Systems
Release 2 (8.1.6)
--Chapter 4
 -- In-Doubt transactions topic

Hope this helps.


-----------------------------------------------------------
Manually Overriding In-Doubt Transactions

Use the COMMIT or ROLLBACK statement with the FORCE option and a text string that indicates either the local or global transaction ID of the in-doubt transaction to commit.
 
This section contains the following topics:

Manually Committing an In-Doubt Transaction

Manually Rolling Back an In-Doubt Transaction

Manually Committing an In-Doubt Transaction
Before 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 TRANSACTION
 
 


Committing Using Only the Transaction ID
The following SQL statement is the command to commit 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_ID columns of the DBA_2PC_PENDING data dictionary view.

For example, assume that you query DBA_2PC_PENDING and determine the local transaction ID for a distributed transaction:

LOCAL_TRAN_ID          1.45.13


You 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 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 following 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;

Manually Rolling Back an In-Doubt Transaction
Before attempting to roll back the in-doubt distributed 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 TRANSACTION
 
 


The following SQL statement is the command to roll back an 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_ID 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';


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
banickiCommented:
We have a case where RECO lacks security on our mainframe.
So if the mainframe goes down or the Oracle Gateway to DB2 or our NT Oracle server,  during a distributed transaction, we sometimes get records in our 2pc_pending tables.  Because RECOs not working, we have to delete them by hand and then chase the individual transaction(database integrity) manually.    
terry
0
banickiCommented:
delete from dba_2pc_pending where state = 'collecting';
or whatever state your in...
Terry

P.S. What state are the in?  and has RECO been eating a lot of CPU for the last year?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

banickiCommented:
Warning:  Deleting from PC2_Pending table will loss data integrity between the databases.  
But than, you have been running that way for a year now anyway.
0
johnnyceCommented:
Hi, neilsvdc,

The following has helped me -- it is an extract from a document I found in Oracle Metalink:

=======================================================

Before you begin, make note of the local transaction ID, <local_tran_id>, from any error message reported.  You can also look for this in the dba_2pc_pending view (e.g. as SYSTEM user).

1. Determine if you want to attempt a commit or rollback of this transaction. You can do the following select to help determine what action to take:

SQL> select state, advice from dba_2pc_pending where local_tran_id = '<local_tran_id>';

2. Commit or rollback the transaction.

To commit:

SQL> commit force '<local_tran_id>';

To rollback:
SQL> rollback force '<local_tran_id>';

3. If your are using release 7.3.x or greater and the previous step fails, execute the following command in either Server Manager or SQL*Plus:

SQL> execute dbms_transaction.purge_lost_db_entry('<local_tran_id>');

NOTE:     The purge_lost_db_entry function is fully documented in the "dbmsutil.sql" script located in the "$ORACLE_HOME/rdbms/admin" directory.

4. If running a release below 7.3 -OR- both previous steps have failed, do the following:

Connect to Server Manager or SQL*Plus (as SYSTEM) and execute the following commands:

SQL> set transaction use rollback segment system;
SQL> delete from dba_2pc_pending where local_tran_id = '<local_tran_id>';
SQL> delete from pending_sessions$ where local_tran_id = '<local_tran_id>';
SQL> delete from pending_sub_sessions$ where local_tran_id = '<local_tran_id>';
SQL> commit;

=======================================================

Good luck!
0
MindphaserCommented:
Please update and finalize this old, open question. Please:

1) Award points ... if you need Moderator assistance to split points, comment here with details please or advise us in Community Support with a zero point question and this question link.
2) Ask us to delete it if it has no value to you or others
3) Ask for a refund so that we can move it to our PAQ at zero points if it did not help you but may help others.

EXPERT INPUT WITH CLOSING RECOMMENDATIONS IS APPRECIATED IF ASKER DOES NOT RESPOND.

Thanks,

** Mindphaser - Community Support Moderator **

P.S.  Click your Member Profile, choose View Question History to go through all your open and locked questions to update them.
0
MindphaserCommented:
Force accepted

** Mindphaser - Community Support Moderator **
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.