当前位置:数据库 > Oracle >>

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;
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,