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