Solved

Remove in-doubt transactions

Posted on 2001-06-11
7
1,866 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 3

Accepted Solution

by:
mathavra earned 25 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

Technology Partners: 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!

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

738 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