topic Re: user-schema geodatabase deprecation in ArcGIS Enterprise Questions

user-schema geodatabase deprecation

coolgis — Mon, 16 Oct 2023 11:00:34 GMT

We have Arcgis Enterprise 10.8.1 with Oracle geodatabase.

It has several user-schema geodatabases with existing database users.

I want to move the data from the user-schema geodatabases into the sde geodatabase.

From arcgis pro , I connected to userschema geodatabase as a schema user. and connected to sde schema as a sde user.

Can I copy data from user-schema geodatabases and paste into the sde geodatabase.

Is this process correct?

If I have ABC user with ABC schema. I just copy the data into SDE schema. and then assign the privileges to ABC user?

I find the esri documentation confusing. why esri doesn’t make it clear ?

why there is a need to create a new database user if one already exists ?

If there is no user schema , how do we distinguish between different users or type of data?

How does users access their data only and not being able to edit others data?

Is there any issues with all the users data owned by SDE?

Thanks

Re: user-schema geodatabase deprecation

George_Thompson — Mon, 16 Oct 2023 16:32:37 GMT

~~You can create a user schema within the main Oracle SDE geodatabase (i.e., DATA / GIS / Whatever). Then copy data in as that schema that you just created.~~

There are multiple options provided that you can do for the migration of the user-schema geodatabases: https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/manage-oracle/migrate-user-schema-geodatabases.htm as you noted.

What do you have in your instances tables in the SDE schema?

This will show how many user schema EGDB's are present.

Re: user-schema geodatabase deprecation

George_Thompson — Mon, 16 Oct 2023 16:24:30 GMT

@MarceloMarques anything to add here?

Re: user-schema geodatabase deprecation

MarceloMarques — Mon, 16 Oct 2023 16:37:22 GMT

The Esri User-Schema Geodatabase is now deprecated in the Oracle database.

You need to use Oracle Pluggable Databases going forward for consolidation.

Consolidation = 1 Oracle Database Instance hosts many Esri Geodatabases.

There is no automatic migration path from User-Schema Geodatabases.

Move user-schema geodatabases to stand-alone geodatabases in Oracle—ArcGIS Pro | Documentation

You cannot just copy the data from the User-Schema Geodatabase into the SDE Master Geodatabase because your Geodatabase Repository will still be an User-Schema Geodatabase.

You need to create a new Oracle CDB Container Database Instance, then create Pluggable Databases PDBs in the CDB.

Each PDB will be an Esri Geodatabase.

Then you need to create the sde user and create the Geodatabase Repository in the PDB, next create data owner users, and then use ArcGIS Pro to copy the data from the old User-Schema Geodatabase into the new Geodatabase.

You can find my database template scripts and more information in the links below.

community.esri.com - Mapping and Charting Solutions (MCS) Enterprise Databases Best Practices

Database Connections Best Practices

Tip : read the Production Mapping Guide Book for Oracle, those are generic and the best practices can be applied to any industry, and you can download my database template scripts for Oracle, the scripts show in detail how to setup the Oracle CDB, PDB, sde repository, data owner users, and editor and viewer users.

I hope this clarifies your question.

Re: user-schema geodatabase deprecation

coolgis — Tue, 17 Oct 2023 08:25:01 GMT

Thanks @George_Thompson @MarceloMarques

@George_Thompson Instances table in sde master schema database shows only one row with SDE.

Should this have all other user schema geodatabases listed here?

@MarceloMarques Esri documentation doesn’t mention that new database is required. Which should be clearly mentioned. I will have a look at your scripts later. Its much more advanced level than me. I want to use simple frontend tools to do the job.

Re: user-schema geodatabase deprecation

George_Thompson — Tue, 17 Oct 2023 11:23:04 GMT

Then you may not have user schema geodatabases configured right now. I believe that if you have them configured, the other geodatabases would be listed there.

I have to admit, it has been a while since I used a user-schema Oracle geodatabase.

Re: user-schema geodatabase deprecation

MarceloMarques — Tue, 17 Oct 2023 16:54:04 GMT

Move user-schema geodatabases to stand-alone geodatabases in Oracle—ArcGIS Pro | Documentation

the documentation mentions 3 options:

1. Move the data from the user-schema geodatabases into the sde geodatabase
2. Move all user-schema and sde geodatabase data to a new geodatabase
3. Move each user-schema geodatabase into its own stand-alone geodatabase

I recommend using the 3rd option and move to an Oracle Container Database and Oracle Pluggable Databases, each Pluggable Database is a Geodatabase.

I hope this clarifies.

Re: user-schema geodatabase deprecation

coolgis — Tue, 17 Oct 2023 23:00:00 GMT

Hi @George_Thompson @MarceloMarques

I checked both test and prod Oracle database Instances table and they have only one SDE row.

How else do I know that there is no user-schema geodatabases?

I can see different schemas in arcgis pro catalog when i connected as different schema owners sde connection file. and the tables in each schema are prefixed by schema name.

@MarceloMarques

If these user-schema geodatabases exist. I was planning to use option1.

Create new Oracle DB first as you recommended. Then create SDE geodatabase.

Then move the data into SDE and then assign the privileges to the DB users.

May I know why are you recommending option 3?

Does this increase the Oracle cost?

Thanks

Re: user-schema geodatabase deprecation

MarceloMarques — Wed, 18 Oct 2023 15:01:13 GMT

How else do I know that there is no user-schema geodatabases?

[Marcelo]: Run "select * from sde.instances;" if returns only the "SDE master" then you do not have any User-Schema Geodatabases.

I can see different schemas in arcgis pro catalog when i connected as different schema owners sde connection file. and the tables in each schema are prefixed by schema name.

[Marcelo]: this only means that you have multiple data owner users in the sde master geodatabase, it does not mean that you have user-schema geodatabases. This is a known best practice configuration to separate different data models. You have a geodatabase connection file for each data owner user and if you check the geodatabae connection properties you will see that the default sde version is SDE.DEFAULT meaning you are connecting to the SDE master. The Geodatabase can have multiple data owner users, but sometimes it makes more sense to separate into different geodatabases depending on the geodatabase versioning strategy and how the data will be accessed, edited, and consumed. There is no one rule fits all, this is a design preference, but if you visit my community.esri.com blog you will see that I recommend creating a geodatabase for each data model for different cartographic scales and one geodatabase for each ArcGIS Product workspace like ArcGIS Data Reviewer, ArcGIS Workflow Manager, etc., etc.

May I know why are you recommending option 3?

[Marcelo]: Oracle introduced the multi-tenant architecture in 12c, the Container Database CDB and Pluggable Databases PDB's.

In 12c and 19c we can still create Non-CDBs and CDBs.

Non CDB is the traditional standalone Oracle Instance, that is not Multi-Tenant.

In 21c and 23c we are able to only create CDBs going forward.

Therefore, customers need to move to CDBs and learn how to use Multi-Tenant and Pluggable Databases.

Oracle SE - Standard Editon License - up to 3 PDBs in the CDB
Oracle EE - Enterprise Edition License - 252 PDBs in the CDB

Oracle EE - Enterprise Edition License + Multi-Tenant License - 4096 PDBs in the CDB

Read more here: 19c Licensing Information (oracle.com)

19c is still the long-term release till 23c is released by the end of this year 2023.

23c will be the new long-term release.

19c is certified and supported with ArcGIS Pro 3.1 and ArcGIS Enterprise 11.1

23c will only be supported in a future release of ArcGIS.

I hope this clarifies your questions.

Re: user-schema geodatabase deprecation

coolgis — Wed, 18 Oct 2023 09:55:49 GMT

Hi @MarceloMarques

I don’t have access v$instances table. It shows message that this table doesn’t exist.

I have access just instances table , which has SDE row in it.

There is versions table which has Name: SDE and Description :Default SDE version as the values in the row.

I am using SQL Developer client to connect to Oracle 19c database.

When I check the geodatabae connection properties for each data owner sde file. It doesn’t have any field for sde version.

Thanks

Re: user-schema geodatabase deprecation

MarceloMarques — Wed, 18 Oct 2023 15:23:46 GMT

1. User-Schema Geodatabases

Sorry, v$instances was a typo, I meant sde.instances table.

Only the sysdba can query the v$instances system view.

select * from sde.instances;

You must be connected as sysdba or as the "sde" user to be able to query the sde.instances table.

If you have only the row named "SDE" then you only have the sde master geodatabase and you do not have any user-schemas geodatabases.

Example:

2. Geodatabase Connection Properties

Right click the geodatabase connection file

Choose "Connection Properties" - shows the database connection info

Choose "Geodatabase Connection Properties" - shows the geodatabase version info

These screenshots are from ArcGIS Pro but ArcMap dialogs are similar.

I hope this clarifies your questions.

Re: user-schema geodatabase deprecation

coolgis — Thu, 19 Oct 2023 08:12:21 GMT

Hi @MarceloMarques

Database query and ArcGIS pro screenshots match as yours.

You both are amazing and of great help.

Really appreciated.

Thanks