Oracle's Data Recovery--flashback usage Summary

/*

The flashbackup of 11g is divided into several kinds, which are different in use.

A.flashback database Flashback, the simple idea is to flash the database back to a previous point in time,

The earliest SCN that can be recovered depends on the earliest SCN recorded in the flashback log.

B.flashback drop recycles the database table for recovery after the table has been accidentally drop. Recycle Bin similar to Windows.

C.flashback Query Flashback table record for data table record recovery

D.FALSHABCK table Flash back database tables,

The latter two are the focus, as explained below.

*/

–a.flashback Database Related

/*

1. The flashback database is closed by default and can be opened in the Mount exclusive state.

After you set up the Flashback recovery area, you can start the Flashback database feature.

*/

–1. Check if the Flash recovery area is activated

Show Parameter Db_recovery_file

–2. Check if the archive is enabled

Archive log list;

–3.flashback database is off by default, view methods

Select flashback_on from V$database;

–4. Querying the current SCN

SELECT CURRENT_SCN from V$database;

The current time of the query

Select To_char (sysdate, ' yy-mm-dd hh24:mi:ss ') time from dual;

–6. View the correspondence between SCN and timestamp:

Select Scn,to_char (TIME_DP, ' Yyyy-mm-dd hh24:mi:ss ') from Sys.smon_scn_time;

–7. Restore to a point in time, or revert to the SCN

Flashback database to timestamp to_timestamp (' 09-10-14 14:37:05′, ' yy-mm-dd hh24:mi:ss ');

Flashback database to SCN 947921;

–b. Flashback Table recovery Error Drop list

drop table sphsy.login_table;

SELECT * from Flash_table;

–purge table sphsy.login_table; empty Recycle Bin

Flashback table sphsy.login_table to before drop;

SELECT * from Sphsy.login_table;

–c. Flashback query for row-level recovery

/*

The flashback query is used to get table row-level data for the previous point in time. When you use the flashback query,

You need to specify the as of timestamp clause or as of the SCN clause after the table name, where as of timestamp is used to specify an earlier point in time,

The as of SCN is used to specify the earlier SCN values, as shown in the following example:

*/

–1. Check the original record, there are 62 lines in the interval

SELECT *

From Sphsy.login_table A

where a.ID > 201204171078

and a.ID < 201204171141

Order BY a.ID;

–2.3016 later than the interval

Select Program,count (*)

From Sphsy.login_table A

where a.ID >= 201204171141

Group BY Program;

–3. Delete

Delete from Sphsy.login_table a

where a.ID > 201204171078

and a.ID < 201204171141

–4. Using the Flashback feature to find the range, there are 62 rows

SELECT * FROM Sphsy.login_table

As of timestamp to_timestamp (' 2012-04-17 17:20:30′, ' yyyy-mm-dd HH24:MI:SS ')

where ID > 201204171078

and ID < 201204171141

Not using the Flashback feature, direct query found no

SELECT * FROM Sphsy.login_table

where ID > 201204171078

and ID < 201204171141

–6. Perform data recovery

– Prohibit triggers on the table

Alter TRIGGER SPHSY. t_ins_login_table Disable;

– Recover Data

INSERT INTO sphsy.login_table

SELECT * FROM Sphsy.login_table

As of timestamp to_timestamp (' 2012-04-17 17:20:30′, ' yyyy-mm-dd HH24:MI:SS ')

where ID > 201204171078

and ID < 201204171141

– Recovery triggers

Alter TRIGGER SPHSY. T_ins_login_table enable;

–7. Data later than the interval returned 3130 = 3016 +62 + later data. The error deletion of interval recovery is realized.

Select Program,count (*)

From Sphsy.login_table A

where a.ID >= 201204171078

Group BY Program;

–d. Flashback table Restore to Previous state

/*

The flashback query can be used to recover the table row data that was mistakenly deleted, but the user performs other DML statement misoperation (INSERT or UPDATE) on the table, and the table data cannot be restored directly to the previous point in time using the flashback query, starting with oracle10g. Using the Flashback table statement to restore a table to a previous point in time, by using this feature, you can avoid performing a point-in-time incomplete recovery, and note that if you want to use the Flashback table feature on a table, you must have the following conditions:

A. The user must have flashback any table system permissions or Flashback object permissions

B. User compulsory have select Insert Delete and ALTER permission on the table

C. Initialize parameter undo_retention must be set reasonably to ensure that the undo information is retained for sufficient time

D. The line Movement feature must be activated: ALTER TABLE table_name enable row movement;

*/

–1. Check the original record, there are 62 lines in the interval

SELECT *

From Sphsy.login_table A

where a.ID > 201204171078

and a.ID < 201204171141

Order BY a.ID;

–2.3074 later than the interval

Select COUNT (*)

From Sphsy.login_table A

where a.ID >= 201204171141;

–3. Delete, first note the point in time, 2012-04-17 17:43:46

Select To_char (sysdate, ' Yyyy-mm-dd HH24:MI:SS ') from dual;

Delete from Sphsy.login_table a

where a.ID > 201204171078

and a.ID < 201204171141

–4. Table Sphysy.login_table continues to be modified after deletion, line 3082

Select COUNT (*)

From Sphsy.login_table A

where a.ID >= 201204171141;

To activate a line move feature

ALTER TABLE sphsy.login_table enable row movement

–6. Using the Flashback feature to restore directly to the point in time before deletion

Flashback table Sphsy.login_table to timestamp to_timestamp (' 2012-04-17 17:43:46′, ' yyyy-mm-dd HH24:MI:SS ');

–7. Data later than interval is returned to 3080, indicating that changes after the point in time are lost.

Select COUNT (*)

From Sphsy.login_table A

where a.ID >= 201204171141

–8. Push forward 1 points and revert to delete before deleting the 62 strips also returned.

Flashback table Sphsy.login_table to timestamp to_timestamp (' 2012-04-17 17:40:46′, ' yyyy-mm-dd HH24:MI:SS ');

–62 Line

Select COUNT (*)

From Sphsy.login_table A

where a.ID > 201204171078

and a.ID < 201204171141

– The data after deletion is 3074, which represents the loss of modifications.

Select COUNT (*)

From Sphsy.login_table A

where a.ID >= 201204171141

/*

Summary: Method C, Method D can be used to recover data.

Method C safe, restore trouble. Method D is simple and may be data loss.

*/

Oracle's Data Recovery--flashback usage Summary