logminer抽取日誌案例

客戶發現了一個表的資料沒有了，要求我找原因，不能在生產庫上做。我一聽，傻眼了，哪個天殺的乾的這種事！看來這回客戶動真格了，不過幹這種事，很累，會掉層皮，但還是答應做了，我跟客戶說，容我整理一下步驟，另外把這一段時間的歸檔留住一下：
思路大抵是這樣的：
1、在搭建一個測試環境，目錄與生產庫基本相當
2、做異機恢復
3、由於原資料庫太大，所以只考慮恢復資料庫必要的表空間：system\sysaux\undo就可以了！
4、使用logminer來挖掘日誌

 具體實施：
1、從生產庫建立一個pfile,然後拷貝到測試環境，有些引數需要修改一下，這個就不細說

 測試環境中，啟動資料庫到nomount狀態下：
SQL> startup nomount pfile='/u02/pfile.ora';
 ORACLE instance started.

 Total System Global Area 701485056 bytes
 Fixed Size 2216664 bytes
 Variable Size 301993256 bytes
 Database Buffers 394264576 bytes
 Redo Buffers 3010560 bytes

 2、建立一個密碼檔案，這個也可以在後來建立也行

[oracle@testdata u02]$ orapwd file='/u01/oracle/product/11.2.0/db1/dbs/pwduptest.ora' password=zixin01 entries=10;

 3、開始恢復

 如果有catalog的話，可以用rman target / catalog username/password@catalog
如果沒有的話就直接rman target / 就行了。
 我將備份拷貝到測試機相應的位置，然後進行恢復

[oracle@testdata u02]$ rman target /

 Recovery Manager: Release 11.2.0.1.0 - Production on Thu May 10 15:58:25 2011

 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

 connected to target database: UPTEST (not mounted)

--首先恢復的是控制檔案

RMAN> restore control from '/u02/backup/UPTEST_CTL_20110508_1.bak

 Starting restore at 10-MAY-11
 using target database control file instead of recovery catalog
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: SID=19 device type=DISK

 channel ORA_DISK_1: restoring control file
 channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
 Finished restore at 10-MAY-11

--恢復資料庫檔案

RMAN> alter database mount;
 run
 {
 set newname for datafile '/u01/oradata/undotbs1' to '/u01/oradata/undotbs1.dbf';
 set newname for datafile '/u01/oradata/system' to '/u01/oradata/system.dbf';
 set newname for datafile '/u01/oradata/sysaux' to '/u01/oradata/sysaux.dbf';
 restore database skip forever tablespace INCATEST,USERS,DB_USERS;
 switch datafile all;
 }

 executing command: SET NEWNAME

 executing command: SET NEWNAME

 executing command: SET NEWNAME

 Starting restore at 10-MAY-11
 using target database control file instead of recovery catalog
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: SID=18 device type=DISK

 channel ORA_DISK_1: starting datafile backup set restore
 channel ORA_DISK_1: specifying datafile(s) to restore from backup set
 channel ORA_DISK_1: restoring datafile 00001 to /u01/oradata/system.dbf
 channel ORA_DISK_1: restoring datafile 00002 to /u01/oradata/sysaux.dbf
 channel ORA_DISK_1: restoring datafile 00003 to /u01/oradata/undotbs1.dbf
 channel ORA_DISK_1: reading from backup piece /u02/backup/UPTEST_DF_20110508_1.bak
 channel ORA_DISK_1: piece handle=/u02/backup/UPTEST_DF_20110508_1.bak tag=TAG20110508T083516
 channel ORA_DISK_1: restored backup piece 1
 channel ORA_DISK_1: reading from backup piece /u02/backup/UPTEST_DF_20110510_2.bak
 channel ORA_DISK_1: piece handle=/u02/backup/UPTEST_DF_20110508_2.bak tag=TAG20110508T083516
 channel ORA_DISK_1: restored backup piece 2
 channel ORA_DISK_1: restore complete, elapsed time: 00:03:25
 Finished restore at 10-MAY-11

 datafile 1 switched to datafile copy
 input datafile copy RECID=8 STAMP=782929747 file name=/u01/oradata/system.dbf
 datafile 2 switched to datafile copy
 input datafile copy RECID=9 STAMP=782929747 file name=/u01/oradata/sysaux.dbf
 datafile 3 switched to datafile copy
 input datafile copy RECID=10 STAMP=782929747 file name=/u01/oradata/undotbs1.dbf

--資料庫的恢復
RMAN> recover database skip forever tablespace INCATEST,USERS,DB_USERS; ----使用skip的時候，會將不恢復的檔案offline drop!

 Starting recover at 10-MAY-11
 using channel ORA_DISK_1

 Executing: alter database datafile 7 offline drop
 Executing: alter database datafile 8 offline drop
 Executing: alter database datafile 4 offline drop
 Executing: alter database datafile 5 offline drop
 Executing: alter database datafile 6 offline drop
 Executing: alter database datafile 9 offline drop

 starting media recovery

 archived log file name=/u01/oradata/882170_1_782312693.log thread=1 sequence=882170
 archived log file name=/u01/oradata/882171_1_782312693.log thread=1 sequence=882171
 archived log file name=/u01/oradata/882172_1_782312693.log thread=1 sequence=882172
 archived log file name=/u01/oradata/882173_1_782312693.log thread=1 sequence=882173
 unable to find archived log
 archived log thread=1 sequence=882174
 RMAN-00571: ===========================================================
 RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
 RMAN-00571: ===========================================================
 RMAN-03002: failure of recover command at 05/10/2011 16:36:41
 RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 882174 and starting SCN of 67322261057
所有的歸檔，我不可能全用，所以到此為些，我只需要資料字典來分析日誌而己

4、試著開一下資料庫
[oracle@testdata ~]$ sqlplus "/as sysdba"

 SQL*Plus: Release 11.2.0.1.0 Production on Thu May 10 16:37:38 2011

 Copyright (c) 1982, 2009, Oracle. All rights reserved.

 Connected to:
 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options

 SQL> select status from v$instance;

 STATUS
 ------------
 MOUNTED

 SQL> alter database open resetlogs;

 Database altered.

資料庫開啟了，那就比較好說了！

5、恢復歸檔日誌，通過查詢

 由於歸檔日誌，由於我的資料庫已經被resetlogs開啟control,要把日誌再恢復回來，必須關掉資料庫！
 所以，重新恢復一個控制檔案出來，把引數檔案裡的控制檔案指向新恢復出來的控制檔案

 開始恢復歸檔:

RMAN> run {
 2> set archivelog destination to '/u01/archback';
 3> SQL 'ALTER SESSION SET NLS_DATE_FORMAT="YYYY-MM-DD:HH24:MI:SS"';
 4> restore archivelog time between '2011-05-07 09:00:00' and '2011-05-10 10:10:00';
 5> }

經過一段時間，歸檔日誌恢復出來了，現在可以用logminer來發現誰在幹壞事了

6、使用logminer分析歸檔

--必須執行以下指令碼，建立相應的包和表

SQL> @$ORACLE_HOME/rdbms/admin/dbmslm.sql

 Package created.

 Grant succeeded.

 Synonym created.

 SQL> @$ORACLE_HOME/rdbms/admin/dbmslmd.sql

 Package created.

 Synonym created.

 SQL> @$ORACLE_HOME/rdbms/admin/dbmslms.sql

 Package created.

 No errors.

 Grant succeeded.

--指出資料字典存放的位置（先建好目錄），並重啟資料庫

SQL> alter system set utl_file_dir='/u02/my_dictory' scope=spfile;

 System altered.

 SQL> shutdown immediate
 Database closed.
 Database dismounted.
 ORACLE instance shut down.
 SQL> startup
 ORACLE instance started.

 Total System Global Area 701485056 bytes
 Fixed Size 2216664 bytes
 Variable Size 322964776 bytes
 Database Buffers 373293056 bytes
 Redo Buffers 3010560 bytes
 Database mounted.
 Database opened.

 SQL> EXECUTE dbms_logmnr_d.build( 'mydictionary.ora', '/u02/my_dictory');

--加入要分析的歸檔日誌

execute dbms_logmnr.add_logfile('/u01/oradata/882101_1_782312693.log',dbms_logmnr.addfile);
 execute dbms_logmnr.add_logfile('/u01/oradata/882102_1_782312693.log',dbms_logmnr.addfile);
 execute dbms_logmnr.add_logfile('/u01/oradata/882103_1_782312693.log',dbms_logmnr.addfile);
 execute dbms_logmnr.add_logfile('/u01/oradata/882104_1_782312693.log',dbms_logmnr.addfile);
 execute dbms_logmnr.add_logfile('/u01/oradata/882105_1_782312693.log',dbms_logmnr.addfile);
 execute dbms_logmnr.add_logfile('/u01/oradata/882106_1_782312693.log',dbms_logmnr.addfile);
 execute dbms_logmnr.add_logfile('/u01/oradata/882107_1_782312693.log',dbms_logmnr.addfile);
 execute dbms_logmnr.add_logfile('/u01/oradata/882108_1_782312693.log',dbms_logmnr.addfile);
 execute dbms_logmnr.add_logfile('/u01/oradata/882109_1_782312693.log',dbms_logmnr.addfile);
 execute dbms_logmnr.add_logfile('/u01/oradata/882110_1_782312693.log',dbms_logmnr.addfile);
 execute dbms_logmnr.add_logfile('/u01/oradata/882111_1_782312693.log',dbms_logmnr.addfile);
 execute dbms_logmnr.add_logfile('/u01/oradata/882112_1_782312693.log',dbms_logmnr.addfile);
 execute dbms_logmnr.add_logfile('/u01/oradata/882113_1_782312693.log',dbms_logmnr.addfile);
 execute dbms_logmnr.add_logfile('/u01/oradata/882114_1_782312693.log',dbms_logmnr.addfile);
 execute dbms_logmnr.add_logfile('/u01/oradata/882115_1_782312693.log',dbms_logmnr.addfile);
 execute dbms_logmnr.add_logfile('/u01/oradata/882116_1_782312693.log',dbms_logmnr.addfile);
 execute dbms_logmnr.add_logfile('/u01/oradata/882117_1_782312693.log',dbms_logmnr.addfile);
 execute dbms_logmnr.add_logfile('/u01/oradata/882118_1_782312693.log',dbms_logmnr.addfile);
 execute dbms_logmnr.add_logfile('/u01/oradata/882119_1_782312693.log',dbms_logmnr.addfile);
 execute dbms_logmnr.add_logfile('/u01/oradata/882120_1_782312693.log',dbms_logmnr.addfile);
 execute dbms_logmnr.add_logfile('/u01/oradata/882121_1_782312693.log',dbms_logmnr.addfile);
 execute dbms_logmnr.add_logfile('/u01/oradata/882122_1_782312693.log',dbms_logmnr.addfile);
 execute dbms_logmnr.add_logfile('/u01/oradata/882123_1_782312693.log',dbms_logmnr.addfile);

--分析歸檔日誌

EXECUTE dbms_logmnr.start_logmnr( DictFileName=>'/u02/my_dictory/mydictionary.ora');

將臨時表的資料插入到一個固定表中
create table logmnrtab1 as select * from v$logmnr_contents;

 commit;

結束分析

SQL> exec sys.dbms_logmnr.end_logmnr;

 

 5、根據表查詢所要找的物件和使用者

SELECT sql_redo FROM logmnrtab1 WHERE username='-----' AND seg_name='-----';

根據條件可以判斷出誰幹的壞事！後來查出來是一個傢伙在9號上午10點左右drop了表，然後馬上又重建！那哥們被我害了

 實際上，我在測試環境中，每次分析100個歸檔日誌，產生了4個表，每個表約40個g，最後根據sqllog\sessioninfo等，找到當事人的機器、ip、osname及sql語句等資訊，具體的，大家可以測試看一下！

 要注意檢視以下幾個臨時表

V$LOGMNR_DICTIONARY-------查詢使用的資料字典檔案

V$LOGMNR_PARAMETERS-------查詢當前LogMiner設定的引數

V$LOGMNR_LOGS-------查詢分析的日誌檔案

V$LOGMNR_CONTENTS-------日誌檔案的內容

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/21752515/viewspace-1062683/，如需轉載，請註明出處，否則將追究法律責任。