/*2008/10/23 星期三
*蒙昭良
*環境：windowsXP + Oracle10gR2
*使用Logminer工具分析DML和DDL操作
*/

LogMiner是Oracle資料庫提供的一個工具，它用於分析重做日誌和歸檔日誌所記載的事務操作。
一、確定資料庫的邏輯損壞時間。假定某個使用者執行drop table誤刪除了重要表sales，透過

LogMiner可以準確定位該誤操作的執行時間和SCN值，然後透過基於時間恢復或者基於SCN恢復可

以完全恢復該表資料。

二、確定事務級要執行的精細邏輯恢復操作。假定某些使用者在某表上執行了一系列DML操作並提

交了事務，並且其中某個使用者的DML操作存在錯誤。透過LogMiner可以取得任何使用者的DML操作及

相應的UNDO操作，透過執行UNDO操作可以取消使用者的錯誤操作。

三、執行後續審計。透過LogMiner可以跟蹤Oracle資料庫的所有DML、DDL和DCL操作，從而取得

執行這些操作的時間順序、執行這些操作的使用者等資訊。

1、LogMiner基本物件
源資料庫（source database）：該資料庫是指包含了要分析重做日誌和歸檔日誌的產品資料庫

。
分析資料庫（mining database）：該資料庫是指執行LogMiner操作所要使用的資料庫。
LogMiner字典：LogMiner字典用於將內部物件ID號和資料型別轉換為物件名和外部資料格式。使

用LogMiner分析重做日誌和歸檔日誌時，應該生成LogMiner字典，否則將無法讀懂分析結果。

2、LogMiner配置要求
（1）源資料庫和分析資料庫（源資料庫和分析資料庫可以是同一個資料庫）
源資料庫和分析資料庫必須執行在相同硬體平臺上；
分析資料庫可以是獨立資料庫或源資料庫；
分析資料庫的版本不能低於源資料庫的版本；
分析資料庫與源資料庫必須具有相同的字符集。
（2）LogMiner字典：LogMiner字典必須在源資料庫中生成。
（3）重做日誌檔案
當分析多個重做日誌和歸檔日誌時，它們必須是同一個源資料庫的重做日誌和歸檔日誌；
當分析多個重做日誌和歸檔日誌時，它們必須具有相同的resetlogs scn；
當分析的重做日誌和歸檔日誌必須在Oracle8.0版本以上。

3、補充日誌（suppplemental logging）
重做日誌用於實現例程恢復和介質恢復，這些操作所需要的資料被自動記錄在重做日誌中。但是

，重做應用可能還需要記載其他列資訊到重做日誌中，記錄其他列的日誌過程被稱為補充日誌。

預設情況下，Oracle資料庫沒有提供任何補充日誌，從而導致預設情況下LogMiner無法支援以下

特徵：
索引簇、鏈行和遷移行；
直接路徑插入；
摘取LogMiner字典到重做日誌；
跟蹤DDL；
生成鍵列的SQL_REDO和SQL_UNDO資訊；
LONG和LOB資料型別。

因此，為了充分利用LogMiner提供的特徵，必須啟用補充日誌。在資料庫級啟用補充日誌的示例

如下：
SQL> conn /as sysdba
已連線。
SQL> alter database add supplemental log data;
資料庫已更改。

注意：這啟用不用重啟資料庫，資料庫聯機即可。

4、LogMiner支援的資料型別和表儲存屬性
char、nchar、varchar2、varchar、nvarchar2；
number；
date、timestamp、timestamp with time zone、timestamp with local time zone；
interval year to month、interval day to second；
raw；
clob、nclob、blob；
long、long raw；
binary_float、binary_double；
函式索引；
沒有lob列的索引組織表（IOT）。

5、LogMiner不支援的資料型別和表儲存屬性
bfile；
抽象資料型別；
集合型別（巢狀表和varray）；
參照物件；
xmltype；
具有LOB列的索引組織表；
使用compress特徵的表。

提供LogMiner字典有3種選項:
1、使用源資料庫資料字典（Online Catalog）
使用源資料庫分析重做日誌或歸檔日誌時，如果要分析表的結構沒有發生任何變化，Oracle建議

使用該選項分析重做日誌和歸檔日誌。為了使LogMiner使用當前資料庫的資料字典，啟動

LogMiner時應執行如下操作：
SQL> execute dbms_logmnr.start_logmnr

(options=>dbms_logmnr.dict_from_online_catalog);
需要注意，dbms_logmnr.dict_from_online_catalog要求資料庫必須處於open狀態，並且該選項

只能用於跟蹤DML操作，而不能用於跟蹤DDL操作。（本人一般用這種查DML操作）

2、摘取LogMiner字典到重做日誌
使用分析資料庫分析重做日誌或歸檔日誌，或者被分析表的結構發生改變時，Oracle建議使用該

選項分析重做日誌和歸檔日誌。為了摘取LogMiner字典到重做日誌，要求源資料庫必須處於

archivelog模式，並且該資料庫處於open狀態。示例如下：
SQL> execute dbms_logmnr_d.build(options=>dbms_logmnr_d.store_in_redo_logs);
（本人比較少用這種方式）

3、摘取LogMiner字典到字典檔案
字典檔案用於存放物件ID號和物件名資訊，該選項是為了與早期版本相容而保留的。需要注意，

使用字典檔案分析重做日誌時，如果要分析新建的物件。必須重新建立字典檔案。如下所示：
SQL> execute dbms_logmnr_d.build

('dict.ora','d:\demo',dbms_logmnr_d.store_in_flat_file);
（本人一般用這種方式來查DDL的操作記錄，如資料庫沒有配置utl_file_dir引數，需要配置後

需要重啟資料庫）

----------------------------------------------------------------------------

下面使用源資料庫資料字典（Online Catalog）來分析DML操作
1、先進行DML和DDL的操作，以便下面分析。
SQL> conn /as sysdba
已連線。

SQL> show parameter utl;

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------
create_stored_outlines               string
utl_file_dir                         string

SQL> conn scott/mzl
已連線。

SQL> insert into dept
2 values(80,'meng','PanJan');

已建立 1 行。

SQL> update dept set loc='shang hai' where deptno=70;

已更新 1 行。

SQL> commit;

提交完成。

SQL> delete from dept where deptno=40;

已刪除 1 行。

SQL> commit;

提交完成。

SQL> alter table dept add(phone varchar2(32));

表已更改。

SQL> conn mzl/mzl
已連線。
SQL> insert into dept
2 values(70,'Hello','guangzhou');

已建立 1 行。

SQL> commit
2 ;

提交完成。

SQL> insert into scott.dept
2 values(50,'Xiao','XiangGuang','138138000');

已建立 1 行。

SQL> commit;

提交完成。

SQL> alter table scott.dept add(address varchar2(300));

表已更改。

2、把線上重做日誌變成歸檔日誌，這樣分析歸檔日誌就可以了

SQL> alter system switch logfile;

系統已更改。

3、建立日誌分析列表：
SQL> conn /as sysdba
已連線。
SQL> execute dbms_logmnr.add_logfile(-
> logfilename=>'D:\oracle\product\10.2.0

\flash_recovery_area\ORCL\ARCHIVELOG\2008_10_23

\O1_MF_1_41_4HZPW700_.ARC',options=>dbms_logmnr.new);

PL/SQL 過程已成功完成。

SQL> execute dbms_logmnr.add_logfile(logfilename=>'D:\oracle\product\10.2.0

\flash_recovery_area\ORCL\ARCHIVELOG\2008_10_23

\O1_MF_1_40_4HZKMGPY_.ARC',options=>dbms_logmnr.addfile);

PL/SQL 過程已成功完成。

4、啟動LogMiner
SQL> execute dbms_logmnr.start_logmnr

(options=>dbms_logmnr.dict_from_online_catalog);

PL/SQL 過程已成功完成。

5、檢視日誌分析結果：

(也可以建立一個表來存放v$logmnr_contents,這樣便於以後查詢，

create table logminer_test tablespace test_space

as

select * from v$logmnr_contents ;

請著一個比較空閒的表空間，因為表v$logmnr_contents的資料量很大

把表的許可權賦予給其他的使用者，這樣方便mzl使用者使用第三方工具查詢（plsql等）：

grant select on logminer_test to mzl;)

SQL> col username format a8
SQL> col sql_redo format a50
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

會話已更改。

SQL> select username,timestamp,sql_redo from v$logmnr_contents where

seg_name='DEPT';

USERNAME TIMESTAMP           SQL_REDO
-------- ------------------- --------------------------------------------------
MZL      2008-10-23 10:02:22 insert into "SCOTT"."DEPT"("COL 1","COL 2","COL 3"
                             ,"COL 4") values (HEXTORAW('c133'),HEXTORAW('58696
                             16f'),HEXTORAW('5869616e674775616e67'),HEXTORAW('3
                             13338313338303030'));

USERNAME TIMESTAMP SQL_REDO
-------- ------------------- --------------------------------------------------
MZL 2008-10-23 10:03:20 alter table scott.dept add(address varchar2(300));

6、結束LogMiner
SQL> execute dbms_logmnr.end_logmnr;

PL/SQL 過程已成功完成。

---------------------------------------------------------------------------------
用摘取LogMiner字典到字典檔案分析DDL操作：
1、進行DDL操作，以便分析
SQL> conn scott/mzl
已連線。
SQL> drop table emp;

表已刪除。

SQL> drop table dept;

表已刪除。

SQL> conn /as sysdba
已連線。
SQL> alter system switch logfile;

系統已更改。

2、使用字典檔案，請檢視資料庫是否配置utl_file_dir,這個引數為字典檔案的目錄。
SQL> show user;
USER 為 "SYS"
SQL> show parameter utl;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------
create_stored_outlines               string
utl_file_dir                         string

可以看出沒有配置該引數，配置該引數後，需要重啟資料庫。
SQL> alter system set utl_file_dir='d:\demo' scope=spfile;

系統已更改。

SQL>shutdown immediate;
SQL>startup;
或者強制性重啟資料庫
SQL> startup force;
ORACLE 例程已經啟動。

Total System Global Area 289406976 bytes
Fixed Size                  1248576 bytes
Variable Size              75498176 bytes
Database Buffers          205520896 bytes
Redo Buffers                7139328 bytes
資料庫裝載完畢。
資料庫已經開啟。
SQL> show parameter utl

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
create_stored_outlines               string
utl_file_dir                         string      d:\demo

3、建立字典檔案：
SQL> execute dbms_logmnr_d.build

('dict.ora','d:\demo',dbms_logmnr_d.store_in_flat_file);

PL/SQL 過程已成功完成。

4、建立日誌分析列表：
SQL> execute dbms_logmnr.add_logfile(logfilename=>'D:\oracle\product\10.2.0

\flash_recovery_area\ORCL\ARCHIVELOG\2008_10_23

\O1_MF_1_42_4HZRHJQX_.ARC',options=>dbms_logmnr.new);

PL/SQL 過程已成功完成。

SQL> execute dbms_logmnr.add_logfile(logfilename=>'D:\oracle\product\10.2.0

\flash_recovery_area\ORCL\ARCHIVELOG\2008_10_23

\O1_MF_1_41_4HZPW700_.ARC',options=>dbms_logmnr.addfile);

PL/SQL 過程已成功完成。

5、啟動LogMiner
SQL> execute dbms_logmnr.start_logmnr

(dictfilename=>'d:\demo\dict.ora',options=>dbms_logmnr.ddl_dict_tracking);

PL/SQL 過程已成功完成。

6、查詢分析日誌結果：
SQL> select username,timestamp,sql_redo from v$logmnr_contents
2 where lower(sql_redo) like '%table%';

USERNAME TIMESTAMP           SQL_REDO
-------- ------------------- --------------------------------------------------
SCOTT    2008-10-23 09:59:29 alter table dept add(phone varchar2(32));
SCOTT    2008-10-23 10:29:50 drop table emp AS "BIN$j3Z4SzJ3QS6l/AGiD5RJiA==$0"
                              ;

SCOTT    2008-10-23 10:30:05 ALTER TABLE "SCOTT"."DEPT" RENAME TO "BIN$u6gL7Khf
                             TZC+67ipacjOHw==$0" ;

SCOTT    2008-10-23 10:30:05 drop table dept AS "BIN$u6gL7KhfTZC+67ipacjOHw==$0
                             " ;

或者其他的查詢：
SQL> select username,timestamp,sql_redo from v$logmnr_contents
2 where username='SCOTT';

USERNAME TIMESTAMP           SQL_REDO
-------- ------------------- --------------------------------------------------
SCOTT    2008-10-23 09:55:49 set transaction read write;
SCOTT    2008-10-23 09:55:49 insert into "UNKNOWN"."OBJ# 53917"("COL 1","COL 2"
                             ,"COL 3") values (HEXTORAW('c151'),HEXTORAW('6d656
                             e67'),HEXTORAW('50616e4a616e'));

SCOTT    2008-10-23 09:56:25 update "UNKNOWN"."OBJ# 53917" set "COL 3" = HEXTOR
                             AW('7368616e6720686169') where "COL 3" = HEXTORAW(
                             '6265696a696e67') and ROWID = 'AAANKdAAEAAAANEAAA'
                             ;

SCOTT    2008-10-23 09:56:33 commit;

SCOTT    2008-10-23 09:59:29 alter table dept add(phone varchar2(32));
SCOTT    2008-10-23 10:29:50 drop table emp AS "BIN$j3Z4SzJ3QS6l/AGiD5RJiA==$0"
                              ;

SCOTT 2008-10-23 10:30:05 drop table dept AS "BIN$u6gL7KhfTZC+67ipacjOHw==$0
" ;

7、結束LogMiner
SQL> execute dbms_logmnr.end_logmnr;

PL/SQL 過程已成功完成。

用Logminer能夠獲得一些更進一步資訊，根據Log中的刪除時間，結合listener.log日誌登陸時間，基本可以確定是who從where登陸的資料庫，能查到人的。(listener.log位於$oracle_home\network\log目錄中)

上面很多內容摘自王海亮等人著的《精通Oracle10g 系統管理》

使用Logminer工具分析DML和DDL操作

相關文章