Oracle and Geodatabase Migration (18.6 - 19.6 / 10.7 to 11.1) - Created INVALID Indexes (Axxx_IX1)

[JamesMorrison1]

Hello Everyone - we have been trying the following Oracle and Geodatabase upgrade:

Current environment:                                     

• Geodatabase 10.7.
• ArcGIS Pro 2.9.4                                                         
• Oracle 18.6.0.0 
• ST_GEOMETRY
• Windows                                                    

Upgrade Environment:

• Geodatabase 11.1
• ArcGIS Pro 3.1.0
• Oracle 19.16.0.0
• ST_GEOMETRY
• Linux

The installation of the libst_shapelib.so on Linux was ok.
The upgrade geodatabase ran without error.

However if I check the following: 
SELECT * FROM DBA_OBJECTS WHERE STATUS!= 'VALID' ORDER BY OWNER, OBJECT_TYPE, OBJECT_NAME;

It looks like all the Axxx_IX1 indexes are now  invalid.

Can anyone help with what our next steps would be to make them valid? 

Much appreciated.

[MarceloMarques]

@JamesMorrison1 

1. check if there are invalid objects, then recompile the invalid objects, for more information read my white paper.
community.esri.com - Oracle eGDB SDE Repo Upgrade Using Oracle Restore Point

2. if there are invalid indexes then you can rebuild the indexes using SQL statement to fix the issue.

You can also rebuild the normal indexes and/or the Spatial Indexes using ArcGIS Pro GP Tool.
Rebuild Indexes (Data Management)—ArcGIS Pro | Documentation

In this other thread that I have replied, I wrote more about indexes, and that can help you to understand more about the indexes in an Oracle Geodatabase.   Solved: Rebuild Indexes after moving tablespace - Esri Community

[JamesMorrison1]

Hello Marcelo

Thanks for the quick reply much appreciated. Your documentation is my normal first port of call.

1. I have been using EXEC dbms_utility.compile_schema( 'SDE', compile_all => FALSE ); to recompile the objects as you suggest. One open question that I have not found the answer too so far in my google search - Can you compile objects in other Schema using the as sysdba account?

So for example, I log in as user JAMES as SYSDBA (not real!!) I could run the following:

• EXEC dbms_utility.compile_schema( 'SDE', compile_all => FALSE );
• EXEC dbms_utility.compile_schema( 'GIS1', compile_all => FALSE );
• EXEC dbms_utility.compile_schema( 'GIS2', compile_all => FALSE );
•  

Or must I log in as the SDE User, the GIS1 User and the GIS2 User?

2. Ok thanks I will try a index rebuild

3. I read the reply you wrote on rebuild indexes after moving tablespace - one point I may have missed the person who posted said that their data wa not registered as versioned and why do you get a A-named index? 

We have the same case our data is not versioned so no A or D-Tables but we have three indexes per feature class and tablespace = null for one is this ok?

SELECT * FROM dba_indexes where table_name = 'JAMES_123' and table_owner = 'JAMES'; 

1. R11416_SDE_ROWID_UK NORMAL UNIQUE GEO
2. SYS_IL0000525977C00018$$ LOB UNIQUE GEO
3. A3613_IX1 DOMAIN NONUNIQUE NULL

Once again thanks for your feedback it always goes into more helpful detail.

[MarceloMarques · Accepted solution]

@JamesMorrison1 - I am glad to know that the white papers and documentation that I wrote and keep current and relevant is helping folks like you. I appreciated the feedback.

About your questions.

1. yes, sysdba can compile objects in another schema.

2. Axxx_IX1 index in a featureclass is for the Spatial Index, so you get one even if the featureclass is not registered as traditional versioning.

I hope this clarifies. 😊

If you like an answer, then I ask you, please give "kudos" and mark it as "Accept as Solution", that will help others as well.

[JamesMorrison1]

Hello Marcelo - I’ve been working with Oracle / SDE since 2005 and your documentation is by far the best I refer too. I just wish I found it sooner. Thanks for your answers. 

[MarceloMarques]

@JamesMorrison1 - Thank You. 😀

[Bud]

FYI - I noticed this 2023 Dev Summit session recording: Upgrading ArcGIS Enterprise. I haven't watched it, but it might be useful to people viewing this post.