當使用者誤刪除ORACLE資料的時候,作為DBA我們應該怎麼去處理呢?通常有三種處理方法:

1.FLASHBACK

2.LOGMINER

3.不完全恢復

第三中方法這裡不打算講,可以參考ORACLE相關的備份恢復文件.前兩種恢復方法我到網上搜集了兩個案例,讀完後覺的有參考價值,特摘錄了下來.

Oracle9i 中新增的閃回查詢（Flashback Query）功能對於誤刪除或者誤更新並且已經commit 了的情況，提供了簡便快捷的恢復方法，而在Oracle 提供閃回查詢之前，碰到這種情況只能透過備份來進行基於時間點的恢復，無疑這比閃回查詢要麻煩而且費時。

什麼是Flashback Query

利用Oracle 多版本讀一致的特性，在需要的時候透過undo 來提供所需的前映象中的資料。利用這個功能，可以看到歷史資料（呵呵，就像時光倒流。月光寶盒？），甚至用歷史資料來修復誤操作引起的錯誤。可以透過指定時間或者SCN 來檢索需要的資料。
前提條件
資料庫必須處於Automatic Undo Management 狀態。
最大可以閃回查詢的時間段由UNDO_RETENTION 初始化引數（單位為秒）指定
可以透過ALTER SYSTEM SET UNDO_RETENTION = ;來修改引數值
如何使用Flashback Query
透過SQL
使用SELECT 語句的AS OF 來進行閃回查詢，語法如下：
SQL> show parameter undo_retention
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 10800
SQL> show parameter undo_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
使用AS OF 關鍵字來對錶，檢視，或者物化檢視進行Flashback Query，如果指定了SCN，
那麼expr 部分必須是一個數字，如果指定了TIMESTAMP，那麼expr 必須是一個timestamp 型別的值。查詢結果將返回在指定的SCN 或者時間點上的資料。
下面我們使用scott 方案來作一個實驗。

[zhangleyi@linux9 bin]$ sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on Thu Oct 9 23:44:07 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect scott/tiger
Connected.
SQL> select sal from emp where empno=7369;
SAL
----------
800
SQL> update emp set sal=4000 where empno=7369;
1 row updated.
SQL> commit;
Commit complete.
SQL> select sal from emp where empno=7369;
SAL
----------
4000
Uses Oracle's multiversion read-consistency capabilities to
restore data by applying undo as needed. You can view and
repair historical data, and you can perform. queries on the
database as of a certain wall clock time or user-specified
system commit number (SCN)
備註：TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY)指查詢距當前時間一天以
前的時間點的資料，如果我們要查詢一小時以前的，那麼需要將DAY 替換成HOUR 即可，
查詢10 分鐘以前的將'1' DAY 替換'10' MINUTE。
以上演示了對於誤更新的欄位進行恢復的方法，但是如果想在update 的子查詢部分使用AS
OF 那麼該查詢只能返回一條記錄，否則將會報錯。如下：
SQL> select empno,sal from emp;
EMPNO SAL
---------- ----------
7369 800
7499 1600
7521 1250
7566 2975
7654 1250
7698 2850
7782 2450
7788 3000
SQL> select sal from emp
2 AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY);
3 where empno=7369; --as of scn &scn 也可以透過SCN
SAL
----------
800
SQL> update emp set sal=
2 (select sal from emp
3 AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY)
4 where empno=7369)
5 where empno=7369;
1 row updated.
SQL> select sal from emp where empno=7369;
SAL
----------
800
SQL> commit;
7839 5000
7844 1500
7876 1100
EMPNO SAL
---------- ----------
7900 950
7902 3000
7934 1300
14 rows selected.
SQL> update emp set sal=4000;
14 rows updated.
SQL> commit;
Commit complete.
SQL> select empno,sal from emp;
EMPNO SAL
---------- ----------
7369 4000
7499 4000
7521 4000
7566 4000
7654 4000
7698 4000
7782 4000
7788 4000
7839 4000
7844 4000
7876 4000
EMPNO SAL
---------- ----------
7900 4000
7902 4000
7934 4000
14 rows selected.

=====================================================

摘錄:http://tolywang.itpub.net/post/48/451660

Logmnr在Oracle 9i以後做了眾多的改進，如可以不需要build flat文字檔案就可以分析日誌了，也就表示可以不需要修改引數utl_file_dir就可以分析日誌了，避免了修改引數utl_file_dir導致的重起資料庫問題。另外也開始支援把字典資訊build到聯機日誌中，在異地分析歸檔日誌。

在flashback也不能幫上忙的時候，logmnr卻是非常有用的。因為只要誤操作時期的歸檔日誌存在，就可以透過歸檔日誌來恢復誤刪除（delete）的資料。

如果在Oracle 9i以上，採用在本地的線上資料字典分析歸檔日誌，就這麼簡單：

Piner@9iR2>exec -
sys.dbms_logmnr.add_logfile(LogFileName=>'/archive_log/archive/1_9.arc',-
options => dbms_logmnr.new);
PL/SQL procedure successfully completed.
Piner@9iR2>exec sys.dbms_logmnr.start_logmnr( -
Options => sys.dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.

可以看到，線上分析其實就只需要這兩步，新增日誌並分析日誌。注意以上OPTIONS => DBMS_LOGMNR.NEW，表示新增第一個日誌，如需要另外新增更多的日誌，可以用如下方式即可。

Piner@9iR2>exec sys.dbms_logmnr.add_logfile(LogFileName=>'/archive_log/archive/1_10.arc');

如果日誌分析完成，可以把需要的資訊儲存到臨時表，如

Piner@9iR2>create table tmp_logmnr as
2 select operation,sql_redo,sql_undo from v$logmnr_contents
3 where seg_name='TEST';

然後，終止日誌分析過程。

Piner@9iR2>exec sys.dbms_logmnr.end_logmnr

對於臨時表中的SQL_UNDO，可以選擇性的恢復，如採用如下的指令碼來恢復，這裡為了減少阻塞，每1000條提交一次。另外，需要注意的是，SQL_UNDO中的分號，如果想用動態SQL來執行的話，是需要去掉的。

declare
mysql varchar2(4000);
num number :=0;
begin
for c_tmp in (select sql_undo from tmp_logmnr where peration = 'DELETE') loop
--去掉語句中的分號,這裡假定只有語句結尾有分號，語句中間並沒有分號。
--如果語句中也有分號，則可以考慮替換語句結尾的;'（分號單引號）為'（單引號）。
mysql := replace(c_tmp.sql_undo,';','');
execute immediate mysql;
num := num + 1;
if mod(num,1000)=0 then
commit;
end if;
end loop;
commit;
exception
when others then
--異常處理
end;

以上的PL/SQL程式碼其實還可以加強，如恢復成功一條，就設定狀態值為1，否則，設定狀態值為-1，方便跟蹤那些記錄恢復成功，哪些記錄恢復失敗了。

另外，需要注意的是，在Oracle 10g以下，LOGMNR的臨時表v$logmnr_contents，使用的是system表空間，在Oracle 10g以後改為sysaux表空間。可以使用如下的命令，更改logmnr的特定表空間，防止system表空間出現空間不夠。

flashback query和logmnr用於恢復資料的誤刪除（轉）

相關文章