nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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.

Najděte více příspěvků označených jako

Enterprise geodatabase

Accepted answers

MMBR

@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.

All comments

MMBR

@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';

R11416_SDE_ROWID_UK NORMAL UNIQUE GEO
SYS_IL0000525977C00018$$ LOB UNIQUE GEO
A3613_IX1 DOMAIN NONUNIQUE NULL

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

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.

MMBR

@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.

Připojte se ke konverzaci

Přihlášení členové mohou přispívat, sledovat aktualizace a další. Jste tu noví? Zaregistrujte si bezplatný účet.

Rychlé odkazy

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.