Oracle flashback 1

ORACLEdatabaseflashbackdbatimestamp

oracle 9i flashback query

oracle 10g flashback table/flashback drop/flashback database/flashback version query/flashback transaction query

oracle 11g flashback data archive

Before oracle 9i, if dbas want to recover some data which is deleted or dropped carelessly by users, it would difficult for dba to complete the task. When upgrade oracle version 9i or more later, there is a tool named flashback of oracle db, it can help dba recovery some data in one time and do not need stopping the db.

Oracle flashback query:

After oracle 9i, oracle uses the automatic control to manage the undo tablespace, which need set the parameter of oracle instance as following:

show parameter undo

undo_management string AUTO

undo_retention integer 86400

undo_tablespace string UNDOTBS1

Except the parameter undo_management setting the auto, it need to set the undo_retention to guarantee the time of history data which needs enough space of undo tablespace. If there is not enough space, the new history data should overwrite the older history data. And you can use the value "guarantee" to guarantee the data being not overwritten in the undo_retention time.

Flashback query

syntax:

select * from table_name as of timestamp to_timestamp('2013-09-06 17:00:00', 'YYYY-MM-DD HH24:MI:SS');

select * from table_name as of scn scn_number;

It also has the defects, it can not recovery the data before 5 days, it can not recovery the data before the table's architecture being modified, it may not recovery data before undo_retention time, and it can not recover drop or truncate data because it hasn't the undo data.

Flashback table

It is different with flashback query, flashback query only query the data which is deleted carelessly by users, but flashback table is recovering the table to the prior time.

Flashback table features:

online

can recover table to a specified scn number or time

automatic recover relational indexes, triggers e.g.

satisfied concurrent feature

can flashback any table

own the select, delete, insert, update, alter privileges of the recovered table

the table must be row movement

Syntax:

flashback table table_name to scn scn_number

flashback table table_name to timestamp ('2013-09-06 18:00:00', 'yyyy-mm-dd hh24:mi:ss');

flashback table table_name to scn 888888 enable triggers;

Flashback drop

If you are aware of you drop a object carelessly, it is difficult to recover the object before oracle 10g. After the version 10g, oracle provides a tool to recover the object which is dropped carelessly by users or dba.

After the version 10g, oracle provide a new function named recycle bin that save the tables which are dropped by users, but there is no enough space on the tablespace or need to extend the extent will empty some dropped table in recycle bin.

The tables that are dropped by users in recycle bin can be select, these tables just like to be renamed. If we find that we have dropped a table carelessly and the table is in the recycle bin, it could be recovered by flashback drop.

Syntax

flashback table table_name to before drop;

flashback table table_name to before drop rename to table_name1;