In Oracle Database, when a DROP TABLE ... PURGE command is executed, the table is permanently deleted and does not go into the recycle bin, making FLASHBACK TABLE unusable.
However, you can recover the table by performing an RMAN incomplete recovery, restoring the database to a point in time before the DROP operation.
- 🔁 When Should You Use Incomplete Recovery?
- ⚠️ Prerequisites for Incomplete Recovery
- 🧩 Article Overview
- 🧱 Installing the SCOTT Schema (Sample Data)
- 💥 Example of a PURGE-DROP Operation
- 🔍 How to Check the Current SCN
- 🛑 Shutdown and Mount the DB in RMAN
- 🔁 Recovery to a Specific Timestamp (Example: 2025/08/03 15:44:00)
- 🔁 Recover by SCN or Log Sequence
- 🧭 Diagram: Incomplete Recovery Flow
- 📌 Key Notes
- ✅ Summary: Even PURGE-Dropped Tables Can Be Restored with RMAN!
🔁 When Should You Use Incomplete Recovery?
Incomplete recovery means rolling back the entire database to a past point in time.
✅ Common Use Cases and Why It Works
| Situation | Why Incomplete Recovery Works |
|---|---|
| ❌ Accidentally dropped a table | You can restore to just before the drop |
❌ Used PURGE to permanently delete | FLASHBACK won’t work—only RMAN can restore |
| ⚠ Mistaken mass UPDATE/DELETE | You can revert the data to the pre-modified state |
| 🦠 Data corruption or virus attack | Recover to a clean point in time to minimize damage |
🔎 Why Does This Work?
Oracle stores transaction history in REDO logs and archived logs.
By using those logs, RMAN can reconstruct the database state as of a specific timestamp, SCN, or log sequence.
⚠️ Prerequisites for Incomplete Recovery
Incomplete recovery cannot be performed unless the following are available:
- 🔹 RMAN backup (datafiles, control files, etc.)
- 🔹 Archived redo logs
✅ You must be in ARCHIVELOG mode.
✅ These files must have been taken before the erroneous operation (e.g., DROP).
🧩 Article Overview
- Installing the SCOTT schema
- Simulating a
DROP TABLE ... PURGEoperation - Performing RMAN incomplete recovery
- Using SCN or log sequence numbers as recovery targets
- Checking the current SCN
- How to shut down the DB via RMAN
🧱 Installing the SCOTT Schema (Sample Data)
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus / as sysdba
SQL> @utlsampl.sql
Click here for the scott sample schema.
💥 Example of a PURGE-DROP Operation
-- Create a test table
SQL> CREATE TABLE emp_copy AS SELECT * FROM emp;
-- Permanently delete the table
SQL> DROP TABLE emp_copy PURGE;
-- FLASHBACK will not work
SQL> FLASHBACK TABLE emp_copy TO BEFORE DROP;
ORA-38305: object not in RECYCLEBIN
🔍 How to Check the Current SCN
SQL> SELECT current_scn FROM v$database;
🛑 Shutdown and Mount the DB in RMAN
$ rman target /
RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;
🔁 Recovery to a Specific Timestamp (Example: 2025/08/03 15:44:00)
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE UNTIL TIME "TO_DATE('2025-08-03 15:44:00', 'YYYY-MM-DD HH24:MI:SS')";
RMAN> ALTER DATABASE OPEN RESETLOGS;
🔁 Recover by SCN or Log Sequence
-- SCN-based recovery
RECOVER DATABASE UNTIL SCN 123456789;
-- Sequence-based recovery
RECOVER DATABASE UNTIL SEQUENCE 127 THREAD 1;
You can find these values in
v$log_historyor thealert.log.
🧭 Diagram: Incomplete Recovery Flow
┌────────────────────────────┐
│ Production (Error Event)│
│ - DROP TABLE emp_copy PURGE │
│ - FLASHBACK fails │
└────┬──────────────────────┘
▼
┌────────────────────────────┐
│ Restore on Clone DB │
│ - SHUTDOWN IMMEDIATE │
│ - STARTUP MOUNT │
│ - RESTORE + RECOVER │
│ - OPEN RESETLOGS │
└────────────────────────────┘
[oracle@orcl19c admin]$ sqlplus scott/tiger
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Aug 3 03:50:16 2025
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Sun Aug 03 2025 03:50:02 -04:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> CREATE TABLE emp_copy AS SELECT * FROM emp;
Table created.
SQL> select empno,ename,job from emp_copy;
EMPNO ENAME JOB
---------- ---------- ---------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
7782 CLARK MANAGER
7788 SCOTT ANALYST
7839 KING PRESIDENT
7844 TURNER SALESMAN
7876 ADAMS CLERK
7900 JAMES CLERK
7902 FORD ANALYST
7934 MILLER CLERK
14 rows selected.
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
Session altered.
SQL> select sysdate from dual;
SYSDATE
-------------------
2025-08-03 03:53:37 ★Time before deletion
SQL> DROP TABLE emp_copy PURGE; ★Accidentally deleted a table
Table dropped.
SQL> select empno,ename,job from emp_copy;
select empno,ename,job from emp_copy
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> FLASHBACK TABLE emp_copy TO BEFORE DROP; ★Flashback table not possible
FLASHBACK TABLE emp_copy TO BEFORE DROP
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@orcl19c admin]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Sun Aug 3 03:55:08 2025
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1734656221)
RMAN> shutdown immediate
using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down
RMAN> startup mount
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 1543500144 bytes
Fixed Size 8896880 bytes
Variable Size 889192448 bytes
Database Buffers 637534208 bytes
Redo Buffers 7876608 bytes
RMAN> RESTORE DATABASE; ★Restore from backup
Starting restore at 03-AUG-25
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ORCL/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ORCL/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ORCL/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/ORCL/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/19.0.0/dbhome_1/dbs/01405kvg_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/01405kvg_1_1 tag=TAG20250803T034800
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 03-AUG-25
RMAN> RECOVER DATABASE UNTIL TIME "TO_DATE('2025-08-03 03:53:37', 'YYYY-MM-DD HH24:MI:SS')"; ★Recover by specifying a time before the table was dropped
Starting recover at 03-AUG-25
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:02
Finished recover at 03-AUG-25
RMAN> ALTER DATABASE OPEN RESETLOGS;
Statement processed
RMAN> select status from v$instance;
STATUS
------------
OPEN
RMAN> select empno,ename,job from scott.emp_copy; ★The table is back
EMPNO ENAME JOB
---------- ---------- ---------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
7782 CLARK MANAGER
7788 SCOTT ANALYST
7839 KING PRESIDENT
7844 TURNER SALESMAN
7876 ADAMS CLERK
7900 JAMES CLERK
7902 FORD ANALYST
7934 MILLER CLERK
14 rows selected
📌 Key Notes
| Item | Description |
|---|---|
| Backup Required | Pre-existing RMAN backup and archived logs required |
| RESETLOGS Needed | Resets log sequence; take a new backup afterward |
| Production Risk | Prefer recovery on a cloned environment |
| Use of SCN | Enables precise recovery and good for incident response |
✅ Summary: Even PURGE-Dropped Tables Can Be Restored with RMAN!
┌──────────────────────────────────────┐
│ Power of RMAN │
├──────────────────────────────────────┤
│ ✔ Recover tables dropped with PURGE │
│ ✔ Recover by time, SCN, or sequence │
│ ✔ Safely restore using a clone DB │
└──────────────────────────────────────┘
[reference]
9.2.2 RMAN Repository
コメント