Summary of oracle accidental deletion data recovery methods and oracle accidental deletion data recovery

Summary of oracle accidental deletion data recovery methods and oracle accidental deletion data recovery

If the user accidentally deletes/updates the data, there is no direct way for the user to recover the data, they must seek help from the DBA to recover the database to Oracle9i, this embarrassing situation has improved. Or (www.jb51.net) acle 9i provides a new technical method-Flashback query. You can use flashback query to obtain data before misoperation in a timely manner and perform corresponding restoration measures for errors, all of this requires no DBA intervention.

I deleted the production data because of a temporary embarrassment.

The delete statement is used, and commit is quickly returned.

The following two statements:

Alter table tablename ENABLE row movement;
Flashback table tablename to timestamp to_timestamp ('2017-09-13 13:00:00 ', 'yyyy-mm-dd hh24: mi: ss ');

Remember the delete --- commit statement that runs around half past two;

The specific execution process can be seen from the following examples;

1. Original table records

 $ sqlplus eygle/eygleSQL*Plus: Release 10.1.0.2.0 - Production on Wed Mar 30 08:52:04 2005Copyright (c) 1982, 2004, Oracle.  All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit ProductionWith the Partitioning, OLAP and Data Mining optionsSQL>select count(*) from t1;  COUNT(*)----------    9318

2. accidentally delete all records
And submit the changes.

SQL>delete from t1;9318 rows deleted.SQL>commit;Commit complete.SQL>select count(*) from t1;  COUNT(*)----------     0

3. Obtain the current SCN
If you know exactly that the SCN is the best before deletion, if you do not know, you can try a flashback query.

SQL>select dbms_flashback.get_system_change_number from dual;GET_SYSTEM_CHANGE_NUMBER------------------------         10671006SQL>select count(*) from t1 as of scn 10671000;  COUNT(*)----------     0SQL>select count(*) from t1 as of scn 10670000;  COUNT(*)----------    9318

We can see that when SCN = 10670000, the data is in.

4. Restore data.

SQL>insert into t1 select * from t1 as of scn 10670000;9318 rows created.SQL>commit;Commit complete.SQL>select count(*) from t1;  COUNT(*)----------    9318

Tutorials for other users

For database operations, you must add where after the delete operation ". Today, I accidentally saw a message on the Internet about oracle accidental deletion of data recovery, which is really helpful. Next I will report my test to you.

1. select * from t_viradsl2 t // query all data in t_viradsl2. Three data entries are displayed. delete t_viradsl2 // delete all data in t_viradsl2, and 3. select * from t_viradsl2 t // no data. 4. insert into t_viradsl2 select * from t_viradsl2 as of timestamp to_Date ('2017-01-19 15:28:00 ', 'yyyy-mm-dd hh24: mi: ss ') // deleted data has been inserted into the table by mistake. 5. select * from t_viradsl2 t // three more data entries are displayed.

Let's analyze the fourth step. Pay attention to this sentence:

Select * from t_viradsl2 as of timestamp to_Date ('2017-01-19 15:28:00 ', 'yyyy-mm-dd hh24: mi: ss'). What does that mean, find all the data at the time point t_viradsl2 at 15:28:00. Now that you have found the data, you can do everything you want.

Here, I would like to share with you