Oracle flashback 1
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');
OR
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;
or
flashback table table_name to before drop rename to table_name1;