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

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

1.打開(kāi)SecureCRT用root用戶登陸數(shù)據(jù)服務(wù)器

2.轉(zhuǎn)到oracle用戶
su - oracle

3.運(yùn)行sqlplus,用管理員權(quán)限連接
sqlplus /nolog
SQL> conn /as sysdba

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

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

當(dāng)前還沒(méi)有設(shè)值,好!那就設(shè)吧
alter system set UTL_FILE_DIR ='/home/oracle/logdict' scope=both;

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

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

5.關(guān)閉實(shí)例,并重新開(kāi)閉
shutdown immediate;
startup;

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

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

注:第二個(gè)參數(shù)的路徑應(yīng)該先建好,如
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);

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

查詢?cè)诰€日志文件用
select * from v$log; --根據(jù)顯示結(jié)果可知當(dāng)前日志的組號(hào)為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; --/根據(jù)組號(hào)可以找到對(duì)應(yīng)的日志文件為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

如果確認(rèn)操作是在當(dāng)前日志中，可以只分析redo02.log。

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

不知為什么，我按網(wǎng)上的資料定了日期段,就是不通過(guò),執(zhí)行結(jié)果如下：
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.這時(shí)可就可根據(jù)V$logmnr_contents視圖的內(nèi)容來(lái)查閱數(shù)據(jù)的歷史操作了

11.導(dǎo)出執(zhí)行過(guò)的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.后來(lái)發(fā)現(xiàn)有更方便的方法
create table means.log_contents as select * from V$logmnr_contents

13.然后用toad來(lái)查就更方便了!

如有錯(cuò)漏請(qǐng)高手指教，TKS!

附v$logmnr_contents的結(jié)構(gòu)和常用字段說(shuō)明

?Name????????????????????????????????????? Null???? Type
?----------------------------------------- -------- ----------------------------
?SCN??????????????????????????????????????????????? NUMBER?--System Change Number 可用select dbms_flashback.get_system_change_number from dual;獲得當(dāng)前改變號(hào)
?CSCN?????????????????????????????????????????????? NUMBER
?TIMESTAMP????????????????????????????????????????? DATE?--執(zhí)行操作的時(shí)間
?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