潛水珽的異想世界

如果archive log因為已經因為rman備份的緣故被刪除時,必須透過rman restore的方式將備份檔中的archive log file還原出來。
首先必須先查一下我們要還原多少的archive log?

## find the archuve seq no.
SQL>select thread#,sequence#,first_time from V$LOG_HISTORY where first_time>sysdate-3 and first_time<sysdate-1;
or
SQL>select * from SYS.V_$LOGHIST;
or
更簡單的方式是去查alert log中redo.log switch的紀錄.

使用rman的restore 將archive log file 還原至相關目錄(透過set archivelog destination命令來設定)
## rman restore archive log:

RMAN>run{
     set archivelog destination to '/nas191/NTUH2/Mon/old';
     restore archivelog low logseq 181461 high logseq 181466;
     }


開始log miner的工作

設定好UTL_FILE_DIR參數,設定完後必須重新啟動資料庫.


產生logminer時所需要的空間來放logminer的資料,可以重複使用,logminer後若要刪除直接砍掉即可.
dictionary_filename名稱可自訂,dictionary_location請和參數檔UTL_FILE_DIR所設定的路徑相同.(19MB大約花10分鐘左右)
SQL>execute dbms_logmnr_d.build(dictionary_filename => 'kittydict.ora',dictionary_location => '/oratestdata/arch/utl_dir');  


將archive log file 加入logminer 數據字典中,透過dbms_logmnr.new、dbms_logmnr.addfile、dbms_logmnr.removefile來控制archive log 的加入與移除。
SQL>execute dbms_logmnr.add_logfile(options => dbms_logmnr.new,logfilename => '/nas191/NTUH2/Mon/old/2041829088_1_181461.ARC');
SQL>execute dbms_logmnr.add_logfile(options => dbms_logmnr.addfile,logfilename => '/nas191/NTUH2/Mon/old/2041829088_1_181462.ARC');
SQL>execute dbms_logmnr.add_logfile(options => dbms_logmnr.addfile,logfilename => '/nas191/NTUH2/Mon/old/2041829088_1_181463.ARC');
SQL>execute dbms_logmnr.add_logfile(options => dbms_logmnr.addfile,logfilename => '/nas191/NTUH2/Mon/old/2041829088_1_181464.ARC');
SQL>execute dbms_logmnr.add_logfile(options => dbms_logmnr.addfile,logfilename => '/nas191/NTUH2/Mon/old/2041829088_1_181465.ARC');
SQL>execute dbms_logmnr.add_logfile(options => dbms_logmnr.addfile,logfilename => '/nas191/NTUH2/Mon/old/2041829088_1_181466.ARC');

加入後請透過v$logmnr_logs來查詢一下是否相符.
SQL>select filename from v$logmnr_logs;

啟動logminer process,進行redo log miner.(56MB大約執行5分鐘左右)
SQL>execute dbms_logmnr.start_logmnr(startTime => to_date('20080811073000','yyyy-mm-dd hh24:mi:ss'),endTime => to_date('20080811080000','yyyy-mm-dd hh24:mi:ss'),DictFileName => '/oradata/arch/utl_dir/dict.ora');

log miner作業結束後,請立即進行查詢,或透過
create table XXX as select * from v$logmnr_contents將v$logmnr_contents的資料
寫入XXX table中,以免session 斷掉時logminer processes停掉而導致V$LOGMNR_*無法查詢.
SQL>create table z_redoinfo as select * v$logmnr_contents;   

全部的作業結束時,可以透過dbms_logmnr.end_logmnr或直接結束session來停掉log miner process.
SQL>execute dbms_logmnr.end_logmnr;


參考:

身為Oracle DBA，我們有時候需要追蹤資料誤刪除或用戶的惡意操作情況，此時我們不僅需要查出執行這些操作的資料庫帳號，還需要知道操作是由哪台用戶端（IP位址等）發出的。針對這些問題，一個最有效實用而又低成本的方法就是分析Oracle資料庫的日誌檔。本文將就Oracle日誌分析技術做深入探討。

一、如何分析即LogMiner解釋

從目前來看，分析Oracle日誌的唯一方法就是使用Oracle公司提供的LogMiner來進行， Oracle資料庫的所有更改都記錄在日誌中，但是原始的日誌資訊我們根本無法看懂，而LogMiner就是讓我們看懂日誌資訊的工具。從這一點上看，它和tkprof差不多，一個是用來分析日誌資訊，一個則是格式化跟蹤檔。通過對日誌的分析我們可以實現下面的目的：

1、查明資料庫的邏輯更改；

2、偵察並更正用戶的誤操作；

3、執行事後審計；

4、執行變化分析。

不僅如此，日誌中記錄的資訊還包括：資料庫的更改歷史、更改類型（INSERT、UPDATE、DELETE、DDL等）、更改對應的SCN號、以及執行這些操作的用戶資訊等，LogMiner在分析日誌時，將重構等價的SQL語句和UNDO語句（分別記錄在V$LOGMNR_CONTENTS視圖的SQL_REDO和SQL_UNDO中）。這裏需要注意的是等價語句，而並非原始SQL語句，例如：我們最初執行的是“delete a where c1 <>'cyx';”，而LogMiner重構的是等價的6條DELETE語句。所以我們應該意識到V$LOGMNR_CONTENTS視圖中顯示的並非是原版的現實，從資料庫角度來講這是很容易理解的，它記錄的是元操作，因為同樣是“delete a where c1 <>'cyx';”語句，在不同的環境中，實際刪除的記錄數可能各不相同，因此記錄這樣的語句實際上並沒有什麼實際意義，LogMiner重構的是在實際情況下轉化成元操作的多個單條語句。

另外由於Oracle重做日誌中記錄的並非原始的物件（如表以及其中的列）名稱，而只是它們在Oracle資料庫中的內部編號（對於表來說是它們在資料庫中的物件ID，而對於表中的列來說，對應的則是該列在表中的排列序號：COL 1, COL 2 等），因此為了使LogMiner重構出的SQL語句易於識別，我們需要將這些編號轉化成相應的名稱，這就需要用到資料字典（也就說LogMiner本身是可以不用資料字典的，詳見下面的分析過程），LogMiner利用DBMS_LOGMNR_D.BUILD()過程來提取資料字典資訊。

LogMiner包含兩個PL/SQL包和幾個視圖：

1、dbms_logmnr_d包，這個包只包括一個用於提取資料字典資訊的過程，即dbms_logmnr_d.build()過程。

2、dbms_logmnr包，它有三個過程：

add_logfile(name varchar2, options number) - 用來添加/刪除用於分析的日誌檔；

start_logmnr(start_scn number, end_scn number, start_time number,end_time number, dictfilename varchar2, options number) - 用來開啟日誌分析，同時確定分析的時間/SCN視窗以及確認是否使用提取出來的資料字典資訊。

end_logmnr() - 用來終止分析會話，它將回收LogMiner所佔用的記憶體。

與LogMiner相關的資料字典。

1、v$logmnr_dictionary，LogMiner可能使用的資料字典資訊，因logmnr可以有多個字典檔，該視圖用於顯示這方面資訊。

2、v$logmnr_parameters，當前LogMiner所設定的參數資訊。

3、v$logmnr_logs，當前用於分析的日誌列表。

4、v$logmnr_contents，日誌分析結果。

二、Oracle9i LogMiner的增強：

1、支援更多資料/存儲類型：鏈結/遷移行、CLUSTER表操作、DIRECT PATH插入以及DDL操作。在V$LOGMNR_CONTENTS的SQL_REDO中可以看到DDL操作的原句（CREATE USER除外，其中的密碼將以加密的形式出現，而不是原始密碼）。如果TX_AUDITING初始化參數設為TRUE，則所有操作的資料庫帳號將被記錄。

2、提取和使用資料字典的選項：現在資料字典不僅可以提取到一個外部檔中，還可以直接提取到重做日誌流中，它在日誌流中提供了操作當時的資料字典快照，這樣就可以實現離線分析。

3、允許對DML操作按事務進行分組：可以在START_LOGMNR()中設置COMMITTED_DATA_ONLY選項，實現對DML操作的分組，這樣將按SCN的順序返回已經提交的事務。

4、支持SCHEMA的變化：在資料庫打開的狀態下，如果使用了LogMiner的DDL_DICT_TRACKING選項，Oracle9i的LogMiner將自動對比最初的日誌流和當前系統的資料字典，並返回正確的DDL語句，並且會自動偵察並標記當前資料字典和最初日誌流之間的差別，這樣即使最初日誌流中所涉及的表已經被更改或者根本已經不存在，LogMiner同樣會返回正確的DDL語句。

5、在日誌中記錄更多列資訊的能力：例如對於UPDATE操作不僅會記錄被更新行的情況，還可以捕捉更多前影資訊。

6、支援基於數值的查詢：Oracle9i LogMiner在支援原有基於元資料（操作、物件等）查詢的基礎上，開始支持基於實際涉及到的資料的查詢。例如涉及一個工資表，現在我們可以很容易地查出員工工資由1000變成2000的原始更新語句，而在之前我們只能選出所有的更新語句。

三、Oracle8i/9i的日誌分析過程

LogMiner只要在實例起來的情況下都可以運行，LogMiner使用一個字典檔來實現Oracle內部物件名稱的轉換，如果沒有這個字典檔，則直接顯示內部物件編號，例如我們執行下面的語句：

如果想要使用字典檔，資料庫至少應該出於MOUNT狀態。然後執行dbms_logmnr_d.build過程將資料字典資訊提取到一個外部檔中。下面是具體分析步驟：

1、確認設置了初始化參數：UTL_FILE_DIR，並確認Oracle對改目錄擁有讀寫許可權，然後啟動實例。 示例中UTL_FILE_DIR參數如下：

這個目錄主要用於存放dbms_logmnr_d.build過程所產生的字典資訊檔，如果不用這個，則可以不設，也就跳過下面一步。

2、生成字典資訊檔：

其中dictionary_location指的是字典資訊檔的存放位置,它必須完全匹配UTL_FILE_DIR的值，例如：假設UTL_FILE_DIR=/data6/cyx/logmnr/，則上面這條語句會出錯，只因為UTL_FILE_DIR後面多了一個“/”，而在很多其他地方對這一“/”是不敏感的。

dictionary_filename指的是放於字典資訊檔的名字，可以任意取。當然我們也可以不明確寫出這兩個選項，即寫成：

如果你第一步的參數沒有設，而直接開始這一步，Oracle會報下面的錯誤：

需要注意的是，在oracle817 for Windows版中會出現以下錯誤：

解決辦法：

保存檔，然後執行一遍這個腳本：

然後重新編譯DBMS_LOGMNR_D包：

3、添加需要分析的日誌檔

這裏的options選項有三個參數可以用：

NEW - 表示創建一個新的日誌檔列表

ADDFILE - 表示向這個列表中添加日誌檔，如下面的例子

REMOVEFILE - 和addfile相反。

4、當你添加了需要分析的日誌檔後，我們就可以讓LogMiner開始分析了：

如果你沒有使用字典資訊檔（此時我們只需要啟動實例就可以了），那麼就不需要跟dictfilename參數：

當然dbms_logmnr.start_logmnr()過程還有其他幾個用於定義分析日誌時間/SCN視窗的參數，它們分別是：

STARTSCN / ENDSCN - 定義分析的起始/結束SCN號，

STARTTIME / ENDTIME - 定義分析的起始/結束時間。

例如下面的過程將只分析從 '2003-09-21 09:39:00'到'2003-09-21 09:45:00'這段時間的日誌：

上面過程第一行結尾的“-”表示轉行，如果你在同一行，則不需要。我們可以看到有效日誌的時間戳：

這裏需要注意的是，因為我之前已經設置NLS_DATE_FORMAT環境變數，所以上面的日期可以直接按這個格式寫就行了，如果你沒有設，則需要使用to_date函數來轉換一下。