# Fung's DBA World

## Recovering a Dropped Table in Oracle and DB2

March 03, 2016

When DBA dropped a table accidentally, always need a quick way to recover the dropped table. What I mean “quick way” is not only recovering the dropped table as soon as possible, but also should not affect other applications in the same database.

### 1. Recover a dropped table in Oracle

If your database version is above 10g, and the recyclebin is enable, you can just simply use recyclebin feature to UNDO the dropped table.

#### 1.1 explanation of recyclebin

You can find more details in “recyclebin” view:

Recyclebin can be purged, if you don’t want to keep the dropped in recyclebin, also can issue drop table command with purge options:

Enable and disable recylebin feature:

Multiple version in recyclebin, you may drop a table in couple of times, this action will generate multiple object with the same original name in recyclbin.

As we can see, each dropped table T is assigned a unique name in the recyclebin, if you flashback the dropped table at that time, the most recently dropped table with that original name is retrieved from recyclebin, you also can indicate the unique name to specify the exactly table you want to recover.

Below action should be the most recently dropped table, which unique name is “BIN$LSLiyo5tB/LgU8g4qMDT/Q==$0”, because we recover a same original name, you cannot recover it without indicate the “rename” option, or you should get “ORA-38312: original name is used by an existing object” error

### 2. Recover a dropped table in DB2

Recovery dropped in DB2 is somewhat more complex than in Oracle. In Oracle 10g and above, if you enable recyclebin feature(default enabled), you can easily take recovery without any downtime, no rman recovery need.
But in DB2, you need a backup image to accomplish this task. With backup image, restore the tablespace which the dropped table reside on, and import the data from recover data. Here’s an example.

First, try to find out whether the tablespace support “DROPPED TABLE RECOVERY” or not

Second, find out the dropped table

From the output, we can retrieve the dropped table name, which tablespace did the table reside on, and the DDL of the table.
Third, restore a database-level or tablespace-level backup image taken before the table was dropped.

Next, create an export directory to which files contaning the table data are to be written, and rollforward to a PIT.

Next, re-create the dropped table (DDL can obtain from list history command ).

Finally, we can import the data from restore directory.

### 3. Supplemental

There’s still one mistaken operation that always happens. Delete table records happens frequently, maybe you delete some records, or you delete all the table records. So, what’s the difference between DB2 and Oracle database while recovering “deleted records”?

#### 3.1 recovering deleted records in Oracle

It’s very simple to use flashback query feature to undo the deleted data in tables. If you didn’t commit the delete command, you also can rollback the delete statement easily.

Now, let’s delete table data and commit it.

Undo the delete operation by using flashback query feature:

Data is back. If you don’t know what the exactly deletion time, you can adjust the timestamp until find the proper one. Flashback query feature is based on undo tablespace, if undo tablespace or the parameter undo_retention (value by seconds) not big or long enough, you also may need take a RMAN recovery to undo the detetion.

#### 3.2 recovering deleted records in DB2

DB2 recovery technology is always more complex than Oracle. Oracle can just use flashback feature to recover deleted records. And auto-commit is enable in DB2 by default, if you didn’t disable auto-commit, you cann’t even use rollback to undo the delete command.

In DB2, the best way to recover a deleted table is redirect restore. Let’s simulate a circumstance to explain it.

I didn’t take any backup after I create this table. I use the previous backup in step 2.

Generate the redirect restore command, I don’t want to pollute current production environment. So I decide restore the DB/Tablespace in another place.

Edit the redirect.clp file to proper values, modify the DBPATH, LOGPATH and the DBNAME, and execute the restore operation.

After I edited the redirect.clp file, it should like below.

Redirect the database into NEWDB with rebuild tablespace option:

Find out which logs are needed by rollforward operation, and copy those logs file into the new log path:

And now, you can export the data from the redirect db to the source db:

### 4. summary

Recover a deleted table is very simple in Oracle, but you need recover it as soon as possible, because depends on your production environment, no one knows how long will the undo data retain in the undo tablespace. So when you created your database, modify the parameter “undo_retention” to meet your business requirments.
In DB2, the “dropped table recovery” feature do not support export XML column, so when recovering this type of data, PIT recovery should be a better choice. Besides, import will generate lots of logs and may have big impact of performance, when loading large tables into the source db, db2 LOAD utilities will be more suitable.

EOF