logminer使用实践

最新推荐文章于 2026-03-14 01:21:42 发布

原创最新推荐文章于 2026-03-14 01:21:42 发布 · 388 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

oracle 专栏收录该内容

48 篇文章

订阅专栏

   在开发测试部门有多套oracle数据库环境，经常发生误删数据或者误操作的情况，想找到肇事者又没人承认。
辛辛苦苦造的测试数据总有人修改，不管是有意还是无意，已经影响了正常的工作。
   我们领导要知道是谁干的，是否有办法恢复误操作的数据。
   这个可以用oracle自带的logminer工具来分析日志。
   一参数设置
     如果要使用logminer，需要打开SUPPLEMENTAL LOG。
     查看SUPPLEMENTAL LOG 是否打开，

     1. 查看 supplemental状态
    > select name ,supplemental_log_data_min from v$database;
    NAME      SUPPLEME
    --------- --------
    ora10g     NO

   2. 修改supplemental的状态

    > alter database add supplemental log data;
    Database altered.
      (如果要关闭，使用 alter database drop supplemental log data;)
   3.确认supplemental的状态
    > select name,supplemental_log_data_min from v$database;
    NAME      SUPPLEME
    --------- --------
    ora10g     YES
    1 row selected.

      如果要看以前的归档，需要将归档日志打开，如下：
      1.查看归档情况
          SQL> archive log list;
     Database log mode              No Archive Mode
     Automatic archival             Disabled
     Archive destination            USE_DB_RECOVERY_FILE_DEST
     Oldest online log sequence     111
     Current log sequence           113
       2 打开归档模式
         startup mount;
         alter database archivlog;
         alter database open;
         --关闭归档
         alter database noarchivlog;
       3 查看归档文件的位置
         查看初始化参数show parameter log_archive_dest看到是哪个目录就是它了。
         如果没有值，再查show parameter db_recovery_file_dest
         Select name, sequence#, first_change# FROM v$archived_log;
二安装logminer

logminer实际上是由两个pl/sql内建包(dbms_logmnr和dbms_logmnr_d)和4个v$动态性能视图组成的.
v$logmnr_logs
v$logmnr_contents
v$logmnr_parameters

安装logminer首先要以sys管理员的身份运行这个pl/sql脚本
sql> conn /as sysdba
sql> @?/rdbms/admin/dbmslm.sql
sql> @?/rdbms/admin/dbmslmd.sql

三执行 logminer
1 增加分析的文件
exec dbms_logmnr.add_logfile('/oradata/orcl10g/redo02.log',dbms_logmnr.new);
2 开始分析
exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
1).无限制条件，即用数据字典文件对要分析的日志文件所有内容做分析
     SQL> execute dbms_logmnr.start_logmnr(DictFileName => 'G:\oracle\logs\dict.ora');
     PL/SQL procedure successfully completed
   2).带限制条件，可以用scn号或时间做限制条件,也可组合使用
     --分析日志列表中时间从07.02.28从10:00到15:00的内容
      SQL> execute dbms_logmnr.start_logmnr(startTime => to_date('20070228100000','yyyy-mm-dd hh24:mi:ss'),endTime => to_date('20070228150000','yyyy-mm-dd hh24:mi:ss'),DictFileName => 'G:\oracle\logs\dict.ora');

3 查看并保存分析结果
    select count(*) from v$logmnr_contents;
    create table ulog.log4 as select * from v$logmnr_contents;
    因为日志分析只在当前session内有效，所以要转存出来方便处理。

4 分析后释放内存
    exec dbms_logmnr.end_logmnr ;

四样例
    select a.info,a.session_info,a.sql_redo,a.sql_undo,a.operation from ulog.log4 a where a.table_name='P2'

     INFO SESSION_INFO SQL_REDO SQL_UNDO OPERATION
1  login_username=SYS client_info= OS_username=phb ...... insert into "ULOG"."P2"("F1","B1","B2","B3") values ('xx3','5','5','ddd'); delete from "ULOG"."P2" where "F1" = 'xx3' and "B1" = '5' and "B2" = '5' and "B3" = 'ddd' and ROWID = 'AAANJZAAEAAAABcAAA'; INSERT
2  login_username=SYS client_info= OS_username=phb ...... delete from "ULOG"."P2" where "F1" = 'xx3' and "B1" = '5' and "B2" = '5' and "B3" = 'ddd' and ROWID = 'AAANJZAAEAAAABcAAA'; insert into "ULOG"."P2"("F1","B1","B2","B3") values ('xx3','5','5','ddd'); DELETE
3  login_username=SYS client_info= OS_username=phb ...... delete from "ULOG"."P2" where "F1" = 'xx3' and "B1" = '5' and "B2" = '5' and "B3" = 'ddd' and ROWID = 'AAANJZAAEAAAABdAAE'; insert into "ULOG"."P2"("F1","B1","B2","B3") values ('xx3','5','5','ddd'); DELETE
4  login_username=SYS client_info= OS_username=phb ...... delete from "ULOG"."P2" where "F1" = 'xx3' and "B1" = '5' and "B2" = '5' and "B3" = 'ddd' and ROWID = 'AAANJZAAEAAAABdAAF'; insert into "ULOG"."P2"("F1","B1","B2","B3") values ('xx3','5','5','ddd'); DELETE
5  login_username=SYS client_info= OS_username=phb ...... delete from "ULOG"."P2" where "F1" = 'xx3' and "B1" = '5' and "B2" = '5' and "B3" = 'ddd' and ROWID = 'AAANJZAAEAAAABdAAG'; insert into "ULOG"."P2"("F1","B1","B2","B3") values ('xx3','5','5','ddd'); DELETE
6  login_username=SYS client_info= OS_username=phb ...... delete from "ULOG"."P2" where "F1" = 'xx3' and "B1" = '5' and "B2" = '5' and "B3" = 'ddd' and ROWID = 'AAANJZAAEAAAABdAAH'; insert into "ULOG"."P2"("F1","B1","B2","B3") values ('xx3','5','5','ddd'); DELETE