手工備份恢復--LogMiner

　　在以前的練習中我們討論了歸檔重做日志文件對歸檔重做日志文件進行了備份並用於恢復操作利用這些文件可以把改變傳遞到一個備用數據庫中並將一個表空間及時恢復到一個特定的時間點你是否考慮到什麼方法可以直接觀察一個Oracle重做日志文件的內部結構？從Oraclei開始可以是用LogMiner工具查看一個或者多個日志文件的內容包括一些數據字典視圖和存儲過程在本練習中將練習安裝LogMiner並使用LogMiner來查看和分析數據庫重做日志文件的內容
當Oracle改變數據塊時它把重做信息寫入當前的聯機重做日志文件重做日志文件包括更改時間對象標識符更改的SCN號數據塊發生的操作和其他的重要信息該文件不但包括使用者對數據塊所做的更改也包括回退段中恢復塊的更改Oracle在數據字典中使用數字標識符來標識對象的相關信息例如為一個表制定一個對象號並且每列有一個列標志符每列都有一個相應的數據類型標識符表示該列是varchardatenumber等變量使用LogMiner可以讀出重做文件的內容然後重新產生或恢復產生原始重做信息的SQL表達式使用數據字典文件LogMiner將Oracle對象標識符翻譯成可以看得懂的表和列
在進行本練習之前對相關數據進行解釋

當前數據庫(Current database)當LogMiner使用產生日志文件的服務器來分析重做日志文件時重做日志文件來自當前數據庫
外部數據庫(Foreign database)當LogMiner可以分析異於當前數據庫的其他數據庫產生的重做日志文件該數據庫就是有一個外部數據庫
字典文件(Dictionary file)LogMiner使用字典文件將Oracle內部對象標識與表名稱列數據類型以及其他對象關聯起來需要注意的是字典文件必須由創建重做日志文件的數據庫產生

　　很多情況下需要使用LogMiner如數據庫表中的數據莫名其妙被修改了使用LogMiner可以檢查哪個改變的運行細節也可以使用LogMiner來撤銷這些更改可以使用LogMiner檢查一個或多個表中SQL改變發生的次數從而檢查表上的工作量通過進一步檢查LogMiner可以指出一個錯誤的DROP TABLE或者一個DDL語句發生的准確時間和SCN
使用LogMiner需要理解以下要點

可以在一個已經加載或者未加載的數據庫上使用LogMiner
分析一個來自外來數據庫重做日志文件時當前數據庫的塊大小必須和外部數據庫一樣的或者更大因為在熱備份模式表空間中一個塊的首次改變作為一個單獨的重做記錄在重做流中創建一個完整的鏡像如果LogMiner使用較小的塊尺寸的數據庫讀取重做會發生錯誤因為單獨的重做記錄無法放入一個塊中
雖然LogMiner不解釋重做日志的DDL語句如DROP TABLE這樣的DDL語句但仍舊會在數據字典創建DML這些字典DML語句可以用來檢查對數據庫發布的DDL命令
LogMiner不會重構原始的無日志的SQL操作但在數據字典上的DML操作結果將記錄到日志文件裡
雖然LogMiner可以偶然用來日志文件分析但不能當做常規意義上使用的一種功能特別是一個產生龐大數量重做信息的數據庫挖掘重做信息使用LogMiner分析龐大數量的重做信息是一件非常耗費時間的事情
如果LogMiner用來從其他數據庫的日志文件裡挖掘信息無論原數據庫還是被分析的數據庫都必須在使用相同的硬件平台和操作系統而且原數據庫和被分析數據庫必須使用相同的字符集
LogMiner無法分析某些數據庫對象或數據類型產生的重做例如按索引組織的表成簇的表/索引非標量數據類型和鏈接的行
盡管可以創建數據字典DML但LogMiner無法從直接的路徑插入操作中生成原始的SQL
LogMiner將顯示為提交的事務因為重做日志包含提交的和未提交的數據

　　練習分析重做文件

　　通過LogMiner讀取重做日志事務涉及使用Oracle提供的包過程數據字典視圖和一個數據字典外部文件下面是應用LogMiner分析重做日志文件的操作過程使用LogMiner存儲過程創建一個外部數據字典文件然後使用另一個存儲過程創建一個分析重做文件的列表最後執行另一個存儲過程來啟動LogMiner在完成這些步驟以後從一個顯示日志文件內容的數據字典視圖裡進行選擇當查詢這個視圖時Oracle讀取日志文件然後以特定格式返回結果一旦分析列出的日志文件就調用另一個存儲過程停止LogMiner

　　類型

　　過程名

　　用途

　　過程

　　Dbms_logmnr_dbuild

　　創建一個數據字典文件

　　過程

　　Dbms_logmnradd_logfile

　　在類表中增加日志文件以供分析

　　過程

　　Dbms_logmnrstart_logmnr

　　使用一個可選的字典文件和前面確定要分析日志文件來啟動LogMiner

　　過程

　　Dbms_logmnrend_logmnr

　　停止LogMiner分析

　　視圖

　　V$logmnr_dictionary

　　顯示用來決定對象ID名稱的字典文件的信息

　　視圖

　　V$logmnr_logs

　　在LogMiner啟動時顯示分析的日志列表

　　視圖

　　V$logmnr_contents

　　LogMiner啟動後可以使用該視圖在SQL提示符下輸入SQL語句來查詢重做日志的內容

　　在本練習中將觀察SCOTTEMP表中一行記錄的特定改變的詳細情況然後看到如何通過分析一個特定表上記錄改變操作的重做日志文件來檢查這個表數據庫更改操作最後將學會怎樣為一個特定的DROP TABLE命令確定准確的時間和SCN

　　步驟一創建數據字典文件

　　使用LogMiner創建數據字典是一個可選步驟字典文件極大增加了日志文件內容的可讀性
數據字典建立Oracle內部對象ID數據到表列以及其他數據類型名稱之間的對應關系數據字典是一個文本文件相當於數據字典的相關部分本步驟中將創建一個數據字典文件然後指示LogMiner應用該文件把內容的數據翻譯成步驟四中分析日志文件能夠識別的名稱

字典文件應該和日志文件的日期一致如果LogMiner分析的一個對象無法在字典文件中找到LogMiner將無法顯示數據庫對象的表和列的名稱
建立字典文件的數據庫必須和建立日志文件的數據庫相同例如不能使用克隆數據庫中的字典文件來分析PRACTICE數據庫中的日志文件

　　在建立數據字典文件之前PRACTICE數據庫必須寫到服務器硬盤的一個目錄下Oracleg之前數據庫參數文件內設置UTL_FILE_DIR這個參數運行為Orace可以使用的PL/SQL文件I/O定義一個或多個目錄可以在這個參數文件裡連續地分行輸入UTL_FILE_DIR以定義更多的目錄如下所示

Utl_file_dir= D:\oracle\CODE\chap而Oracleg可以使用如下進行設置

CREATE DIRECTORY utl AS D:\oracle\CODE\chap;

　　為了創建一個數據字典文件可以執行DBMS_LOGMNR_D包中的BUILD過程該過程查詢當前的數據庫字典表然後創建一個包含其內容基於文本的文件該文件在調用的過程目錄中生成執行的過程如下

SQL>EXECUTE dbms_logmnr_dbuild(dictionary_filename => dictionaryora dictionary_location => D:\oracle\CODE\chap);

該過程執行後在D:\oracle\CODE\chap目錄下打開文件dictionaryora浏覽其內容

　　步驟二產生數據庫操作
本步驟中進行的操作將在步驟五進行分析驗證
首先確認在TINADATE_LOG表中生成行記錄
其次改變SCOTTEMP表的一行記錄使用下面的SQL語句改變其中一個雇員的薪水和傭金

SQL>CONNECT scott/tiger@practice
SQL>UPDATE emp SET sal= comm= where empno=;
SQL>COMMIT;

　　使用SQL*PLUS命令SET TIME ON記錄這些SQL命令運行的時間set time on 命令生效後給SQL提示增加一個時間值如下所示

:: SQL>DROP TABLE bonus;

　　UPDATE和DROP（字典中對應的DML）語句的重做信息將保存在當前聯機重做日志文件中從v$log視圖中找出日志文件的序號最後做一個日志切換使當前聯機日志歸檔運行LogMiner來分析這個新歸檔的日志文件試驗一下是否能從這個日志中找到關於SQL更新和刪除表的信息

SQL>SELECT sequence# FROM v$log WHERE status=CURRENT;
SQL>ALTER SYSTEM SWITCH LOGFILE;

　　在本練習討論中日志切換前的當前日志序號是因此日志切換期間產生的歸檔日志文件的名稱是ARC_

　　步驟三分析指定日志文件
LogMiner需創建一個可供分析日志文件列表來分析指定的日志文件使用Oracle提供的DBMS_LOGMNR包中的ADD_LOGFILE過程來添加日志文件增加的日志文件顯示在v$logmnr_logs視圖裡

SQL >conn sys/system@practice as sysdba;
SQL>SELECT db_name thread_sqn filename FROM v$logmnr_logs;

　　前一個任務中SQL所做的改變保存在序號為的日志文件中在LogMiner分析前添加該日志文件和前面的兩個文件

BEGIN
dbms_logmnradd_logfile(logfilename=>D:\oracle\PRACTICE\ARCHIVE\ARC_ options=>dbms_logmnrNEW);
dbms_logmnradd_logfile(logfilename=>D:\oracle\PRACTICE\ARCHIVE\ARC_ options=>dbms_logmnrADDFILE);
dbms_logmnradd_logfile(logfilename=>D:\oracle\PRACTICE\ARCHIVE\ARC_ options=>dbms_logmnrADDFILE);
END;
/

　　Add_logfile過程第一次調用時選項設置為dbms_logmnrNEW這就創建了供LogMiner分析新的日志文件列表之後調用add_logfile過程使用ADDFILE選項為新建立的列表添加文件每次使用NEW選項時任何在此之前添加的文件將從該列表刪除
查詢v$logmnr_logs視圖將看到已經添加到列表中文件

SQL>SELECT db_name thread_sqn filename FROM v$logmnr_logs;

　　可以在DBMS_LOGMNRADD_LOGFILE過程中指定REMOVEFILE選項來刪除重做日志文件在本練習中序號的日志文件沒有包含尋找的重做信息因此可以將序號的日志文件從分析的列表中刪除

EXECUTE dbms_logmnradd_logfile(logfilename=>D:\oracle\PRACTICE\ARCHIVE\ARC_ options=>dbms_logmnrREMOVEFILE);

　　步驟四啟動LogMiner
准備好字典文件和日志文件列表後准備啟動LogMiner

EXECUTE dbms_logmnrstart_logmnr(dictfilename=>D:\oracle\CODE\chap\dictionaryora);

　　可以為該過程有選擇地制定其他幾個參數可以指定分析開始的SCN或結束的SCN可以制定分析開始時間或結束是時間如果必須分析大量的歸檔日志或者只想查看很少一段時間內的重做這兩個參數很有幫助
步驟五分析重做日志文件內容
在V$LOGMNR_CONTENTS視圖中包含日志文件ARC_和ARC_內容當查詢V$LOGMNR_CONTENTS視圖時包括在V$LOGMNR_CONTENTS的日志文件將順序讀出並以V$LOGMNR_CONTENTS視圖定義的行結構返回數據（該視圖有列）所有日志文件內容不是永久存放在一個表或者內存中因此每次選擇有關V$LOGMNR_CONTENTS內容時日志文件都被掃描如果需要多次檢查大量的日志文件來尋找需要的內容那麼最好使用CREATE TABLE AS SELECT(CTAS)命令制作一個結果的表

　　列名稱

　　描述

　　OPERATION

　　重做記錄中記錄的操作(INSERTUPDATEDELETE等)

　　TIMESTAMP

　　數據改變發生的日期和時間

　　SCN

　　特定數據變化的系統變更號

　　SEG_OWNER

　　段所有者名稱（如果段發生變更）

　　SEG_NAME

　　數據發生改變的段名稱

　　SEG_TYPYE

　　數據發生改變的段類型

　　TABLE_SPACE_NAME

　　變化段的表空間名稱

　　ROW_ID

　　特定數據變化行的ID

　　USER_NAME

　　執行數據改變的用戶名

　　SESSION_INFO

　　數據發生變化時用戶進程信息

　　SQL_REDO

　　可以為重做記錄重做指定行變化的SQL語句

　　SQL_UNDO

　　可以為重做記錄回退或恢復指定行變化的SQL語句

　　在該步驟中將使用PRACTICE數據庫中的重做信息完成三件事情

　　檢查數據更改的細節

　　數據庫裡的數據可能因為意想不到的原因或者因為錯誤而發生改變在重做日志文件中可以找到這些更改的細節在本練習中可能有報告顯示一個雇員的薪水和傭金發生了改變經理告訴你有一個名叫Turner的銷售員的薪水和傭金發生改變了她希望你能查出發生什麼事以及改變前的薪水和傭金數她還想知道更多的細節比如是誰做了這些改變以及改變是如何發生
回答她的問題需要查找設計SCOTTEMP表的所有SQL語句該表有一個表示當前薪水的SAL列和表示傭金的COMM列要找到所有涉及這個表的SQL可以運行如下SELECT語句

SELECT operationtimestampscn
   FROM v$logmnr_contents
  WHERE seg_name=EMP
    AND seg_owner=SCOTT
    AND seg_type_name=TABLE;

　　可以看出結果在月日上午對SCOTTEMP表執行了更新操作要找出該表所有的具體改變最好查看SQL_REDO和SQL_UNDO列

SELECT sql_redo sql_undo
   FROM v$logmnr_contents
  WHERE seg_name=EMP
    AND seg_owner=SCOTT
    AND seg_type_name=TABLE;

　　SQL_REDO列定義了特定的SQL語句可以使你得到與步驟二中SQL同樣的結果SQL_UNDO列包括的SQL允許撤銷SQL_REDO的改變實際上它表示更改前的數據值找到了數據的更改和更改發生的時間還想弄清楚誰做了這個更改？查看V$LOGMNR_CONTENTS的USERNAME列和SESSION_INFO列在這些列中將知道有用的會話信息可以幫助進一步解釋Tuner的薪水是如何改變的

SELECT username session_info
   FROM v$logmnr_contents
  WHERE seg_name=EMP
    AND seg_owner=SCOTT
    AND seg_type_name=TABLE;

可以看到某人使用用戶名SCOTT登錄並執行更新操作知道該嫌疑人使用名叫YIREN的操作系統登錄名叫YIREN機器上你可以把這個信息報告給經理同時使用SQL_UNDO列的內容把數據恢復到改變以前的數值檢查當前存在表內該行的內容確保他沒有改變後續的時間

SQL>update SCOTTEMP set SAL = COMM = where ROWID = AAAMBCAAEAAAAAgAAI;
SQL>COMMIT;

　　執行容量分析

　　作為DBA需要做一些性能調整或容量計劃要完成該任務可能設計到使用許多不同的工具和程序LogMiner是用來檢查數據庫表發生活動非常合適的工具例如LogMiner幫助調查表中DML次數和頻率雖然使用表審計來實現同樣的功能但當更新表的時候使用LogMiner不會減低運行時間性能而是用表審計會導致運行時間能能開銷過大
下面我們查詢一下TINADATE_LOG表的SQL操作啟動LogMiner檢查這個表執行插入更新和刪除的次數下面這個非常簡單的例子為分析數據表上DML操作提供了一些思路和方法

SELECT operation to_char(timestamp HH) hour count(*) total
   FROM v$logmnr_contents
  WHERE seg_name=EMP
    AND seg_owner=SCOTT
  GROUP BY operation to_char(timestamp HH);

　　查詢的結果顯示DATE_LOG每小時增加行記錄
當必須分析大量中作信息時要在全面分析和分析速度之間進行權衡添加到LogMiner列表中待分析的日志文件越多可以分析的重做信息也就越多然而LogMiner列表太多或者太大的日志文件訪問V$LOGMNR_CONTENTS視圖將會花費更長的時間

　　尋找DDL命令的細節

　　就像本練習所做的一樣在前面練習模擬了一個一位刪除表的操作在那些練習中我們曾經提到要知道錯誤刪除表的時間比較困難使用LogMiner可以推算出刪除表語句的確切時間和SCN可以將這個時間或者SCN用於不完全數據庫恢復或者表空間時時檢點恢復（TSPITR）
如前所述LogMiner只是將重做日志信息翻譯成數據處理語言（插入更新刪除）在V$LOGMNR_CONTENTS視圖中將不會找到DROP TABLE語句既然在DROP TABLE語句在數據字典表上運行了DML那麼可以從SYSOBJ$和SYSTAB$數據字典表中搜索發生刪除操作的視圖

SELECT seg_name operation scn timestamp count(*)
   FROM v$logmnr_contents
  WHERE operation=DELETE and timestamp >= to_date( :: yyyyMMdd HH:mi:ss)
    and timestamp <= to_date( :: yyyyMMdd HH:mi:ss)
  GROUP BY seg_name operation scn timestamp
  ORDER BY scn;

　　從以上SQL結果可以看出在 ::一個對象一個表和四個列(SCOTTBONUS有四列)從數據字典刪除了這個數據字典對象刪除操作可能就是刪除bonus表語句接下來可以查看DROP TABLE命令的SQL_REDO

SQL>SELECT sql_redo FROM v$logmnr_contents WHERE scn = AND seg_name=OBJ$;

　　在SQL_REDO列的輸出中有OBJ#這個和OBJ#相關聯的數值和存放在數據字典表TAB$OBJ#紅的Oracle ID一致要將刪除表和執行刪除表的SQL語句發生的時間時間和SCN聯系到一起需要刪除表在何時刪除以及表中包含多少列使用這個信息就可以找出准確的時間以及用戶信息通過文本編輯器打開字典文件查找單詞BONUS將看到如下插入語句
如果沒有在字典文件中找到相關BONUS的記錄就不可能在數據庫中已有這個表的情況下創建這個字典文件如果是這種情況就必須根據自己的判斷拼湊被刪除表的數據在SQL_REDO列內的OBJ#數值和第一次為刪除表而插入到數據字典文件的數值完全相同如果把這些信息關聯起來就可以確信刪除表的時間為 ::SCN號為如果必要使用這些數值可以執行TSPITR
簡而言之可以通過將數據字典文件的對象編碼與刪除操作發生的SYSOBJ$數據字典表的SQL_REDO列的對象標識符向匹配已找到刪除表命令發生的時間和SCN
步驟六關閉LogMiner
使用LogMiner後需要釋放該程序使用的資源當完成LogMiner工作後V$LOGMNR_CONTENTS的內容就不再可用如果需要更進一步分析重做日志文件的內容使用V$LOGMNR_CONTENTS視圖內容創建一個永久的數據庫表將非常有幫助

SQL>CREATE TABLE logmnr_contents AS SELECT * FROM v$logmnr_contents;

　　一旦完成重做日志的檢查運行DBMS_LOGMNR中的END_LOGMNR

EXECUTE dbms_lognmrend_logmnr;

　　結束LogMiner時釋放為重做日志分析分配的會話內存

From:http://tw.wingwit.com/Article/program/Oracle/201311/18477.html