Oracle 誤刪資料 ？！

有時候操作人員可能眼花了，誤刪了線上DB內的Table資料，發現事態嚴重後來求助DBA

這時DBA也別跟著慌張，先了解人員刪除資料的方式是什麼！


在Oracle 刪除資料有好多種方式
1. delete + commit
2. drop
3. truncate

以下一一操作還原示範
操作版本 :  11.2.0.1.0

1. delete + commit 
狀態：操作人員發現誤刪了Table資料並且也已經commit，並且立刻求助DBA

剛刪除的資料一定還在UNDO裡，這邊我們使用Flashback Query  技術

語法

code

--SCN SELECT <column_name_list>         FROM <table_name>         AS OF <SCN>                           --timestamp  SELECT <column_name_list>         FROM <table_name>         AS OF <TIMESTAMP>

1.1查詢一下目前有的資料、時間與SCN
SQL> select * from TW100;

NAME
-----------------------------------

1
2
3
4

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

sysdate
-------------------
2014-07-01 14:25:45

SQL> select to_char(current_scn,'99999999990') from v$database;

scn
------------
 10301120060
           

1.2 delete+commit

SQL> delete TW100;

4 rows deleted.

SQL> commit;

Commit complete.

SQL>


1.3 Flashback Query

SQL> select * from TW100 as of timestamp to_timestamp('2014-07-01 14:25:45','yyyy-mm-dd hh24:mi:ss');

NAME
------------------------------------------------
1
2
3
4

或是

SQL> select * from tw100 as of scn  10301120060;

NAME
---------------------------------------------------

1
2
3
4

1.4.insert into 
在把資料重新 insert 進去

SQL> insert into tw100 select * from tw100 as of scn 10301120060;

4 rows created.

SQL> commit;

資料就還原回來了


其實操作人員不會知道當下刪除的SCN，通常都只知道刪除的大概時間，用 timestamp 慢慢去推算時間可能會好一點。

因為這是利用 UNDO ，資料的保留時間跟 undo tablespace size 和 undo_retention參數有關
要是太久之前的資料如1天前，那就可不保證能還原回來了！！！


2. drop
狀態：操作人員發現drop 錯了 Table，過了一天後發現才來求助DBA

在Drop Table 後 Oracle 會把刪除的Table 放入類似回收筒的地方叫 Recyclebin (功能預設是開啟)

查詢功能使否開啟
SQL> show parameter recyclebin

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------
recyclebin                           string      on
SQL>

2.1 查詢 Recyclebin  內的 Table

SQL> select OBJECT_NAME,ORIGINAL_NAME,OPERATION from dba_recyclebin;

OBJECT_NAME                                                ORIGINAL_NAME              OPERATION                    DROPTIME
-------------------------------------------                         --------------------                  ---------                        ----------------------------
BIN$9x57SsRkSTOYj9JLTeq8PQ==$0                       TW100                          DROP                   2014-07-01:14:54:52

SQL>

可以看到他現在的名子叫 BINXXXXXX ，原本的名子叫TW100


2.2 還原

SQL> FLASHBACK TABLE TW100 TO BEFORE DROP;

Flashback complete.

SQL>

或者

SQL> FLASHBACK TABLE "BIN$9x57SsRkSTOYj9JLTeq8PQ==$0" TO BEFORE DROP;

Flashback complete.

SQL>

又或者還原順便替 Table 改名

SQL> FLASHBACK TABLE "BIN$9x57SsRkSTOYj9JLTeq8PQ==$0" TO BEFORE DROP RENAME TO TW101;

Flashback complete.

SQL>

成功

3. truncate
truncate 還原 相較於delete 與 drop 說是來的困難很多

第一作法 : 把先前datafile、 archive log file 備份還原到另外一台備援機上， 在expdp + impdp

第二作法 : 使用 flash database 把整個資料庫回覆到刪除前 (需要db shudown, 較適合使用再 測試機或不重要的db上)

第三作法 : Oracle 它有自己的恢復工具叫 DUL (需 Oracle Support), 網路上也有個強人開發的恢復工具叫: AUL (有限度式的免費) AUL 還原示範連結


以下是模擬第二個做法 FLASHBACK

3.1 使用  FLASHBACK database
先確定 自己的  FLASHBACK是否開啟

SQL> select flashback_on,force_logging from v$database;

FLASHBACK_ON       FOR
------------------ ---
YES                YES


3.2 查看一下 DB目前可以恢復的時間

SQL> select oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log;

OLDEST_FLASHBACK_SCN               OLDEST_FLASHBACK_TI
---------------------------------                     --------------------------------
             4666749                                       2014-07-02 11:55:00

SQL> select sysdate from dual;

SYSDATE
-------------------
2014-07-02 13:12:59


3.3 模擬Table 被 truncate

SQL> truncate table tw100;

Table truncated.


3.4 進行恢復

SQL> shutdown immediate;

SQL> startup mount exclusive;

SQL> Flashback Database to timestamp(to_date('2014-07-02 13:12:59','yyyy-mm-dd hh24:mi:ss'));

Flashback complete.

SQL> alter database open read only;

Database altered.

3.5 查詢是否成功

SQL> select * from tw100;

NAME

-------------------------------------------------

1

1

1

1

1

1

6 rows selected.

SQL>

恢復成功