Friday, December 16, 2011

How ORA-13236 was about to steal Christmas

Recently an error was reported to me ...

Message: ORA-00604: error occurred at recursive SQL level 1
ORA-13236: internal error in R-tree processing: [insertion at root (mdrbin_mem_ins_rt)]
ORA-13236: internal error in R-tree processing: [partition and pair bucket (mdrbin_optmz_mem_ins_node)]
ORA-13236: internal error in R-tree processing: [pair buckets (mdrbin_partition_pair_bckts)]
ORA-13236: internal error in R-tree processing: [mdrugnd - getting a node (mdrbin_pair_bckts)]
ORA-13234: failed to access R-tree-index table [MDRT Table]
ORA-29400: data cartridge error
Error - OCI_NODATA
ORA-06512: at "MDSYS.SDO_IDX", line 149
ORA-06512: at line 1

StackTrace: at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure)

at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src)
at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()
at X.SasquatchEngine.ProcessingUtilities.DataLayerRetrieval.RetrieveTaskData(Int32 orderTaskID, Int32 summitGroupID)




According to Oracle Support Note: Ora-13236 Internal Error In R-Tree Processing During Heavy DML [ID 443422.1] this error is due to a Bug (4570769) The solution procedure is described in the note. This bug was first seen in Oracle 10g, and so far it has not been updated.

The effects of this bug was the impossibility of insertion to a table containing an geometry element. Some geometries could be queried, some others on a very particular region couldn't be queried, so it was a symptom that a specific geographic region was corrupted inside the R-Index.

Actually we didn't try the workaround defined in the previously referred M.O.S. note, we rebuilt the R-Index instead.