Oracle数据恢复--flashback
对于表的恢复
db_flashback_retention_target参数决定删除表以及表中数据保留时间
SQL>conn / as sysdba Connected. SQL>alter system set db_flashback_retention_target=3600; System altered. SQL>
确认处于归档模式 SQL>select dbid,name,log_mode from v$database; DBID NAME LOG_MODE ---------- --------- ------------ 1573521836 FENGZIARCHIVELOG SQL> 查询所有表删除其中一个表 SQL>conn u2/u2 SQL>select * from tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- BB2 TABLE EMPTY TABLE TEMP TABLE SQL>drop table bb2; Table dropped. 查询回收站 SQL>show recyclebin ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ ------------------- BB2 BIN$98CBLGsSLR/gQKjACjgQQQ==$0 TABLE 2014-04-24:09:09:44 恢复表 SQL>flashback table bb2 to before drop; Flashback complete. SQL>select * from bb2; ID ---------- 1 12 rows selected. 如果删除期间创建了原表名的表在删除在回收站会有两个选项 SQL>create table bb2 as select * from temp; Table created. BB2 BIN$98Cx0ajtOxDgQKjACjgQYg==$0 TABLE 2014-04-24:09:23:20 BB2 BIN$98Cx0ajuOxDgQKjACjgQYg==$0 TABLE 2014-04-24:09:24:59 SQL> 如果还使用BB2名称恢复表的话，得到的表为最后一次删除的表内容。 想要恢复之前的表需要 BB2BIN$98Cx0ajtOxDgQKjACjgQYg==$0TABLE2014-04-24:09:23:20 SQL> flashback table "BIN$98Cx0ajtOxDgQKjACjgQYg==$0" to before drop; 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'); SQL>alter table bb2 disable row movement; SQL>
比较实用的方法 2 14 rows selected. 2014-04-24 09:55:07 SQL>delete bb2 where id=2; 2 rows deleted. SQL>select * from bb2 as of timestamp to_timestamp('2014-04-24 09:55:07','yyyy-mm-dd hh24:mi:ss')where id=2; SQL>select * from bb2 where id=2; no rows selected （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |