實戰(zhàn):在linux as4的oracle9i (9.2.0.6.0)利用logminer恢復誤刪數據
author: LiuYX
date:2007-06-23
Blog: http://www.tkk7.com/liuyxit

?? 今天在客戶現場開發(fā)，突然計劃排產員跑了上來，說發(fā)現一件很奇怪的事，剛打印的生產計劃單，再也找不到了，另有一張未審的單也同時不見了。
打開應用系統(tǒng)的日志，發(fā)現他只刪了一張單，經確認這張是的確要刪的單據，并不是上面兩張單的其中之一。
?? 唯一解析是有人誤刪了這兩張單,沒有辦法之下只有查看Oracle的操作日志了,于是logminer就擺了上臺,之前大部分在測試環(huán)境下操作,這次來了個實戰(zhàn),心底未免有些緊張。
下面記錄恢復過程以備后用!

1.打開SecureCRT用root用戶登陸數據服務器

2.轉到oracle用戶
su - oracle

3.運行sqlplus,用管理員權限連接
sqlplus /nolog
SQL> conn /as sysdba

4.先查一下系統(tǒng)參數UTL_FILE_DIR的當前值
SQL> show parameter UTL_FILE_DIR

NAME???????????????????????????????? TYPE??????? VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir???????????????????????? string?????

當前還沒有設值,好!那就設吧
alter system set UTL_FILE_DIR ='/home/oracle/logdict' scope=both;

發(fā)現出錯
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

才發(fā)現原來一些參數不能即時生效的，必須修改到spfile，重啟數據庫才可以。只好：
alter system set UTL_FILE_DIR ='/home/oracle/logdict' scope=spfile;

5.關閉實例,并重新開閉
shutdown immediate;
startup;

6.安裝logminer工具需要運行下面兩個sql(環(huán)境變量$ORACLE_HOME用實際的路徑代替)
SQL> @$ORACLE_HOME/rdbms/admin/dbmslm.sql
SQL> @$ORACLE_HOME/rdbms/admin/dbmslmd.sql
注：　這兩個腳本必須均以SYS用戶身份運行。其中第一個腳本用來創(chuàng)建DBMS_LOGMNR包，該包用來分析日志文件。第二個腳本用來創(chuàng)建DBMS_LOGMNR_D包，該包用來創(chuàng)建數據字典文件。

7.創(chuàng)建數據字典文件
EXECUTE dbms_logmnr_d.build( 'dictionary.ora', '/home/oracle/logdict');

注:第二個參數的路徑應該先建好,如
cd /home/oracle
mkdir logdict

8.加入需要分析的在線重作日志文件
EXECUTE dbms_logmnr.add_logfile('/opt/oracle/oradata/means/redo01.log', dbms_logmnr.new);
EXECUTE dbms_logmnr.add_logfile('/opt/oracle/oradata/means/redo02.log', dbms_logmnr.addfile);
EXECUTE dbms_logmnr.add_logfile('/opt/oracle/oradata/means/redo03.log', dbms_logmnr.addfile);

注：
如果需要從分析列表里去掉一個文件用
EXECUTE dbms_logmnr.add_logfile('d:\ORACLE\ORADATA\ORA\REDO03_1.LOG',dbms_logmnr.removefile);

查詢在線日志文件用
select * from v$log; --根據顯示結果可知當前日志的組號為2

GROUP#??? THREAD#? SEQUENCE#????? BYTES??? MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
???????? 1????????? 1??????? 169? 104857600????????? 1 NO? INACTIVE
???? 14162736 23-JUN-07

???????? 2????????? 1??????? 170? 104857600????????? 1 NO? CURRENT
???? 14181604 23-JUN-07

???????? 3????????? 1??????? 171? 104857600????????? 1 NO? INACTIVE
???? 14230307 23-JUN-07

select * from v$logfile; --/根據組號可以找到對應的日志文件為redo02.log
?GROUP# STATUS? TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
???????? 1???????? ONLINE
/opt/oracle/oradata/means/redo01.log

???????? 2???????? ONLINE
/opt/oracle/oradata/means/redo02.log

???????? 3???????? ONLINE
/opt/oracle/oradata/means/redo03.log

如果確認操作是在當前日志中，可以只分析redo02.log。

9.執(zhí)行日志分析(全部分析出來)
EXECUTE dbms_logmnr.start_logmnr( DictFileName=>'/home/oracle/logdict/dictionary.ora');

不知為什么，我按網上的資料定了日期段,就是不通過,執(zhí)行結果如下：
EXECUTE dbms_logmnr.start_logmnr( DictFileName => '/home/oracle/logdict/dictionary.ora',StartTime => to_date('2007-6-23 00:00:00','YYYY-MM-DD HH24:MI:SS'),EndTime => to_date('2007-6-23 11:00:00','YYYY-MM-DD HH24:MI:SS'));
ERROR at line 1:
ORA-01291: missing logfile
ORA-06512: at "SYS.DBMS_LOGMNR", line 53
ORA-06512: at line 1

10.這時可就可根據V$logmnr_contents視圖的內容來查閱數據的歷史操作了

11.導出執(zhí)行過的sql
set?? heading?? off
spool /home/oracle/logdict/log.txt?
SELECT sql_redo FROM V$logmnr_contents WHERE sql_redo like 'delete from "MEANS"."AL_MAIN_PLAN" where "L_ORDER_ID" = ''6073''%';
spool off

12.后來發(fā)現有更方便的方法
create table means.log_contents as select * from V$logmnr_contents

13.然后用toad來查就更方便了!

如有錯漏請高手指教，TKS!

附v$logmnr_contents的結構和常用字段說明

?Name????????????????????????????????????? Null???? Type
?----------------------------------------- -------- ----------------------------
?SCN??????????????????????????????????????????????? NUMBER?--System Change Number 可用select dbms_flashback.get_system_change_number from dual;獲得當前改變號
?CSCN?????????????????????????????????????????????? NUMBER
?TIMESTAMP????????????????????????????????????????? DATE?--執(zhí)行操作的時間
?COMMIT_TIMESTAMP?????????????????????????????????? DATE
?THREAD#??????????????????????????????????????????? NUMBER
?LOG_ID???????????????????????????????????????????? NUMBER
?XIDUSN???????????????????????????????????????????? NUMBER
?XIDSLT???????????????????????????????????????????? NUMBER
?XIDSQN???????????????????????????????????????????? NUMBER
?PXIDUSN??????????????????????????????????????????? NUMBER
?PXIDSLT??????????????????????????????????????????? NUMBER
?PXIDSQN??????????????????????????????????????????? NUMBER
?RBASQN???????????????????????????????????????????? NUMBER
?RBABLK???????????????????????????????????????????? NUMBER
?RBABYTE??????????????????????????????????????????? NUMBER
?UBAFIL???????????????????????????????????????????? NUMBER
?UBABLK???????????????????????????????????????????? NUMBER
?UBAREC???????????????????????????????????????????? NUMBER
?UBASQN???????????????????????????????????????????? NUMBER
?ABS_FILE#????????????????????????????????????????? NUMBER
?REL_FILE#????????????????????????????????????????? NUMBER
?DATA_BLK#????????????????????????????????????????? NUMBER
?DATA_OBJ#????????????????????????????????????????? NUMBER
?DATA_OBJD#???????????????????????????????????????? NUMBER
?SEG_OWNER????????????????????????????????????????? VARCHAR2(32)
?SEG_NAME?????????????????????????????????????????? VARCHAR2(256)
?SEG_TYPE?????????????????????????????????????????? NUMBER
?SEG_TYPE_NAME????????????????????????????????????? VARCHAR2(32)
?TABLE_SPACE??????????????????????????????????????? VARCHAR2(32)
?ROW_ID???????????????????????????????????????????? VARCHAR2(19)
?SESSION#?????????????????????????????????????????? NUMBER
?SERIAL#??????????????????????????????????????????? NUMBER
?USERNAME?????????????????????????????????????????? VARCHAR2(30)
?SESSION_INFO?????????????????????????????????????? VARCHAR2(4000)
?TX_NAME??????????????????????????????????????????? VARCHAR2(256)
?ROLLBACK?????????????????????????????????????????? NUMBER
?OPERATION????????????????????????????????????????? VARCHAR2(32)
?OPERATION_CODE???????????????????????????????????? NUMBER
?SQL_REDO?????????????????????????????????????????? VARCHAR2(4000)
?SQL_UNDO?????????????????????????????????????????? VARCHAR2(4000)
?RS_ID????????????????????????????????????????????? VARCHAR2(32)
?SEQUENCE#????????????????????????????????????????? NUMBER
?SSN??????????????????????????????????????????????? NUMBER
?CSF??????????????????????????????????????????????? NUMBER
?INFO?????????????????????????????????????????????? VARCHAR2(32)
?STATUS???????????????????????????????????????????? NUMBER
?REDO_VALUE???????????????????????????????????????? RAW(4)
?UNDO_VALUE???????????????????????????????????????? RAW(4)
?SQL_COLUMN_TYPE??????????????????????????????????? VARCHAR2(32)
?SQL_COLUMN_NAME??????????????????????????????????? VARCHAR2(32)
?REDO_LENGTH??????????????????????????????????????? NUMBER
?REDO_OFFSET??????????????????????????????????????? NUMBER
?UNDO_LENGTH??????????????????????????????????????? NUMBER
?UNDO_OFFSET??????????????????????????????????????? NUMBER