回復 Oracle 刪除或是已更新的資料

我們都知道還原資料最多就是只能還原到最近一前的備份點，
但是備份不可能是隨時隨地都在備份，
Oracle 有一個 undo 的機制，會將資料進行異動之前就進行 Data Image，
原本的用意為 A 進行 Query 之後 B 又進行 Update 時，A 的 Query 結果會是當時下達指令時點的資料，
就不會被 B 在 Update 時所影響，也不會造成資料不一致。
因此我們就可以利用此功能回復到你所想要的某一時間的資料。
undo 區分為 ACTIVE、UNEXPIRED、EXPIRED 三種，
當資料 commit 或 rollback 後就會改變為非活動中的狀態。

查看目前 undo 的狀態：
select * from DBA_UNDO_EXTENTS

利用 Oracle 的 undo log 來查到底是誰把資料做刪除或變更的，
使用 LogMiner 來開啟 undo log 或 archived log 檔案，就可以查出 SQL_TEXT 和 Session ，
就知道到底是誰幹的好事。

先來看 Oracle 的參數：
sqlplus： show parameter undo;
或
select * from v$parameter where name like '%undo%'

NAME                   VALUE
--------------------------
undo_management  AUTO
undo_tablespace     UNDOTBS1
undo_retention        900

undo_retention 保存時間(秒) 900 秒，
設定 undo_retention 越大就會佔用更多的 undo tablespace 的空間。
要是 undo 空間不足時，才會把 undo_retention 以前的資料蓋掉，
當 undo tablespace 設定 RETENTION GUARANTEE 時，
還是不夠 undo 空間就會自動擴展，增加 undo tablespace 檔案容量，
必須要保証磁碟空間是足夠的，當有大量資料異動時，會造成 undo 檔案過大。
設定為 RETENTION NOGUARANTEE ， undo tablespace 不會擴增，
當容量不足時就會一直把非活動的空間蓋掉，所以 undo_retention 不保証一定會保存完整資料。

將 undo_retention 改為 1 小時，指令如下：

alter system set undo_retention = 3600

修改 undo tablespace 的屬性，開啟 retention 自動擴展：
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE

關閉 retention 自動擴展：
ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE

預設 undo_management 都是 auto 管理，
undo_retention 的值會由 Oracle 自動進行最佳的調整，
所以超過 undo_retention 的時間，也有可能狀態還是在 UNEXPIRED 。
因為 Oracle 每 10 分鐘就會依 undo tablespace 的空間和 Oracle 操作狀況來設定最佳的 undo_retention 值。

查看 Oracle 所設定的 undo_retention (秒)
SELECT * FROM v$undostat;

要如何將資料還原，只要加上 timestamp 就可以查到當時的時間點上資料內容。
select * from ds.zx_file as of timestamp to_timestamp('2013-03-15 15:55:29', 'yyyy-mm-dd hh24:mi:ss');

查出來結果應該會很開心，好險資料還在。
再來就是把資料還原就可以了，一般都是用 create table 的方式，再比對資料後做更新。
create table ds.zx_temp as
select * from ds.zx_file as of timestamp to_timestamp('2013-03-15 15:55:29', 'yyyy-mm-dd hh24:mi:ss');

scn (System Change Number) 當資料異動時，就會將 scn + 1 保存在 undo log 。

比較建議還是用 scn 會比較避免同一秒的資料異動時，資料還原的完整性。

再來就是 scn 和 timestamp 在 9i 會有 5 分鐘的資料同步，

所以 9i 用 timestamp 進行 flashback query 時會查不到 5 分鐘內異動的資料。

在 10g 以後的版本就修正此情況， scn 和 timestamp 會在 LogMiner 取得。

改用 scn 的方式來查詢：

select * from ds.zx_file as of scn 2267300000

查詢最近一次資料異動的 scn 和 timestamp ：

select scn_to_timestamp(dbms_flashback.get_system_change_number) ,dbms_flashback.get_system_change_number from dual;

雖然 Oracle 這個機制可以讓我們放心資料異動的問題，但還是有限制：

1. Table Schema 變更就不能回復，因 flashback query 是用目前的資料字典。

2. 不能 flashback 到 5 天前的資料。

3. 不能保証 undo_retention 所保存資料是否完整，undo tablespace 過小會蓋掉舊的。

4. drop、truncate 不需 commit ，所以就不能回復，所以要把 truncate 和 drop 指令鎖住。

一般的帳號也可以使用 flashback 的功能，設定權限：

grant execute on dbms_flashback to USER ;

想要看某筆資料所有異動的紀錄，在 Oracle 有 versions 的指令來查看每個版本的資料和起始、結束時間
欄位 versions_starttime 表示版本開始時間，欄位 versions_endtime  表示版本結束時間
使用方式只要在 table 後面加上 versions between timestamp minvalue and maxvalue 就可以了。
範例：select zx_file.*,versions_starttime,versions_endtime from ds.zx_file versions between timestamp minvalue and maxvalue