<span>&nbsp;&nbsp;&nbsp; Oracle 11g</span><span style="font-family:宋体;">可以使用</span><span>LOGMNR</span><span style="font-family:宋体;">在线分析和挖掘日志，使用当前在线的数据字典，非常方便；很多时候拿</span><span>LOGMNR</span><span style="font-family:宋体;">来追踪一些误操作也是很有效的方式。<br /> &nbsp;&nbsp;&nbsp; 总的说来，</span><span>LogMiner</span><span style="font-family:宋体;">工具的主要用途有：</span> <p style="text-indent:-21pt;margin-left:42pt;"> <span style="font-family:Wingdings;"><span>l</span></span><span style="font-family:宋体;">跟踪数据库的变化：可以离线的跟踪数据库的变化，而不会影响在线系统的性能。</span> </p> <p style="text-indent:-21pt;margin-left:42pt;"> <span style="font-family:Wingdings;"><span>l</span></span><span style="font-family:宋体;">回退数据库的变化：回退特定的变化数据，减少</span><span>point-in-time recovery</span><span style="font-family:宋体;">的执行。</span> </p> <p style="text-indent:-21pt;margin-left:42pt;"> <span style="line-height:150%;font-family:Wingdings;font-size:11.5pt;"><span>l</span></span><span style="font-family:宋体;">优化和扩容计划：可通过分析日志文件中的数据以分析数据增长模式</span><span style="line-height:150%;font-family:'Arial','sans-serif';font-size:11.5pt;"></span> </p> <p> <span></span> </p> <p> <span style="font-family:宋体;">&nbsp;&nbsp;&nbsp; <br /> &nbsp;&nbsp;&nbsp; 如果要使用</span>LOGMNR分析日志，我们需要安装LogMiner工具。下面分享一下我安装LogMiner工具并使用LOGMNR的DICT_FROM_ONLINE_CATALOG分析REDO日志的过程。<br /> </p> <h2> <span>0&nbsp; </span><span style="font-family:黑体;">环境准备</span> </h2> <p> <span style="font-family:宋体;">&nbsp;&nbsp;&nbsp; 我们在</span><span>Oracle11g</span><span style="font-family:宋体;">进行操作。<br /> <div> <div class="codeheads"> <p> 点击(<span style="color:red;cursor:pointer;" onclick="code_hide('code62')">此处</span>)折叠或打开 </p> </div> <div id="code62" class="codeText"> <ol style="padding-bottom:5px;margin:0px 1px 0px 0px;padding-left:0pt;padding-right:0pt;padding-top:5px;" class="dp-css none_number"> <li> <span style="color:#000000;">SQL<span style="color:#0000cc;">&gt;</span> <br /> </li> <li> <br /> </li> <li> SQL<span style="color:#0000cc;">&gt;</span> <span style="color:#0000ff;">select</span> <span style="color:#0000cc;">*</span> <span style="color:#0000ff;">from</span> v$version<span style="color:#0000cc;">;</span><br /> </li> <li> <br /> </li> <li> <br /> </li> <li> BANNER<br /> </li> <li> <br /> </li> <li> <span style="color:#ff9900;">--------------------------------------------------------------------------------<br /> </li> <li> </span><br /> </li> <li> <br /> </li> <li> Oracle <span style="color:#0000ff;">Database</span> 11g Enterprise Edition <span style="color:#ff0000;">Release</span> 11<span style="color:#0000cc;">.</span>2<span style="color:#0000cc;">.</span>0<span style="color:#0000cc;">.</span>3<span style="color:#0000cc;">.</span>0 <span style="color:#0000cc;">-</span> Production<br /> </li> <li> <br /> </li> <li> PL<span style="color:#0000cc;">/</span>SQL <span style="color:#ff0000;">Release</span> 11<span style="color:#0000cc;">.</span>2<span style="color:#0000cc;">.</span>0<span style="color:#0000cc;">.</span>3<span style="color:#0000cc;">.</span>0 <span style="color:#0000cc;">-</span> Production<br /> </li> <li> <br /> </li> <li> CORE 11<span style="color:#0000cc;">.</span>2<span style="color:#0000cc;">.</span>0<span style="color:#0000cc;">.</span>3<span style="color:#0000cc;">.</span>0 Production<br /> </li> <li> <br /> </li> <li> TNS <span style="color:#0000ff;">for</span> Linux<span style="color:#0000cc;">:</span> Version 11<span style="color:#0000cc;">.</span>2<span style="color:#0000cc;">.</span>0<span style="color:#0000cc;">.</span>3<span style="color:#0000cc;">.</span>0 <span style="color:#0000cc;">-</span> Production<br /> </li> <li> <br /> </li> <li> NLSRTL Version 11<span style="color:#0000cc;">.</span>2<span style="color:#0000cc;">.</span>0<span style="color:#0000cc;">.</span>3<span style="color:#0000cc;">.</span>0 <span style="color:#0000cc;">-</span> Production<br /> </li> <li> <br /> </li> <li> <br /> </li> <li> SQL<span style="color:#0000cc;">&gt;</span></span> </li> </ol> </div> </div> <br /> <br /> </span> <p> <br /> </p> <h2> <span>1&nbsp; </span><span style="font-family:黑体;">添加补充日志</span> </h2> <p> <span style="font-family:宋体;">&nbsp;&nbsp;&nbsp; 如果我们需要捕获</span><span>DML</span><span style="font-family:宋体;">，就必须确保数据库的补充日志功能是打开的。（<strong><span style="color:#e53333;">前面几天的测试中，由于没有添加该日志一直都没有成功，所以这一步尤为关键</span></strong>。）</span> </p> <p> <span style="font-family:宋体;">&nbsp;&nbsp;&nbsp; 首先我们通过查询</span><span>v$database</span><span style="font-family:宋体;">动态视图来获取补充日志的相关信息，如果数据库没有打开补充日志功能，我们可以通过执行</span><span>alter database add supplemental log…</span><span style="font-family:宋体;">语句来添加补充日志。</span><span> </p> <p> <div> <div class="codeheads"> <p> 点击(<span style="color:red;cursor:pointer;" onclick="code_hide('code515')">此处</span>)折叠或打开 </p> </div> <div id="code515" class="codeText"> <ol style="padding-bottom:5px;margin:0px 1px 0px 0px;padding-left:0pt;padding-right:0pt;padding-top:5px;" class="dp-css none_number"> <li> <span style="color:#000000;">SQL<span style="color:#0000cc;">&gt;</span> <span style="color:#0000ff;">SELECT</span> SUPPLEMENTAL_LOG_DATA_PK<span style="color:#0000cc;">,</span> SUPPLEMENTAL_LOG_DATA_UI <span style="color:#0000ff;">FROM</span> V$<span style="color:#0000ff;">DATABASE</span><span style="color:#0000cc;">;</span><br /> </li> <li> <br /> </li> <li> <br /> </li> <li> SUP SUP<br /> </li> <li> <br /> </li> <li> <span style="color:#ff9900;">--- ---<br /> </li> <li> </span><br /> </li> <li> <br /> </li> <li> <span style="background-color:#ffe500;">NO NO</span><br /> </li> <li> <br /> </li> <li> <br /> </li> <li> SQL<span style="color:#0000cc;">&gt;</span><br /> </li> <li> <br /> </li> <li> SQL<span style="color:#0000cc;">&gt;</span> <span style="background-color:#ffe500;color:#0000ff;">alter</span> <span style="background-color:#ffe500;color:#0000ff;">database</span> <span style="background-color:#ffe500;color:#0000ff;">add</span><span style="background-color:#ffe500;"> supplemental </span><span style="background-color:#ffe500;color:#ff0000;">log</span> <span style="background-color:#ffe500;color:#0000ff;">data</span><span style="background-color:#ffe500;color:#0000cc;">(</span><span style="background-color:#ffe500;color:#0000ff;">primary</span> <span style="background-color:#ffe500;color:#0000ff;">key</span><span style="background-color:#ffe500;color:#0000cc;">,</span><span style="background-color:#ffe500;color:#0000ff;">unique</span> <span style="background-color:#ffe500;color:#0000ff;">index</span><span style="background-color:#ffe500;color:#0000cc;">)</span><span style="background-color:#ffe500;"> columns</span><span style="background-color:#ffe500;color:#0000cc;">;</span><br /> </li> <li> <br /> </li> <li> <br /> </li> <li> <span style="color:#0000ff;">Database</span> altered<span style="color:#0000cc;">.</span><br /> </li> <li> <br /> </li> <li> SQL<span style="color:#0000cc;">&gt;</span> <br /> </li> <li> <br /> </li> <li> SQL<span style="color:#0000cc;">&gt;</span> <span style="color:#0000ff;">SELECT</span> SUPPLEMENTAL_LOG_DATA_PK<span style="color:#0000cc;">,</span> SUPPLEMENTAL_LOG_DATA_UI <span style="color:#0000ff;">FROM</span> V$<span style="color:#0000ff;">DATABASE</span><span style="color:#0000cc;">;</span><br /> </li> <li> <br /> </li> <li> <br /> </li> <li> SUP SUP<br /> </li> <li> <br /> </li> <li> <span style="color:#ff9900;">--- ---<br /> </li> <li> </span><br /> </li> <li> <br /> </li> <li> <span style="background-color:#ffe500;">YES YES</span><br /> </li> <li> <br /> </li> <li> <br /> </li> <li> SQL<span style="color:#0000cc;">&gt;</span></span> </li> </ol> </div> <br /> </span> </div> <p> <br /> </p> <h2> <span>2&nbsp; </span><span style="font-family:黑体;">安装</span><span>LogMiner</span><span style="font-family:黑体;">工具</span> </h2> <p> <span style="font-family:宋体;">&nbsp;&nbsp;&nbsp; 要安装</span><span>LogMiner</span><span style="font-family:宋体;">工具，必须首先要运行下面三个脚本，并且这三个脚本必须以</span><span>SYS</span><span style="font-family:宋体;">用户身份运行。</span> </p> <p style="text-indent:-21pt;margin-left:42pt;"> <span style="font-family:Wingdings;"><span>l<span>&nbsp; </span></span></span><span>$ORACLE_HOME/rdbms/admin/dbmslm.sql</span> </p> <p style="text-indent:-21pt;margin-left:42pt;"> <span style="font-family:Wingdings;"><span>l<span>&nbsp; </span></span></span><span>$ORACLE_HOME/rdbms/admin/dbmslmd.sql</span> </p> <p style="text-indent:-21pt;margin-left:42pt;"> <span style="font-family:Wingdings;"><span>l<span>&nbsp; </span></span></span><span>$ORACLE_HOME/rdbms/admin/dbmslms.sql</span> </p> <p> <span style="font-family:宋体;">&nbsp;&nbsp;&nbsp; 其中第一个脚本用来创建</span><span>DBMS_LOGMNR</span><span style="font-family:宋体;">包，该包用来分析日志文件；第二个脚本用来创建</span><span>DBMS_LOGMNR_D</span><span style="font-family:宋体;">包，该包用来创建数据字典文件；第三个脚本用来创建</span><span>dbms_logmnr_session</span><span style="font-family:宋体;">包并授权。</span><span> </p> <p> <div> <div class="codeheads"> <p> 点击(<span style="color:red;cursor:pointer;" onclick="code_hide('code484')">此处</span>)折叠或打开 </p> </div> <div id="code484" class="codeText"> <ol style="padding-bottom:5px;margin:0px 1px 0px 0px;padding-left:0pt;padding-right:0pt;padding-top:5px;" class="dp-css none_number"> <li> <span style="color:#000000;">SQL<span style="color:#0000cc;">&gt;</span> <span style="color:#0000cc;">@</span><span style="color:#0000cc;">$</span>ORACLE_HOME<span style="color:#0000cc;">/</span>rdbms<span style="color:#0000cc;">/</span>admin<span style="color:#0000cc;">/</span>dbmslm<span style="color:#0000cc;">.</span>sql<br /> </li> <li> <br /> </li> <li> <br /> </li> <li> Package created<span style="color:#0000cc;">.</span><br /> </li> <li> <br /> </li> <li> <br /> </li> <li> <br /> </li> <li> <span style="color:#0000ff;">Grant</span> succeeded<span style="color:#0000cc;">.</span><br /> </li> <li> <br /> </li> <li> <br /> </li> <li> <br /> </li> <li> <span style="color:#0000ff;">Synonym</span> created<span style="color:#0000cc;">.</span><br /> </li> <li> <br /> </li> <li> <br /> </li> <li> SQL<span style="color:#0000cc;">&gt;</span> <span style="color:#0000cc;">@</span><span style="color:#0000cc;">$</span>ORACLE_HOME<span style="color:#0000cc;">/</span>rdbms<span style="color:#0000cc;">/</span>admin<span style="color:#0000cc;">/</span>dbmslmd<span style="color:#0000cc;">.</span>sql<br /> </li> <li> <br /> </li> <li> <br /> </li> <li> Package created<span style="color:#0000cc;">.</span><br /> </li> <li> <br /> </li> <li> <br /> </li> <li> <br /> </li> <li> <span style="color:#0000ff;">Synonym</span> created<span style="color:#0000cc;">.</span><br /> </li> <li> <br /> </li> <li> <br /> </li> <li> SQL<span style="color:#0000cc;">&gt;</span> <span style="color:#0000cc;">@</span><span style="color:#0000cc;">$</span>ORACLE_HOME<span style="color:#0000cc;">/</span>rdbms<span style="color:#0000cc;">/</span>admin<span style="color:#0000cc;">/</span>dbmslms<span style="color:#0000cc;">.</span>sql<br /> </li> <li> <br /> </li> <li> <br /> </li> <li> Package created<span style="color:#0000cc;">.</span><br /> </li> <li> <br /> </li> <li> <br /> </li> <li> No errors<span style="color:#0000cc;">.</span><br /> </li> <li> <br /> </li> <li> <br /> </li> <li> <span style="color:#0000ff;">Grant</span> succeeded<span style="color:#0000cc;">.</span><br /> </li> <li> <br /> </li> <li> <br /> </li> <li> SQL<span style="color:#0000cc;">&gt;</span></span> </li> </ol> </div> <br /> </span> </div> <p> <br /> </p> <h2> <span>3&nbsp; </span><span style="font-family:黑体;">分析</span><span>redo</span><span style="font-family:黑体;">日志</span> </h2> <p> <span style="font-family:黑体;">&nbsp;&nbsp;&nbsp; </span>首先我们需要执行一些DDL或DML操作作为分析素材，然后使用LOGMNR工具进行挖掘、分析。 </p> <h3> <span>3.1 </span><span style="font-family:黑体;">执行</span><span>DDL</span><span style="font-family:黑体;">或</span><span>DML</span><span style="font-family:黑体;">操作</span> </h3> <p> <span style="font-size:12px;">&nbsp;&nbsp;&nbsp; </span><span style="font-size:12px;">首先创建一个hoegh表，插入3条数据并提交；然后，删除一条记录，并提交。<br /> <div> <div class="codeheads"> <p> 点击(<span style="color:red;cursor:pointer;" onclick="code_hide('code859')">此处</span>)折叠或打开 </p> </div> <div id="code859" class="codeText"> <ol style="padding-bottom:5px;margin:0px 1px 0px 0px;padding-left:0pt;padding-right:0pt;padding-top:5px;" class="dp-css none_number"> <li> <span style="color:#000000;">SQL<span style="color:#0000cc;">&gt;</span> <span style="color:#0000ff;">create</span> <span style="color:#0000ff;">table</span> hoegh<span style="color:#0000cc;">(</span>id <span style="color:#0000ff;">number</span><span style="color:#0000cc;">,</span>name <span style="color:#0000ff;">varchar2</span><span style="color:#0000cc;">(</span>20<span style="color:#0000cc;">)</span><span style="color:#0000cc;">)</span><span style="color:#0000cc;">;</span><br /> </li> <li> <br /> </li> <li> <br /> </li> <li> <span style="color:#0000ff;">Table</span> created<span style="color:#0000cc;">.</span><br /> </li> <li> <br /> </li> <li> <br /> </li> <li> SQL<span style="color:#0000cc;">&gt;</span> <span style="color:#0000ff;">insert</span> <span style="color:#0000ff;">into</span> hoegh <span style="color:#0000ff;">values</span><span style="color:#0000cc;">(</span>1<span style="color:#0000cc;">,</span><span style="color:#ff00ff;">'hoegh'</span><span style="color:#0000cc;">)</span><span style="color:#0000cc;">;</span><br /> </li> <li> <br /> </li> <li> <br /> </li> <li> 1 <span style="color:#0000ff;">row</span> created<span style="color:#0000cc;">.</span><br /> </li> <li> <br /> </li> <li> <br /> </li> <li> SQL<span style="color:#0000cc;">&gt;</span> <span style="color:#0000ff;">insert</span> <span style="color:#0000ff;">into</span> hoegh <span style="color:#0000ff;">values</span><span style="color:#0000cc;">(</span>10<span style="color:#0000cc;">,</span><span style="color:#ff00ff;">'hoegh'</span><span style="color:#0000cc;">)</span><span style="color:#0000cc;">;</span><br /> </li> <li> <br /> </li> <li> <br /> </li> <li> 1 <span style="color:#0000ff;">row</span> created<span style="color:#0000cc;">.</span><br /> </li> <li> <br /> </li> <li> <br /> </li> <li> SQL<span style="color:#0000cc;">&gt;</span> <span style="color:#0000ff;">insert</span> <span style="color:#0000ff;">into</span> hoegh <span style="color:#0000ff;">values</span><span style="color:#0000cc;">(</span>100<span style="color:#0000cc;">,</span><span style="color:#ff00ff;">'hoegh'</span><span style="color:#0000cc;">)</span><span style="color:#0000cc;">;</span><br /> </li> <li> <br /> </li> <li> <br /> </li> <li> 1 <span style="color:#0000ff;">row</span> created<span style="color:#0000cc;">.</span><br /> </li> <li> <br /> </li> <li> <br /> </li> <li> SQL<span style="color:#0000cc;">&gt;</span> commit<span style="color:#0000cc;">;</span><br /> </li> <li> <br /> </li> <li> <br /> </li> <li> Commit complete<span style="color:#0000cc;">.</span><br /> </li> <li> <br /> </li> <li> <br /> </li> <li> SQL<span style="color:#0000cc;">&gt;</span> <br /> </li> <li> <br /> </li> <li> SQL<span style="color:#0000cc;">&gt;</span> <span style="color:#0000ff;">delete</span> <span style="color:#0000ff;">from</span> hoegh <span style="color:#0000ff;">where</span> id<span style="color:#0000cc;">=</span>10<span style="color:#0000cc;">;</span><br /> </li> <li> <br /> </li> <li> <br /> </li> <li> 1 <span style="color:#0000ff;">row</span> deleted<span style="color:#0000cc;">.</span><br /> </li> <li> <br /> </li> <li> <br /> </li> <li> SQL<span style="color:#0000cc;">&gt;</span> commit<span style="color:#0000cc;">;</span><br /> </li> <li> <br /> </li> <li> <br /> </li> <li> Commit complete<span style="color:#0000cc;">.</span><br /> </li> <li> <br /> </li> <li> <br /> </li> <li> SQL<span style="color:#0000cc;">&gt;</span> <br /> </li> <li> <br /> </li> <li> SQL<span style="color:#0000cc;">&gt;</span> <span style="color:#0000ff;">select</span> <span style="color:#0000cc;">*</span> <span style="color:#0000ff;">from</span> hoegh<span style="color:#0000cc;">;</span><br /> </li> <li> <br /> </li> <li> <br /> </li> <li> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ID NAME<br /> </li> <li> <br /> </li> <li> <span style="color:#ff9900;">---------- --------------------<br /> </li> <li> </span><br /> </li> <li> <br /> </li> <li> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1 hoegh<br /> </li> <li> <br /> </li> <li> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;100 hoegh<br /> </li> <li> <br /> </li> <li> <br /> </li> <li> SQL<span style="color:#0000cc;">&gt;</span></span> </li> </ol> </div> </div> <br /> </span> <p> <br /> </p> <h3> <span>3.2 </span><span style="font-family:黑体;">查看</span><span>redo</span><span style="font-family:黑体;">日志路径</span> </h3> <p> <span style="font-family:黑体;">&nbsp;&nbsp;&nbsp; </span>通过查询v$log和v$logfile两个动态视图来获取当前状态为Current的redo日志文件路径。<br /> <div> <div class="codeheads"> <p> 点击(<span style="color:red;cursor:pointer;" onclick="code_hide('code609')">此处</span>)折叠或打开 </p> </div> <div id="code609" class="codeText"> <ol style="padding-bottom:5px;margin:0px 1px 0px 0px;padding-left:0pt;padding-right:0pt;padding-top:5px;" class="dp-css none_number"> <li> <span style="color:#000000;">SQL<span style="color:#0000cc;">&gt;</span> <span style="color:#0000ff;">select</span> <span style="color:#0000cc;">*</span> <span style="color:#0000ff;">from</span> v$<span style="color:#ff0000;">log</span><span style="color:#0000cc;">;</span><br /> </li> <li> <br /> </li> <li> &nbsp;&nbsp;&nbsp;&nbsp;<span style="color:#0000ff;">GROUP</span><span style="color:#0000ff;">#</span> THREAD# <span style="color:#0000ff;">SEQUENCE</span><span style="color:#0000ff;">#</span> BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME<br /> </li> <li> <span style="color:#ff9900;">---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------<br /> </li> <li> </span><br /> </li> <li> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1 1 7 52428800 512 1 NO INACTIVE 898138 14<span style="color:#0000cc;">-</span>OCT<span style="color:#0000cc;">-</span>14 918641 07<span style="color:#0000cc;">-</span>AUG<span style="color:#0000cc;">-</span>15<br /> </li> <li> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2 1 8 52428800 512 1 NO ACTIVE 918641 07<span style="color:#0000cc;">-</span>AUG<span style="color:#0000cc;">-</span>15 919289 07<span style="color:#0000cc;">-</span>AUG<span style="color:#0000cc;">-</span>15<br /> </li> <li> &nbsp;&nbsp;&nbsp;&nbsp;<span style="background-color:#ffe500;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3 1 9 52428800 512 1 NO </span><span style="background-color:#ffe500;color:#0000ff;">CURRENT</span><span style="background-color:#ffe500;"> 919289 07</span><span style="background-color:#ffe500;color:#0000cc;">-</span><span style="background-color:#ffe500;">AUG</span><span style="background-color:#ffe500;color:#0000cc;">-</span><span style="background-color:#ffe500;">15 2</span><span style="background-color:#ffe500;color:#0000cc;">.</span><span style="background-color:#ffe500;">8147E</span><span style="background-color:#ffe500;color:#0000cc;">+</span><span style="background-color:#ffe500;">14</span><br /> </li> <li> <br /> </li> <li> SQL<span style="color:#0000cc;">&gt;</span><br /> </li> <li> SQL<span style="color:#0000cc;">&gt;</span> <span style="color:#0000ff;">select</span> <span style="color:#0000ff;">group</span><span style="color:#0000ff;">#</span><span style="color:#0000cc;">,</span><span style="color:#0000ff;">member</span> <span style="color:#0000ff;">from</span> v$logfile<span style="color:#0000cc;">;</span><br /> </li> <li> <br /> </li> <li> &nbsp;&nbsp;&nbsp;&nbsp;<span style="color:#0000ff;">GROUP</span><span style="color:#0000ff;">#</span> <span style="color:#0000ff;">MEMBER</span><br /> </li> <li> <span style="color:#ff9900;">---------- ----------------------------------------------------------------------------------------------------<br /> </li> <li> </span><br /> </li> <li> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="background-color:#ffe500;">&nbsp;3 </span><span style="background-color:#ffe500;color:#0000cc;">/</span><span style="background-color:#ffe500;">u01</span><span style="background-color:#ffe500;color:#0000cc;">/</span><span style="background-color:#ffe500;">app</span><span style="background-color:#ffe500;color:#0000cc;">/</span><span style="background-color:#ffe500;">oracle</span><span style="background-color:#ffe500;color:#0000cc;">/</span><span style="background-color:#ffe500;">oradata</span><span style="background-color:#ffe500;color:#0000cc;">/</span><span style="background-color:#ffe500;">PROD1</span><span style="background-color:#ffe500;color:#0000cc;">/</span><span style="background-color:#ffe500;">redo03</span><span style="background-color:#ffe500;color:#0000cc;">.</span><span style="background-color:#ffe500;color:#ff0000;">log</span><br /> </li> <li> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2 <span style="color:#0000cc;">/</span>u01<span style="color:#0000cc;">/</span>app<span style="color:#0000cc;">/</span>oracle<span style="color:#0000cc;">/</span>oradata<span style="color:#0000cc;">/</span>PROD1<span style="color:#0000cc;">/</span>redo02<span style="color:#0000cc;">.</span><span style="color:#ff0000;">log</span><br /> </li> <li> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1 <span style="color:#0000cc;">/</span>u01<span style="color:#0000cc;">/</span>app<span style="color:#0000cc;">/</span>oracle<span style="color:#0000cc;">/</span>oradata<span style="color:#0000cc;">/</span>PROD1<span style="color:#0000cc;">/</span>redo01<span style="color:#0000cc;">.</span><span style="color:#ff0000;">log</span><br /> </li> <li> <br /> </li> <li> SQL<span style="color:#0000cc;">&gt;</span></span> </li> </ol> </div> <br /> </div> <p> <br /> </p> <p> <span></span> </p> <p> <span></span> </p> <h3> <span>3.3 </span><span style="font-family:黑体;">添加</span><span>redo</span><span style="font-family:黑体;">日志</span> </h3> <p> &nbsp;&nbsp;&nbsp; Oracle的LogMiner可以分析在线（online）和归档（offline）两种日志文件，加入分析日志文件使用dbms_logmnr.add_logfile过程，第一个文件使用dbms_logmnr.NEW参数，后面文件使用dbms_logmnr.ADDFILE参数。<span> </p> <p> <div> <div class="codeheads"> <p> 点击(<span style="color:red;cursor:pointer;" onclick="code_hide('code890')">此处</span>)折叠或打开 </p> </div> <div id="code890" class="codeText"> <ol style="padding-bottom:5px;margin:0px 1px 0px 0px;padding-left:0pt;padding-right:0pt;padding-top:5px;" class="dp-css none_number"> <li> <span style="color:#000000;">SQL<span style="color:#0000cc;">&gt;</span><br /> </li> <li> <br /> </li> <li> SQL<span style="color:#0000cc;">&gt;</span> exec dbms_logmnr<span style="color:#0000cc;">.</span>add_logfile<span style="color:#0000cc;">(</span><span style="color:#ff00ff;">'/u01/app/oracle/oradata/PROD1/redo03.log'</span><span style="color:#0000cc;">,</span><span style="background-color:#ffe500;">options</span><span style="background-color:#ffe500;color:#0000cc;">=</span><span style="background-color:#ffe500;color:#0000cc;">&gt;</span><span style="background-color:#ffe500;color:#e53333;">dbms_logmnr</span><span style="background-color:#ffe500;color:#e53333;">.</span><span style="background-color:#ffe500;color:#e53333;">new</span><span style="color:#0000cc;">)</span><span style="color:#0000cc;">;</span><br /> </li> <li> <br /> </li> <li> <br /> </li> <li> PL<span style="color:#0000cc;">/</span>SQL procedure successfully completed<span style="color:#0000cc;">.</span><br /> </li> <li> <br /> </li> <li> <br /> </li> <li> SQL<span style="color:#0000cc;">&gt;</span></span> </li> </ol> </div> <br /> </span> </div> <p> <br /> </p> <h3> <span>3.4 </span><span style="font-family:黑体;">开启</span><span>LOGMNR</span><span style="font-family:黑体;">挖掘日志</span><br /> <div> <div class="codeheads"> <p> 点击(<span style="color:red;cursor:pointer;" onclick="code_hide('code781')">此处</span>)折叠或打开 </p> </div> <div id="code781" class="codeText"> <ol style="padding-bottom:5px;margin:0px 1px 0px 0px;padding-left:0pt;padding-right:0pt;padding-top:5px;" class="dp-css none_number"> <li> <span style="color:#000000;">SQL<span style="color:#0000cc;">&gt;</span> <span style="background-color:#ffe500;">exec dbms_logmnr</span><span style="background-color:#ffe500;color:#0000cc;">.</span><span style="background-color:#ffe500;">start_logmnr</span><span style="background-color:#ffe500;color:#0000cc;">(</span><span style="background-color:#ffe500;">options</span><span style="background-color:#ffe500;color:#0000cc;">=</span><span style="background-color:#ffe500;color:#0000cc;">&gt;</span><span style="background-color:#ffe500;">dbms_logmnr</span><span style="background-color:#ffe500;color:#0000cc;">.</span><span style="background-color:#ffe500;">dict_from_online_catalog</span><span style="background-color:#ffe500;color:#0000cc;">)</span><span style="background-color:#ffe500;color:#0000cc;">;</span><br /> </li> <li> <br /> </li> <li> <br /> </li> <li> PL<span style="color:#0000cc;">/</span>SQL procedure successfully completed<span style="color:#0000cc;">.</span><br /> </li> <li> <br /> </li> <li> <br /> </li> <li> SQL<span style="color:#0000cc;">&gt;</span></span> </li> </ol> </div> <span><br /> </span> </div> </h3> <p> <span></span> </p> <h3> <span>3.5 </span><span style="font-family:黑体;">查询挖掘结果</span> </h3> <p> <span style="font-family:宋体;">&nbsp;&nbsp;&nbsp; 到现在为止，我们已经分析得到了</span><span>redo</span><span style="font-family:宋体;">日志文件中的内容。动态性能视图</span><span>v$logmnr_contents</span><span style="font-family:宋体;">包含</span><span>LogMiner</span><span style="font-family:宋体;">分析得到的所有的信息。这个信息量可能很大，因此我们可以通过限定用户名和表名来查询特定表的操作。</span> </p> <h4> <span>3.5.1 </span><span style="font-family:黑体;">查询</span><span>redo</span><span style="font-family:黑体;">信息</span><span><br /> </h4> <p> <div> <div class="codeheads"> <p> 点击(<span style="color:red;cursor:pointer;" onclick="code_hide('code515')">此处</span>)折叠或打开 </p> </div> <div id="code515" class="codeText"> <ol style="padding-bottom:5px;margin:0px 1px 0px 0px;padding-left:0pt;padding-right:0pt;padding-top:5px;" class="dp-css none_number"> <li> <span style="color:#000000;">SQL<span style="color:#0000cc;">&gt;</span> <span style="color:#0000ff;">select</span> <span style="color:#ff0000;">to_char</span><span style="color:#0000cc;">(</span>timestamp<span style="color:#0000cc;">,</span><span style="color:#ff00ff;">'yyyy-mm-dd hh24:mi:ss'</span><span style="color:#0000cc;">)</span><span style="color:#0000cc;">,</span><span style="background-color:#ffe500;">sql_redo </span><span style="color:#0000ff;">from</span> v$logmnr_contents <span style="background-color:#ffe500;color:#0000ff;">where</span><span style="background-color:#ffe500;"> username</span><span style="background-color:#ffe500;color:#0000cc;">=</span><span style="background-color:#ffe500;color:#ff00ff;">'SCOTT'</span> <span style="background-color:#ffe500;color:#0000ff;">and</span><span style="background-color:#ffe500;"> table_name</span><span style="background-color:#ffe500;color:#0000cc;">=</span><span style="background-color:#ffe500;color:#ff00ff;">'HOEGH'</span><span style="color:#0000cc;">;</span><br /> </li> <li> <br /> </li> <li> <br /> </li> <li> <span style="color:#ff0000;">TO_CHAR</span><span style="color:#0000cc;">(</span>TIMESTAMP<span style="color:#0000cc;">,</span><span style="color:#ff00ff;">' SQL_REDO<br /> </li> <li> <br /> </li> <li> ------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------<br /> </li> <li> <br /> </li> <li> 2015-08-07 13:44:15 create table hoegh(id number,name varchar2(20));<br /> </li> <li> <br /> </li> <li> 2015-08-07 13:44:26 insert into "SCOTT"."HOEGH"("ID","NAME") values ('</span>1<span style="color:#ff00ff;">','</span>hoegh<span style="color:#ff00ff;">');<br /> </li> <li> <br /> </li> <li> 2015-08-07 13:44:39 insert into "SCOTT"."HOEGH"("ID","NAME") values ('</span>10<span style="color:#ff00ff;">','</span>hoegh<span style="color:#ff00ff;">');<br /> </li> <li> <br /> </li> <li> 2015-08-07 13:44:45 insert into "SCOTT"."HOEGH"("ID","NAME") values ('</span>100<span style="color:#ff00ff;">','</span>hoegh<span style="color:#ff00ff;">');<br /> </li> <li> <br /> </li> <li> 2015-08-07 13:44:54 delete from "SCOTT"."HOEGH" where "ID" = '</span>10<span style="color:#ff00ff;">' and "NAME" = '</span>hoegh<span style="color:#ff00ff;">' and ROWID = '</span>AAASwNAAEAAAAIOAAB</span> </li> </ol> </div> <br /> </span> </div> <p> <br /> </p> <p> <span></span> </p> <p> <span></span> </p> <h4> <span>3.5.2 </span><span style="font-family:黑体;">查询</span><span>undo</span><span style="font-family:黑体;">信息</span><span><br /> </h4> <p> <div> <div class="codeheads"> <p> 点击(<span style="color:red;cursor:pointer;" onclick="code_hide('code937')">此处</span>)折叠或打开 </p> </div> <div id="code937" class="codeText"> <ol style="padding-bottom:5px;margin:0px 1px 0px 0px;padding-left:0pt;padding-right:0pt;padding-top:5px;" class="dp-css none_number"> <li> <span style="color:#000000;">SQL<span style="color:#0000cc;">&gt;</span> <span style="color:#0000ff;">select</span> <span style="color:#ff0000;">to_char</span><span style="color:#0000cc;">(</span>timestamp<span style="color:#0000cc;">,</span><span style="color:#ff00ff;">'yyyy-mm-dd hh24:mi:ss'</span><span style="color:#0000cc;">)</span><span style="color:#0000cc;">,</span><span style="background-color:#ffe500;">sql_undo </span><span style="color:#0000ff;">from</span> v$logmnr_contents <span style="color:#0000ff;">where</span> <span style="background-color:#ffe500;">username</span><span style="background-color:#ffe500;color:#0000cc;">=</span><span style="background-color:#ffe500;color:#ff00ff;">'SCOTT'</span> <span style="background-color:#ffe500;color:#0000ff;">and</span><span style="background-color:#ffe500;"> table_name</span><span style="background-color:#ffe500;color:#0000cc;">=</span><span style="background-color:#ffe500;color:#ff00ff;">'HOEGH'</span><span style="color:#0000cc;">;</span><br /> </li> <li> <br /> </li> <li> <br /> </li> <li> <span style="color:#ff0000;">TO_CHAR</span><span style="color:#0000cc;">(</span>TIMESTAMP<span style="color:#0000cc;">,</span><span style="color:#ff00ff;">' SQL_UNDO<br /> </li> <li> <br /> </li> <li> ------------------- ----------------------------------------------------------------------------------------------------<br /> </li> <li> <br /> </li> <li> 2015-08-07 13:44:15<br /> </li> <li> <br /> </li> <li> 2015-08-07 13:44:26 delete from "SCOTT"."HOEGH" where "ID" = '</span>1<span style="color:#ff00ff;">' and "NAME" = '</span>hoegh<span style="color:#ff00ff;">' and ROWID = '</span>AAASwNAAEAAAAIOAAA<span style="color:#ff00ff;">';<br /> </li> <li> <br /> </li> <li> 2015-08-07 13:44:39 delete from "SCOTT"."HOEGH" where "ID" = '</span>10<span style="color:#ff00ff;">' and "NAME" = '</span>hoegh<span style="color:#ff00ff;">' and ROWID = '</span>AAASwNAAEAAAAIOAAB<span style="color:#ff00ff;">';<br /> </li> <li> <br /> </li> <li> 2015-08-07 13:44:45 delete from "SCOTT"."HOEGH" where "ID" = '</span>100<span style="color:#ff00ff;">' and "NAME" = '</span>hoegh<span style="color:#ff00ff;">' and ROWID = '</span>AAASwNAAEAAAAIOAAC<span style="color:#ff00ff;">';<br /> </li> <li> <br /> </li> <li> 2015-08-07 13:44:54 insert into "SCOTT"."HOEGH"("ID","NAME") values ('</span>10<span style="color:#ff00ff;">','</span>hoegh</span> </li> </ol> </div> <br /> </span> </div> <p> <br /> </p> <h3> <span>3.6 </span><span style="font-family:黑体;">停止</span><span>LOGMNR</span><span style="font-family:黑体;">挖掘日志</span> </h3> <p> <span style="font-family:宋体;">&nbsp;&nbsp;&nbsp;&nbsp;需要注意的是，视图<span>v$logmnr_contents</span><span style="font-family:宋体;">中的分析结果仅在我们运行过程</span><span>'dbms_logmrn.start_logmnr'</span><span style="font-family:宋体;">这个会话的生命周期中存在。这是因为所有的</span><span>LogMiner</span><span style="font-family:宋体;">存储都在</span><span>PGA</span><span style="font-family:宋体;">内存中，所有其他的进程是看不到它的，同时随着进程的结束，分析结果也随之消失。</span></span><span style="font-family:宋体;">&nbsp;&nbsp;&nbsp; <br /> &nbsp;&nbsp;&nbsp; 最后，当我们完成</span><span>redo</span><span style="font-family:宋体;">日志的分析工作以后，可以使用过程</span><span>DBMS_LOGMNR.END_LOGMNR</span><span style="font-family:宋体;">来终止日志分析事务，此时</span><span>PGA</span><span style="font-family:宋体;">内存区域被清除，分析结果也随之不再存在。 </p> <p> <div> <div class="codeheads"> <p> 点击(<span style="color:red;cursor:pointer;" onclick="code_hide('code875')">此处</span>)折叠或打开 </p> </div> <div id="code875" class="codeText"> <ol style="padding-bottom:5px;margin:0px 1px 0px 0px;padding-left:0pt;padding-right:0pt;padding-top:5px;" class="dp-css none_number"> <li> <span style="color:#000000;">SQL<span style="color:#0000cc;">&gt;</span> <br /> </li> <li> <br /> </li> <li> SQL<span style="color:#0000cc;">&gt;</span> <span style="background-color:#ffe500;">exec dbms_logmnr</span><span style="background-color:#ffe500;color:#0000cc;">.</span><span style="background-color:#ffe500;">end_logmnr</span><span style="background-color:#ffe500;color:#0000cc;">(</span><span style="background-color:#ffe500;color:#0000cc;">)</span><span style="background-color:#ffe500;color:#0000cc;">;</span><br /> </li> <li> <br /> </li> <li> <br /> </li> <li> PL<span style="color:#0000cc;">/</span>SQL procedure successfully completed<span style="color:#0000cc;">.</span><br /> </li> <li> <br /> </li> <li> <br /> </li> <li> SQL<span style="color:#0000cc;">&gt;</span></span> </li> </ol> </div> </div> <br /> </span> <p> <br /> </p> <div style="line-height:26px;background-color:#ffffff;text-indent:32px;font-family:宋体, Arial;white-space:normal;color:#666666;"> <span style="font-family:'Courier New';font-size:small;">hoegh</span> </div> <div style="line-height:26px;background-color:#ffffff;text-indent:32px;font-family:宋体, Arial;white-space:normal;color:#666666;"> <span style="font-family:'Courier New';font-size:small;">15.8.7</span> </div> <div style="line-height:26px;background-color:#ffffff;text-indent:32px;font-family:宋体, Arial;white-space:normal;color:#666666;"> <span style="font-family:'Courier New';font-size:small;">-- The End --</span> </div>