Unable to start SDE instance after Oracle export/import of SDE

[Anonymous User]

Original User: aguan@hess.com

We have a data vendor who delivers complete SDE data through the traditional Oracle export utility (10.2). We received an Oracle export file that has two schemas, SDE and PODs. The PODS is the business data. The SDE data is in ArcSDE 9.3.1 with no SPs. Following their instructions, I imported SDE schema first following by PODS. The imports went well with no problem at all. But when I tried to start the sde instance (on Linux) using "sdemon -o start", I got this error:

.....
SQL Stmt: <ALTER TYPE st_domain_methods COMPILE BODY>

WARNING: Type st_domain_methods did not compile!

ERROR in creating system stored procedures.
Error: -51

DBMS error code: 24344
ORA-24344: success with compilation error


Could not start ArcSDE -- Check Network, $SDEHOME disk, DBMS settings and dbinit.sde.

I checked SDE.st_domain_methods type body. It is invalid, and when I compiled it, it gave this error:

PLS-00538: subprogram or cursor 'ODCIINDEXSTART' is declared in an object type specification and must be defined in the object type body.

I see there are 3 overloaded ODCIINDEXSTART functions in SDE.st_domain_methods type header, and also there are 3 matched ODCIINDEXSTART functions in the type body so I don't know why it complains about mismatched header and body.

The vendor never saw this error before.

Any advice is greatly appreciated.

Allen

[Anonymous User]

Original User: jnderose

Hello Allen,

Following the import of the SDE schema it is a good idea to check for any invalid packages etc that may exist prior to proceeding with the import of the other schemas.  This may still be done following the import of all however.

Check for invalid objects:

COLUMN object_name FORMAT A30
SELECT owner,
       object_type,
       object_name,
       status
FROM   dba_objects
WHERE  status = 'INVALID'
ORDER BY owner, object_type, object_name;

Are you aware of the spatial type being used in this geodatabase?  If using ST_Geometry the following article may be of interest:

Problem:  Exporting and importing an Oracle SDE schema does not include public synonyms
http://support.esri.com/en/knowledgebase/techarticles/detail/34328

If these were not created prior to the import there is also the possibility that the incorrect ST_Functions are being referenced.  Oracle maintains their own ST Type for Oracle Spatial functions and these may have become referenced by error.  Connected as SDE, it may be a good idea to drop the existing ST_Functions followed by the creation of the new ones outlined in the KB and a recompile of the SDE schema.

Finally, has the following been granted:

GRANT EXECUTE on dbms_lock TO PUBLIC;
GRANT EXECUTE on dbms_pipe TO PUBLIC;

[Anonymous User]

Original User: aguan@hess.com

Jon,


Thanks for the help. I did created those public synonyms for SDE, but still have the problem of compile st_domain_type. Following the advice of some SDE admin friends, I repeated the install process from scratch (dropping sde and pods schemas first). The imports are all clean. Then I ran "exec dbms_utility.compile_schema('SDE')"  and "exec dbms_utility.compile_schema('PODS')", all objects under SDE and PODS are valid. I then ran sdesetup -o install, it did  install all those missing st_xxx public synonyms but many objects became invalid. So I reran "exec dbms_utility.compile_schema('SDE')"  and found all SDE objects become valid except one, st_domain_method type body, with this same error:Error(112,51): PLS-00538: subprogram or cursor 'ODCIINDEXSTART' is declared in an object type specification and must be defined in the object type body. I started to believe this is a SDE bug.

Allen