Logminer是Oracle推出的一項日誌挖掘技術和工具，可用於分析對資料庫的DML操作，獲取操作的REDO SQL和UNDO SQL。它既可以分析線上日誌，也可以分析離線日誌，既可以分析自身資料庫的日誌,也可以分析其它資料庫的日誌。利用獲取到的這些SQL，可以實現對特定事務的審計、重做和還原。可以建立源庫到目標庫的應用，源庫和目標庫可以是同一個，也可以是不同的。如果不同，要求目標庫資料庫版本高於或等於源庫，字符集要相同，作業系統、硬體平臺要相同。

使用Logminer之前可能需要確認Oracle是否已裝有Logminer分析包，可以DBA身份登入系統，檢視系統中是否存在執行Logminer所需要的dbms_logmnr、dbms_logmnr_d這兩個包，如果沒有則需要先安裝Logminer工具，需要以DBA使用者身份執行以下兩個指令碼：

$ORACLE_HOME/rdbms/admin/dbmslm.sql

$ORACLE_HOME/rdbms/admin/dbmslmd.sql

其中第一個指令碼用來建立dbms_logmnr包，該包用來分析日誌檔案，第二個指令碼用來建立dbms_logmnr_d包，該包用來建立資料字典檔案。

一、Logminer的使用方法

源庫應在歸檔模式，並開啟補充日誌功能。非歸檔模式不是不可以做日誌挖掘，但由於日誌沒有歸檔，因此只能對聯機日誌做挖掘。補充日誌也不是必需，但如果不啟用，則解析後很多有用的資訊都沒有。

檢視存檔模式和補充日誌狀態是否開啟

select log_mode, supplemental_log_data_min from v$database;

LOG_MODE SUPPLEME

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

ARCHIVELOG NO

開啟補充日誌

alter database add supplemental log data;

可單獨建立一個表空間，用於記錄Logminer的資訊，這樣不佔用系統表空間

create tablespace logminer datafile 'd:\oradata\mes\logminer01.dbf' size 500m;

execute dbms_logmnr_d.set_tablespace('logminer');

可單獨建立一個負責日誌挖掘的使用者並授權

create user logminer identified by logminer default tablespace logminer;

grant dba to logminer;

在使用Logminer工具分析redo log之前，應使用dbms_logmnr_d包將資料字典匯出為一個文字檔案。該字典檔案是可選的，但是如果沒有它，Logminer解釋出來的語句中關於資料字典的部分（如表名、列名等）和數值都將是16進位制的形式，難以理解。建立資料字典的目的是讓Logminer引用到涉及內部資料字典中的部分時，將它們解釋為實際的名字。如果要分析的資料庫的表有變化，影響到資料字典變化時，就需要重新建立字典檔案。另外一種情況是，在分析另一個資料庫的redo log時，也必須重新生成一次被分析資料庫的資料字典檔案。

建立資料字典檔案目錄

alter system set utl_file_dir='d:\dict' scope=spfile;

這裡增加了一個dict的目錄用於存放日誌挖掘的資料字典檔案。稍後在開始日誌挖掘之前我們會建立和更新資料字典檔案。

重啟資料庫

shutdown immediate

startup

做幾個具有代表意義的操作，以便後面觀察日誌是如何記錄的

create table scott.emp1 tablespace users as select * from scott.emp;

insert into scott.emp1 values(1001, 'LIULN', 'ANALYST', 7369, '2012-05-01 00:00:00', '4000', '', 20);

update scott.emp1 set sal = 5000 where empno = 1001;

commit;

delete from scott.emp1 where empno = 1001;

rollback;

create index scott.idx_emp1_empno on scott.emp1(empno) tablespace users;

select * from scott.emp1 where empno = 1001;

insert into scott.emp values(1001, 'LIULN', 'ANALYST', 7369, '2012-05-01 00:00:00', '4000', '', 20);

update scott.emp set sal = 5000 where empno = 1001;

delete from scott.emp where empno = 1001;

commit;

select * from scott.emp;

建立和更新資料字典檔案到目錄中

begin

dbms_logmnr_d.build(dictionary_filename => 'dict.ora',

dictionary_location => 'd:\dict');

end;

/

Logminer的字典模式除了以上使用外部OS檔案的方式外，還有以下兩種方式：

1）使用線上字典，適用於在源庫做Logminer

begin

dbms_logmnr_d.build(options => dbms_logmnr.dict_from_online_catalog);

end;

/

2）把字典放到線上日誌檔案，適用於源庫與目標庫不同這樣的方式

begin

dbms_logmnr_d.build(options => dbms_logmnr_d.store_in_redo_logs);

end;

/

可以檢視字典存放到了哪個歸檔日誌中

select name from v$archived_log where dictionary_begin = 'YES';

select name from v$archived_log where dictionary_end = 'YES';

檢視當前操作對應的聯機日誌檔案

col member for a50

select t1.sequence#, t2.member from v$log t1, v$logfile t2 where t1.group# = t2.group# and t1.status = 'CURRENT' and rownum = 1;

SEQUENCE# MEMBER

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

214 D:\ORADATA\MES\REDO01.LOG

把要分析的日誌檔案加進來，新增第一個日誌時，options選項用new，再新增則用addfile，如要刪除則用removefile。

conn logminer/logminer

begin

dbms_logmnr.add_logfile(logfilename => 'd:\oradata\mes\redo01.log',

options => dbms_logmnr.new);

end;

/

挖掘日誌

begin

dbms_logmnr.start_logmnr(dictfilename => 'd:\dict\dict.ora');

end;

/

可以按時間縮小日誌挖掘範圍，如

begin

dbms_logmnr.start_logmnr(starttime => to_date('2017-04-22 12:00:00', 'yyyy-mm-dd hh24:mi:ss'),

endtime => to_date('2017-04-22 13:00:00', 'yyyy-mm-dd hh24:mi:ss'),

dictfilename => 'd:\dict\dict.ora')

end;

/

也可以按SCN號縮小日誌挖掘範圍，如

begin

dbms_logmnr.start_logmnr(startscn => 6239000,

endscn => 6240000,

dictfilename => 'd:\dict\dict.ora')

end;

/

還可以加入options選項對日誌挖掘做一些限定，如

begin

dbms_logmnr.start_logmnr(dictfilename => 'd:\dict\dict.ora',

Options => dbms_logmnr.committed_data_only +

dbms_logmnr.no_sql_delimiter +

dbms_logmnr.no_rowid_in_stmt);

end;

/

dbms_logmnr.committed_data_only：只挖掘已提交的事務。

dbms_logmnr.no_sql_delimiter：去掉redo_sql和undo_sql最後的分號，這個在以CURSOR方式迴圈執行解析出的SQL時會很方便。

dbms_logmnr.no_rowid_in_stmt：去掉redo_sql和undo_sql中的rowid，適用於在源庫以外的其它庫重新執行解析出的SQL,因為相應的rowid在目標庫是不存在的。

從v$logmnr_contents獲取變更資訊。注意檢視v$logmnr_contents中的分析結果僅在我們執行過程dbms_logmnr.add_logfile和dbms_logmnr.start_logmnr這個會話的生命期中存在。這是因為所有的Logminer都儲存在PGA記憶體中，因此其它程式是看不到它的。而當使用過程dbms_logmnr.end_logmnr終止日誌分析時，PGA記憶體區域將被清除，分析結果也隨之不再存在。另外由於每次查詢v$logmnr_contents檢視時都會實際觸發一次日誌解析，也就是說這個檢視的內容不是start_logmnr生成的，而是每次查詢時生成的，為此可以生成一個實體表留住結果，這樣可以大幅度降低對系統的開銷。

create table logminer.lgmr_contents as

select scn, to_char(timestamp, 'yyyy-mm-dd hh24:mi:ss') timestamp, operation, operation_code, rollback,

seg_owner, seg_name, seg_type_name, table_space, row_id, username, machine_name,

substr(substr(session_info, instr(session_info, 'OS_program_name=')), 17) program,

thread#, rel_file#, data_blk#, data_obj#, sql_redo, sql_undo

from v$logmnr_contents

where seg_owner = 'SCOTT';

按時間先後順序查詢一下變更資訊

select * from logminer.lgmr_contents order by scn desc;

變更資訊內容還是比較豐富的，包含了操作時的SCN和時間戳、操作類別包括DML和DDL、構建的redo是否用於回滾、操作物件的資訊、記錄的ROWID、登入使用者和計算機名及程式、REDO和UNDO語句等。

再來重點看一下REDO資訊

col sql_redo for a100

select scn, sql_redo from logminer.lgmr_contents order by scn desc;

SCN SQL_REDO

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

1751090 delete from "SCOTT"."EMP" where "EMPNO" = '1001' and "ENAME" = 'LIULN' and "JOB" = 'ANALYST' and "MG

R" = '7369' and "HIREDATE" = TO_DATE('2012-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and "SAL" = '50

00' and "COMM" IS NULL and "DEPTNO" = '20' and ROWID = 'AAAF7JAAEAAAAFVAAC';

1751088 update "SCOTT"."EMP" set "SAL" = '5000' where "SAL" = '4000' and ROWID = 'AAAF7JAAEAAAAFVAAC';

1751084 insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('100

1','LIULN','ANALYST','7369',TO_DATE('2012-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'),'4000',NULL,'20'

);

1751061 create index scott.idx_emp1_empno on scott.emp1(empno) tablespace users;

1751048 insert into "SCOTT"."EMP1"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('10

01','LIULN','ANALYST','7369',TO_DATE('2012-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'),'5000',NULL,'20

');

1751044 delete from "SCOTT"."EMP1" where "EMPNO" = '1001' and "ENAME" = 'LIULN' and "JOB" = 'ANALYST' and "M

GR" = '7369' and "HIREDATE" = TO_DATE('2012-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and "SAL" = '5

000' and "COMM" IS NULL and "DEPTNO" = '20' and ROWID = 'AAAF8DAAEAAAAGNAAA';

1751037 update "SCOTT"."EMP1" set "SAL" = '5000' where "SAL" = '4000' and ROWID = 'AAAF8DAAEAAAAGNAAA';

1751033 insert into "SCOTT"."EMP1"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('10

01','LIULN','ANALYST','7369',TO_DATE('2012-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'),'4000',NULL,'20

');

1751021 Unsupported

1751016 create table scott.emp1 tablespace users as select * from scott.emp;

可以看出，日誌中記錄的REDO SQL和原先執行的SQL在文字表達上有所不同，這是為了記錄變更之前的資料，構建UNDO資訊，並且所有的update和delete都是基於ROWID的。實際中如要將這些SQL重新應用，特別是應用在不同庫中，需要做一些技巧性處理。

停止日誌挖掘

begin

dbms_logmnr.end_logmnr;

end;

/

刪除補充日誌

alter database drop supplemental log data;

刪除之前試驗建立的表

drop table logminer.lgmr_contents purge;

drop table scott.emp1 purge;

二、Logminer相關檢視

除了上面用於檢視日誌挖掘結果的v$logmnr_contents檢視外，還有以下幾個有關logminer的檢視。

檢視用於logminer的日誌列表

col filename for a50

select filename, type, db_id, db_name, low_time, high_time, low_scn, next_scn from v$logmnr_logs;

FILENAME TYPE DB_ID DB_NAME LOW_TIME HIGH_TIME LOW_SCN NEXT_SCN

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

d:\oradata\mes\redo03.log ONLINE 2023487221 MES 2017-04-24 21:34:54 1988-01-01 00:00:00 1538785 2.8147E+14

檢視logminer設定的引數資訊

select start_date, start_scn, end_date, end_scn, options from v$logmnr_parameters;

START_DATE START_SCN END_DATE END_SCN OPTIONS

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

1538785 2111-01-01 00:59:59 0 0

檢視logminer利用的資料字典資訊

select db_id, db_name, db_created, to_char(timestamp, 'yyyy-mm-dd hh24:mi:ss') timestamp, dictionary_scn, filename from v$logmnr_dictionary;

DB_ID DB_NAME DB_CREATED TIMESTAMP DICTIONARY_SCN FILENAME

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

2023487221 MES 2017-03-29 13:40:05 2017-04-24 21:45:27 1538894 d:\dict\dict.ora

logminer日誌挖掘技術

相關文章