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:
Upgrade Environment:
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:
Questions
Feedback much appreicated.
Solved! Go to 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
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.
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
Super. Thanks for the link and documentation.
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.
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
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?
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.
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.
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.
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