Restoring a single table from a OCI Cloud Manager backup

When upgrading an Oracle E-Business Suite environment to both 23ai (see here) and leveraging the new, pre-built integration between EBS and Oracle APEX (https://www.linkedin.com/posts/johannes-michler-099892ab_enhanced-integration-with-ebs-122-and-apex-activity-7265427474598043648-lIsr) I accidentally screwed up the FND_ORACLE_USERID table of my testing environment.

To avoid having to do a new Production to test clone to try again I investigated the (quite new) feature of RMAN to restore a single table within my PDB. Fortunately, I had taken a backup using E-Business Suite Cloud manager a few days ago; and since that table doesn't change a lot this backup should have all the relevant data.

Let's see how the restore was performed:

Connecting to OCI Backup Service

Since the instance where the problem occurred was a testing instance that is not regularly backed up and the Cloud Manager tooling, I used to create the backup a few days ago, only created a temporary connection, I first had to setup the OCI Cloud Backup module.

After downloading the module from http://www.oracle.com/technetwork/database/availability/oracle-cloud-backup-2162729.html the first step was to create a installation and configuration of the tool. This is done with the following command:

java -jar oci_install.jar -host https://objectstorage.eu-frankfurt-1.oraclecloud.com -pvtKeyFile johannes.key.pem -pubFingerPrint a0:... -uOCID ocid1.user.oc1..yyyy -tOCID ocid1.tenancy.oc1..zzzz -walletDir /tmp/opc/wallet/ -libDir /tmp/opc/lib/ -bucket TESTD_23a1DB

Connecting to rman

The next step was to verify the backup. In my case since the control file still knew everything about the backup that was straightforward. But in some situations you may have to look into cataloging the backup first as described here.

To verify the backup I ran:

SET ENCRYPTION ON IDENTIFIED BY 'Top-SeCrEt2024' ONLY;
run{
ALLOCATE CHANNEL CH1 TYPE SBT PARMS='SBT_LIBRARY=/tmp/opc/lib/libopc.so, SBT_PARMS=(OPC_PFILE=/u01/app/oracle/product/23.0.0.0/dbhome_1/dbs/opcCDBTESTD.ora)';
RESTORE DATABASE PREVIEW SUMMARY FROM TAG='TESTD250117_23A1';
RESTORE DATABASE VALIDATE HEADER FROM TAG='TESTD250117_23A1';
RELEASE CHANNEL CH1;
}

Running recover table

After connectivity and access is established the actual restore is very easy. The documentation for this is https://docs.oracle.com/en/database/oracle/oracle-database/23/bradv/rman-recovering-tables-partitions.html#GUID-B8245791-9B75-4AE7-ACE9-D9EDBC2DACC0.

The only challenge I faced was that the command does not allow to be executed in a run block with explicitly allocated channels (see 2105325.1). Instead, I had to configure the device first:

configure channel device type sbt_tape FORMAT '%U' PARMS='SBT_LIBRARY=/tmp/opc/lib/libopc.so, SBT_PARMS=(OPC_PFILE=/u01/app/oracle/product/23.0.0.0/dbhome_1/dbs/opcCDBTESTD.ora)';
CONFIGURE DEVICE TYPE sbt PARALLELISM 4;

Then I was able to run the actual recover command as follows:

recover table applsys.FND_ORACLE_USERID 
OF PLUGGABLE DATABASE TESTD
UNTIL SCN 12489980593436
auxiliary destination '/u01/install/custom/aux' 
remap table 'APPLSYS'.'FND_ORACLE_USERID':'OLD_FND_ORACLE_USERID';

Let's break this down:

• First, I had to provide the table to be restored
• Then it is important to specify the "of pluggable database" clause; otherwise, the command will search for the table in the CDB
• then you must of course provide the scn or sequence or "as of" date to which you want to recover
• furthermore, you need to provide a (aux) location where the restore/recovery takes place. In my case for a (small) part of the E-Business suite that location used up to 75 GB of space during the restore
• Finally, you can remap the table in order to not overwrite the original table. It's also possible to just create a dump file that can then be imported with impdb.

After starting the command, it took roughly 30 minutes where the rman command created a temporary instance, restored the relevant datafiles, recovered the relevant table, dumped it using expdb and finally imported it under a new name into my actual database.

export of the relevant table from the aux database

After the process completed I was able to access my lost/broken data again 🙂

Accessing the remapped and restored table data

Summary

The process allows restoring a single (or some) tables without restoring the entire database. Of course if you "know and plan" before there are more efficient ways as well (flashback or just a create table as select copy). But as a last mean, the approach shown here was very helpful.

Do you need support running your Oracle E-Business Suite? With extensive experience in the Oracle ecosystem, PROMATIS ensures efficient data backup while also optimizing your Oracle Cloud environment strategically.