Steps to Restore Feature Classes / Tables from Oracle 19c Instance / Geodatabase 11.1

[JamesMorrison1]

Oracle 19 / Linux
Geodatabase 11.1
ST_GEOMETRY
ArcGIS Pro 3.1

Hello Everyone

In the coming days we need to discuss with our IT department who are responsible for the backup of our Oracle (geo) Databases two main cases:

CASE 1. Accidental Data Deletion or Modification
- Use Case: A user accidentally deletes or modifies critical data in a feature class / table need to be restored without affecting the entire database.

CASE 2. Schema or Object-Level Recovery
- Use Case: Specific schema or database object feature class / table need to be restored without affecting the entire database.

...My thinking:

[1] They would have to make a restore of the whole instance on a separate server at a point-in-time before the deletion and we could get the data back

[2] They could restore just the feature class / table to our Test Instance and we could get the data back.

Naturally, I know you can write (and we do) a Python script to export each feature class / table to a fGDB on say a nightly basis but we would like the IT department to handle the backups.

Given our requirements can anyone outline the process involved and any key points we have to be aware of if the IT department would handle the backup? 

With the basic question being should we the GEO team handle the backups and restore via Python scripts or the IT department should handle backups and restore via Oracle tools.

Feedback appreciated.

[MarceloMarques]

Hello @JamesMorrison1 

The Oracle Geodatabase backup is handled by the Oracle Database Administrator. The Oracle Database has much better tools to handle backup and recovery and Oracle is also capable to perform point in time recovery, the main tool used is Oracle RMAN - Recovery Manager for backup and recovery operations, it is also possible for the Oracle Database Administrator to setup the undo retention for 24 hours and not even need to perform Oracle RMAN operations to restore the data, Oracle can use the undo retention to perform flashback database to restore the data. The Oracle Database Administrator can also move or clone Pluggable databases without the need to perform any Oracle Data Pump Export and Import operation, each Pluggable database is a geodatabase, and 1 Oracle Container Database can have many Pluggable databases.

For more best practices visit my community.esri.com page.

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

FAQ - Frequent Asked Questions

How Load Large Featureclass Oracle Geodatabase
Pro Branch Version & Oracle Table Compression
Oracle eGDB SDE Repo Upgrade Using Oracle Restore Point | Download PDF Version
How to Move the Oracle Enterprise Geodatabase with the Oracle Data Pump Utility
How to Install Database Clients for ArcGIS
How to Install the Oracle Database Client for ArcGIS?

I hope this helps.

[forestknutsen1 · Accepted solution]

Is your SDE versioned?

For un-versioned data I would to the following:

1. Let the DBA handle the database backup
2. If you do need to restore an object or objects. Have the DBA restore the database to a new/different instance of Oracle. Then I would use Esri tools to move the data from the restored database to the production database. This could be a python script, FME, or ArcCatalog. Doing things this way ensures your SDE metadata tables don't get out of sync with the tables in the database. Then you will also have to deal with any grants you may have had on the refreshed tables. You can replace them with python, ArcCatalog, or SQL.