topic Re: Schema out of date 10.7 client to 10.3.1 SDE Oracle 12c in Data Management Questions

Schema out of date 10.7 client to 10.3.1 SDE Oracle 12c

JoëlHempenius3 — Mon, 20 May 2019 11:11:17 GMT

My organization is planning to update all Arcgis Software from 10.3.1 to 10.7. I'm currently testing the 10.7 products and I've run into an error with using a small number of tables from my Enterprise Geodatabase from ArcCatalog 10.7 and Arcgis Server 10.7

When I try to get the properties of this table in ArcCatalog, I get a message: Schema out of date, Retry as owner or sdeadmin [THEMA_BAG.VBO_ADRESSEN][STATEID = 90368]

From Arcgis Server I get:

WARNING May 20, 2019, 12:01:54 AM The Layer:'Verblijfsobjecten' in Map:'Layers' is invalid. The base table definition string "THEMA_BAG.VBO_ADRESSEN" is invalid. Schema is out of date, Retry as owner or sdeadmin [THEMA_BAG.VBO_ADRESSEN][STATE_ID = 90368]. Geodiensten/Externe_Data.MapServer

Arcgis Server was installed with 10.3.1 and the mapservice was created using a 10.3.1 client, after installing and creating the mapservices, I upgraded to 10.7

When I run this code in Arcpy from ArcCatalog 10.7:
fields = arcpy.ListFields(r"E:\SDE\geodiensten_view@barp.sde\THEMA_BAG.VBO_ADRESSEN')
print (str(len(fields))), I get 0, so Arcpy can see the table, but it cannot get the fields.

Arcmap, ArcCatalog and Arcgis Server 10.3.1 can use the table without any error.

My Enterprise geodatabase contains 1000+ Featureclass, most of them can be used with 10.7 without any problem, there are only around 10 tables which result in the same error and 0 arcpy fields.

The user has only select on the table in both cases.

I use Oracle 12c 12.1.0.2.0

Running this code with ArcCatalog 10.3.1 gives:

>>> desc= arcpy.Describe('E:\SDE\geodiensten_view@barp.sde')

>>> desc.currentRelease

True

>>>

Normally, when I get this error, I would use Schema owner to connect in ArcCatalog and hit the table to fix the Schema out of date, but I do this with both client and SDE at the same version.

In this case I'm afraid I might break something on the 10.3.1 side when I use the Schema owner on ArcCatalog 10.7 to connect to this table. The Enterprise Geodatabase contains 1TB+ of data, so restoring the database from backup after I break something is possible, but time consuming.

So my question is: can I solve this Schema out of date with a 10.7 ArcCatalog and is it safe to do for the exiting 10.3.1 clients?

Re: Schema out of date 10.7 client to 10.3.1 SDE Oracle 12c

RobertBorchert — Mon, 20 May 2019 13:48:36 GMT

Right click on the database in Catalog and upgrade the database to 10.7

Re: Schema out of date 10.7 client to 10.3.1 SDE Oracle 12c

JoëlHempenius3 — Mon, 20 May 2019 14:19:38 GMT

That will definitly break things according to this page:

http://desktop.arcgis.com/en/arcmap/latest/manage-data/geodatabases/client-geodatabase-compatibility.htm

Until I've updated all my 10.3.1, I cannot update to SDE 10.7

Re: Schema out of date 10.7 client to 10.3.1 SDE Oracle 12c

RobertBorchert — Mon, 20 May 2019 14:24:06 GMT

I would suggest creating a 10.7 SDE database (as a copy of your 10.3.1 data) load it up and then see if it works. Using copy's of your 10.3.1 mxd's or pro projects.

Personally we are holding off on the upgrade until 10.7.1

Re: Schema out of date 10.7 client to 10.3.1 SDE Oracle 12c

JoëlHempenius3 — Mon, 20 May 2019 14:32:02 GMT

No I'm afraid that is not an option for me, because I need to have 10.3.1 Enterprise Geodatabase working with 10.7 clients. According to the linked page, a 10.3.1 client cannot use a 10.7 Enterprise Geodatabase, so this Enterprise Geodatabase upgrade has to be done after all clients are upgraded. Therefore, I will have a month between client upgrade and Enterprise Geodatabase upgrade and during this, these tables must be available.

Re: Schema out of date 10.7 client to 10.3.1 SDE Oracle 12c

RobertBorchert — Mon, 20 May 2019 14:58:25 GMT

Ouch,

Good luck. Fortunately we only have 6 licenses to worry about and five of us are on the same work group and the 6th is fully onboard.

Re: Schema out of date 10.7 client to 10.3.1 SDE Oracle 12c

Asrujit_SenGupta — Tue, 21 May 2019 05:08:37 GMT

Error: Schema is out of date, retry as owner or sdeadmin

Do you normally modify the schema from the DBMS end?

Maybe you can check and compare the schema from DBMS side and ArcCatalog 10.3.1 side, to check if there were schema changes. Though, you mentioned that ArcGIS 10.3.1 can work with these tables just fine. Still worth a check.

We normally use a Testing instance, to check everything is in order before such migration\upgrades. You could probably do the same. Maybe, just backup one or two of those problematic tables and experiment with that only.

Re: Schema out of date 10.7 client to 10.3.1 SDE Oracle 12c

JoëlHempenius3 — Tue, 21 May 2019 05:59:43 GMT

I have a DTAP for the Oracle Database, but I also have 50+ developers who cannot work when I break things in these databases, so I have to be very careful here. I will get another database for this upgrade, but this takes a lot of time.

Re: Schema out of date 10.7 client to 10.3.1 SDE Oracle 12c

PeterHanmore — Thu, 13 Jun 2019 19:17:07 GMT

Joël,

Did you find a solution to this problem? I just ran into this issue today, connecting from Desktop 10.6.1 to SDE 10.4.1 on Oracle. Most layers are fine but there are a few that throw the Schema out of date error. The state_id is different though - I'm getting 14670. Our DBA has connected to the problem layers as the schema owner and they draw fine for him. They also draw fine for me using the exact same .mxd file from ArcCatalog 10.5.1.

I did notice that when looking at the problem layers properties in ArcMap, the connection details were different from the other layers even though they were all added through the same database connection file (.sde).

Re: Schema out of date 10.7 client to 10.3.1 SDE Oracle 12c

MichaelVolz — Thu, 13 Jun 2019 19:24:57 GMT

Does the user only have select privileges on the data that throws the error? Have you tried granting the user additional privileges to see if you can get past this error? I know from experience that privileges that worked in older GIS versions did not work in newer versions and my org needed to grant some additional privileges. Not saying it will solve the problem, but something to check.

Re: Schema out of date 10.7 client to 10.3.1 SDE Oracle 12c

PeterHanmore — Thu, 13 Jun 2019 19:33:28 GMT

I have not asked our DBA to change any permissions up until now. The vast majority of layers in the schema will draw just fine and they all have been granted the same Oracle role (it is a read only role) so I'm not sure that that is the issue but it's worth checking.
Do you recall what additional privileges you had to grant?

Re: Schema out of date 10.7 client to 10.3.1 SDE Oracle 12c

MichaelVolz — Thu, 13 Jun 2019 19:44:58 GMT

See if this thread provides any insight into the issue you are seeing:

https://community.esri.com/message/680816-desktop-client-issues-in-105-when-using-oracle-sde-database

Re: Schema out of date 10.7 client to 10.3.1 SDE Oracle 12c

JoëlHempenius3 — Fri, 14 Jun 2019 05:12:10 GMT

Yes,

My local support team asked me to create a query layer of this oracle table and in this process I could not use the ID field als objectid. So there was something wrong with the ID field. The problem was that the ID field was a NUMBER type and this should be NUMBER(38,0) in Oracle. After this was fixed, the error messages disappeared in 10.7

Re: Schema out of date 10.7 client to 10.3.1 SDE Oracle 12c

MichaelVolz — Fri, 14 Jun 2019 10:58:01 GMT

So when you created the query layer you added an additional field in Oracle that was defined as number (38,0)? This step solved the original problem that you were facing of schema out of date?

Re: Schema out of date 10.7 client to 10.3.1 SDE Oracle 12c

JoëlHempenius3 — Fri, 14 Jun 2019 11:19:34 GMT

No, I did not create the query layer, because it triggered to look into the data type of this column. A Oracle specialist created a new column with NUMBER (38,0), copied the ID column values into the new column and renamed both columns so the ID column had the NUMBER (38,0) and then I fixed the schema out of date when I connected to this table with schema owner.

Re: Schema out of date 10.7 client to 10.3.1 SDE Oracle 12c

MichaelVolz — Fri, 14 Jun 2019 12:32:09 GMT

So if new records get added to this database table will you need a script that will create these values in this new field in order to avoid this error in the future?

Have you tried to perform a subquery (query of a query) on this query layer and then try sorting the query layer? This is an issue I have encountered, but it seems like the new field you created might avoid this scenario. In our scenario we used WireShark to determine that the SQL statement being sent to the Oracle database was being truncated so sorting of a subquery was failing.

Re: Schema out of date 10.7 client to 10.3.1 SDE Oracle 12c

MarcoBoeringa — Fri, 14 Jun 2019 13:56:49 GMT

Do note that the OBJECTID field added by e.g. the Add Incrementing ID Field tool (that can be used against tables not registered with a geodatabase to create a unique ID field), will normally also create a database sequence on the same field, to auto-update the objectids whenever data is added to the same table. So simply copying existing data from another OBJECTID field to a newly created field, is not the same if you do not manually also add such a sequence to the new field.

Re: Schema out of date 10.7 client to 10.3.1 SDE Oracle 12c

ReinholdSchnizer — Tue, 09 Jul 2019 15:08:34 GMT

Hello,
I had a similar problem with Arc* Clients (Versions 10.6.1 and 10.2.1) and Geodatabase 10.2.1 in Oracle 12.2.
We too have 1000+ feature classes and 350 registered views, created mostly with the old sdetools.
A few of them make problems with "schema out of date...", opening the view as owner + OK or recreating the view and new registration didn't help.

At least for one view I found the reason:
There was a difference in string lengths between Oracle data dictionary and SDE Repository.
The reason was a column with a string concatenation which included an implicit date to string conversion.
The result differed between languages (we use german clients and english servers), therefore the stored column details in SDE were different to the column as seen in the client.
For the analysis I needed days, but the solution was simple:
I added an explicit format in the expression, so Oracle data dictionary and SDE repository are always consistent.

In my case I had to drop/recreate the view and register it with 10.6.1, since registering views in 10.2.1 with arcpy isn't possible.
see https://community.esri.com/thread/210919-unregistering-a-spatial-view
Until now I only tried it in my testing environment, I am also waiting for an answer from ESRI/Partner about using a newer ArcCatalog for administration of an older Geodatabase.

Here is my SQL to select registered tables/views with different character string lengths:

select * from (
select c.owner,c.table_name,c.column_name,c.sde_type,c.column_size,d.data_type,d.data_length
from dba_tab_columns d, sde.column_registry c
where c.owner=d.owner and c.table_name=d.table_name and c.column_name=d.column_name
)
where column_size <> data_length
and data_type in ('VARCHAR2','NVARCHAR2')
and owner <> 'SDE'
;