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.