topic The problem of Reconcile:ORA-00604, ORA-13236,ORA-29400,ORA-06512 in Data Management Questions

The problem of Reconcile:ORA-00604, ORA-13236,ORA-29400,ORA-06512

szcszc — Sat, 29 Jan 2011 02:38:40 GMT

I develop a GIS Edit system in ArcEngine, supporting versioned editing. At present, there are

about 25 versions which inherit from a Parent verion in common. There is a problem of the method

Reconcile4. After calling Reconcile4 and StopEditing�?then StartEditing again, the method of

StartEditing return FDO_E_VERSION_UNEDITABLE. The data is lost between last reconcile and this reconcile.

Then I try reconciling in ArcMap, the result is the same as our system.

SDE Log:
   S_set_logdata_insert() USER Transaction Logfile INSERT - No Commit on Insert
   [Tue Jan 25 14:27:47 2011] [488935] [golden-optest]
   db_log_add_user_ids_sql() USER Transaction Logfile INSERT - No Commit on Insert
   [Tue Jan 25 14:27:47 2011] [488935] [golden-optest]
   db_log_add_user_ids_sql() USER Transaction Logfile INSERT - No Commit on Insert
   [Tue Jan 25 14:27:48 2011] [488935] [golden-optest]
   db_log_add_user_ids_sql() USER Transaction Logfile INSERT - No Commit on Insert
   [Tue Jan 25 14:27:49 2011] [488935] [golden-optest]

Our environment�?
   Sever�?
     Operate System�? AS 5.4(64 bit)
     Database�?       Oracle 10.2.0.3
     ArcSDE�?    oracle_linux for sde 64bit 9.3.1
     ArcSDE Server Build�? for Oracle10g Build 1632 Thu Feb 26 12:05:37 2009

   Client�?
     ArcGIS Engine SDK for CPP 9.3
     ArcMap 9.3

After this problem, we upgrade ArcGIS in server and client, environment after upgrading as

follows:
     Sever�?
     Operate System�? AS 5.4(64 bit)
     Database�?       Oracle 10.2.0.3
     ArcSDE�?    oracle_linux for sde 64bit 9.3.1 sp2

     Client�?
       ArcMap 9.3.1 sp2

Then I reconcile in ArcMap, the Arcmap pop this clue:
     Reconcile against USER_20101015.Parent:An unexpected failure occurred.
     Underlying DBMS error[ORA-00604:error occurred at recursive SQL level 1
     ORA-13236:internal error in R-tree processing: [reading node(mdrbin_mem_ins_rt)]
     ORA-29400:data cartridge error
     Error - OCI_NODATA
     ORA-06512: at "MDSYS.SDO_IDX", line 142
     ORA-06512: at line 1
     ]

How to solve this problem?

Re: The problem of Reconcile:ORA-00604, ORA-13236,ORA-29400,ORA-06512

szcszc — Wed, 09 Feb 2011 01:24:14 GMT

Who know how to solve this problem?

Re: The problem of Reconcile:ORA-00604, ORA-13236,ORA-29400,ORA-06512

huangJary — Tue, 15 Feb 2011 07:30:35 GMT

I meet the same problem as you, then I upgrade oracle to 10.2.0.5 version.Then ArcMap report an error:

Reconcile against USER.Parent:An unexpected failure occurred.
     Underlying DBMS error[ORA-00604:error occurred at recursive SQL level 1
     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 103
     ORA-06512: at line 1
     ]

Re: The problem of Reconcile:ORA-00604, ORA-13236,ORA-29400,ORA-06512

TomBrown — Tue, 15 Feb 2011 14:22:13 GMT

Based on the error message it indicates a failure in the Oracle spatial type. Have you searched for this error on any of the Oracle forums/websites?

It's not a problem in reconcile, you could capture an Oracle trace an identify exactly which statement is failing and you will then know which table has the problem.

If you need further assistance, I would contact ESRI Support. They would be able to help you narrow down the issue.

Good luck.

Re: The problem of Reconcile:ORA-00604, ORA-13236,ORA-29400,ORA-06512

szcszc — Thu, 24 Feb 2011 12:09:35 GMT

I suspect the problem is brought out by oracle bug, there is an article in oracle, ã??Ora-
13236Internal Error In R-Tree Processing During Heavy DML [ID 443422.1]ã??.I modify attribute
parameter, upgrade linux 10.2 to latest version according to solutions in the articleï¼? but the
error still exsits.

Then I detect bad block all data files in oracle, and don't find abnormal phenomenon.

Then I trace this error of ArcMap, the .trc file as follows:
=====================
PARSING IN CURSOR #137 len=80 dep=2 uid=78 oct=3 lid=78 tim=1267332140230147 hv=1378123736

ad='8fcbe930'
SELECT info from "USER_20101015".MDRT_19FCD$ where rowid = :rid for update
END OF STMT
PARSE #137:c=0,e=119,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=1,tim=1267332140230143
EXEC #137:c=0,e=354,p=0,cr=1,cu=0,mis=1,r=0,dep=2,og=1,tim=1267332140230560
FETCH #137:c=0,e=4,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=1267332140230587
EXEC #139:c=192012,e=180481,p=0,cr=224,cu=1,mis=0,r=0,dep=1,og=1,tim=1267332140230708
ERROR #139:err=13236 tim=2044901203
WAIT #0: nam='db file sequential read' ela= 10296 file#=102 block#=17161 blocks=1 obj#=-1

tim=1267332140245801
WAIT #0: nam='db file sequential read' ela= 25 file#=29 block#=131697 blocks=1 obj#=-1

tim=1267332140249836
WAIT #0: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1

tim=1267332140259189
*** 2011-02-15 13:35:57.100
WAIT #0: nam='SQL*Net message from client' ela= 44408699 driver id=1650815232 #bytes=1 p3=0

obj#=-1 tim=1267332184667958
EXEC #28:c=0,e=248,p=0,cr=7,cu=3,mis=0,r=1,dep=1,og=1,tim=1267332184668389
XCTEND rlbk=0, rd_only=0
EXEC #41:c=0,e=244,p=0,cr=0,cu=1,mis=0,r=0,dep=1,og=0,tim=1267332184668696
WAIT #40: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1

tim=1267332184668963
EXEC #40:c=0,e=945,p=0,cr=7,cu=4,mis=0,r=1,dep=0,og=1,tim=1267332184668993
WAIT #40: nam='SQL*Net message from client' ela= 354 driver id=1650815232 #bytes=1 p3=0 obj#=-1

tim=1267332184669402
EXEC #19:c=0,e=20,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1267332184669468
FETCH #19:c=0,e=11,p=0,cr=2,cu=0,mis=0,r=0,dep=0,og=1,tim=1267332184669509
WAIT #19: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1

tim=1267332184669551
WAIT #19: nam='SQL*Net message from client' ela= 70 driver id=1650815232 #bytes=1 p3=0 obj#=-1

tim=1267332184669649
EXEC #25:c=4001,e=17,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1267332184669708
WAIT #25: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1

tim=1267332184669776
FETCH #25:c=0,e=46,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,tim=1267332184669799
WAIT #25: nam='SQL*Net message from client' ela= 225 driver id=1650815232 #bytes=1 p3=0 obj#=-1

tim=1267332184670066
=====================
PARSING IN CURSOR #136 len=138 dep=0 uid=78 oct=3 lid=78 tim=1267332184670429 hv=3971663499

ad='8e917e28'
SELECT /* ESRI GDM_4 */ state_id,owner,creation_time,closing_time,lineage_name,parent_state_id

FROM SDE.states WHERE parent_state_id = :id
END OF STMT
PARSE #136:c=0,e=305,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1267332184670423
EXEC #136:c=0,e=931,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1267332184671444
WAIT #136: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1

tim=1267332184671492
FETCH #136:c=0,e=29,p=0,cr=2,cu=0,mis=0,r=0,dep=0,og=1,tim=1267332184671557
WAIT #136: nam='SQL*Net message from client' ela= 126 driver id=1650815232 #bytes=1 p3=0 obj#=-1

tim=1267332184671747
EXEC #60:c=0,e=39,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1267332184671830
FETCH #60:c=0,e=83,p=0,cr=10,cu=0,mis=0,r=0,dep=0,og=1,tim=1267332184671942
WAIT #60: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1

tim=1267332184672018
WAIT #60: nam='SQL*Net message from client' ela= 60 driver id=1650815232 #bytes=1 p3=0 obj#=-1

tim=1267332184672106
EXEC #9:c=0,e=21,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1267332184672168
WAIT #9: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1

tim=1267332184672224
FETCH #9:c=0,e=49,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=1,tim=1267332184672246
WAIT #9: nam='SQL*Net message from client' ela= 193 driver id=1650815232 #bytes=1 p3=0 obj#=-1

tim=1267332184672482
EXEC #61:c=0,e=141,p=0,cr=3,cu=0,mis=0,r=0,dep=0,og=2,tim=1267332184672699
FETCH #61:c=0,e=69,p=0,cr=2,cu=0,mis=0,r=0,dep=0,og=2,tim=1267332184672802
WAIT #61: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1

tim=1267332184672869
WAIT #61: nam='SQL*Net message from client' ela= 77 driver id=1650815232 #bytes=1 p3=0 obj#=-1

tim=1267332184672978

Get infomation:
SELECT info from "USER_20101015".MDRT_19FCD$ where rowid = :rid for update, this sql result in
error.

This is DDL of USER_20101015".MDRT_19FCD$ table:
CREATE TABLE "USER_20101015"."MDRT_19FCD$"
(
"NODE_ID" NUMBER,
"NODE_LEVEL" NUMBER,
"INFO" BLOB
)
PCTFREE 2 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING STORAGE
(
INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1

FREELIST GROUPS 1 BUFFER_POOL DEFAULT
)
TABLESPACE "TBS_USER_20101015" LOB
(
"INFO"
)
STORE AS
(
TABLESPACE "TBS_USER_20101015" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10 CACHE

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1

FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
)
MONITORING ENABLE ROW MOVEMENT ;
CREATE UNIQUE INDEX "USER_20101015"."SYS_IL0000106446C00003$$" ON

"USER_20101015"."MDRT_19FCD$"
(
PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1

MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

TABLESPACE "TBS_USER_20101015" PARALLEL (DEGREE 0 INSTANCES 0) ;

I analyse structure of index "USER_20101015"."SYS_IL0000106446C00003$$",don't find
abnormal phenomenon.

Then I locate primary table,
1.
select sdo_index_owner,sdo_index_type,sdo_index_table,sdo_index_name
from all_sdo_index_metadata
where sdo_index_table='MDRT_19FCD$' and sdo_index_owner='USER_20101015'

SDO_INDEX_OWNER SDO_INDEX_TYPE SDO_INDEX_TABLE

SDO_INDEX_NAME
-------------------------------- -------------------------------- -----------------------------

--- --------------------------------
USER_20101015 RTREE MDRT_19FCD$

A446_IX1_A

2.
select owner,index_name,index_type,table_name from all_indexes where index_name='A446_IX1_A'

OWNER INDEX_NAME INDEX_TYPE

TABLE_NAME
------------------------------ ------------------------------ --------------------------- -----

-------------------------
USER_20101015 A446_IX1_A DOMAIN A3921

3.
select registration_id,table_name,owner from SDE.table_registry where registration_id='3921'

REGISTRATION_ID TABLE_NAME OWNER
--------------- ---------------------- ---------
3921 LOG_OPERATE_FEATURE USER_20101015

The table which result in error isï¼?LOG_OPERATE_FEATURE
Then I rebuid the spatial index of LOG_OPERATE_FEATURE, reuse ArcMap, the error don't appearã??

Is this error oralce bug? Does this error happen by accident?

Re: The problem of Reconcile:ORA-00604, ORA-13236,ORA-29400,ORA-06512

szcszc — Thu, 24 Feb 2011 12:15:03 GMT

Related diagnostic document�?

Document one�?Ora-13236 Internal Error In R-Tree Processing During Heavy DML [ID 443422.1]
Symptoms
A job runs periodically that in turn runs a procedure to truncate and insert rows into a table that has an associated spatial index. The data appears to populate the table but when a commit is performed the following error occurred:
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: [integrating nonleaf bucket (mdrbin_optmz_mem_ins_node)]
ORA-13236: internal error in R-tree processing: [wrong entry level (mdrbin_intgrt_nonleaf_bckt)]
ORA-06512: at "MDSYS.SDO_IDX", line 142
ORA-06512: at line 1
Cause
The cause of this problem has been identified and verified in an unpublished Bug 4570769.
This is only seen under heavy DML conditions.
As of the 10g release Spatial does not update the index until after the commit operation completes the transaction, and it updates the index in batches of operations determined by the SDO_DML_BATCH_SIZE value. This is implemented to increase DML performance.
It also explains why the errors are only seen at commit time.
For more details see the Spatial User's Guide and Reference release 10.2
Solution
Workaround
Use the following workaround until Bug 4729792 is fixed. Set SDO_DML_BATCH_SIZE = 1 for indexes having this problem.
SQL> connect mdsys
SQL> update SDO_INDEX_METADATA_TABLE
set SDO_DML_BATCH_SIZE = 1
where sdo_index_owner = <INDEX/SCHEMA Owner>
and sdo_index_name = 'INDEX_NAME';
SQL> commit;

References
BUG:5693812 - ORA-13236 INTERNAL ERROR IN R-TREE PROCESSING WHEN RUNNING PROCEDURE
BUG:5754537 - ORA-13236 WHILE SAVING A MASS OF DATAS