LogMiner如何在Oracle中使用
这篇文章给大家介绍LogMiner如何在Oracle中使用，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。
LogMiner介绍
LogMiner 是Oracle公司从产品8i以后提供的一个实际非常有用的分析工具，使用该工具可以轻松获得Oracle 重做日志文件（归档日志文件）中的具体内容，LogMiner分析工具实际上是由一组PL/SQL包和一些动态视图组成，它作为Oracle数据库的一部分来发布，是oracle公司提供的一个完全免费的工具。
环境：Oracle 11.2.0.4 RAC
1.查询当前日志组
使用sys用户查询Oracle数据库的当前日志组:
--1.currentlogSQL>select*fromv$log;GROUP#THREAD#SEQUENCE#BYTESBLOCKSIZEMEMBERSARCSTATUSFIRST_CHANGE#FIRST_TIMENEXT_CHANGE#NEXT_TIME--------------------------------------------------------------------------------------------------------------------------------1129524288005122YESINACTIVE154783825-JUN-17154784025-JUN-172130524288005122NOCURRENT156789727-JUN-172.8147E+1427-JUN-173225524288005122NOCURRENT156790227-JUN-172.8147E+144224524288005122YESINACTIVE156790027-JUN-17156790227-JUN-17
这里当前日志（current）是：
thread 1 sequence 30
thread 2 sequence 25
2.业务用户插入操作
模拟业务用户jingyu插入T2表数据：
--2.业务用户插入操作sqlplusjingyu/jingyu@jyzhaoSQL>selectcount(1)fromt2;COUNT(1)----------0SQL>insertintot2selectrownum,rownum,rownum,dbms_random.string('b',50)fromdualconnectbylevel<=100000orderbydbms_random.random;commit;100000rowscreated.SQL>Commitcomplete.SQL>selectcount(1)fromt2;COUNT(1)----------100000
3.归档日志切换
为了区分每个日志的不同操作，这里对数据库进行手工归档切换，模拟现实中实际的归档切换。
--3.模拟归档日志切换SQL>altersystemarchivelogcurrent;Systemaltered.SQL>select*fromv$log;GROUP#THREAD#SEQUENCE#BYTESBLOCKSIZEMEMBERSARCSTATUSFIRST_CHANGE#FIRST_TIMENEXT_CHANGE#NEXT_TIME--------------------------------------------------------------------------------------------------------------------------------1131524288005122NOCURRENT157251727-JUN-172.8147E+142130524288005122YESACTIVE156789727-JUN-17157251727-JUN-173225524288005122YESACTIVE156790227-JUN-17157252127-JUN-174226524288005122NOCURRENT157252127-JUN-172.8147E+14
4.业务用户插入操作
模拟业务用户jingyu删除T2表部分数据：
--4.业务用户删除操作SQL>deletefromt2whereid<10000;9999rowsdeleted.SQL>commit;Commitcomplete.SQL>selectcount(1)fromt2;COUNT(1)----------90001
5.归档日志切换
为了区分每个日志的不同操作，这里对数据库进行手工归档切换，模拟现实中实际的归档切换。
--5.模拟归档日志切换SQL>altersystemarchivelogcurrent;Systemaltered.SQL>select*fromv$log;GROUP#THREAD#SEQUENCE#BYTESBLOCKSIZEMEMBERSARCSTATUSFIRST_CHANGE#FIRST_TIMENEXT_CHANGE#NEXT_TIME--------------------------------------------------------------------------------------------------------------------------------1131524288005122YESACTIVE157251727-JUN-17157429327-JUN-172132524288005122NOCURRENT157429327-JUN-172.8147E+143227524288005122NOCURRENT157429627-JUN-172.8147E+144226524288005122YESACTIVE157252127-JUN-17157429627-JUN-17
6.业务用户更新操作
模拟业务用户jingyu更新T2表部分数据：
--6.业务用户更新操作SQL>updateT2SETcontents='xxx'whereid>99998;2rowsupdated.SQL>commit;Commitcomplete.
7.归档日志切换
为了区分每个日志的不同操作，这里对数据库进行手工归档切换，模拟现实中实际的归档切换。
--7.模拟归档日志切换SQL>altersystemarchivelogcurrent;Systemaltered.SQL>select*fromv$log;GROUP#THREAD#SEQUENCE#BYTESBLOCKSIZEMEMBERSARCSTATUSFIRST_CHANGE#FIRST_TIMENEXT_CHANGE#NEXT_TIME--------------------------------------------------------------------------------------------------------------------------------1133524288005122NOCURRENT157548027-JUN-172.8147E+142132524288005122YESACTIVE157429327-JUN-17157548027-JUN-173227524288005122YESACTIVE157429627-JUN-17157545827-JUN-174228524288005122NOCURRENT157545827-JUN-172.8147E+14
8.确认需要分析的日志
确认之后需要使用LogMiner分析的日志：
--8.确认需要分析的日志thread#1sequence#30thread#2sequence#25这部分日志肯定是有记录插入操作thread#1sequence#31thread#2sequence#26这部分日志肯定是有记录删除操作thread#1sequence#32thread#2sequence#27这部分日志肯定是有记录更新操作
9.备份归档日志
将相关的归档都copy备份出来：
--9.将相关的归档都copy备份出来RUN{allocatechanneldev1devicetypediskformat'/tmp/backup/arc_%h_%e_%t';backupascopyarchivelogsequence30thread1;backupascopyarchivelogsequence31thread1;backupascopyarchivelogsequence32thread1;backupascopyarchivelogsequence25thread2;backupascopyarchivelogsequence26thread2;backupascopyarchivelogsequence27thread2;releasechanneldev1;}
备份出来的归档日志文件如下：
[oracle@jyrac1backup]$ls-lrthtotal17M-rw-r-----1oracleasmadmin2.3MJun2721:50arc_1_30_947800247-rw-r-----1oracleasmadmin591KJun2721:50arc_1_31_947800249-rw-r-----1oracleasmadmin143KJun2721:50arc_1_32_947800250-rw-r-----1oracleasmadmin9.5MJun2721:50arc_2_25_947800251-rw-r-----1oracleasmadmin3.6MJun2721:50arc_2_26_947800253-rw-r-----1oracleasmadmin77KJun2721:50arc_2_27_947800254
10.使用LogMiner分析
使用LogMiner分析归档日志：
--使用LogMiner分析归档日志--应该有插入操作的日志begindbms_logmnr.add_logfile('/tmp/backup/arc_1_30_947800247');dbms_logmnr.add_logfile('/tmp/backup/arc_2_25_947800251');dbms_logmnr.start_logmnr(Options=>dbms_logmnr.dict_from_online_catalog);end;/--应该有删除操作的日志begindbms_logmnr.add_logfile('/tmp/backup/arc_1_31_947800249');dbms_logmnr.add_logfile('/tmp/backup/arc_2_26_947800253');dbms_logmnr.start_logmnr(Options=>dbms_logmnr.dict_from_online_catalog);end;/--应该有更新操作的日志begindbms_logmnr.add_logfile('/tmp/backup/arc_1_32_947800250');dbms_logmnr.add_logfile('/tmp/backup/arc_2_27_947800254');dbms_logmnr.start_logmnr(Options=>dbms_logmnr.dict_from_online_catalog);end;/
查询v$logmnr_contents
setlines180pages500colusernameformata8colsql_redoformata50selectusername,scn,timestamp,sql_redofromv$logmnr_contentswheretable_name='T2';selectusername,scn,timestamp,sql_redofromv$logmnr_contentswhereusername='JINGYU';selectusername,scn,timestamp,sql_redofromv$logmnr_contentswheresql_redolike'%JINGYU%';selectusername,scn,timestamp,sql_redofromv$logmnr_contentswheresql_redolike'insert%JINGYU%';selectusername,scn,timestamp,sql_redofromv$logmnr_contentswheresql_redolike'delete%JINGYU%';selectusername,scn,timestamp,sql_redofromv$logmnr_contentswheresql_redolike'update%JINGYU%';
实验发现，以username为条件无法查询到相关记录，最终确认username都是unknown而不是真正执行语句的业务用户jingyu。
而挖掘出的日志sql_redo这个字段是完整的SQL，可以采用like的方式查询，比如我分析更新操作的日志，就可以得到下面这样的结果：
SQL>--应该有更新操作的日志SQL>begin2dbms_logmnr.add_logfile('/tmp/backup/arc_1_32_947800250');3dbms_logmnr.add_logfile('/tmp/backup/arc_2_27_947800254');4dbms_logmnr.start_logmnr(Options=>dbms_logmnr.dict_from_online_catalog);5end;6/PL/SQLproceduresuccessfullycompleted.SQL>selectcount(1)fromv$logmnr_contents;COUNT(1)----------388SQL>selectusername,scn,timestamp,sql_redofromv$logmnr_contentswhereusername='JINGYU';norowsselectedSQL>selectusername,scn,timestamp,sql_redofromv$logmnr_contentswheresql_redolike'%JINGYU%';USERNAMESCNTIMESTAMP----------------------------------------------------SQL_REDO--------------------------------------------------------------------------------UNKNOWN157542027-JUN-17update"JINGYU"."T2"set"CONTENTS"='xxx'where"CONTENTS"='YSWGNNLCLMYWPSLQETVLGQJRKQIEAMOEYUFNRUQULVFRVPEDRV'andROWID='AAAVWVAAGAAAAHnABj';UNKNOWN157542027-JUN-17update"JINGYU"."T2"set"CONTENTS"='xxx'where"CONTENTS"='WHCWFOZVLJWHFWLJDNVSMQTORGJFFXYADIOJZWJCDDOYXAOQJG'andROWID='AAAVWVAAGAAAAOYAAE';SQL>
至此，LogMiner基本的操作实验已完成。
附：与LogMiner有关的一些操作命令参考：
conn/assysdba--安装LOGMINER@$ORACLE_HOME/rdbms/admin/dbmslmd.sql;@$ORACLE_HOME/rdbms/admin/dbmslm.sql;@$ORACLE_HOME/rdbms/admin/dbmslms.sql;@$ORACLE_HOME/rdbms/admin/prvtlm.plb;--停止logmnrexecdbms_logmnr.end_logmnr--查询附加日志开启情况：selectsupplemental_log_data_min,supplemental_log_data_pk,supplemental_log_data_uifromv$database;--开启附加日志alterdatabaseaddsupplementallogdata;--取消补充日志alterdatabasedropsupplementallogdata(primarykey)columns;alterdatabasedropsupplementallogdata(unique)columns;alterdatabasedropsupplementallogdata;--最后一个即为新的归档selectname,dest_id,thread#,sequence#fromv$archived_log;
最后确认如果开启了附加日志，username就可以捕获到正确的值：
SQL>setlines180SQL>/GROUP#THREAD#SEQUENCE#BYTESBLOCKSIZEMEMBERSARCSTATUSFIRST_CHANGE#FIRST_TIMENEXT_CHANGE#NEXT_TIME--------------------------------------------------------------------------------------------------------------------------------1135524288005122YESINACTIVE159058927-JUN-17159193527-JUN-172136524288005122NOCURRENT159193527-JUN-172.8147E+143229524288005122YESINACTIVE159059427-JUN-17159193827-JUN-174230524288005122NOCURRENT159193827-JUN-172.8147E+141,362,30SQL>updatet2setcontents=2'aaa'whereid=44449;1rowupdated.SQL>commit;Commitcomplete.RUN{allocatechanneldev1devicetypediskformat'/tmp/backup/arc_%h_%e_%t';backupascopyarchivelogsequence36thread1;backupascopyarchivelogsequence30thread2;releasechanneldev1;}begindbms_logmnr.add_logfile('/tmp/backup/arc_1_36_947808116');dbms_logmnr.add_logfile('/tmp/backup/arc_2_30_947808118');dbms_logmnr.start_logmnr(Options=>dbms_logmnr.dict_from_online_catalog);end;/SQL>selectusername,scn,timestamp,sql_redofromv$logmnr_contentswhereusername='JINGYU';USERNAMESCNTIMESTAMP----------------------------------------------------SQL_REDO------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------JINGYU159344827-JUN-17settransactionreadwrite;JINGYU159344827-JUN-17update"JINGYU"."T2"set"CONTENTS"='aaa'where"CONTENTS"='WZTSQZWYOCNDFKSMNJQLOLFUBRDOHCBMKXBHAPJSHCMWBYZJVH'andROWID='AAAVWVAAGAAAACLAAL';JINGYU159345027-JUN-17commit;
关于LogMiner如何在Oracle中使用就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
