查看归档日志

# 以sysdba身份登录
sqlplus / as sysdba

# 创建package，

SQL> @D:\oracle\product\10.2.0\db_1\rdbms\admin\dbmslm.sql;
SQL> @D:\oracle\product\10.2.0\db_1\rdbms\admin\dbmslmd.sql;

# 设置utl_file_dir参数
SQL> alter system set utl_file_dir = 'D:\oracle\logminer' scope = spfile;

SQL> shutdown immediately;
SQL> startup;
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIV STATUS                           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- ------ -------------------------------- ------------- ------------
         1          1        938   52428800          1 NO     INACTIVE                              17445492 30-NOV-08
         2          1        939   52428800          1 NO     INACTIVE                              17494420 01-DEC-08
         3          1        940   52428800          1 NO     CURRENT                               17508573 01-DEC-08

# 分析ARC07758_0647999818.001日志
SQL> exec dbms_logmnr.add_logfile('Z:\oracle_archive\ARC07758_0647999818.001', dbms_logmnr.new);
SQL> exec dbms_logmnr.start_logmnr(DictFileName=>'D:\oracle\logminer\dictionary.ora');
SQL> select count(*) from V$LOGMNR_CONTENTS;

# 将V$LOGMNR_CONTENTS试图的内容导出到文件中
SQL> spool d:\V$LOGMNR_CONTENTS
SQL> set head off
SQL> select * from V$LOGMNR_CONTENTS;
SQL> spool off

# 停止执行日志分析
SQL> exec dbms_logmnr.end_logmnr;