4 Replies Latest reply on Apr 16, 2007 3:58 AM by Scott Russell

    ORA-00604: error occurred at recursive SQL level 52 on XA

    Tomas Huryn Newbie

      Hi,
      I've a problem with XA connection to Oracle 10.2.0.1.0. Connection was working, but after one restart of jboss (4.0.5), Exception is thrown suddenly:


      17:58:45,975 INFO [Ejb3Deployment] EJB3 deployment time took: 142
      17:58:49,839 WARN [JBossManagedConnectionPool] Throwable while attempting to get a new connection: null
      org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (java.sql.SQLException: ORA-00603: ORACLE server session terminated by fatal error
      ORA-00604: error occurred at recursive SQL level 52
      ORA-00036: maximum number of recursive SQL levels (50) exceeded
      ORA-00604: error occurred at recursive SQL level 52
      ORA-00036: maximum number of recursive SQL levels (50) exceeded
      )
      at org.jboss.resource.adapter.jdbc.xa.XAManagedConnectionFactory.createManagedConnection(XAManagedConnectionFactory.java:162)
      at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.createConnectionEventListener(InternalManagedConnectionPool.java:565)
      at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.getConnection(InternalManagedConnectionPool.java:250)
      at org.jboss.resource.connectionmanager.JBossManagedConnectionPool$BasePool.getConnection(JBossManagedConnectionPool.java:491)
      at org.jboss.resource.connectionmanager.BaseConnectionManager2.getManagedConnection(BaseConnectionManager2.java:341)
      at org.jboss.resource.connectionmanager.TxConnectionManager.getManagedConnection(TxConnectionManager.java:301)
      at org.jboss.resource.connectionmanager.BaseConnectionManager2.allocateConnection(BaseConnectionManager2.java:396)
      at org.jboss.resource.connectionmanager.BaseConnectionManager2$ConnectionManagerProxy.allocateConnection(BaseConnectionManager2.java:842)
      at org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:88)
      at org.jboss.ejb.plugins.cmp.jdbc.SQLUtil.fixTableName(SQLUtil.java:173)
      at org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCEntityBridge.init(JDBCEntityBridge.java:157)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.initStoreManager(JDBCStoreManager.java:435)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.start(JDBCStoreManager.java:368)


      piece of oracle log:
      Wed Jan 3 17:42:25 2007
      Errors in file /opt/oracle/admin/clubdb/udump/clubdb_ora_17346.trc:
      ORA-00604: error occurred at recursive SQL level 52
      ORA-00036: maximum number of recursive SQL levels (50) exceeded
      ORA-00604: error occurred at recursive SQL level 52
      ORA-00036: maximum number of recursive SQL levels (50) exceeded

      and snipet from trace file:
      =====================
      PARSING IN CURSOR #1 len=129 dep=1 uid=0 oct=6 lid=0 tim=1140471235576830 hv=2635489469 ad='5de602a0'
      update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,cache=:7,highwater=:8,audit$=:9,flags=:10 where obj#=:1
      END OF STMT
      PARSE #1:c=0,e=342,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1140471235576815
      EXEC #1:c=4001,e=1034,p=0,cr=2,cu=2,mis=0,r=1,dep=1,og=4,tim=1140471235579303
      STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='UPDATE SEQ$ (cr=2 pr=0 pw=0 time=841 us)'
      STAT #1 id=2 cnt=1 pid=1 pos=1 obj=101 op='INDEX UNIQUE SCAN I_SEQ1 (cr=2 pr=0 pw=0 time=142 us)'
      WAIT #0: nam='SQL*Net message to client' ela= 6 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=1140471235580231
      WAIT #0: nam='SQL*Net message from client' ela= 1043 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=1140471235581348
      =====================
      PARSING IN CURSOR #1 len=82 dep=1 uid=0 oct=3 lid=0 tim=1140471235581923 hv=3873422482 ad='5de411a8'
      select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#>0
      END OF STMT
      PARSE #1:c=0,e=134,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1140471235581916
      EXEC #1:c=0,e=133,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1140471235582174
      FETCH #1:c=0,e=206,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=1140471235582424
      =====================
      PARSING IN CURSOR #2 len=131 dep=1 uid=0 oct=3 lid=0 tim=1140471235582598 hv=2747636884 ad='5e9cec38'
      select privilege#,level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0
      END OF STMT
      PARSE #2:c=0,e=117,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1140471235582592
      EXEC #2:c=4001,e=150,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1140471235582854
      FETCH #2:c=0,e=86,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=1140471235582983
      STAT #2 id=1 cnt=0 pid=0 pos=1 obj=0 op='CONNECT BY WITH FILTERING (cr=2 pr=0 pw=0 time=152 us)'
      STAT #2 id=2 cnt=0 pid=1 pos=1 obj=86 op='TABLE ACCESS BY INDEX ROWID SYSAUTH$ (cr=2 pr=0 pw=0 time=60 us)'
      STAT #2 id=3 cnt=0 pid=2 pos=1 obj=104 op='INDEX RANGE SCAN I_SYSAUTH1 (cr=2 pr=0 pw=0 time=24 us)'
      STAT #2 id=4 cnt=0 pid=1 pos=2 obj=0 op='NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us)'
      STAT #2 id=5 cnt=0 pid=4 pos=1 obj=0 op='BUFFER SORT (cr=0 pr=0 pw=0 time=0 us)'
      STAT #2 id=6 cnt=0 pid=5 pos=1 obj=0 op='CONNECT BY PUMP (cr=0 pr=0 pw=0 time=0 us)'
      STAT #2 id=7 cnt=0 pid=4 pos=2 obj=104 op='INDEX RANGE SCAN I_SYSAUTH1 (cr=0 pr=0 pw=0 time=0 us)'
      STAT #2 id=8 cnt=0 pid=1 pos=3 obj=86 op='TABLE ACCESS FULL SYSAUTH$ (cr=0 pr=0 pw=0 time=0 us)'
      FETCH #1:c=0,e=18,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,tim=1140471235583233
      =====================
      PARSING IN CURSOR #2 len=131 dep=1 uid=0 oct=3 lid=0 tim=1140471235583300 hv=2747636884 ad='5e9cec38'
      select privilege#,level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0
      END OF STMT
      PARSE #2:c=0,e=20,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1140471235583294
      EXEC #2:c=0,e=68,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1140471235583459
      FETCH #2:c=0,e=48,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=1140471235583543
      STAT #2 id=1 cnt=0 pid=0 pos=1 obj=0 op='CONNECT BY WITH FILTERING (cr=2 pr=0 pw=0 time=86 us)'
      STAT #2 id=2 cnt=0 pid=1 pos=1 obj=86 op='TABLE ACCESS BY INDEX ROWID SYSAUTH$ (cr=2 pr=0 pw=0 time=46 us)'
      STAT #2 id=3 cnt=0 pid=2 pos=1 obj=104 op='INDEX RANGE SCAN I_SYSAUTH1 (cr=2 pr=0 pw=0 time=23 us)'
      STAT #2 id=4 cnt=0 pid=1 pos=2 obj=0 op='NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us)'
      STAT #2 id=5 cnt=0 pid=4 pos=1 obj=0 op='BUFFER SORT (cr=0 pr=0 pw=0 time=0 us)'
      STAT #2 id=6 cnt=0 pid=5 pos=1 obj=0 op='CONNECT BY PUMP (cr=0 pr=0 pw=0 time=0 us)'
      STAT #2 id=7 cnt=0 pid=4 pos=2 obj=104 op='INDEX RANGE SCAN I_SYSAUTH1 (cr=0 pr=0 pw=0 time=0 us)'
      STAT #2 id=8 cnt=0 pid=1 pos=3 obj=86 op='TABLE ACCESS FULL SYSAUTH$ (cr=0 pr=0 pw=0 time=0 us)'
      FETCH #1:c=0,e=15,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=1140471235583797
      STAT #1 id=1 cnt=2 pid=0 pos=1 obj=0 op='INLIST ITERATOR (cr=6 pr=0 pw=0 time=250 us)'
      STAT #1 id=2 cnt=2 pid=1 pos=1 obj=104 op='INDEX RANGE SCAN I_SYSAUTH1 (cr=6 pr=0 pw=0 time=163 us)'
      =====================
      PARSING IN CURSOR #2 len=81 dep=1 uid=0 oct=42 lid=0 tim=1140471235584130 hv=1585369810 ad='40648020'
      ALTER SESSION SET TIME_ZONE='+1:00' NLS_LANGUAGE='GERMAN' NLS_TERRITORY='GERMANY'
      END OF STMT
      PARSE #2:c=0,e=110,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1140471235584124
      EXEC #2:c=0,e=179,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1140471235584384
      =====================
      PARSING IN CURSOR #1 len=55 dep=1 uid=0 oct=3 lid=0 tim=1140471235584582 hv=1950821498 ad='5deaaa80'
      select value$ from props$ where name = 'GLOBAL_DB_NAME'
      END OF STMT
      PARSE #1:c=0,e=94,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1140471235584576
      EXEC #1:c=0,e=32,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1140471235584692
      FETCH #1:c=0,e=168,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=1140471235584896
      FETCH #1:c=0,e=17,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=1140471235584951
      =====================
      PARSING IN CURSOR #2 len=55 dep=2 uid=0 oct=3 lid=0 tim=1140471235585061 hv=1950821498 ad='5deaaa80'
      select value$ from props$ where name = 'GLOBAL_DB_NAME'
      END OF STMT
      PARSE #2:c=0,e=32,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1140471235585054
      EXEC #2:c=0,e=27,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1140471235585162
      FETCH #2:c=0,e=47,p=0,cr=3,cu=0,mis=0,r=1,dep=2,og=4,tim=1140471235585244
      FETCH #2:c=0,e=10,p=0,cr=1,cu=0,mis=0,r=0,dep=2,og=4,tim=1140471235585290
      =====================
      PARSING IN CURSOR #3 len=55 dep=3 uid=0 oct=3 lid=0 tim=1140471235585449 hv=1950821498 ad='5deaaa80'
      select value$ from props$ where name = 'GLOBAL_DB_NAME'
      END OF STMT
      PARSE #3:c=0,e=65,p=0,cr=0,cu=0,mis=0,r=0,dep=3,og=4,tim=1140471235585443
      EXEC #3:c=0,e=27,p=0,cr=0,cu=0,mis=0,r=0,dep=3,og=4,tim=1140471235585553
      FETCH #3:c=0,e=44,p=0,cr=3,cu=0,mis=0,r=1,dep=3,og=4,tim=1140471235585633
      FETCH #3:c=0,e=13,p=0,cr=1,cu=0,mis=0,r=0,dep=3,og=4,tim=1140471235585683
      =====================
      PARSING IN CURSOR #4 len=55 dep=4 uid=0 oct=3 lid=0 tim=1140471235585841 hv=1950821498 ad='5deaaa80'
      select value$ from props$ where name = 'GLOBAL_DB_NAME'
      END OF STMT
      PARSE #4:c=0,e=37,p=0,cr=0,cu=0,mis=0,r=0,dep=4,og=4,tim=1140471235585835


      configuration of the data sources:
      <xa-datasource>
      <jndi-name>NameDS</jndi-name>
      <track-connection-by-tx/>
      <isSameRM-override-value>false</isSameRM-override-value>
      <xa-datasource-class>oracle.jdbc.xa.client.OracleXADataSource</xa-datasource-class>
      <xa-datasource-property name="URL">jdbc:oracle:thin:@192.168.xx.xx:1521:sidname</xa-datasource-property>
      <xa-datasource-property name="User">user</xa-datasource-property>
      <xa-datasource-property name="Password">password</xa-datasource-property>
      <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
      <no-tx-separate-pools/>
      <blocking-timeout-millis>60000</blocking-timeout-millis>
      </xa-datasource>

      I've no idea, wat's happen.