Drop,delete,truancate Table Recovery in Oracle

Drop,delete,truancate Table Recovery in Oracle

In Oracle, there are often errors that cause the table to be deleted, and here are some of the methods I've written to restore the table.

The flashback mode satisfies the criteria ( enabling the Flashback zone and enabling archiving ):

1. Check if the Flash recovery area is activated

Show Parameter Db_recovery_file

2. Check if the archive is enabled

Archive log list;

(i)recovery of drop tables

If you follow the usual method of deleting a table: (Drop table tablename;). Tables are not deleted immediately, but are stored in the Recycle Bin (only after Oracle 10g ). We can restore the table by flashback (Flashback).

To view the tables that were deleted from the Recycle Bin:

Select * from Dba_recyclebin (where type= ' TABLE '); --Find out if the corresponding table exists

Flash Back table:

Flashback tbale table name to before drop;

* * The Recycle Bin does not only store tables, but also other objects

(ii)Recovery of Delete table

Similarly, the delete can be recovered by either a flashback timestamp or a flashback to the SCN number.

Timestamp -based recovery

① determines when data is deleted (the time before data is deleted, but preferably at the point in time when data is deleted)

② use the following statement to find the deleted data:

SELECT * from table name as of timestamp to_timestamp (' Delete point in time ', ' yyyy-mm-dd hh24:mi:ss ')

③ Turn on line move function

ALTER TABLE name enable row movement

④ to reinsert the deleted data into the original table:

Insert into table name (SELECT * from table name as of timestamp to_timestamp (' Delete point in time ', ' yyyy-mm-dd hh24:mi:ss ')); Note to ensure that the primary key is not duplicated.

⑤ Turn off line movement (don't forget)

ALTER TABLE name disable row movement

recovery based on SCN number

① get the current SCN number
Select Dbms_flashback.get_system_change_number from dual; --assuming that the SCN number is 10672000

② If you know that the SCN number is the best before deleting it, try the flashback query if you don't know it.

Select COUNT (*) from table name as of SCN 10671000; --If the amount of data is 0, advance SCN number is continued

Select COUNT (*) from table name as of SCN 10670000;--The amount of data queried is the original data volume, OK

③ Flashback recovery by querying the SCN number

INSERT into table name SELECT * FROM tablename as of SCN 10670000;

Three Recovery of Truncate tables

The TRUNCATE table does not generate log records and rollback segment space usage, and cannot be recovered with a query flash back.

① easiest way: Download using PRM (Database Disaster recovery Tool)

② Database Flashback (not recommended in the production library):

Select Sysdate from dual; --Check the timestamp, confirm that the flashback has been turned on, if not, only use the ① method;

Shutdown immediate; --Close the database

startup Mount; --Boot to Mount state

Flashback database to timestamp to_timestamp (' 2017-07-30 18:22:33 ', ' yyyy-mm-dd hh24:mi:ss '); --Flash back time to delete a point in time

ALTER DATABASE open; --Open Database

Drop,delete,truancate Table Recovery in Oracle