Log Miner的知识点有哪些

2025-03-11 技术教程

这篇文章主要讲解了“Log Miner的知识点有哪些”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“Log Miner的知识点有哪些”吧！

|Log Miner简介

Log Miner是Oracle自Oracle 8i以后推出的一个可以分析数据库redo log和archivelog内容的工具，可以通过日志分析所有对数据库的DDL和DML操作，也可以分析出操作的时间与操作时的SCN和进行操作的机器，对于DML操作还可以查询出还原操作的sql。

|Log Miner组成

源数据库产生LogMiner分析的所有重做日志文件的数据库

挖掘数据库是执行LogMiner分析时使用的数据库。

LogMiner数据字典是LogMiner使用字典将内部对象标识符和数据类型转换为可读数据。如果没有字典，Log Miner分析的结果会显示为二进制数据。

|Log Miner数据字典选项

当LogMiner分析重做数据时，需要一个数据字典将日志的对象ID转换为可读数据。LogMiner提供了三个使用数据字典的方式。

1、使用在线目录( Online Catalog)

使用catalog的数据字典，必须在源数据库执行。启动命令为：

SQL>executedbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

2、将LogMiner字典提取到archive log。启动命令为：

SQL>executedbms_logmnr_d.build(options=>dbms_logmnr_d.store_in_redo_logs); 使用这种操作的

3、将LogMiner字典提取到操作系统文件。启动命令为：

SQL>executedbms_logmnr_d.build('directory_name'，'/xxx/xxx/'，dbms_logmnr_d.store_in_flat_file); 使用这种方式的话，需要设置utl_file_dir参数，该参数需要重启才能生效。

这个工具使用起来并不复杂。由于将Log Miner数据字典提取到操作系统文件在未设置参数的情况下需要重启数据库，使用场景比较狭隘，所以以下测试场景为使用Online catalog数据字典模式和将字典提取到redo log。

|测试场景

1、确认数据库开启了补充日志

sys@RAC11G>selectSUPPLEMENTAL_LOG_DATA_MINfromv$database; SUPPLEME -------- YES --如果返回结果为no，通过以下命令开启 ALTERDATABASEADDSUPPLEMENTALLOGDATA;

注意：在使用Log Miner分析的日志文件之前，必须启用补充日志。
启用补充日志时，会在重做日志流中记录其他信息。如果不开启，LogMiner的挖掘的一些信息无法正常显示。

2、创建测试表，并做一些DML与DDL操作

sys@RAC11G>createtabletest1(NAMEvarchar2(20),IDnumber); Tablecreated. sys@RAC11G>insertintotest1values('x','1'); 1rowcreated. sys@RAC11G>insertintotest1values('xx','2'); 1rowcreated. sys@RAC11G>insertintotest1values('xxx','3'); 1rowcreated. sys@RAC11G>commit; Commitcomplete. sys@RAC11G>updatetest1setname='xxxx'whereid=3; 1rowupdated. sys@RAC11G>commit; Commitcomplete. sys@RAC11G>truncatetabletest1; Tabletruncated.

3、切换归档日志

sys@RAC11G>altersystemswitchlogfile; Systemaltered. sys@RAC11G>altersystemswitchlogfile; Systemaltered. --然后查看最后生成的归档日志 sys@RAC11G>select*from(selectnamefromv$archived_logwherenamelike'%archive%'orderbySEQUENCE#desc)whererownum<3; NAME --------------------------------------------------------------------------------------------------------------------------------------- +DATADG/rac11g/archivelog/2018_10_14/thread_1_seq_293.847.989533723 +DATADG/rac11g/archivelog/2018_10_14/thread_1_seq_292.846.989533631

4、Log Miner添加需要分析的归档日志

--添加日志 sys@RAC11G>executedbms_logmnr.add_logfile(logfilename=>'+DATADG/rac11g/archivelog/2018_10_14/thread_1_seq_293.847.989533723',options=>dbms_logmnr.new); PL/SQLproceduresuccessfullycompleted. --添加多个日志，使用DBMS_LOGMNR.ADDFILE选项 sys@RAC11G>executedbms_logmnr.add_logfile(logfilename=>'+DATADG/rac11g/archivelog/2018_10_14/thread_1_seq_292.846.989533631',options=>DBMS_LOGMNR.ADDFILE); PL/SQLproceduresuccessfullycompleted.

5、启动Log Miner

sys@RAC11G>executedbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog); PL/SQLproceduresuccessfullycompleted.

6、通过视图v$logmnr_contents进行分析结果查询

v$logmnr_contents只有在开启了Log Miner后才可以进行查询

selectto_char(timestamp,'yyyy-mm-ddhh34:mm:ss'), operation, username, SESSION_INFO, sql_redo fromv$logmnr_contents wheretable_name='TEST1'; TO_CHAR(TIMESTAMP,'OPERATIONUSERNAMESESSION_INFOSQL_REDO ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 2018-10-1422:10:50DDLSYSlogin_username=SYSclient_info=OS_username=oracleMachine_name=11gnode1OScreatetabletest1(NAMEvarchar2(20),IDnumber); _terminal=pts/5OS_process_id=17273OS_program_name=sqlplus@11gnode1(TNSV 1-V3) 2018-10-1422:10:29INSERTSYSlogin_username=SYSclient_info=OS_username=oracleMachine_name=11gnode1OSinsertinto"SYS"."TEST1"("NAME","ID")values('x','1'); _terminal=pts/5OS_process_id=17273OS_program_name=sqlplus@11gnode1(TNSV 1-V3) 2018-10-1422:10:33INSERTSYSlogin_username=SYSclient_info=OS_username=oracleMachine_name=11gnode1OSinsertinto"SYS"."TEST1"("NAME","ID")values('xx','2'); _terminal=pts/5OS_process_id=17273OS_program_name=sqlplus@11gnode1(TNSV 1-V3) 2018-10-1422:10:37INSERTSYSlogin_username=SYSclient_info=OS_username=oracleMachine_name=11gnode1OSinsertinto"SYS"."TEST1"("NAME","ID")values('xxx','3'); _terminal=pts/5OS_process_id=17273OS_program_name=sqlplus@11gnode1(TNSV 1-V3) 2018-10-1422:10:51UPDATESYSlogin_username=SYSclient_info=OS_username=oracleMachine_name=11gnode1OSupdate"SYS"."TEST1"set"NAME"='xxxx'where"NAME"='xxx'andROWID=' _terminal=pts/5OS_process_id=17273OS_program_name=sqlplus@11gnode1(TNSVAAAE6eAABAAAKHBAAC'; 1-V3) 2018-10-1422:10:12DDLSYSlogin_username=SYSclient_info=OS_username=oracleMachine_name=11gnode1OStruncatetabletest1; _terminal=pts/5OS_process_id=17273OS_program_name=sqlplus@11gnode1(TNSV 1-V3) .

7、关闭Log Miner

EXECUTEDBMS_LOGMNR.END_LOGMNR（）;

注意：Log Miner的数据只存在PGA中，如果查询的会话断开连接，Log Miner也会随之关闭。

8、将数据字典提取到redo log

sys@RAC11G>EXECUTEDBMS_LOGMNR_D.BUILD(OPTIONS=>DBMS_LOGMNR_D.STORE_IN_REDO_LOGS); PL/SQLproceduresuccessfullycompleted. 根据字典的大小，它可能包含在多个归档文件中。如果已归档相关的重做日志文件，则可以找出包含提取的字典的开头和结尾的归档日志。可以查询V$ARCHIVED_LOG视图 sys@RAC11G>SELECTNAMEFROMV$ARCHIVED_LOGWHEREDICTIONARY_BEGIN='YES'; NAME --------------------------------------------------------------------------------------------------------------------------------------- +DATADG/rac11g/archivelog/2018_10_15/thread_1_seq_308.869.989570647 sys@RAC11G>SELECTNAMEFROMV$ARCHIVED_LOGWHEREDICTIONARY_END='YES'; NAME --------------------------------------------------------------------------------------------------------------------------------------- +DATADG/rac11g/archivelog/2018_10_15/thread_1_seq_309.871.989570649

9、添加包含数据字典的归档日志以及需要分析的归档日志

sys@RAC11G>executedbms_logmnr.add_logfile(logfilename=>'+DATADG/rac11g/archivelog/2018_10_15/thread_1_seq_308.869.989570647',options=>dbms_logmnr.new); PL/SQLproceduresuccessfullycompleted. sys@RAC11G>executedbms_logmnr.add_logfile(logfilename=>'+DATADG/rac11g/archivelog/2018_10_15/thread_1_seq_309.871.989570649',options=>dbms_logmnr.addfile); PL/SQLproceduresuccessfullycompleted. sys@RAC11G>executedbms_logmnr.add_logfile(logfilename=>'+DATADG/rac11g/archivelog/2018_10_14/thread_1_seq_293.847.989533723',options=>dbms_logmnr.addfile); PL/SQLproceduresuccessfullycompleted. sys@RAC11G>executedbms_logmnr.add_logfile(logfilename=>'+DATADG/rac11g/archivelog/2018_10_14/thread_1_seq_292.846.989533631',options=>dbms_logmnr.addfile); PL/SQLproceduresuccessfullycompleted.

10、启动Log Miner

sys@RAC11G>executedbms_logmnr.start_logmnr(options=>dbms_logmnr.DICT_FROM_REDO_LOGS); PL/SQLproceduresuccessfullycompleted.

11、通过视图v$logmnr_contents进行分析结果查询

selectto_char(timestamp,'yyyy-mm-ddhh34:mm:ss'), operation, username, SESSION_INFO, sql_redo fromv$logmnr_contents 7wheretable_name='TEST1'; TO_CHAR(TIMESTAMP,'OPERATIONUSERNAME --------------------------------------------------------------------------------- SESSION_INFO --------------------------------------------------------------------------------------------------------------------------------------- SQL_REDO --------------------------------------------------------------------------------------------------------------------------------------- 2018-10-1422:10:50DDLSYS login_username=SYSclient_info=OS_username=oracleMachine_name=11gnode1OS_terminal=pts/5OS_process_id=17273OS_program_name=sqlplus@ 11gnode1(TNSV1-V3) createtabletest1(NAMEvarchar2(20),IDnumber); 2018-10-1422:10:29INSERTSYS login_username=SYSclient_info=OS_username=oracleMachine_name=11gnode1OS_terminal=pts/5OS_process_id=17273OS_program_name=sqlplus@ 11gnode1(TNSV1-V3) insertinto"SYS"."TEST1"("COL1","COL2")values(HEXTORAW('78'),HEXTORAW('c102')); 2018-10-1422:10:33INSERTSYS login_username=SYSclient_info=OS_username=oracleMachine_name=11gnode1OS_terminal=pts/5OS_process_id=17273OS_program_name=sqlplus@ 11gnode1(TNSV1-V3) insertinto"SYS"."TEST1"("COL1","COL2")values(HEXTORAW('7878'),HEXTORAW('c103')); 2018-10-1422:10:37INSERTSYS login_username=SYSclient_info=OS_username=oracleMachine_name=11gnode1OS_terminal=pts/5OS_process_id=17273OS_program_name=sqlplus@ 11gnode1(TNSV1-V3) insertinto"SYS"."TEST1"("COL1","COL2")values(HEXTORAW('787878'),HEXTORAW('c104')); 2018-10-1422:10:51UPDATESYS login_username=SYSclient_info=OS_username=oracleMachine_name=11gnode1OS_terminal=pts/5OS_process_id=17273OS_program_name=sqlplus@ 11gnode1(TNSV1-V3) update"SYS"."TEST1"set"COL1"=HEXTORAW('78787878')where"COL1"=HEXTORAW('787878')andROWID='AAAE6eAABAAAKHBAAC'; 2018-10-1422:10:12DDLSYS login_username=SYSclient_info=OS_username=oracleMachine_name=11gnode1OS_terminal=pts/5OS_process_id=17273OS_program_name=sqlplus@ 11gnode1(TNSV1-V3) truncatetabletest1;