Upgrade Geodatabase 10.7 to 11.0 in combination with upgrade Oracle 18.6 to 19.16

[JamesMorrison1]

Hello Fellow EGDB'ers.

We are Planning to upgrade our Oracle Database and the Geodatabase and I was hoping on some first-hand feedback to achieve this goal using Oracle Data Pump EXPDP and IMPDP.

Current environment:                                     

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

Upgrade Environment:

• Geodatabase 11.0
• ArcGIS Pro 3.0.1
• Oracle 19.16.0.0
• ST_GEOMETRY
• Windows

If our current Oracle 18.6 Instance contained the following Schema – names not actually real….

SDE                                        SDE Tables
DATA1                                    1000 Feature Classes
DATA2                                    1000 Feature Classes
USER1                                    Simple User to read privileges feature classes in DATA1, DATA2
USER2                                    Simple User to read privileges feature classes in DATA1, DATA2
USER3                                    Simple User to read privileges feature classes in DATA1, DATA2

Planned Steps:

1. Create a Test Environment
2. Install empty oracle 19.16. instance on Test Environment
3. EXPDP dump full oracle 18.3 includes SDE, DATA1, DATA2, USER1, USER2, USER3
4. IMPDB dump to oracle 19.16. includes SDE, DATA1, DATA2, USER1, USER2, USER3
5. Add the shapelib file to the Test server with the oracle 19.16. instance
6. Upgrade Geodatabase directly to 11.0 Using ArcGIS Pro 3.0 in oracle 19.16. instance

Questions

1. Are these any obvious problems with the steps 1-5?
2. Have there been any issues with invalid objects (Procedures,…) in the SDE Schema between different Oracle versions and Geodatabase Versions?
3. Is it “better” to create an empty oracle 19.16. instance, install new (not upgrade) Geodatabase 11.0 and import data using Arcpy script?

Feedback much appreicated.

[MarceloMarques]

Questions

1. Are these any obvious problems with the steps 1-5?

[Marcelo]: the sde user must be the first one to be imported, then fix any invalid objects, only then import the data owner users.

2. Have there been any issues with invalid objects (Procedures,…) in the SDE Schema between different Oracle versions and Geodatabase Versions?

[Marcelo]: No, differenct Oracle versions will not cause invalid objects, but after you import each user you must check for invalid objects and fix any problems, if you encounter any ORA-xxxx errors during import with "dpimp" then search support.esri.com for that error.

3. Is it “better” to create an empty oracle 19.16. instance, install new (not upgrade) Geodatabase 11.0 and import data using Arcpy script?

[Marcelo]: yes, you can also perform this, create a new empty geodatabase and use ArcCatalog / Pro / DataInterop / FME / Arcpy Script / etc. to copy the data from the old geodatabase to the new geodatabase, but you will lose any traditional versions or branch versions if your geodatabase schema is registered as versioned, hence you will need to reconcile and post all versions, delete the versions and then perform a sde compress (traditonal versioning) , on the other hand with data pump export / import the geodatabase versions and the state of the data is preserved.

[MarceloMarques]

See below my blog posts.

Oracle eGDB SDE Repo Upgrade Using Oracle Restore Point | Download PDF Version
How to Move eGDB using Oracle Data Pump

[JamesMorrison1]

Super. Thanks for the link and documentation.

[JamesMorrison1]

Thank you very much for the detail answers - much appreciated. Follow-up to point 3: the current database is read-only no versions or versioning.

[MarceloMarques · Accepted solution]

Hi James, 

I made a few edits please get the latest version of the data pump white paper.

Oracle eGDB SDE Repo Upgrade Using Oracle Restore Point | Download PDF Version
How to Move eGDB using Oracle Data Pump

Thanks,

Marcelo

[JamesMorrison1]

Hello Marcelo

Thank you for the answers and documentation. Very helpful.

Given your experience do you think the same process would work if we cloned the 18c database to a TEST Server?

• Clone 18c PROD environment  to 18c TEST environment.
• Upgrade Oracle 18c to 19c on TEST Environment.
• Upgrade geodabase 10.7 to 11.0. on TEST environment.

So this process would not involve any EXPDP/IMPDP.

The clone process would be via RMAN or Flash Storage or clone the windoes virtual machine or ???…I would have to ask our IT department about what is  possible.

I realize that this clone process is more in the Oracle world but perhaps you have used it in combination with oracle and geodatabase upgrades and can say if it a good approach or not?

Thank you very much.

[MarceloMarques]

If you try to clone the server using an image backup ( vmware vm or physical ) then the hostname will change and this can cause issues for your Oracle Listener and you will have to make changes and troubleshoot any problems. That is why it's probably easier to create a new server and install Oracle 18c + Latest Patches and then clone the oracle database instance on the new server or simply get the oracle rman backups copy to the new server and perform a restore, after that you can then install Oracle 19c + Latest Patches on the new server ( Oracle software only ) and then use Oracle Database Upgrade Assistant DBUA to upgrade the oracle database from 18c to 19c. Now you pause for a minute to think what takes less work and what has less risk of problems, there are always few issues that appear after the DBUA upgrade, performance, bugs, etc. and this is well known by Oracle Database Administrators, then the answer, yes data pump will be the easier path to move the database and upgrade with less risk in my opinion. 

[JamesMorrison1]

Thank you for you for the feedback Marcelo greatly appreciated.

What we’ll do is copy PROD database to a new server and upgrade it to Oracle 19c.

• Clone 18c PROD environment  to 18c TEST environment. (clone the LUNs or copy the files, depending on database size)
• Upgrade Oracle 18c to 19c on TEST Environment. (Oracle Database Upgrade Assistant)
• Upgrade geodabase 10.7 to 11.0. on TEST environment. (ESRI tools)

[MarceloMarques]

Oracle Support Document 2543981.1 (Oracle 19c - Complete Checklist for upgrading Oracle 12c, 18c Container Database (CDB) to Oracle 19c Release using DBUA) can be found at: https://support.oracle.com/epmos/faces/DocumentDisplay?id=2543981.1