As of Oracle Database 12c and later, recovering a table from an RMAN backup is greatly simplified. Before, you would have to restore a copy of the database, then export and import the table data that you needed. That could take a significant amount of time to do, especially if you haven’t rehearsed it. As of 12c, you can in theory do it with one command: RECOVER TABLE. In practice, there’s a bit more to it than that.

Under the hood, RMAN takes the same steps that a DBA would have done prior to 12c. RMAN creates an auxiliary instance, restoring the required backup to the point in time specified. It then uses Data Pump to export the table from the auxiliary instance into the instance in which you’re trying to restore it. The database into which the table is being restored remains open and available for use throughout the entire process. The biggest overhead here is that you need to have enough headroom on the server to allow for the creation of this auxiliary instance.

One RMAN command to note before you get started is:

RMAN> SET AUXILIARY INSTANCE PARAMETER FILE TO '/path/to/initaux.ora'.

In theory, you shouldn’t need this, in practice, you may. From a real world example, I had to modify the value assigned to parameter PROCESSES to something greater than the default to allow the RECOVER TABLE command to succeed. Why Oracle doesn’t pick a suitable default, I do not know.

If your RECOVER TABLE command fails, you can read the output to see what parameters were set in the auxiliary instance spfile, then create your own from that. Below is a sample of the output from a failed RECOVER TABLE command, trimmed for the sake of brevity.

RMAN> RUN {
2>   RECOVER TABLE SCOTT.EMP
3>   OF PLUGGABLE DATABASE HR
4>   UNTIL TIME "TO_DATE('2025-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')"
5>   AUXILIARY DESTINATION '/u01/aux'
6>   REMAP TABLE SCOTT.EMP:EMP_20250101;
7> }
Starting recover at 2025-01-08 07:44:59
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1222 device type=DISK

Creating automatic instance, with SID='aadx'

initialization parameters used for automatic instance:
db_name=CDB1
db_unique_name=aadx_pitr_HR_CDB1
compatible=19.0.0
db_block_size=8192
db_files=1024
diagnostic_dest=/u01/app/oracle
_pdb_name_case_sensitive=false
_system_trig_enabled=FALSE
sga_target=10272M
processes=200
db_create_file_dest=/u01/aux
log_archive_dest_1='location=/u01/aux'
enable_pluggable_database=true
_clone_one_pdb_recovery=true

...

Great, we see what parameters are in use by default. Create a copy of this output as a file, modifying or adding whatever parameters are needed.

oracle@testdb $ vi /home/oracle/initaux.ora
db_name=CDB1
db_unique_name=aadx_pitr_HR_CDB1
compatible=19.0.0
db_block_size=8192
db_files=1024
diagnostic_dest=/u01/app/oracle
_pdb_name_case_sensitive=false
_system_trig_enabled=FALSE
sga_target=10272M
processes=500
db_create_file_dest=/u01/aux
log_archive_dest_1='location=/u01/aux'
enable_pluggable_database=true
_clone_one_pdb_recovery=true

Save that file, and attempt the RECOVER TABLE command again, this time setting the path to the parameter file for the auxiliary instance in the same RUN block as RECOVER TABLE.

RMAN> RUN {
2>   SET AUXILIARY INSTANCE PARAMETER FILE TO '/home/oracle/initaux.ora';
3>   RECOVER TABLE SCOTT.EMP
4>   OF PLUGGABLE DATABASE HR
5>   UNTIL TIME "TO_DATE('2025-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')"
6>   AUXILIARY DESTINATION '/u01/aux'
7>   REMAP TABLE SCOTT.EMP:EMP_20250101;
8> }

This time, when the auxiliary instance is created, it will respect the parameters that you have set in initaux.ora. This may otherwise allow the failing recovery to run to completion.

Unless you’re a fan of full table scans (what kind of DBA are you, anyway?) then make sure to gather statistics on the recovered table after it’s imported, as Data Pump under the hood excludes optimizer statistics, presumably to make the export and import run quicker.

SQL> select last_analyzed from dba_tables where table_name = 'EMP_20250101';
SQL> exec dbms_stats.gather_table_stats('SCOTT', 'EMP_20250101');
SQL> select last_analyzed from dba_tables where table_name = 'EMP_20250101';

A shortcoming of RECOVER TABLE, at least still in 19c, is that it doesn’t properly clean up after itself. After a successful recovery, check the following directories for now obsolete files. There is an enhancement request already filed for RECOVER TABLE to automatically remove some of these files, but until then manual cleanup is needed.

• $ORACLE_BASE/diag/rdbms
  • Contains an ADR directory for the auxiliary instance, rm -r it manually.
• $ORACLE_HOME/dbs
  • Contains three obsolete files, rm them:
    • spfile<auxiliary-sid>.ora
    • hc_<auxiliary-sid>.dat
    • lk_<auxiliary-sid>
• The path given for AUXILIARY DESTINATION
  • May contain files associated with the auxiliary instance that are now safe to remove.
  • If you didn’t specify a DATAPUMP DESTINATION in your recover command, this location may contain the Data Pump dump file if its import was not successful.

If there’s anything else that I’ve left out that you should be mindful of as it relates to RECOVER TABLE, please let me know in the comments.

Hope this saves you some head-scratching,
Dustin