How to Perform Table-Level Recovery Using Oracle RMAN

English

When you accidentally delete (DROP) a table, you may want to restore only that specific table without reverting the entire backup. This article explains in detail the procedure for “Table Point-in-Time Recovery (Table PITR),” which uses Oracle RMAN to restore a table by specifying an SCN identified via Oracle SQL*Plus or other tools.

1. Introduction (Summary)

The trouble of “accidentally dropping an important table” is one of the most stressful scenarios for a DBA. Normally, recovering a table requires a full database recovery; however, by using the RECOVER TABLE feature in RMAN, you can restore specific tables to a past point in time without stopping the running database. This article introduces the reliable recovery procedure using Oracle RMAN in a 19c environment.

2. Conclusion / Quick Steps (To-Do List)

The shortest steps for table recovery (Table PITR) via RMAN are as follows:

  • Verify Backups: Confirm that a full backup and ARCHIVELOGs exist from before the deletion.
  • Identify Recovery Point (SCN): Identify the SCN immediately preceding the table drop.
  • Prepare Auxiliary Area: Secure temporary disk space for use by the auxiliary instance.
  • Execute Recovery Command: Run the RECOVER TABLE command in RMAN.
  • Verify Data: Confirm that the table has been re-imported into the production environment.

3. Background and Fundamentals: What is Table PITR?

Table Point-in-Time Recovery (Table PITR) is a feature introduced in Oracle 12c that allows for the recovery of specific tables or table partitions to a past point in time.

  • Automatic Generation of Auxiliary Instance: RMAN automatically stands up a temporary “auxiliary database” in the background.
  • Mechanism: RMAN restores only the SYSTEM tablespace, the undo tablespaces, and the tablespaces containing the target tables to the auxiliary side. After recovering to the specified SCN, it uses Data Pump (expdp/impdp) to write the target tables back into the production environment.

4. Procedures and Implementation: Prerequisites for Recovery

Before executing on a live machine, please verify the following prerequisites:

  • Target OS: Oracle Linux 7/8/9, etc.
  • Target Edition: Enterprise Edition only (Not available in Standard Edition 2).
  • DB Mode: Must be in ARCHIVELOG mode.
  • Privileges: Execute as a user with SYSDBA or SYSBACKUP privileges.
  • SCN Record: The CURRENT_SCN from before the drop operation is required.

5. Execution Example: Recovery Specifying an SCN

In the following example, the test1.emp_rman table is restored to SCN 2276451, which was the state before the deletion.

① Verification of Recovery Target (sqlplus)

First, verify the normal state before the accidental deletion and note the SCN.

-- Check the current SCN and make a note of it
SELECT CURRENT_SCN FROM v$database;

-- (Accidental Operation) Drop the table
DROP TABLE test1.emp_rman PURGE;

② Execution of Recovery Command via RMAN

Start RMAN and issue the command while specifying an auxiliary destination (/u01/app/oracle/auxdest).

# Connect to RMAN
rman target /

# Execute table-level recovery
RECOVER TABLE test1.emp_rman
  UNTIL SCN 2276451
  AUXILIARY DESTINATION '/u01/app/oracle/auxdest';

SQL Intent and Results:

With this command, RMAN deploys temporary control files and data files to the specified path and automatically imports the deleted table back to the production DB via Data Pump. Once completed, the temporary files are automatically deleted.

SQL> CREATE TABLE emp_rman AS SELECT * FROM scott.emp;  ★Create table

Table created.

SQL> set linesize 100 pagesize 100
SQL> select empno,ename,job,deptno from emp_rman
  2  order by deptno;

     EMPNO ENAME                          JOB                             DEPTNO
---------- ------------------------------ --------------------------- ----------
      7839 KING                           PRESIDENT                           10
      7782 CLARK                          MANAGER                             10
      7934 MILLER                         CLERK                               10
      7902 FORD                           ANALYST                             20
      7369 SMITH                          CLERK                               20
      7566 JONES                          MANAGER                             20
      7900 JAMES                          CLERK                               30
      7844 TURNER                         SALESMAN                            30
      7654 MARTIN                         SALESMAN                            30
      7521 WARD                           SALESMAN                            30
      7499 ALLEN                          SALESMAN                            30
      7698 BLAKE                          MANAGER                             30

12 rows selected.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.21.0.0.0
[oracle@v19single ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Mar 22 11:01:47 2025
Version 19.21.0.0.0

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

connected to target database: V19 (DBID=2957249400)

RMAN> BACKUP DATABASE;  ★Backup

Starting backup at 25-03-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=72 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/V19/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/V19/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/V19/undotbs01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/V19/users01.dbf
channel ORA_DISK_1: starting piece 1 at 25-03-22
channel ORA_DISK_1: finished piece 1 at 25-03-22
piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/013kvrd7_1_1_1 tag=TAG20250322T110159 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 25-03-22

Starting Control File and SPFILE Autobackup at 25-03-22
piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/c-2957249400-20250322-00 comment=NONE
Finished Control File and SPFILE Autobackup at 25-03-22

RMAN> exit


Recovery Manager complete.
[oracle@v19single ~]$ sqlplus test1/test1

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 22 11:02:38 2025
Version 19.21.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Last Successful login time: Sat Mar 22 2025 10:59:42 +09:00


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.21.0.0.0

SQL> SELECT CURRENT_SCN FROM v$database;

CURRENT_SCN
-----------
    2276451  ★Verify current SCN

SQL> drop table emp_rman purge;  ★DROP table

Table dropped.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.21.0.0.0
[oracle@v19single ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Mar 22 11:08:53 2025
Version 19.21.0.0.0

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

connected to target database: V19 (DBID=2957249400)

RMAN> RECOVER TABLE test1.emp_rman
2>     UNTIL SCN 2276451  ★Specify SCN before table DROP
3>     AUXILIARY DESTINATION '/u01/app/oracle/auxdest';

Starting recover at 25-03-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=72 device type=DISK
RMAN-05026: WARNING: the following set of tablespaces will be revitalized to the specified point in time

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

Creating automatic instance, with SID='sdxz'

initialization parameters used for automatic instance:
db_name=V19
db_unique_name=sdxz_pitr_V19
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=1472M
processes=200
db_create_file_dest=/u01/app/oracle/auxdest
log_archive_dest_1='location=/u01/app/oracle/auxdest'
#No auxiliary parameter file used


starting up automatic instance V19

Oracle instance started

Total System Global Area   1543500120 bytes

Fixed Size                     8925528 bytes
Variable Size                369098752 bytes
Database Buffers            1157627904 bytes
Redo Buffers                   7847936 bytes
Automatic instance created

contents of Memory Script:
{
# set requested point in time
set until  scn 2276451;
# restore the controlfile
restore clone controlfile;

# mount the controlfile
sql clone 'alter database mount clone database';

# archive current online log
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET until clause

Starting restore at 25-03-22
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=34 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/product/19.0.0/dbhome_1/dbs/c-2957249400-20250322-00
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/c-2957249400-20250322-00 tag=TAG20250322T110224
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/auxdest/V19/controlfile/o1_mf_mxw6yc53_.ctl
Finished restore at 25-03-22

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  scn 2276451;
# 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/app/oracle/auxdest/V19/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 25-03-22
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) in backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/auxdest/V19/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/auxdest/V19/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/auxdest/V19/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/product/19.0.0/dbhome_1/dbs/013kvrd7_1_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/013kvrd7_1_1_1 tag=TAG20250322T110159
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 25-03-22

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=1196420976 file name=/u01/app/oracle/auxdest/V19/datafile/o1_mf_system_mxw6yk9t_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=1196420976 file name=/u01/app/oracle/auxdest/V19/datafile/o1_mf_undotbs1_mxw6ykb5_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=1196420976 file name=/u01/app/oracle/auxdest/V19/datafile/o1_mf_sysaux_mxw6ykb1_.dbf

contents of Memory Script:
{
# set requested point in time
set until  scn 2276451;
# 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 25-03-22
using channel ORA_AUX_DISK_1

executing: alter database datafile 7 offline
starting media recovery

archived log for thread 1 with sequence 13 is already on disk as file /u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_13_1153872185.dbf
archived log file name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_13_1153872185.dbf thread=1 sequence=13
media recovery complete, elapsed time: 00:00:00
Finished recover at 25-03-22

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/app/oracle/auxdest/V19/controlfile/o1_mf_mxw6yc53_.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   1543500120 bytes

Fixed Size                     8925528 bytes
Variable Size                369098752 bytes
Database Buffers            1157627904 bytes
Redo Buffers                   7847936 bytes

sql statement: alter system set  control_files =   ''/u01/app/oracle/auxdest/V19/controlfile/o1_mf_mxw6yc53_.ctl'' comment= ''RMAN set'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area   1543500120 bytes

Fixed Size                     8925528 bytes
Variable Size                369098752 bytes
Database Buffers            1157627904 bytes
Redo Buffers                   7847936 bytes

sql statement: alter database mount clone database

contents of Memory Script:
{
# set requested point in time
set until  scn 2276451;
# 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 25-03-22
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=37 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) in backup set
channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oracle/auxdest/SDXZ_PITR_V19/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/product/19.0.0/dbhome_1/dbs/013kvrd7_1_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/013kvrd7_1_1_1 tag=TAG20250322T110159
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 25-03-22

datafile 7 switched to datafile copy
input datafile copy RECID=8 STAMP=1196421024 file name=/u01/app/oracle/auxdest/SDXZ_PITR_V19/datafile/o1_mf_users_mxw70hjo_.dbf

contents of Memory Script:
{
# set requested point in time
set until  scn 2276451;
# 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 25-03-22
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 13 is already on disk as file /u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_13_1153872185.dbf
archived log file name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_13_1153872185.dbf thread=1 sequence=13
media recovery complete, elapsed time: 00:00:00
Finished recover at 25-03-22

database opened

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

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/auxdest''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/auxdest''

Performing export of tables...
   EXPDP> Starting "SYS"."TSPITR_EXP_sdxz_oFuE":
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   EXPDP> . . exported "TEST1"."EMP_RMAN"                          8.703 KB      12 rows
   EXPDP> Master table "SYS"."TSPITR_EXP_sdxz_oFuE" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_sdxz_oFuE is:
   EXPDP>   /u01/app/oracle/auxdest/tspitr_sdxz_30059.dmp
   EXPDP> Job "SYS"."TSPITR_EXP_sdxz_oFuE" successfully completed at Sat Mar 22 11:11:19 2025 elapsed 0 00:00:36
Export completed


contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script

Oracle instance shut down

Performing import of tables...
   IMPDP> Master table "SYS"."TSPITR_IMP_sdxz_xFvp" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_sdxz_xFvp":
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   IMPDP> . . imported "TEST1"."EMP_RMAN"                          8.703 KB      12 rows
   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
   IMPDP> Job "SYS"."TSPITR_IMP_sdxz_xFvp" successfully completed at Sat Mar 22 11:12:00 2025 elapsed 0 00:00:31
Import completed


Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/app/oracle/auxdest/V19/datafile/o1_mf_temp_mxw6z20s_.tmp deleted
auxiliary instance file /u01/app/oracle/auxdest/SDXZ_PITR_V19/onlinelog/o1_mf_3_mxw70ltw_.log deleted
auxiliary instance file /u01/app/oracle/auxdest/SDXZ_PITR_V19/onlinelog/o1_mf_2_mxw70l3f_.log deleted
auxiliary instance file /u01/app/oracle/auxdest/SDXZ_PITR_V19/onlinelog/o1_mf_1_mxw70kp4_.log deleted
auxiliary instance file /u01/app/oracle/auxdest/SDXZ_PITR_V19/datafile/o1_mf_users_mxw70hjo_.dbf deleted
auxiliary instance file /u01/app/oracle/auxdest/V19/datafile/o1_mf_sysaux_mxw6ykb1_.dbf deleted
auxiliary instance file /u01/app/oracle/auxdest/V19/datafile/o1_mf_undotbs1_mxw6ykb5_.dbf deleted
auxiliary instance file /u01/app/oracle/auxdest/V19/datafile/o1_mf_system_mxw6yk9t_.dbf deleted
auxiliary instance file /u01/app/oracle/auxdest/V19/controlfile/o1_mf_mxw6yc53_.ctl deleted
auxiliary instance file tspitr_sdxz_30059.dmp deleted
Finished recover at 25-03-22

RMAN> exit


Recovery Manager complete.
[oracle@v19single ~]$ sqlplus test1/test1

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 22 11:12:16 2025
Version 19.21.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Last Successful login time: Sat Mar 22 2025 11:08:17 +09:00


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.21.0.0.0

SQL> set linesize 100 pagesize 100
SQL> select empno,ename,job,deptno from emp_rman  ★Table is restored
  2  order by deptno;

     EMPNO ENAME                          JOB                             DEPTNO
---------- ------------------------------ --------------------------- ----------
      7839 KING                           PRESIDENT                           10
      7782 CLARK                          MANAGER                             10
      7934 MILLER                         CLERK                               10
      7902 FORD                           ANALYST                             20
      7369 SMITH                          CLERK                               20
      7566 JONES                          MANAGER                             20
      7900 JAMES                          CLERK                               30
      7844 TURNER                         SALESMAN                            30
      7654 MARTIN                         SALESMAN                            30
      7521 WARD                           SALESMAN                            30
      7499 ALLEN                          SALESMAN                            30
      7698 BLAKE                          MANAGER                             30

12 rows selected.

6. Troubleshooting: Representative ORA Errors

Error CodeCauseAction
ORA-39002Import operation failureCheck if a table with the same name already exists in production.
RMAN-05026Insufficient capacity in the auxiliary areaCheck if there is enough free space in the AUXILIARY DESTINATION.
RMAN-06023Backup not foundCheck if a valid backup exists prior to the specified SCN.

7. Operations, Monitoring, and Security Notes

  • Resource Consumption: Since an auxiliary instance starts up, it temporarily consumes memory (SGA) and CPU. Avoid execution during periods of high business load.
  • Impact Scope: Because existing tables are overwritten, there is a risk of losing the most recent data after recovery.
  • Method of Reversion (Recommended): To be safe, use the REMAP TABLE option to restore the table with a different name, verify the data, and then swap it with the production table.

8. FAQ: Frequently Asked Questions

  • Q: How is this different from Flashback Table?
    • A: Flashback Table is fast because it uses UNDO data, but it cannot handle a DROP with PURGE. RMAN restores from a backup, making more reliable recovery possible.
  • Q: How much free space is required for the auxiliary area?
    • A: A rough estimate is the total size of the SYSTEM, SYSAUX, UNDO, and the tablespaces containing the target tables.
  • Q: Do I need to stop the database during execution?
    • A: No. It can be executed while the production database is started (OPEN).

9. Summary

  • Oracle RMAN is an effective last resort for rescue from a DROP TABLE.
  • By specifying an SCN, you can return to a pinpoint “moment” in time.
  • RMAN handles the management of the auxiliary instance automatically, reducing DBA workload.
  • Always check the disk capacity of the auxiliary area before execution.

This article targets Oracle Database 19c (screens and default values may differ for other versions).

[reference]
Oracle Database Backup and Recovery Reference, 19c

How to Perform Complete Recovery with RMAN up to the Point of Failure (with Diagram & RESETLOGS Explained)
In Oracle Database operations, data file corruption, accidental deletion, or disk failure can occur at any time.When suc…
oracle-master00000.com
2025.08.14

コメント

Copied title and URL