Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Remove in-doubt transactions

Posted on 2001-06-11
7
Medium Priority
?
1,946 Views
Last Modified: 2007-11-27
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?
0
Comment
Question by:nielsvdc
7 Comments
 
LVL 3

Accepted Solution

by:
mathavra earned 100 total points
ID: 6178302
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
 
LVL 2

Expert Comment

by:banicki
ID: 6179015
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
 
LVL 2

Expert Comment

by:banicki
ID: 6179025
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Expert Comment

by:banicki
ID: 6179075
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
 

Expert Comment

by:johnnyce
ID: 6183956
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
 
LVL 6

Expert Comment

by:Mindphaser
ID: 7036035
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
 
LVL 6

Expert Comment

by:Mindphaser
ID: 7043430
Force accepted

** Mindphaser - Community Support Moderator **
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question