Spatial index fails to create after online RMAN tape backup (Doc ID 1472298.1)

Last updated on JULY 02, 2012

Applies to:

Oracle Spatial - Version 11.2.0.1 to 11.2.0.3 [Release 11.2]
Information in this document applies to any platform.

Symptoms

 
If the customer runs an ONLINE RMAN backup, after it successfully completes, any attempt to create a Spatial index fails with

SQL> CREATE INDEX LIG_SG ON LIG (GEOMETRIE)  INDEXTYPE IS MDSYS.SPATIAL_INDEX;
CREATE INDEX LIG_SG ON LIG (GEOMETRIE)  INDEXTYPE IS MDSYS.SPATIAL_INDEX
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-00913: too many values
ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 10

If you generate an error stack trace on the ORA-00913 with the following

alter session set events '913 trace name ERRORSTACK LEVEL 3';

We can then compare the trace files between a successful index creation and a failure, which shows the following difference

Good:
PARSING IN CURSOR #329647440 len=240 dep=1 uid=55 oct=2 lid=55 tim=69450219690 hv=668622888 ad='7ff7a154310' sqlid='6rs8jkcmxns18'
 INSERT INTO SDO_INDEX_METADATA_TABLE VALUES (:1, :2, 0, 0, 64, -1, NLS_UPPER(:3), :4,     1, :5, :6, 0, 0, 2, 0, :7, :8, NULL, :9, :10, :11, NULL, NULL, NULL, 2, :12, 10, NULL, NULL, NULL, NULL, :13, 'VALID', :14, 1000, 0.0000000000 , NULL) -> The value 0.0 has a "dot" separator
END OF STMT
PARSE #329647440:c=0,e=40,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=0,tim=69450219689
EXEC #329647440:c=0,e=125,p=0,cr=1,cu=7,mis=0,r=1,dep=1,og=1,plh=0,tim=69450219911
STAT #329647440 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL  (cr=1 pr=0 pw=0 time=93 us)'
CLOSE #329647440:c=0,e=14,dep=1,type=1,tim=69450219977


Bad:
Error trace:
PARSE ERROR #91715360:len=240 dep=1 uid=55 oct=2 lid=55 tim=69797766118 err=913
 INSERT INTO SDO_INDEX_METADATA_TABLE VALUES (:1, :2, 0, 0, 64, -1, NLS_UPPER(:3), :4,     1, :5, :6, 0, 0, 2, 0, :7, :8, NULL, :9, :10, :11, NULL, NULL, NULL, 2, :12, 10, NULL, NULL, NULL, NULL, :13, 'VALID', :14, 1000, 0,0000000000 , NULL) -> -> The value 0,0 has a "comma" separator
XCTEND rlbk=0, rd_only=1, tim=69797766309

An RMAN OFFLINE backup works fine

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms