oracle進行表資料恢複

1、建表

-- Create table
create table DARCY
(
ID   NUMBER,
INFO NVARCHAR2(32)
)
tablespace DATA_SGPM
pctfree 10
initrans 1
maxtrans 255
storage
(
    initial 64K
    minextents 1
    maxextents unlimited
);

2、插入資料

insert into "SGPM"."DARCY"("ID","INFO") values ('1','aaa');

insert into "SGPM"."DARCY"("ID","INFO") values ('2','bbb');

insert into "SGPM"."DARCY"("ID","INFO") values ('3','ccc');

3、刪除資料

SQL> select * from darcy;

        ID INFO
---------- --------------------------------------------------------------------------------
         1 aaa
         2 bbb
         3 ccc

SQL> delete from darcy where id = 1;

1 row deleted

SQL> commit;

Commit complete

SQL> select * from darcy;

        ID INFO
---------- --------------------------------------------------------------------------------
         2 bbb
         3 ccc

4、恢複資料

方法1：

查詢最新的系統變更number

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                18144344

查看此次變更後的表記錄

SQL> select * from darcy as of scn 18144344;

        ID INFO
---------- --------------------------------------------------------------------------------
         2 bbb
         3 ccc

說明這是刪除資料後的表記錄，我們只要找到某個scn，即刪除表記錄前的scn，

恢複到這個scn時的記錄。

SQL> SELECT * FROM DARCY as of scn 18144252;

        ID INFO
---------- --------------------------------------------------------------------------------
         1 aaa
         2 bbb
         3 ccc

然後直接執行insert語句

方法2：

SQL> select * from flashback_transaction_query where table_name='DARCY';

XID               START_SCN START_TIMESTAMP COMMIT_SCN COMMIT_TIMESTAMP LOGON_USER                     UNDO_CHANGE# OPERATION                        TABLE_NAME                                                                       TABLE_OWNER                      ROW_ID              UNDO_SQL
---------------- ---------- --------------- ---------- ---------------- ------------------------------ ------------ -------------------------------- -------------------------------------------------------------------------------- -------------------------------- ------------------- --------------------------------------------------------------------------------
07001500AB280000   18144149 2010-9-9 10:14:   18144281 2010-9-9 10:17:1 SGPM                                      1 DELETE                           DARCY                                                                            SGPM                             AAAYQwAAcAAAKk2AAA insert into "SGPM"."DARCY"("ID","INFO") values ('1','aaa');
080018005F370000   18144244 2010-9-9 10:16:   18144252 2010-9-9 10:16:3 SGPM                                      1 INSERT                           DARCY                                                                            SGPM                             AAAYQwAAcAAAKk2AAC delete from "SGPM"."DARCY" where ROWID = 'AAAYQwAAcAAAKk2AAC';
080018005F370000   18144244 2010-9-9 10:16:   18144252 2010-9-9 10:16:3 SGPM                                      2 INSERT                           DARCY                                                                            SGPM                             AAAYQwAAcAAAKk2AAB delete from "SGPM"."DARCY" where ROWID = 'AAAYQwAAcAAAKk2AAB';
080018005F370000   18144244 2010-9-9 10:16:   18144252 2010-9-9 10:16:3 SGPM                                      3 INSERT                           DARCY                                                                            SGPM                             AAAYQwAAcAAAKk2AAA delete from "SGPM"."DARCY" where ROWID = 'AAAYQwAAcAAAKk2AAA';

執行UNDO_SQL，即：insert into "SGPM"."DARCY"("ID","INFO") values ('1','aaa');即可恢複資料。

或者直接運行：

SQL> flashback table DARCY to timestamp to_timestamp('2010-9-9 10:16:3','yyyy-mm-dd hh24:mi:ss');

flashback table DARCY to timestamp to_timestamp('2010-9-9 10:16:3','yyyy-mm-dd hh24:mi:ss')

ORA-08189: 因為未啟用行移動功能, 不能閃回表

SQL> alter table DARCY enable row movement;

Table altered

SQL> flashback table DARCY to timestamp to_timestamp(2010-9-9 10:17:1,'yyyy-mm-dd hh24:mi:ss');

Done

SQL> SELECT * FROM DARCY;

        ID INFO
---------- --------------------------------------------------------------------------------
         1 aaa
         2 bbb
         3 ccc

5、drop表後的恢複

SQL> drop table darcy;

Table dropped

SQL> select * from darcy;

select * from darcy

ORA-00942: 表或視圖不存在

SQL> select * from recyclebin;

OBJECT_NAME                    ORIGINAL_NAME                    OPERATION TYPE                      TS_NAME                        CREATETIME          DROPTIME               DROPSCN PARTITION_NAME                   CAN_UNDROP CAN_PURGE    RELATED BASE_OBJECT PURGE_OBJECT      SPACE
------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ---------- ----------- ------------ ----------
BIN$CrbfFp0nRTWzETrAMvbD+A==$0 DARCY                            DROP      TABLE                     DATA_SGPM

SQL> SELECT * FROM USER_RECYCLEBIN;

OBJECT_NAME                    ORIGINAL_NAME                    OPERATION TYPE                      TS_NAME                        CREATETIME          DROPTIME               DROPSCN PARTITION_NAME                   CAN_UNDROP CAN_PURGE    RELATED BASE_OBJECT PURGE_OBJECT      SPACE
------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ---------- ----------- ------------ ----------
BIN$CrbfFp0nRTWzETrAMvbD+A==$0 DARCY                            DROP      TABLE                     DATA_SGPM                      2010-09-09:10:15:50 2010-09-09:11:12:04   18154031                                  YES        YES            99376       99376        99376          8

SQL> flashback table darcy to before drop;

Done

SQL> select * from darcy;

        ID INFO
---------- --------------------------------------------------------------------------------
         1 aaa
         2 bbb
         3 ccc