本文主要是介绍Oracle数据恢复--flashback，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
对于表的恢复
db_flashback_retention_target 参数决定删除表以及表中数据保留时间
SQL> conn / as sysdba
Connected.
SQL> alter system set db_flashback_retention_target=3600;
System altered.
SQL>
确认处于归档模式
SQL> conn / as sysdba
Connected.
SQL> select dbid,name,log_mode from v$database;
DBID NAME LOG_MODE
---------- --------- ------------
1573521836 FENGZI ARCHIVELOG
SQL>
查询所有表删除其中一个表
SQL> conn u2/u2
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BB2 TABLE
EMPTY TABLE
TEMP TABLE
SQL> drop table bb2;
Table dropped.
SQL>
查询回收站
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
BB2 BIN$98CBLGsSLR/gQKjACjgQQQ==$0 TABLE 2014-04-24:09:09:44
SQL>
恢复表
SQL> flashback table bb2 to before drop;
Flashback complete.
SQL> select * from bb2;
ID
----------
1
1
1
1
1
1
1
1
1
1
12 rows selected.
SQL>
如果删除期间创建了原表名的表在删除 在回收站会有两个选项
SQL> drop table bb2;
Table dropped.
SQL> create table bb2 as select * from temp;
Table created.
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
BB2 BIN$98Cx0ajtOxDgQKjACjgQYg==$0 TABLE 2014-04-24:09:23:20
SQL>
SQL> drop table bb2;
Table dropped.
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
BB2 BIN$98Cx0ajuOxDgQKjACjgQYg==$0 TABLE 2014-04-24:09:24:59
BB2 BIN$98Cx0ajtOxDgQKjACjgQYg==$0 TABLE 2014-04-24:09:23:20
SQL>
如果还使用BB2名称恢复表的话，得到的表为最后一次删除的表内容。
想要恢复之前的表需要
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
BB2 BIN$98Cx0ajuOxDgQKjACjgQYg==$0 TABLE 2014-04-24:09:24:59
BB2 BIN$98Cx0ajtOxDgQKjACjgQYg==$0 TABLE 2014-04-24:09:23:20
SQL>
SQL> flashback table "BIN$98Cx0ajtOxDgQKjACjgQYg==$0" to before drop;
Flashback complete.
SQL> select * from bb2;
ID
----------
1
1
1
1
1
1
1
1
1
1
1
12 rows selected.
SQL>
对于表中行的恢复
这种方法有点时候是不实用的
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2014-04-24 09:38:05
SQL> update bb2 set id=id+1 where id=1;
12 rows updated.
SQL> commit;
Commit complete.
SQL> alter table bb2 enable row movement;
Table altered.
SQL> flashback table bb2 to timestamp to_timestamp('2014-04-24 09:38:05','yyyy-mm-dd hh24:mi:ss');
Flashback complete.
SQL> alter table bb2 disable row movement;
Table altered.
SQL> select * from bb2;
ID
----------
1
1
1
1
1
1
1
1
1
1
1
ID
----------
1
12 rows selected.
SQL>
比较实用的方法
SQL> select * from bb2;
ID
----------
1
1
1
1
1
1
1
1
1
1
1
ID
----------
1
2
2
14 rows selected.
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2014-04-24 09:55:07
SQL> delete bb2 where id=2;
2 rows deleted.
SQL> commit;
Commit complete.
SQL> select * from bb2 as of timestamp to_timestamp('2014-04-24 09:55:07','yyyy-mm-dd hh24:mi:ss');
ID
----------
1
1
1
1
1
1
1
1
1
1
1
ID
----------
1
2
2
14 rows selected.
SQL> select * from bb2 as of timestamp to_timestamp('2014-04-24 09:55:07','yyyy-mm-dd hh24:mi:ss')where id=2;
ID
----------
2
2
SQL> select * from bb2 where id=2;
no rows selected
这篇关于Oracle数据恢复--flashback的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
