Oracle Flash back Query recovery delete deleted data

Flashback query ( flash back ) principle

Based on the undo information, Oracle uses the undo data, similar to the consistent read method, to place the table at a point in time ( or SCN) before deletion to retrieve the data.

Flashback query ( flash back ) premise:

Sql> show parameter undo;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

Undo_management string AUTO

Undo_retention Integer 900

Undo_tablespace string UNDOTBS1

where undo_management = Auto, set automatic undo Management (AUM), the default setting is:auto;

undo_retention = n ( seconds ), setting determines the maximum save time for undo , and the larger the value, the more support for undo Tablespace is required. The command to modify undo_retention is as follows:

Sql> alter system set undo_retention = 3600;

System Altered

Flash-Back Implementation mode

1. obtain a point-in-time or SCNbefore the data is deleted, as follows:

Sql>select to_char (sysdate, ' yyyy-mm-dd hh24:mi:ss ') time, To_char (Dbms_flashback.get_system_change_number) SCN from dual;

Time SCN

------------------- ----------------------------------------

2010-06-29 23:03:14 1060499

2. Query the point-in-time (or SCN) data as follows:

Sql> SELECT * from t as of timestamp to_timestamp (' 2010-06-29 22:57:47 ', ' yyyy-mm-dd hh24:mi:ss ');

Sql> SELECT * FROM T as of SCN 1060174;

3. Add the queried data to the table. You can also use more direct methods , such as:

sql>CREATE TABLE Tab_test as SELECT * from T of timestamp to_timestamp (' 2010-06-29 22:57:47 ', ' yyyy-m M-dd hh24:mi:ss ');
sql>INSERT INTO tab_test select * from1060174;

Example:

Create table T (ID number);

Insert into t values (1);
INSERT into T values (2);
INSERT into T values (3);
INSERT into T values (4);
INSERT into T values (5);

1. View the raw data in the T table

Sql> select * from T;

Id

---------

1

2

3

4

5

2. Get a point in time or SCN before data is deleted

Sql> Select To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss ') time, To_char (Dbms_flashback.get_system_change_number) SCN from dual;

Time SCN

------------------- ----------------------------------------

2010-06-29 23:23:33 1061279

3. Delete data from the T table and submit things

Sql> Delete from T;

5 rows deleted

Sql> commit;

Commit Complete

4. When you view the T table, the data in the T table is deleted

Sql> select * from T;

Id

----------

5. View data when the SCN is 1061279 ( or 2010-06-29 23:23:33) in the T table

Sql> SELECT * FROM T as of SCN 1061279;

Id

----------

1

2

3

4

5

6. After confirming that you want to recover, restore the data in the T table to data when the SCN is 1061279 ( or 2010-06-29 23:23:33) and commit the thing

sql> INSERT INTO T-select * from T as of SCN 1061279;

5 rows inserted

Sql> commit;

Commit Complete

7. Confirm the restore of the t table data

Sql> select * from T;

Id

----------

1

2

3

4

5

Note: It is recommended to use the SCNdue to the oracle9i , because the SCN is synchronized with the time point of 5 minutes, if the data within the last 5 minutes needs Falshback query Query, may be missing, and SCN does not exist this problem. This issue has been corrected in oracle10g (theSCN is roughly related to the point in time and can be obtained through the LOGMNR Analysis Archive log).

Limitations of falshback query queries:

1. data that cannot be falshback to 5 days ago.

2. The flashback query cannot be recovered until the table structure changes because the flashback query uses the current data dictionary.

3. affected by the undo_retention parameter, theFlashback is not guaranteed to Flashback the data prior to undo_retention success.

4. operations that do not record rollback, such as drop,truncate , cannot be recovered.

5. Ordinary users use the Dbms_flashback package and must be authorized by the Administrator. The command is as follows :

Sql>grant execute on Dbms_flashback to Scott;