DBAtlas · Database operations atlas

Recover a single table to a past point in time from an RMAN backup (RECOVER TABLE)

OracleRMAN

Command

# Restore under a new name (the original table is not touched):
RECOVER TABLE hr.employees
  UNTIL TIME "TO_DATE('2026-01-15 03:00:00','YYYY-MM-DD HH24:MI:SS')"
  AUXILIARY DESTINATION '/u03/aux'
  REMAP TABLE hr.employees:employees_rec;
# Multitenant: a table inside a PDB
RECOVER TABLE hr.employees OF PLUGGABLE DATABASE <pdb>
  UNTIL SCN 12345678
  AUXILIARY DESTINATION '/u03/aux'
  REMAP TABLE hr.employees:employees_rec;
# Only produce a Data Pump dump, do not import:
RECOVER TABLE hr.employees
  UNTIL SCN 12345678
  AUXILIARY DESTINATION '/u03/aux'
  DATAPUMP DESTINATION '/u03/dump'
  DUMP FILE 'employees_rec.dmp'
  NOTABLEIMPORT;

What it does

RMAN recovers an automatic auxiliary instance to the requested time, exports the table (or partition) with Data Pump and imports it into the target database under a different name with REMAP; the auxiliary instance is then removed.

When to use it

To recover the data of a single table that was deleted/updated by mistake or dropped with PURGE, beyond what Flashback Table/Query undo retention allows.

Caution

AUXILIARY DESTINATION needs free space for SYSTEM, SYSAUX, UNDO and the table's tablespace; the operation can take hours. Without REMAP TABLE the operation fails if a table with the same name exists; always use REMAP or NOTABLEIMPORT to avoid overwriting the original. Tables in the SYS/SYSTEM schemas and in the SYSTEM/SYSAUX tablespaces cannot be recovered this way. Requires Enterprise Edition.

Open in DBAtlas →

Related