知识要点

• 挖掘联机日志
• 挖掘归档日志

挖掘联机日志

1. 添加日志文件
   execute dbms_logmnr.add_logfile(‘/u01/app/oracle/oradata/sky2/redo01.log’,dbms_logmnr.new);
   execute dbms_logmnr.add logfile(‘filename’,dbms_logmnr.addfile);
2. 启动日志分析
   execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog+dbms_logmnr.committed_data_only);
3. 查找操作
   select sql_redo,sql_undo from v$logmnr_contents where table_name = ‘T’;
4. 结束日志分析
   exec dbms_logmnr.end_logmnr();

挖掘归档日志

1. 设置utl_file_dir
   alter system set utl_file_dir=’/u01/app/oracle’ scope=spfile;
2. 添加日志文件
   exec dbms_logmnr_d.build(‘log.ora’,’/u01/app/oracle’,dbms_logmnr_d.store_in_flat_file);
   execute dbms_logmnr.add_logfile(‘/u01/app/oracle/SKY2/archivelog/2017_07_06/o1_mf_1_3_dov3bf1l_.arc’,dbms_logmnr.new);
   execute dbms_logmnr.add_logfile(‘/u01/app/oracle/SKY2/archivelog/2017_07_06/o1_mf_1_2_dov39y21_.arc’,dbms_logmnr.addfile);
   execute dbms_logmnr.add_logfile(‘/u01/app/oracle/SKY2/archivelog/2017_07_06/o1_mf_1_1_dov39cjn_.arc’,dbms_logmnr.addfile);
3. 启动日志分析
   exec dbms_logmnr.start_logmnr(dictfilename=>’/u01/app/oracle/log.ora’);
4. 查找操作
   select sql_redo from v$logmnr_contents where table_name = ‘EMP’;
5. 结束日志分析 exec dbms_logmnr.end_logmnr();