Shulou(Shulou.com)06/01 Report--

Recovery of Drop,delete,truancate table in Oracle

In oracle, tables are often deleted due to some mistakes. Here are some of the table recovery methods I wrote.

Flashback mode must meet the conditions (enable flashback area and enable archiving):

1. Check if flash recovery area is started

Show parameter db_recovery_file

two。 Check if archiving is enabled

Archive log list

(1) recovery of Drop table

If you delete the table in the usual way: (Drop table tablename;). The table is not deleted immediately, but is stored in the Recycle Bin (not until after Oracle 10g). We can restore the watch by Flashback.

View the deleted tables in the Recycle Bin:

Select * from dba_recyclebin (where type='TABLE');-- find out whether the corresponding table exists

Flashback table:

Flashback tbale table name to before drop

* * the Recycle Bin stores not only tables, but also other objects

(2) recovery of Delete table

Similarly, it can be recovered after Delete by flashback timestamp or flashback SCN number.

Recovery based on timestamp

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

② uses the following statement to find the deleted data:

Select * from table name as of timestamp to_timestamp ('delete point in time', 'yyyy-mm-dd hh34:mi:ss')

③ enables the line mobility function

Alter table table name enable row movement

④ reinserts 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 hh34:mi:ss')); make sure that the primary key is not duplicated.

⑤ turns off line movement (don't forget)

Alter table table name disable row movement

Restore based on SCN number

① gets the current SCN number

Select dbms_flashback.get_system_change_number from dual;-assume that the scn number is 10672000

If ② knows the SCN number before deletion, try it through flashback query if you don't know.

Select count (*) from table name as of scn 10671000;-- if the amount of data is 0, continue to advance the scn number

Select count (*) from table name as of scn 10670000;-- the amount of data queried is the original amount of data, then ok

③ performs flashback recovery by querying the SCN number

Insert into table name select * from tablename as of scn 10670000

(3) recovery of Truncate table

The use of log recording and rollback segment space will not be generated after the truncate table, and cannot be recovered by query flashback.

The easiest way to ①: download and use PRM (Database disaster recovery tool)

② for database flashback (not recommended in production database):

Select sysdate from dual;-check the timestamp to confirm that flashback is enabled. If it is not enabled, you can only use ① method.

Shutdown immediate;-shuts down the database

Startup mount;-- Boot to mount state

Flashback database to timestamp to_timestamp ('2017-07-30 18-22-22-33-minute flashback deletion time point in time)

Alter database open;-Open the database

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.