Give me a scenario where I will have in doubt transaction and which I have to rollback dynamically.

I have never encountered this kind of a situation , but believe this is very useful.

Kindly illustrate with a scenario

Tom,

Can i force a commit in another user session if its not a distributed database (local database).

Thanks..

Thanks Tom

Rollback segment status is 'Partialy Active' even after restarting database. I think there are some pending transactions in the rollback segment. What should I do to do rollback the transaction?

Tom,
We have a heterogeneous system with one Oracle and one non-Oracle database. A third party TPM initiates the 2pc transactions. Once the transactions enter the prepare phase something happens and the TPM can neither commit or rollback. We are investigating this issue. The problem is that on the Oracle side, we observe that till we manually clear this in-doubt transaction, all the tables, even the ones which do not participate in 2pc transaction, are locked for reads. Is this expected behaviour? The administrator's guide says as:
=======================================================
Prepare Phase
The first phase in committing a distributed transaction is the prepare phase. In this phase, Oracle does not actually commit or roll back the transaction. Instead, all nodes referenced in a distributed transaction (except the commit point site, described in the "Commit Point Site") are told to prepare to commit. By preparing, a node:

Records information in the online redo logs so that it can subsequently either commit or roll back the transaction, regardless of intervening failures

Places a distributed lock on modified tables, which prevents reads
========================================================
The last line says that only the modified tables are locked. I have read lot of articles on Metalink, but cannot understand what is going on.
Thanks.

Tom,
Please ignore my post. It was a typical case of communication distortion when going through a noisy channel.
Thanks.

I have Cloned a database from 1 server to another changing the dB name on the new instance. I notice I have in-doubt transactions now on the new instance. How can I kill them?

WHen I try to commit or rollback I get a ORA-2058 error

Tom,
in a project I'm working right now we need to perform distributed transactions. It seems that this can be done either via some middle-tier framework, which can manage distributed transactions on its own, or via database links, which is solution that I would prefer for various reasons (ability to manipulate data in pure SQL being the most important one to me). The database links would be between different 10.2.0.3 Oracle databases and in one case between Oracle DB and MS SQL Server. (SQL Server would be the remote DB)

Customer's DBAs argue against database links with two principal arguments:

a) database links can cause unresolved in-doubt transactions in case the connection between databases gets broken,

b) when using database links, username and password travel in plain text over the network.

Could you please tell me whether the following is true:

1) if the connection between middle-tier framework and one (or both) of the databases gets broken in the middle of the two-phase commit, the situation is basically the same as if the database link gets broken - that is, results in hanging in-doubt transaction,

2) when I modify data over DB link in the remote database only in single transaction (eg. exporting data to other DB), this will not result in distributed transaction,

3) is it really impossible to encrypt the password when initiating the DB link (the customer uses Enterprise edition).

If you would have any other comments on this situation, I'd be grateful.

"when using database links, username and password travel in plain text over the network."

I would bet anything that usernames and passwords travel over the network the same way, whether the client is a middle tier or another database. Why would any database supplier build specific login software just for database links?

The results from two queries are as follows:
SQL> exec p ( ' select * from dba_2pc_pending where LOCAL_TRAN_ID = ''5.45.15590''');

LOCAL_TRAN_ID :5.45.15590
GLOBAL_TRAN_ID :PROD.WORLD.355ec77f.5.45.15590
STATE :commited
MIXED :no
ADVICE :
TRAN_COMMENT :
FAIL_TIME :2/3/2011 11:41:24 AM
FORCE_TIME :
RETRY_TIME :2/3/2011 12:43:58 PM
OS_USER :system
OS_TERMINAL :location01
HOST :location01
DB_USER :LOC01
COMMIT# :1995534655

PL/SQL procedure successfully completed.

SQL>
SQL> exec p ( ' select * from sys.pending_trans$ where LOCAL_TRAN_ID = ''5.45.15590''');

LOCAL_TRAN_ID :5.45.15590
GLOBAL_TRAN_FMT :306206
GLOBAL_ORACLE_ID :PROD.WORLD.355ec77f.5.45.15590
GLOBAL_FOREIGN_ID :
TRAN_COMMENT :
STATE :commited
STATUS :P
HEURISTIC_DFLT :
SESSION_VECTOR :00000001
RECO_VECTOR :00000001
TYPE# :0
FAIL_TIME :2/3/2011 11:41:24 AM
HEURISTIC_TIME :
RECO_TIME :2/3/2011 1:53:20 PM
TOP_DB_USER :LOC01
TOP_OS_USER :SYSTEM
TOP_OS_HOST :location01
TOP_OS_TERMINAL :location01
GLOBAL_COMMIT# :1995534655
SPARE1 :
SPARE2 :
SPARE3 :
SPARE4 :

PL/SQL procedure successfully completed.

I have some questions:
1)The error in the alert log says the transaction roll back. but the query shows the commit#. Does this mean it already committed?
2)This transaction is still in the sys.pending_trans$, does this mean
this transaction is still pending?

Hi Tom

Is it possible for a locked txn (which is in-doubt) to block inserts into the same table? How could I recreate this scenario in a test situation?

Thanks
Gareth

Thanks for that, the scenario I've been trying to emulate has a function based index (non-unique).

From the documentation, I see that

> A query or DML statement that requires locks on a local database can be blocked indefinitely due to the locked resources of an in-doubt distributed transaction

In theory, could a 'locked resource' mentioned above mean... a data block? And hence cause inserts to wait on the resolution of an in-doubt transaction...?

Just as an aside, and why I ask is that I see 'enq: TX - row lock contention' on INSERTs into the said table (which has no PK)...

>yes, it could be a data block. but it doesn't seem you have any failed/in doubt transactions - if it resolves itself quickly. why are you looking down that path?

A little background (sorry to drip feed previously but am conscious this is a followup)

* we have a 'local' table and another table over a dblink
* the 'local' table is populated with 'new' rows by an application throughout the day (i.e processed_flag = 'N')
* a periodic PL/SQL job updates 'new' rows with a processed flag of 'Y' and the data is inserted into the remote table - once inserted then the daemon job commits (i.e. a distributed transaction).

The failed/in-doubt transaction occurred when the remote system failed. The application's inserts into the 'local' table appeared to be blocked... just wondering how this could be possible?

>is this a normal b*tree index?

It's a function based index on a single column, it is b*tree (not bitmap), basically to identify already processed rows:
DECODE(proc, 'Y', NULL, 'N')

Following up from above, would we avoid blocking 'application' INSERTS if the 'local' database had a higher COMMIT_POINT_STRENGTH than the 'remote' (they're equal at the moment)?

Hi Tom ,I m little new to DBA group but in my production environment .in-doubt not cleared after force roll-back and dbms-purge package .On regular basis ,we facing issue this issue .Is there any parameter which impact force roll or force commit to indoubt traction .