logmnr学习实践

先建立相关的包程序：

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> @H:/oracle/product/10.2.0/db_1/rdbms/admin/dbmslm.sql;

Package created.

Grant succeeded.

SQL> @H:/oracle/product/10.2.0/db_1/rdbms/admin/dbmslmd.sql;

Package created.

 

包程序建立完毕后执行出错：

SQL> exec dbms_logmnr_d.build(dictionary_filename=>'xxxx.ora', dictionary_location =>'h:/orabak');
BEGIN dbms_logmnr_d.build(dictionary_filename=>'xxxx.ora', dictionary_location =>'h:/orabak'); END;

*
ERROR at line 1:
ORA-01308: initialization parameter utl_file_dir is not set
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 3474
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 3552
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 12
ORA-06512: at line 1

 

设置utl_file_dir参数：

SQL>alter system  set  utl_file_dir='h:/orabak'  scope=spfile;

或
SQL>CREATE DIRECTORY utl AS 'h:/orabak';

 

重启服务：
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  209715200 bytes
Fixed Size                  1248116 bytes
Variable Size              83887244 bytes
Database Buffers          117440512 bytes
Redo Buffers                7139328 bytes
Database mounted.
Database opened.

 

设建立外部文档：
SQL> exec dbms_logmnr_d.build(dictionary_filename=>'xxxx.ora', dictionary_location =>'h:/orabak');

PL/SQL procedure successfully completed.

 

设置要查询的日志：

SQL> EXECUTE dbms_logmnr.add_logfile(LogFileName=>'+MYTESTDISC/mytest/arc00115_0705151208.001',Options=>dbms_logmnr.new);

PL/SQL procedure successfully completed.

 

开始导出：

SQL> EXECUTE dbms_logmnr.start_logmnr(DictFileName=>'h:/orabak/xxxx.ora');

PL/SQL procedure successfully completed.

 

这个时候就可以查询SELECT sql_redo FROM v$logmnr_contents; 或看外部文档了

 

结束：

SQL> EXECUTE dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.

SQL>

 

 

其它：

增加查询的日志：

EXEC dbms_logmnr.add_logfile(LogFileName=>'+MYTESTDISC/mytest/arc00115_0705151208.002',Options=>dbms_logmnr.addfile);

 

 

减少查询的日志：

 

 

 

 

 

EXEC dbms_logmnr.add_logfile(LogFileName=>'+MYTESTDISC/mytest/arc00115_0705151208.002',Options=>dbms_logmnr.REMOVEFILE);

 

增加查询日志的条件：

 

SQL> EXECUTE dbms_logmnr.start_logmnr(DictFileName=>'h:/orabak/xxxx.ora', StartTime => to_date('2010-01-18 00:00:00','YYYY-MM-DD HH24:MI:SS')，EndTime => to_date(''2010-01-18 23:59:59','YYYY-MM-DD HH24:MI:SS '));

 

SQL> EXECUTE dbms_logmnr.start_logmnr(DictFileName=>'h:/orabak/xxxx.ora', StartScn => 20,EndScn => 50);