Solved

Remove in-doubt transactions

Posted on 2001-06-11
7
1,806 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 25 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 2

Expert Comment

by:banicki
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Force accepted

** Mindphaser - Community Support Moderator **
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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 …
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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…
Via a live example, show how to take different types of Oracle backups using RMAN.

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now