Cant Delete ArcSDE ORACLE

[irishadar]

hi,

We had full working Arcsde 10.4 and we needed to delete all the objects on the database , including arcsde's.

We didn't delete the users .

The goal was to import the fresh data from production.

In the process Arcsde became invalid and from now on we are not able to delete nor repair, so we deleted all object of the user sachems and then tried to delete from the SDE schema but left with 2 objects that cannot be deleted :GDB_ITEMS,ST_DOMAIN STATS.

this is the error we are getting :

[‎30/‎05/‎2016 12:02] Evgenia Yertsenkin:

SQL> drop table SDE.GDB_ITEMS cascade constraints;

drop table SDE.GDB_ITEMS cascade constraints

               *

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-21700: object does not exist or is marked for delete

SQL> drop type SDE.ST_DOMAIN_STATS force;

drop type SDE.ST_DOMAIN_STATS force

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-21700: object does not exist or is marked for delete

What should we do, do we need to drop and recreate the whole database including ArcSDE or is there a way that we can drop and create only ArcSDE .

it is a very critical issue for us and prompt answer will be much appreciated.

iris hadar

IAA

[Anonymous User]

It seems to be a problem similar to this one:

Ora-21700 Object Does Not Exist Or Is Marked For Delete  With ST_GEOMETRY

Try

DROP USER SDE CASCADE;

It this work, you can recreate again the SDE user/schema.  If not:

"Either restore the Oracle instance from backup (restore both the SDE user and the schema with st_geometry attributes) or drop the impacted users from the Oracle instance." at http://support.esri.com/technical-article/000009908

Regards

Jesús de Diego

[irishadar]

thank you for the prompt answer ,

we tried , it didn't work ,

this is the error msg:

SQL> DROP USER SDE CASCADE;

DROP USER SDE CASCADE

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-21700: object does not exist or is marked for delete

any idea what to do ?

[VinceAngelo]

Unfortunately, you've corrupted the Oracle instance beyond its ability to recover.  The only ways to clear this issue are:

1. Restore from backup, then delete all user-owned tables referencing ST_GEOMETRY or ST_RASTER objects, then drop all of SDE, or
2. Create a new instance and reload.

Since your goal was to repopulate, the second option will likely be easier.

- V

[irishadar]

Hi,

Thank you for the quick response.

Our local distributor suggested that we will try to use the  "Create Enterprise Geodatabase" Tool. We did so and it allowed us to delete the SDE user.

From that point we were able to create SDE user again and import the data correctly  from production.

Could you please advise us what is the correct procedure for reloading data from production, given that we don't want to delete the entire database?

These are the steps we believe should be done:

1. delete all user-owned objects from all schemas except SDE (using sql drop command).

2. we have two ways here to remove SDE schema :

    a. using the "Delete Schema Geodatabase" tool.

    b. using sql drop command - should we delete first the tables referencing ST_GEOMETRY or ST_RASTER and then delete those types or the order doesn't matter?

We would be happy to receive your opinion of all of the above.

Iris and Evgenia

IAA

[AlexanderBrown5]

Iris,

If I understand correctly, your normal workflow is to just reload all of your production data into this database?  Do you have an Oracle DBA on staff?  Can you utilize Oracle Database replication?

One option, if possible, delete this newer database and have your DBA clone production to have the same Database name, tns information, as your deleted database (destination of production data).

Another option is to write a python script that will loop through all datasets, feature classes, etc and truncate and append the new features (will work as long as there are no schema changes).

Can you elaborate on your desired procedure, database items (number of items, sizes), and anything else that can be helpful?

Regards,

Alex

[BirajaNayak]

You can take a look at the following procedure which generally we follow on testing environment:

1. Drop all user except sde with cascade option

2. Drop sde user with cascade option

3. Recreate all users including sde

4. Grant required permission

5. Reload the data by imp or impdp -   import  sde schema

6.  Import remaining schema.

Hope this helps.

- Biraja

[irishadar]

Thank you all for the suggestions,

It is not working with the “Drop cascade option”, it has dependencies in “table_registry”, and we had to move the data from one machine to another.

What we ended up doing was:

1. Disable all FK
2. Delete all layers that has entry in “table_registry” using python or arc Catalog.
3. Drop all objects from all the users
4. Drop SDE user cascade
5. Import all data from the other machine.
6. Rebuild FK

Best,

Iris hadar