The geodatabase system table could not be created. [GDB_Items : Error (-51)]

[EmadAl-Mousa]

i am in the process of upgrading my ArcSDE geodatabase from version 9.3.1 SP2 to 10.1 SP1

my current environment setup:
IBM AIX 6.1
Oracle 11gR2 (11.2.0.3.8)
ArcSDE 10.1 SP1
*.* Remark: This Geodatabase Environment is used for Ericsson network engineer product.


when i ran the pre-requisite check up from ArcCatalog 10.1 SP1 it went through fine.

the issue occurs while doing the actual upgrade, with the following error output:

//-------------------------------------------------------------------------------------//
Executing: UpgradeGDB "Database Connections\XXX.sde" NO_PREREQUISITE_CHECK UPGRADE
Start Time: Thu Dec 26 09:38:41 2013
Paused the database for upgrade.
There are no other active connections.
User has privileges required to upgrade.
Connected RDBMS instance is not setup for ST_GEOMETRY configuration. [Unable to determine current version of ST_SHAPELIB. The latest ST_GEOMETRY and dependent libraries need to be copied to the correct software location. Refer to the ArcGIS help topics for more details.
]Running Pre-Requisite check (C:\Users\XXX\AppData\Roaming\ESRI\Desktop10.1\ArcCatalog\XXX.sde).
Instance supports XML type.
Server tables and stored procedures are up to date.
The geodatabase system table could not be created. [GDB_Items : Error (-51)]
The geodatabase system table could not be created. [GDB_Items : Error (-51)]Unpaused the database.
Failed to execute (UpgradeGDB).
Failed at Thu Dec 26 09:38:54 2013 (Elapsed Time: 13.00 seconds)

//--------------------------------------------------------------------------------------------------------------------------

the error is generic, did somebody faced this issue before ?

Regards,
Emad

[VinceAngelo]

Your description of the command and the command itself disagree.  This isn't
as surprising as disagreement between the command and the output.  Did you
edit the output of several different passes to post in this query?

The error indicates that you don't have sufficient permissions to upgrade the
9.x instance to 10.1.  I generally grant DBA privileges to the "SDE" user for
the brief duration of the upgrade (then immediately revoke them again).

If your user does have sufficient permissions, there may be some other reason
why the GDB_ITEMS table can't be created -- you should look in the ArcSDE
upgrade log and in the database alert log to see what may be happening.

- V

[EmadAl-Mousa]

Vince,

1. I DID NOT DO ANY EDITING regarding the error message, i have copy pasted the error i have received. your implication that i have edited the message is "disrespectfull" !!!!

2. SDE has 'DBA' permission temporarely before intiating the upgrade itself.

3. check the attached snap shot images.

4. i have checked the "GDBUpgrade.log" which is located localy on my machine: C:\Users\mousaem\AppData\Local\ESRI\Desktop10.1

It had the following entries:

[12/29/2013 10:01:30 AM] Checked domains.
[12/29/2013 10:01:30 AM] Checking replicas.
[12/29/2013 10:01:30 AM] Checked replicas.
[12/29/2013 10:01:30 AM] Checking historical markers.
[12/29/2013 10:01:30 AM] Checking historical marker (DEFAULT).
[12/29/2013 10:01:30 AM] Check succeeded (DEFAULT).
[12/29/2013 10:01:30 AM] Checked historical markers.
[12/29/2013 10:01:30 AM] Finished checking prerequisites.
[12/29/2013 10:01:30 AM] Updating server tables and stored procedures.
[12/29/2013 10:04:09 AM] Unpaused the database.
[12/29/2013 10:04:11 AM] Paused the database for upgrade.
[12/29/2013 10:04:11 AM] Updated server tables and stored procedures.
[12/29/2013 10:04:15 AM] Error(Check Datasets): The geodatabase system table could not be created. [GDB_Items : Error (-51)]
[12/29/2013 10:04:15 AM] Error(Check Datasets): The geodatabase system table could not be created. [GDB_Items : Error (-51)]
[12/29/2013 10:04:15 AM] Unpaused the database.

5. I have checked the Oracle Log File, nothing gives any indication that there is a problem.

The error is deceptive, as it indicates the failure of creating an ArcSDE system table â??GDB_ITEMSâ?� which means a privielge issue, although the SDE user has â??DBAâ?? privilege.

all tables GDB_ITEMS, GDB_ITEMTYPES,....etc have not been created under 'SDE' schema ...!

[VinceAngelo]

How can it be disrespectful to suggest that you edited the output when you did
edit the output, unless you want me to accept your Windows account is "XXX"?

I certainly didn't mean to imply that you intentionally altered the output to
confuse the issue, but cut/paste errors are common enough, and the possibility of
multiple upgrade attempts is significant, since, in my experience, if the first 9.x->10.x
upgrade attempt fails, subsequent upgrades will fail with a different error, and it's
likely that you'll need to roll back your database to a snapshot from before the first
failed attempt before attempting another upgrade (you'd also need to fix whatever
caused the initial failure after the rollback is complete).

If GDB_ITEMS can't be created, then the reason is often that the table already exists
(as in an unsuccessful upgrade without rollback), or that some other database reason
exists for the creation failure (quota or storage on SDE tablespace,...).  Only the alert
log will tell why (unless you had SDEVERBOSE set, in which case the database error
should have been reported in the upgrade log).

At this point you'd be best off working directly with Tech Support, since they're likely
to have more tools and techniques available than those of us who've only run a few
dozen 9.x-> 10.x upgrades.

- V

[KimPeter]

You might also make sure your $PATH variable is set to the location of the 10.1sp1 st_shapelib.so file.  Part of the error message indicates that the upgrade tool can't find it.

[EmadAl-Mousa · Accepted solution]

Let Me start my thread by saying.....after performing upgrade over the years to many ArcSDE Geodatabase instances...this was by far the most difficult one in terms of troubleshooting and understanding the root cause of the issue.

When i wanted to upgrade my ArcSDE Geodatabase from version 9.3.1 SP2 to 10.1 SP1. I ran the pre-requisite tool which scanns fully my geodatabase and stated the scan passed successfully and your geodatabase is ready for upgrade.

After running the actual upgrade, i faced the below error:

//â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??

]Running Pre-Requisite check (C:\Users\XXX\AppData\Roaming\ESRI\Desktop10.1\ArcCatalog\XXX.sde).
Instance supports XML type.
Server tables and stored procedures are up to date.
The geodatabase system table could not be created. [GDB_Items : Error (-51)]
The geodatabase system table could not be created. [GDB_Items : Error (-51)]Unpaused the database.
Failed to execute (UpgradeGDB).
Failed at Thu Dec 26 09:38:54 2013 (Elapsed Time: 13.00 seconds)

//â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??â??-

so, at the begining i though it could be something related to â??missing privilegesâ?? , which was NOT !!!

after more digging, i checked my local ArcGIS Desktop Logs (located under your user profile: C:\Users\youruserprofile\AppData\Local\ESRI\Desktop10.1)

from sde_setup.log file:

***********************************************************************************************************************

[Sun Jan 05 07:58:31 2014] SQL Stmt: <alter indextype SDE.st_spatial_index using SDE.st_domain_methods>
[Sun Jan 05 07:58:44 2014] St_Geometry type created or updatedâ?¦

[Sun Jan 05 07:58:48 2014] Error creating GDB_Items tableâ?¦
[Sun Jan 05 07:58:48 2014] ERROR Creating Geodatabase tables
, Error = -511
,EXT_Error = 29855
,EXT_ERROR1 = ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-10700: preference does not exist: CTXSYS.DEFAULT_LEXER
ORA-06512: at â??CTXSYS.DRUEâ??, line 160
ORA-06512: at â??CTXSYS.TEXTINDEXMETHODSâ??, line 366

************************************************************************************************************************

The errors seems to me to reference two issues (st_spatial_index issue) and (CTXSYS which is the Oracle Text).

The first thing i have performed is to re-validate my Oracle Text Component, which was performed successfully.

from sdedc_Oracle.log file:

***********************************************************************************************

PLS-00201: identifier â??SDE.GDB_UTILâ?? must be declared
ORA-06550: line 1, column 26:
PL/SQL: Statement ignored

*********************************************************************************************************************

after examining the GDB_items table in another ArcSDE Geodatabase version 10, i found out that the table has a â??domain indexâ?? associated with it being created by an indextype named st_spatial_index .

sooo BOOM i found the real issue which indextype is for some reason missing from my Geodatabase !!!

when i tried to create the index type using this query:

=========================================================================

CREATE OR REPLACE INDEXTYPE â??SDEâ??.â??ST_SPATIAL_INDEXâ?? FOR â??SDEâ??.â??ST_BUFFER_INTERSECTSâ?? (â??SDEâ??.â??ST_GEOMETRYâ??, â??SDEâ??.â??ST_GEOMETRYâ??, NUMBER), â??SDEâ??.â??ST_CROSSESâ?? (â??SDEâ??.â??ST_GEOMETRYâ??, â??SDEâ??.â??ST_GEOMETRYâ??), â??SDEâ??.â??ST_ENVINTERSECTSâ?? (â??SDEâ??.â??ST_GEOMETRYâ??, â??SDEâ??.â??ST_GEOMETRYâ??), â??SDEâ??.â??ST_ENVINTERSECTSâ?? (â??SDEâ??.â??ST_GEOMETRYâ??, â??SDEâ??.â??ST_GEOMETRYâ??, VARCHAR2),â??SDEâ??.â??ST_ENVINTERSECTSâ?? (â??SDEâ??.â??ST_GEOMETRYâ??, NUMBER, NUMBER, NUMBER, NUMBER, VARCHAR2), â??SDEâ??.â??ST_ENVINTERSECTSâ?? (â??SDEâ??.â??ST_GEOMETRYâ??, NUMBER, NUMBER, NUMBER, NUMBER), â??SDEâ??.â??ST_EQUALSâ?? (â??SDEâ??.â??ST_GEOMETRYâ??, â??SDEâ??.â??ST_GEOMETRYâ??), â??SDEâ??.â??ST_INTERSECTSâ?? (â??SDEâ??.â??ST_GEOMETRYâ??, â??SDEâ??.â??ST_GEOMETRYâ??), â??SDEâ??.â??ST_ORDERINGEQUALSâ?? (â??SDEâ??.â??ST_GEOMETRYâ??, â??SDEâ??.â??ST_GEOMETRYâ??), â??SDEâ??.â??ST_OVERLAPSâ?? (â??SDEâ??.â??ST_GEOMETRYâ??, â??SDEâ??.â??ST_GEOMETRYâ??), â??SDEâ??.â??ST_RELATEâ?? (â??SDEâ??.â??ST_GEOMETRYâ??, â??SDEâ??.â??ST_GEOMETRYâ??, VARCHAR2), â??SDEâ??.â??ST_TOUCHESâ?? (â??SDEâ??.â??ST_GEOMETRYâ??, â??SDEâ??.â??ST_GEOMETRYâ??),â??SDEâ??.â??ST_WITHINâ?? (â??SDEâ??.â??ST_GEOMETRYâ??, â??SDEâ??.â??ST_GEOMETRYâ??)USING â??SDEâ??.â??ST_DOMAIN_METHODSâ?? WITH LOCAL RANGE PARTITION;

*********************************************************************************************************************

the error faced is the following:

Image



so the inextype could not be created because there are missing â??operatorsâ?? for some reason ??!!!

so i compared the list of operators between my current geodatabase that i need to upgrade to another 10.0 geodatabase instance, using the sql query: select * from user_operators;

Image



after comparing the two lists, i found out the â??missingâ?? operators and i have created them.

In order to extract the sql definition of the operator use the following query as an example:

select dbms_metadata.get_ddl(â??OPERATORâ??,â??ST_WITHINâ??) from dual;

after creating all missing operators, try executing the previous sql query mentioned: â??CREATE OR REPLACE INDEXTYPE â??SDEâ??.â??ST_SPATIAL_INDEXâ??

then use the following sql, to check indextype was created:

select * from user_indextypes;

Image

I re-ran the upgrade again, and it was SUCCESSFUL !

my blog reference: http://geodatamaster.wordpress.com/2014/01/25/the-geodatabase-system-table-could-not-be-created-gdb_...