Home » RDBMS Server » Server Administration » another ORA-01591: lock held by in-doubt distributed transaction xx problem (oracle 10.2.0.1.0 linux x86-64)
another ORA-01591: lock held by in-doubt distributed transaction xx problem [message #304953] Fri, 07 March 2008 05:23 Go to next message
fozerol
Messages: 17
Registered: March 2007
Junior Member
Hello

main database shutdown abnormally because of power problem
after that, database startup normally
but there is pending transactions when i update or delete specific row with statement

update rezervasyonlar set durum=3 where rezervasyonid=2950335
error occurs
ORA-01591: lock held by in-doubt distributed transaction 3.3.142964
here is brief dba_2pc_pending query result

select local_tran_id, global_tran_id, state, mixed, host, commit# from dba_2pc_pending

3.3.142964
1145324612.C4FD65EB567392428453748509853C8000000000
prepared no
WORKGROUP\KKBSNET
5967368082522

i try to force commit and rolback with the statement
commit force '3.3.142964'
and
rollback force '3.3.142964'
and
commit force <GLOBAL_TANSAC_ID>
nothing changed
and last try to purge with

EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('3.3.142964');
error occured when i execute this statement

BEGIN dbms_transaction.purge_lost_db_entry('3.3.142964'); END;
Error at line 1
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.DBMS_TRANSACTION", line 94
ORA-06512: at line 1

any suggestion will be appreciated

thanks
Re: another ORA-01591: lock held by in-doubt distributed transaction xx problem [message #304968 is a reply to message #304953] Fri, 07 March 2008 06:08 Go to previous messageGo to next message
mkbhati
Messages: 93
Registered: February 2007
Location: Mumbai
Member

Please check your trace files. RECO will automatically attempt to recover/resolve your pending 2PC transaction when you bring up your database after power outage.

Regards

Manjit Kumar [mkbhati]
Re: another ORA-01591: lock held by in-doubt distributed transaction xx problem [message #304972 is a reply to message #304953] Fri, 07 March 2008 06:28 Go to previous messageGo to next message
fozerol
Messages: 17
Registered: March 2007
Junior Member
no the problem still exists after shutdown immediate- startup, or shutdown abort- startup, even i have create physical copy of main database (restored backup and applied log files until now) and same problem exists at that database
Re: another ORA-01591: lock held by in-doubt distributed transaction xx problem [message #304977 is a reply to message #304953] Fri, 07 March 2008 07:04 Go to previous messageGo to next message
fozerol
Messages: 17
Registered: March 2007
Junior Member
i solved

i used
DBMS_TRANSACTION.rollback_force(xid)
this changed state prepared to forced_rollback after that DBMS_TRANSACTION.PURGE_MIXED('3.3.142964');
completed successfuly
at physical copy of main database, i will try it on main database now


thanks
Re: another ORA-01591: lock held by in-doubt distributed transaction xx problem [message #304980 is a reply to message #304977] Fri, 07 March 2008 07:17 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback.

Regards
Michel
Previous Topic: ora-01001
Next Topic: database copy
Goto Forum:
  


Current Time: Thu Dec 08 21:51:30 CST 2016

Total time taken to generate the page: 0.27270 seconds