2 Replies Latest reply: Mar 26, 2013 9:29 AM by 664517 RSS

    ORA-01591 In-Doubt transaction when doing basic CTAS

    664517
      Hi,

      Our application is generating ORA-01591 when trying to save a record in the application. During testing, the devs realised that they could also trigger the same error at will by doing a CTAS.

      Windows 2008R2
      Oracle 11gR2 11.2.0.1 SE 64bit no CPU's

      To replicate the error I do:

      create table t1 as select * from <table>;

      It's just one specific table, as doing CTAS against other objects that i've tested does not generate this error.

      When I get the error when doing CTAS, I immediately query DBA_2PC_PENDING, but there are no rows. When the application generates the error, rows do appear here and we force commit them, but they do not disappear or they do, but then another one appears within a short period of time.

      This is not a distributed database, but we do use .NET connection pooling for an IIS web app on the application server. Shutting down the application and restarting the DB did not help; When I restarted the DB without bringing up the application, I still get the same error with the ctas!

      I can see in the alertlog that there were never any "DISTRIB TRANS" errors from november last year up until 19th march, and at that point the db crashed and performed instance recovery during subsequent startup, which succeeded. two hours later we got the first DISTRIB TRANS error and it continued every several minutes for the next two days. Im not 100% sure that the DISTRIB TRANS error is connected to the ORA-01591 errors we are now getting, but thought i'd mention it anyway.

      I thought maybe it was due to some block corruption error, but I can select all the rows from this table with select * from <table>; without getting any errors. I also ran backup validate check logical database and got no rows in v$database_block_corruption.

      The very first error that the alertlog logged was for a mview not connected to our current problem:

      Tue Mar 19 09:48:18 2013
      Following on-commit snapshots not refreshed :
      <schema>.APP_VW_SERV
      Error 2051 trapped in 2PC on transaction 8.4.15766. Cleaning up.
      Error stack returned to user:
      ORA-02054: transaktionen 8.4.15766 är oviss
      ORA-02051: en annan session eller gren i samma transaktion misslyckades eller avslutades
      Tue Mar 19 09:48:18 2013
      DISTRIB TRAN 44444444.F3B746FFBCCC944A849C560C5B0F14BB00000000
      is local tran 8.4.15766 (hex=08.04.3d96)
      insert pending prepared tran, scn=465069611 (hex=0.1bb8662b)

      We have an identical DB on the same server, running the same version of the application, same Oracle Home same DB options, and both having the same exact 6 application owned objects that won't compile (it's a dev server), but we do not see the ORA-01591 issues in that database at all.
        • 1. Re: ORA-01591 In-Doubt transaction when doing basic CTAS
          664517
          Never mind. I following the instructions in this link:

          Re: ORA-1591 error

          to insert a dummy row into dba_2pc_pending, so that I could then clean up that transaction. Now everything works again.


          from link:

          ACCEPT TRANSACTION_ID PROMPT "Enter TRANSACTION ID: "
          alter system disable distributed recovery;
          insert into pending_trans$ (
          LOCAL_TRAN_ID,
          GLOBAL_TRAN_FMT,
          GLOBAL_ORACLE_ID,
          STATE,
          STATUS,
          SESSION_VECTOR,
          RECO_VECTOR,
          TYPE#,
          FAIL_TIME,
          RECO_TIME)
          values( '&&TRANSACTION_ID',
          306206, /* */
          'XXXXXXX.12345.1.2.3', /* These values can be used without any */
          'prepared','P', /* modification. Most of the values are */
          hextoraw( '00000001' ), /* constant. */
          hextoraw( '00000000' ), /* */
          0, sysdate, sysdate );
          insert into pending_sessions$
          values( '&&TRANSACTION_ID',
          1, hextoraw('05004F003A1500000104'),
          'C', 0, 30258592, '',
          146
          );
          commit;
          rollback force '&&TRANSACTION_ID'; /* or commit force */
          commit;
          alter system enable distributed recovery;
          exec dbms_transaction.purge_lost_db_entry( '&&TRANSACTION_ID' );
          commit;
          delete from pending_trans$ where local_tran_id='&&TRANSACTION_ID';
          delete from pending_sessions$ where local_tran_id='&&TRANSACTION_ID';
          commit;

          Edited by: finite9 on 26-Mar-2013 03:41
          • 2. Re: ORA-01591 In-Doubt transaction when doing basic CTAS
            664517
            was wrong :( error re-appeared. When we perform the operation from the application, it communicates over odp.net to the db, and immediately creates an in-doubt transaction. This in turn causes the CTAS I mentioned to fail when doing a CTAS on that table involved in the application transaction.

            I can clean out the transaction with commit force, then use dbms_transaction to purge the entry, but it re-eappears with a different transaction ID as soon as the app is used again.

            I restarted Microsoft Distributed Transaction Coordinator, and noticed that Oracle MTS service was not created, so I recreated it according to MOS note, and performed an iisreset on the server (app is on same server as DB), but none of this helped.

            I have gone through the master troubleshooting note for distrubuted transactions (100664.1), and the entry in dba_2pc_neighbors points to odp.net as the initiator:

            LOCAL_TRAN_ID : 6.8.20674
            IN_OUT : in
            DATABASE : ODP.NET
            DBUSER_OWNER : APP_SCHEMA
            INTERFACE : N
            DBID : ODP.NET
            SESS# : 1
            BRANCH :
            C0F121F9B468C949B2CDBA78AA294E53010000004354440100C003574D53493030343235302E67726F7570696E6672612E636F6D