深入了解行业动态与专业知识
一.LogMiner Dictionary to a Flat File1.1.查看参数show parameter utl_file_dir;utl_file_dir的值为空1.2.创建目录mkdir /home/oracle/logmnr1.3.修改参数ALTER SYSTEM SET utl_file_dir/home/oracle/logmnr SCOPESPFILE;该参数是静态参数修改完需要重启数据库生效。在 Oracle Database 12c 第 2 版 (12.2) 之前使用 UTL_FILE_DIR 初始化参数来指定目录位置。 但是从 Oracle Database 18c 开始不再支持 UTL_FILE_DIR 初始化参数。 它仍然支持向后兼容但 Oracle 强烈建议您改用目录对象。数据库版本18c及以后版本使用如下方式:CREATE DIRECTORY my_dictionary_dir AS /oracle/database;1.4.重启数据库关闭数据库SHUTDOWN IMMEDIATE;启动数据库STARTUP;1.5.创建数据字典文件1.5.1.数据库版本12.2之前EXECUTE dbms_logmnr_d.build(dictionary_filename dictionary.ora, dictionary_location /home/oracle/logmnr,options dbms_logmnr_d.store_in_flat_file);创建完以后相应的目录下会生成数据字典文件。1.5.2.数据库版本18c及以后版本EXECUTE dbms_logmnr_d.build(dictionary_locationmy_dictionary_dir,dictionary_filenamedictionary.ora,options dbms_logmnr_d.store_in_flat_file);1.6.加入需要分析的归档日志文件1.6.1.添加第一个归档日志EXECUTE DBMS_LOGMNR.ADD_LOGFILE(/oracle/archlog/1_107_1159006485.dbf,optionsdbms_logmnr.NEW);1.6.2.继续添加EXECUTE DBMS_LOGMNR.ADD_LOGFILE(/oracle/archlog/1_106_1159006485.dbf,optionsdbms_logmnr.addfile);1.6.3.移除日志EXECUTE DBMS_LOGMNR.REMOVE_LOGFILE(/oracle/archlog/1_106_1159006485.dbf);1.6.4.查看添加了哪些日志SELECT *FROM V$LOGMNR_LOGS;1.7.开始分析日志execute dbms_logmnr.start_logmnr(dictfilename/home/oracle/logmnr/dictionary.ora);1.8.查看分析日志结果SELECT * FROM V$LOGMNR_CONTENTS;1.9.结束日志分析EXECUTE SYS.DBMS_LOGMNR.END_LOGMNR;二.Using the Online Catalog2.1.添加需要分析的日志文件EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME /archlog/1_206_1106751927.dbf, OPTIONS DBMS_LOGMNR.NEW);2.2.继续添加EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME /archlog/1_207_1106751927.dbf, OPTIONS DBMS_LOGMNR.ADDFILE);2.3.查看添加的日志信息select filename from V$LOGMNR_LOGS;2.4.开始日志挖掘EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);2.5.获取日志挖掘结果SELECT username AS USR, (XIDUSN || . || XIDSLT || . || XIDSQN) AS XID,SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS;2.6.结束日志挖掘EXECUTE DBMS_LOGMNR.END_LOGMNR;三.LogMiner Dictionary to the Redo Log Files3.1.将字典写入归档重做日志EXECUTE DBMS_LOGMNR_D.BUILD( OPTIONS DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);3.2.查询字典所在的日志文件select ,l.dictionary_begin,l.dictionary_end from v$archived_log l where l.dictionary_beginYES OR l.dictionary_end YES;3.3.添加我们需要分析的日志EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME /archlog/1_206_1106751927.dbf, OPTIONS DBMS_LOGMNR.NEW);3.4.添加字典值是前面3.2查询的v$archived_log的结果dbms_logmnr.add_logfile(logfilename /archlog/1_207_1106751927.dbf,options dbms_logmnr.addfile);3.5.开始日志挖掘dbms_logmnr.start_logmnr(options dbms_logmnr.dict_from_redo_logs);3.6.获取挖掘结果SELECT username AS USR, (XIDUSN || . || XIDSLT || . || XIDSQN) AS XID,SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS;3.7.结束挖掘EXECUTE DBMS_LOGMNR.END_LOGMNR;
