ORA-29855 upon index creation after import

Hello,

I am having a problem with JChem cartridge and wonder if you can help.  The situation is a complex one and does not occur on all machines / platforms.  Here is what we are seeing :

1) User 'A' exports Oracle schema 'B' which contains a table of structures using dbms_datapump.  The JChem index and JChem related property tables etc are *not* exported from the schema into the dump file.

2) User 'A' imports the dump file as a new schema, 'C'.  The import succeeds. 

3) We then try to create a JChem index on the table containing the structures.

Now is where it gets complicated.  The first time we try this we (on this particular machine) tend to get this error raised :

ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine

ORA-29532: Java call terminated by uncaught Java exception: oracle.jdbc.driver.OracleSQLException:

ORA-00600: internal error code, arguments: [12260], [123], [], [], [], [], [], []

Looking in the Oracle generated trace file we see this call stack :

at oracle.jdbc.driver.T2SConnection.check_error(T2SConnection.java:154)

at oracle.jdbc.driver.T2SPreparedStatement.checkError(T2SPreparedStatement.java:93)

at oracle.jdbc.driver.T2SPreparedStatement.executeForRows(T2SPreparedStatement.java:450)

at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1169)

at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1289)

at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3545)

at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3590)

at chemaxon.jchem.cartridge.JcMetaDataFunctions.getProperty(JcMetaDataFunctions.java:592)

at chemaxon.jchem.cartridge.JcMetaDataFunctions.getMasterProperty(JcMetaDataFunctions.java:872)

at chemaxon.jchem.cartridge.JFunctions.getStandardizerConfig(JFunctions.java:2346)

at chemaxon.jchem.cartridge.Indexing.indexCreate(Indexing.java:121)

at chemaxon.jchem.cartridge.Indexing.indexCreate(Indexing.java:79)

Steve,

The underlying problem appears to be ORA-00600, a general indication that the backend Oracle session process prematurely terminated in the face of the vicissitudes of life (due to an Oracle bug). Please, could you upload (or send to peter dot kovacs at chemaxon dot hu) the entire Oracle session trace file (or at least a good chunk from the beginning)?

Thanks,

Peter

Hi Peter,

Thanks for that.  I am aware of the horrors of the ORA-600!!   If I could just get some clue as to where it was going wrong then I may be able to work around the issue in some way.

I have emailed the trace file to you.  Please let me know if you don't receive anything.

Regards,

Steve

Hi Steve,

Thank you for the trace file. It doesn't appear to contain any immediately useful information to me, but I've got now a slightly better feel of the problem.

I am trying to better understand one of the factors which appear key to the problem: "long period of inactivity". Does this mean that the database session used to execute the aborting CREATE INDEX command is left open for a long time without any activity over it? I am not familiar with DBMS_SCHEDULER yet, so I am not sure whether it opens a new database session for each scheduled job or it reuses the same database session for multiple jobs with potential long inactivity of the same database session. If DBMS_SCHEDULER reuses the same database session, one factor to the problem on the JChem Cartridge side might be that JChem Cartridge keeps open some of the prepared statements for reuse to execute queries. Perhaps, an Oracle Database component is not prepared to handle elderly prepared statement instances under specific circumstances...

In any case, I suggest to open a Service Request with Oracle, because Oracle Support is the most likely to provide an immediate solution/work-around to this problem. Both the nature of the problem (Oracle process dying "ungracefully") and its reproducibility seem to suggests that an "educated" analysis of the Oracle-generated trace file is one of the best ways to proceed here. Any input based on such analysis may help (combined with what I can eventually add about JChem Cartridge inner workings), let alone the possibility that the database instance in which the problem occurs is, in some way, corrupted or inconsistent. (Last time an ORA-00600 problem was reported to me, Oracle Support traced the cause back to some "fake indexes" which, the reporting DBA speculated, were left in the database by some developers using Toad. I've never learnt what "fake indexes" are...)

Regards,

Peter

Hi Peter,

Thanks for the detailed reply.  It makes perfect sense and we will raise the issue with Oracle Support.

Re: DBMS_SCHEDULER and prepared statements you could be on to something.  The scheduler creates a new session on the database in which the dbms_datapump operation and the 'create index' happen sequentially (i.e there is no significant lag between them) however when the session created by DBMS_SCHEDULER attempts to invoke JChem there is some incompatibility between the prepared statement in JChem that was *not* generated via DBMS_SCHEDULER and the calling statement which was.  However upon the second call to the function the prepared statement is now compatible...

This is all rather speculative but does make some sense!

Are you able to give any indication as to what JChem is trying to do at the point of failure?  (ideally the SQL or stored procedure it is going to call).  This may help us with any workaround.

Many thanks,

Steve

Hi Steve,

Regarding the possibility that prepared statements kept open for a long time may be a source of instability, it may make sense to introduce an option that prevents keeping them open. Such an option would give some flexibility in similar cases. I will consider introducing it in JChem version 5.4.1.

The ORA-00600 error occurs when JChem Cartridge requests the following statement to be executed:

SELECT prop_value FROM vitic_lhasa.jc_idx_property WHERE prop_name = :1

. This statement has been prepared previously and, called with 'standardizerConfig' as parameter, aims in this particular context to obtain the value of the default Standardizer configuration (if any has been specified) in the JChem Cartridge installation scope. This involves querying the JC_IDX_PROPERTY table in the JChem Cartridge owner schema. The JChem Cartridge owner schema is likely to be in a schema different from the schema of the current session -- another potential factor for Oracle instability. 

Regards,

Peter

Hi Steve,

Could you make any progress on this issue? Does the problem persist?

Thanks

Peter

Hi Peter,

We have implemented a two-fold workaround in our code to get around the issue as it seemed to persist.

Firstly we have a 'keep alive' type scheduled task which simply makes a call to JChem's jchem_core_pkg.getenvironment() function every hour.  Wether this keeps something 'awake' or not we don't know but I'm told it helps.

And secondly we have changed the code that was failing to simply try 5 times to create the index instead of just the once as it was previously.  So if it does fail the first time then it should work the second time as the statements are re-prepared (I presume).

Thanks, and best wishes for the New Year to you.

Regards.

Steve

Thank you, Steve, for the feed back.

What granularity do you think would be optimal for the scope of the earlier mentioned option to turn off prepared statement caching? Database session scope would require a specific SQL call within the target database session. Would such a call this be possible in your case?

Thanks

Peter

Hi Steve,

JChem 5.5.1 has been released with the ability to turn off statement caching in JChem Cartridge SQL operations: http://www.chemaxon.com/jchem/doc/dev/cartridge/cartapi.html#jchem_misc_pkg.cache_stmts. This featurette might potentially solve the error you ran into in long running database sessions. Let me know what you found if you have chance to trying it out.

Thanks

Peter