Skip to main content

Recover a Dropped Table from RMAN Backup

RMAN table recovery process from backup to auxiliary instance to Data Pump import

Introduction

Dropping the wrong table, or losing one to a bad batch job, doesn't have to mean restoring the entire database. RMAN's RECOVER TABLE command lets you recover a single table (or a small set of tables) to a specific point in time, using a temporary auxiliary instance rather than touching your production database directly. Once the table is recovered inside that auxiliary instance, RMAN exports it with Data Pump and imports it straight back into your target database. This gives you precise, table-level point-in-time recovery without any downtime for the rest of your environment.

In this article, we'll walk through a complete table recovery using an RMAN backup, from creating the test table through confirming the recovered data.

Prerequisites

  • A valid RMAN backup taken before the table was dropped.
  • Enough free disk space for the auxiliary instance. This depends on the size of the table and its tablespace, plus roughly 5–10 GB for the auxiliary instance itself.

Environment

Server test.oraeasy.com
Database Name testdb
Database Version 19.27

Step 1: Create a Test Table

To demonstrate the recovery, we'll first create a simple table and populate it with a few rows. In a real incident, this step doesn't apply, you'd already have the table and its data before the accidental drop.


SQL> DEF
DEFINE _DATE           = "23-AUG-25" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "testdb" (CHAR)
DEFINE _USER           = "TEST" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1927000000" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0" (CHAR)
DEFINE _O_RELEASE      = "1927000000" (CHAR)
SQL>
SQL> SHOW USER
USER is "TEST"
SQL>

SQL> CREATE TABLE company (
       emp_id   INT,
       name     VARCHAR(255),
       company  VARCHAR(255)
    );

Table created.

SQL> INSERT INTO company VALUES (101, 'Yash', 'WIPRO');

1 row created.

SQL> INSERT INTO company VALUES (102, 'Vijay', 'AIRTEL');

1 row created.

SQL> INSERT INTO company VALUES (103, 'Riya', 'TCS');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT COUNT(*) FROM company;

  COUNT(*)
----------
         3

Step 2: Take a Full RMAN Backup

The recovery depends entirely on having a valid backup that predates the drop. Here we take a full compressed database backup along with all archived logs, so RMAN has everything it needs to reconstruct the table at any point before the drop.


[oracle@test ~]$ RMAN TARGET /

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Aug 23 10:10:58 2025
Version 19.27.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: testdb (DBID=877553793)

RMAN>
RMAN> RUN
{
    ALLOCATE CHANNEL ch1 DEVICE TYPE DISK;
    BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT '/u01/rman/Fullback_%T_%U';
    BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG ALL FORMAT '/u01/rman/Archive_%T_%U';
    RELEASE CHANNEL ch1;
}  
using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: SID=58 device type=DISK

Starting backup at 23-AUG-25
channel ch1: starting compressed full datafile backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/testdb/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/testdb/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/testdb/undotbs01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/testdb/users01.dbf
channel ch1: starting piece 1 at 23-AUG-25
channel ch1: finished piece 1 at 23-AUG-25
piece handle=/u01/rman/Fullback_20250823_0341r31u_3_1_1 tag=TAG20250823T101317 comment=NONE
channel ch1: backup set complete, elapsed time: 00:06:34
Finished backup at 23-AUG-25

Starting backup at 23-AUG-25
current log archived
channel ch1: starting compressed archived log backup set
input archived log thread=1 sequence=12 RECID=2 STAMP=1209298467
input archived log thread=1 sequence=13 RECID=3 STAMP=1209298492
input archived log thread=1 sequence=14 RECID=4 STAMP=1209298520
input archived log thread=1 sequence=15 RECID=5 STAMP=1209298532
input archived log thread=1 sequence=16 RECID=6 STAMP=1209298534
input archived log thread=1 sequence=17 RECID=7 STAMP=1209302329
input archived log thread=1 sequence=18 RECID=8 STAMP=1209303449
input archived log thread=1 sequence=19 RECID=9 STAMP=1209304855
input archived log thread=1 sequence=20 RECID=10 STAMP=1209305991
input archived log thread=1 sequence=21 RECID=11 STAMP=1209307464
input archived log thread=1 sequence=22 RECID=12 STAMP=1209310175
input archived log thread=1 sequence=23 RECID=13 STAMP=1209314873
input archived log thread=1 sequence=24 RECID=14 STAMP=1209322002
input archived log thread=1 sequence=25 RECID=15 STAMP=1209896476
channel ch1: starting piece 1 at 23-AUG-25
channel ch1: finished piece 1 at 23-AUG-25
piece handle=/u01/rman/Archive_20250823_0441r3hb_4_1_1 tag=TAG20250823T102128 comment=NONE
channel ch1: backup set complete, elapsed time: 00:08:32
Finished backup at 23-AUG-25

Starting Control File and SPFILE Autobackup at 23-AUG-25
piece handle=/u01/app/oracle/fast_recovery_area/testdb/autobackup/2025_08_23/o1_mf_s_1209897017_nbllql1r_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 23-AUG-25

released channel: ch1

RMAN> exit

The backup set piece names shown here (Fullback_20250823_0341r31u_3_1_1 and Archive_20250823_0441r3hb_4_1_1) will be referenced automatically by RMAN later during the restore, so you don't need to note them down manually.

Step 3: Note the Current Timestamp

We need a precise "before the drop" timestamp to hand to RECOVER TABLE later. Capturing it now, right before dropping the table, gives us a clean recovery point.


SQL> SELECT TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "Current_Timestamp" FROM DUAL;

Current_Timestamp
-------------------
08-23-2025 10:32:05

Step 4: Drop the Table

This simulates the incident we're recovering from. In production, this could just as easily be an accidental DROP TABLE, a bad migration script, or a developer running cleanup against the wrong schema.


SQL> DROP TABLE test.company;

Table dropped.

SQL> SELECT TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "Current_Timestamp" FROM DUAL;

Current_Timestamp
-------------------
08-23-2025 10:37:07

Step 5: Create Directories for the Auxiliary Instance and Dump File

RECOVER TABLE spins up a temporary auxiliary instance to restore and recover the table in isolation, before exporting it. That auxiliary instance needs its own file location, and the Data Pump export needs a separate directory to write the dump file to.


[oracle@test ~]$ mkdir -p /u01/data
[oracle@test ~]$ mkdir -p /u01/dpump

Step 6: Run the RECOVER TABLE Command

This is the core of the process. RMAN restores the relevant datafiles into the auxiliary instance, recovers them up to the timestamp we captured in Step 3, exports the table with Data Pump, and then imports it back into the target database automatically.

Here's what each clause does:


Table to recover: RECOVER TABLE test.company
Point in time:    UNTIL TIME "TO_DATE('08/23/2025 10:32:00', 'MM/DD/YYYY HH24:MI:SS')"
Auxiliary location: AUXILIARY DESTINATION '/u01/data'
Dump file location: DATAPUMP DESTINATION '/u01/dpump'
Dump file name: DUMP FILE 'company_exp.dmp'

With the clauses explained, here's the full command and its output:


RMAN> RECOVER TABLE test.company
  UNTIL TIME "TO_DATE('08/23/2025 10:32:00', 'MM/DD/YYYY HH24:MI:SS')"
  AUXILIARY DESTINATION '/u01/data'
  DATAPUMP DESTINATION '/u01/dpump'
  DUMP FILE 'company_exp.dmp';

Starting recover at 23-AUG-25
using target database control file instead of recovery catalog
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=497 device type=DISK
RMAN-05026: warning: presuming following set of tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1

Creating automatic instance, with SID='hput'

initialization parameters used for automatic instance:
db_name=testdb
db_unique_name=hput_pitr_testdb
compatible=19.0.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_pdb_name_case_sensitive=false
_system_trig_enabled=FALSE
sga_target=1200M
processes=200
db_create_file_dest=/u01/data
log_archive_dest_1='location=/u01/data'
#No auxiliary parameter file used

starting up automatic instance testdb

Oracle instance started

Total System Global Area    1258288760 bytes

Fixed Size                     8939128 bytes
Variable Size                318767104 bytes
Database Buffers             922746880 bytes
Redo Buffers                   7835648 bytes
Automatic instance created

contents of Memory Script:
{
set until  time "TO_DATE('08/23/2025 10:32:00','MM/DD/YYYY HH24:MI:SS')";
restore clone controlfile;
sql clone 'alter database mount clone database';
sql 'alter system archive log current';
}
executing Memory Script

Starting restore at 23-AUG-25
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restored backup piece 1
output file name=/u01/data/testdb/controlfile/o1_mf_nblms0lh_.ctl
Finished restore at 23-AUG-25

sql statement: alter database mount clone database
sql statement: alter system archive log current

contents of Memory Script:
{
# set requested point in time
set until  time "TO_DATE('08/23/2025 10:32:00','MM/DD/YYYY HH24:MI:SS')";
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile  1 to new;
set newname for clone datafile  4 to new;
set newname for clone datafile  3 to new;
set newname for clone tempfile  1 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 4, 3;

switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u01/data/testdb/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 23-AUG-25
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/data/testdb/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/data/testdb/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/data/testdb/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/rman/Fullback_20250823_0341r31u_3_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/rman/Fullback_20250823_0341r31u_3_1_1 tag=TAG20250823T101317
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:04:09
Finished restore at 23-AUG-25

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=1209898456 file name=/u01/data/testdb/datafile/o1_mf_system_nblmvrso_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=1209898460 file name=/u01/data/testdb/datafile/o1_mf_undotbs1_nblmvt0b_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=1209898463 file name=/u01/data/testdb/datafile/o1_mf_sysaux_nblmvspf_.dbf

contents of Memory Script:
{
# set requested point in time
set until  time "TO_DATE('08/23/2025 10:32:00','MM/DD/YYYY HH24:MI:SS')";
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone "alter database datafile  4 online";
sql clone "alter database datafile  3 online";
# recover and open database read only
recover clone database tablespace  "SYSTEM", "UNDOTBS1", "SYSAUX";
sql clone 'alter database open read only';
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  1 online

sql statement: alter database datafile  4 online

sql statement: alter database datafile  3 online

Starting recover at 23-AUG-25
using channel ORA_AUX_DISK_1

Executing: alter database datafile 2, 5, 7, 8 offline
starting media recovery

archived log for thread 1 with sequence 25 is already on disk as file /u01/app/oracle/fast_recovery_area/testdb/archivelog/2025_08_23/o1_mf_1_25_nbll44m7_.arc
archived log for thread 1 with sequence 26 is already on disk as file /u01/app/oracle/fast_recovery_area/testdb/archivelog/2025_08_23/o1_mf_1_26_nblm8msp_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/testdb/archivelog/2025_08_23/o1_mf_1_25_nbll44m7_.arc thread=1 sequence=25
archived log file name=/u01/app/oracle/fast_recovery_area/testdb/archivelog/2025_08_23/o1_mf_1_26_nblm8msp_.arc thread=1 sequence=26
media recovery complete, elapsed time: 00:02:24
Finished recover at 23-AUG-25

sql statement: alter database open read only

contents of Memory Script:
{
   sql clone "create spfile from memory";
   shutdown clone immediate;
   startup clone nomount;
   sql clone "alter system set  control_files =
  ''/u01/data/testdb/controlfile/o1_mf_nblms0lh_.ctl'' comment=
 ''RMAN set'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
# mount database
sql clone 'alter database mount clone database';
}
executing Memory Script

sql statement: create spfile from memory

database closed
database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1258288760 bytes

Fixed Size                     8939128 bytes
Variable Size                318767104 bytes
Database Buffers             922746880 bytes
Redo Buffers                   7835648 bytes

sql statement: alter system set  control_files =   ''/u01/data/testdb/controlfile/o1_mf_nblms0lh_.ctl'' comment= ''RMAN set'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1258288760 bytes

Fixed Size                     8939128 bytes
Variable Size                318767104 bytes
Database Buffers             922746880 bytes
Redo Buffers                   7835648 bytes

sql statement: alter database mount clone database

contents of Memory Script:
{
# set requested point in time
set until  time "TO_DATE('08/23/2025 10:32:00','MM/DD/YYYY HH24:MI:SS')";
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile  7 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  7;

switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 23-AUG-25
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/data/HPUT_PITR_testdb/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/rman/Fullback_20250823_0341r31u_3_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/rman/Fullback_20250823_0341r31u_3_1_1 tag=TAG20250823T101317
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 23-AUG-25

datafile 7 switched to datafile copy
input datafile copy RECID=8 STAMP=1209898997 file name=/u01/data/HPUT_PITR_testdb/datafile/o1_mf_users_nblnns3s_.dbf

contents of Memory Script:
{
# set requested point in time
set until  time "TO_DATE('08/23/2025 10:32:00','MM/DD/YYYY HH24:MI:SS')";
# online the datafiles restored or switched
sql clone "alter database datafile  7 online";
# recover and open resetlogs
recover clone database tablespace  "USERS", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  7 online

Starting recover at 23-AUG-25
using channel ORA_AUX_DISK_1

Executing: alter database datafile 2, 5, 8 offline
starting media recovery

archived log for thread 1 with sequence 25 is already on disk as file /u01/app/oracle/fast_recovery_area/testdb/archivelog/2025_08_23/o1_mf_1_25_nbll44m7_.arc
archived log for thread 1 with sequence 26 is already on disk as file /u01/app/oracle/fast_recovery_area/testdb/archivelog/2025_08_23/o1_mf_1_26_nblm8msp_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/testdb/archivelog/2025_08_23/o1_mf_1_25_nbll44m7_.arc thread=1 sequence=25
archived log file name=/u01/app/oracle/fast_recovery_area/testdb/archivelog/2025_08_23/o1_mf_1_26_nblm8msp_.arc thread=1 sequence=26
media recovery complete, elapsed time: 00:00:07
Finished recover at 23-AUG-25

database opened

contents of Memory Script:
{
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/dpump''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/dpump''";
}
executing Memory Script

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/dpump''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/dpump''

Performing export of tables...
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   EXPDP> . . exported "TEST"."COMPANY"                            5.976 KB       3 rows
   EXPDP> Dump file set for SYS.TSPITR_EXP_hput_joFn is:
   EXPDP>   /u01/dpump/company_exp.dmp
   EXPDP> Job "SYS"."TSPITR_EXP_hput_joFn" successfully completed at Sat Aug 23 11:24:32 2025 elapsed 0 00:13:02
Export completed

contents of Memory Script:
{
shutdown clone abort
}
executing Memory Script

Performing import of tables...
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   IMPDP> . . imported "TEST"."COMPANY"                            5.976 KB       3 rows
   IMPDP> Job "SYS"."TSPITR_IMP_hput_Fjcx" successfully completed at Sat Aug 23 11:39:43 2025 elapsed 0 00:03:39
Import completed

Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/data/testdb/datafile/o1_mf_temp_nblnc15n_.tmp deleted
auxiliary instance file /u01/data/HPUT_PITR_testdb/onlinelog/o1_mf_7_nblnorlc_.log deleted
auxiliary instance file /u01/data/HPUT_PITR_testdb/onlinelog/o1_mf_6_nblnor5v_.log deleted
auxiliary instance file /u01/data/HPUT_PITR_testdb/onlinelog/o1_mf_5_nblnor4v_.log deleted
auxiliary instance file /u01/data/HPUT_PITR_testdb/onlinelog/o1_mf_4_nblnor40_.log deleted
auxiliary instance file /u01/data/HPUT_PITR_testdb/datafile/o1_mf_users_nblnns3s_.dbf deleted
auxiliary instance file /u01/data/testdb/datafile/o1_mf_sysaux_nblmvspf_.dbf deleted
auxiliary instance file /u01/data/testdb/datafile/o1_mf_undotbs1_nblmvt0b_.dbf deleted
auxiliary instance file /u01/data/testdb/datafile/o1_mf_system_nblmvrso_.dbf deleted
auxiliary instance file /u01/data/testdb/controlfile/o1_mf_nblms0lh_.ctl deleted
auxiliary instance file company_exp.dmp deleted
Finished recover at 23-AUG-25

RMAN> exit

The lines to watch for are the EXPDP and IMPDP sections near the end. They confirm the table was successfully exported from the auxiliary instance and imported back into the target database, exactly 3 rows in this case, matching what we had before the drop. Once the automatic instance is removed, all the temporary files RMAN created under /u01/data are cleaned up automatically.

Step 7: Verify the Recovered Table

With the recovery complete, a simple row count against the original schema confirms the table and its data are back in place.


SQL> SELECT COUNT(*) FROM test.company;

  COUNT(*)
----------
         3

Recovering a Table in a CDB/PDB Environment

If your database uses the multitenant architecture, you need to tell RMAN which pluggable database the table belongs to. Add the OF PLUGGABLE DATABASE clause after the table name:


RECOVER TABLE test.company OF PLUGGABLE DATABASE testpdb
  UNTIL TIME "TO_DATE('08/23/2025 19:55:00', 'MM/DD/YYYY HH24:MI:SS')"
  AUXILIARY DESTINATION '/u01/data'
  DATAPUMP DESTINATION '/u01/dpump'
  DUMP FILE 'company_exp.dmp';

Recovering the Export Without Importing It Automatically

Sometimes you don't want RMAN to import the table straight back into the same database, for example, if you want to review the data first, or import it into a different environment. Adding the NOTABLEIMPORT clause stops RMAN after the Data Pump export, leaving the dump file for you to import manually whenever and wherever you choose.


RECOVER TABLE 'TEST'.'COMPANY'
  UNTIL TIME "TO_DATE('08/23/2025 19:55:00','MM/DD/YYYY HH24:MI:SS')"
  AUXILIARY DESTINATION '/u01/data'
  DATAPUMP DESTINATION '/u01/dpump'
  DUMP FILE 'company_exp.dmp'
  NOTABLEIMPORT;

Thank you for reading!

I hope this content has been helpful to you. Your feedback and suggestions are always welcome. Feel free to leave a comment or reach out with any queries.

Abhishek Shrivastava

Oracle DBA with hands-on experience managing production Data Guard, RAC, GoldenGate, and APEX environments. I write practical, tested installation and troubleshooting guides based on real deployment work.

📧 Email: oraeasyy@gmail.com
🌐 Website: www.oraeasy.com

Follow Us

Location: Noida, Uttar Pradesh, India

Comments

Post a Comment

↑