This discussion is archived
4 Replies Latest reply: Aug 23, 2012 10:42 AM by 940856 RSS

ORA-01591: lock held by in-doubt distributed

940856 Newbie
Currently Being Moderated
Hi All,

We are using oracle 8i to communicate informix through oracle 8i gaeways. we are getting ORA-01591 from quite a few days , the application team complains about the in doubt transcation issue. we tried to investigate and found the local and global cordinater is oracle database and the job trying to query and insert one of the table sitting on informix database. we also discovered that we have many "is local tran" entries reported in alert.log and when I query these transcation , they sometime show me in "prepared state" and after some time , the updated to "rollback forced" in the state column. also , we also have job running for cleaning up in doubt in database , which gets invoked periodically and doees the rollback force. going forward, we just failed to understand one thing that we are able to communicate the gateways through oracle database and also able to query the table for which the application team claims that when they perform insert or select they are getting in doubt. also , you would recommened to move/upgrade since this is oracle 8i , we already started migrating our databases to 11g , since this job is pretty critical , we have no option , but to live with this.I would really appriciate , if you could offer some help to me as I am clueless .
we have many in doubt in alert log

is local tran 22.89.4837399 (hex=16.59.49d017)
is local tran 9.18.5331070 (hex=09.12.51587e)
is local tran 22.89.4837399 (hex=16.59.49d017)
is local tran 2.10.5449011 (hex=02.0a.532533)
is local tran 2.10.5449011 (hex=02.0a.532533)
is local tran 23.18.5210664 (hex=17.12.4f8228)
is local tran 19.49.4958014 (hex=13.31.4ba73e)
is local tran 20.18.4845028 (hex=14.12.49ede4)
is local tran 23.18.5210664 (hex=17.12.4f8228)
is local tran 19.49.4958014 (hex=13.31.4ba73e)
is local tran 20.18.4845028 (hex=14.12.49ede4)

select LOCAL_TRAN_ID, global_TRAN_ID,STATE,ADVICE,MIXED,TRAN_COMMENT from dba_2pc_pending where LOCAL_TRAN_ID ='20.18.4845028';

LOCAL_TRAN_ID             GLOBAL_TRAN_ID            STATE            MIX TRAN_
------------------------- ------------------------- ---------------- --- -----
2.10.5449011              PROD01.US.ESTEE.COM.ceedc prepared         no
                          40c.2.10.5449011

select LOCAL_TRAN_ID, global_TRAN_ID,STATE,ADVICE,MIXED,TRAN_COMMENT from dba_2pc_pending where LOCAL_TRAN_ID ='20.18.4845028';

LOCAL_TRAN_ID             GLOBAL_TRAN_ID            STATE            A MIX TRAN_
------------------------- ------------------------- ---------------- - --- -----
2.10.5449011              PROD01.US.ESTEE.COM.ceedc forced rollback    no
                          40c.2.10.5449011

SELECT LOCAL_TRAN_ID, IN_OUT, DATABASE, DBUSER_OWNER , INTERFACE FROM DBA_2PC_NEIGHBORS where  LOCAL_TRAN_ID ='20.18.4845028';

LOCAL_TRAN_ID IN_OUT DATABASE                  DBUSER_OWNER    INT
------------- ------ ------------------------- --------------- ---
2.10.5449011  in                               DBTRAN          N
2.10.5449011  out    TG4MELV.US.ESTEE.COM      DBTRAN          C



05:35:59 SQL> select count(*) from "recover"."hs_transaction_log"@tg4melv.us.estee.com;

  COUNT(*)
----------
         0

05:37:23 SQL> select count(*) from "recover"."hs_transaction_log"@tg4padc.us.estee.com;

  COUNT(*)
----------
         0
Edited by: 937853 on Aug 23, 2012 3:19 AM
  • 1. Re: ORA-01591: lock held by in-doubt distributed
    mkirtley-Oracle Expert
    Currently Being Moderated
    Hi,
    You haven't said which gateway you are using but is it the TG4Informix or are using HSODBC (generic connectivity).
    I would suggest the following -

    - try to determine which transactions are failing and why to try and prevent them happening.
    - if you are using Tg4Informix then check the recovery user and password so it can connect to the Informix database to automatically resolve transactions that have failed. That may be a reason why they are left in the system.

    The 8i gateways have been desupported for a long time now and once you upgrade your RDBMS to 11g you should then obviously use the 11g gateway.
    You can't use a 8i RDBMS against any 11g gateway so you can't do it before upgrading the RDBMS.
    The Informix transaction log table is empty so the only action is to manually delete the outstanding transactions that have been left in the Oracle database.

    Regards,
    Mike
  • 2. Re: ORA-01591: lock held by in-doubt distributed
    940856 Newbie
    Currently Being Moderated
    thanks mike for your inputs , they are extremely valuable for me . we are using tg4ininformix gateway and not the HSODBC. also, we don't have any such users in informaix database on either of gateways (tg4melv ,tg4padc).
  • 3. Re: ORA-01591: lock held by in-doubt distributed
    mkirtley-Oracle Expert
    Currently Being Moderated
    Hi,
    I asked about using HSODBC because that doesn't support distributed transactions but Tg4informix does support them. However, you need to configure the gateway for this and one of the steps is creating a recovery user on Informix along with the transaction log table.

    Regards,
    Mike
  • 4. Re: ORA-01591: lock held by in-doubt distributed
    940856 Newbie
    Currently Being Moderated
    The informix DBA are saying this in not an option for them. also , one thing really baffels me that , when I query this throgh oracle database , it returns 0 rows. but the database says "table or view does not exist". the latest errors reported in aler.log.
    ORA-02054: transaction 14.36.4991413 in-doubt
    ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
    [Oracle][ODBC Informix Wire Protocol driver][Informix]The specified table (recover.HS_TRANSACTION_LOG) is not in the database.
    ORA-02063: preceding 2 lines from TG4PADC
    ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
    [Oracle][ODBC Informix Wire Protocol driver][Informix]Could not insert new row /* - duplicate value in a UNIQUE INDEX colum

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points