oracle离线文档查dbms_小白小记-logminer工具分析离线归档日志

一、安装logminer工具

SQL>@$ORACLE_HOME/rdbms/admin/dbmslm.sq

SQL>@$ORACLE_HOME/rdbms/admin/dbmslmd.sql

打开日志补全功能

https://www.cndba.cn/Red/article/3514

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

ALTER SYSTEM SET UTL_FILE_DIR ='/logdata' SCOPE=SPFILE;

三、指定logminer的数据字典https://www.cndba.cn/Red/article/3514

EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora', '/logdata', DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '+DATA_FILES/yyzcpt/archivelog/2019_07_18/thread_2_seq_189.327.1013904027', OPTIONS => DBMS_LOGMNR.NEW);

如果还需要继续添加其他日志文件

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '+DATA_FILES/yyzcpt/archivelog/2019_07_18/thread_2_seq_189.327.1013904028', OPTIONS => DBMS_LOGMNR.ADDFILE);

五、查看已经添加的日志文件信息

https://www.cndba.cn/Red/article/3514

select filename from V$LOGMNR_LOGS;

https://www.cndba.cn/Red/article/3514

EXECUTE DBMS_LOGMNR.START_LOGMNR(DICTFILENAME =>'/logdata/dictionary.ora');

查询分析信息结果

select * from v$logmnr_contents;

可以将分析的结果创建成一个表便于查看https://www.cndba.cn/Red/article/3514

create table logminer tablespace users as select * from v$logmnr_contents;

7、结束分析释放资源https://www.cndba.cn/Red/article/3514

EXECUTE DBMS_LOGMNR.END_LOGMNR;

简单工具使用记录