Solved

Remove in-doubt transactions

Posted on 2001-06-11
7
1,875 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

Industry Leaders: 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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

729 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