日志挖掘

1、添加需要挖掘的日志

SQL> execute dbms_logmnr.add_logfile('/opt/oracle/oradata/orcl/redo03.log',dbms_logmnr. new);

PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.add_logfile('/opt/oracle/oradata/orcl/redo02.log',dbms_logmnr. addfile);

PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.add_logfile('/opt/oracle/oradata/orcl/redo01.log',dbms_logmnr. addfile);

PL/SQL procedure successfully completed.

2、开始挖掘

SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog+dbms_logmnr.committed_data_only);

PL/SQL procedure successfully completed.

3、查看挖掘的内容结果

SQL> select sql_undo,sql_redo from v$logmnr_contents where table_name='T' and operation='INSERT';

4、保存挖掘结果（停止挖掘后挖掘结果会全部丢失）

SQL> create table tt as select sql_undo,sql_redo from v$logmnr_contents where table_name='T' and operation='UPDATE';

5、停止挖掘

SQL> execute dbms_logmnr.end_logmnr();

PL/SQL procedure successfully completed.

————————————————————

挖掘归档日志  不懂原理

————————————————————

1、设置参数

SQL> alter system set "utl_file_dir"='/home/oracle' scope=spfile;

System altered.

2、创建平面文件

SQL> execute dbms_logmnr_d.build('log.ora','/home/oracle/',dbms_logmnr_d.store_in_flat_file);

在 /home/oracle/ 下生成文件 log.ora

3、添加需要挖掘的归档日志

SQL> execute dbms_logmnr.add_logfile('/opt/oracle/flash_recovery_area/ORCL/archivelog/2013_06_26/o1_mf_1_19_8wo7h08z_.arc',dbms_logmnr.new);

PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.add_logfile('/opt/oracle/flash_recovery_area/ORCL/archivelog/2013_06_26/o1_mf_1_18_8wo7gq7o_.arc',dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

4、开始挖掘

exec dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/log.ora');

5、查看挖掘的内容结果

SQL> select sql_undo,sql_redo from v$logmnr_contents where table_name='T' and operation='INSERT';

6、保存挖掘结果（停止挖掘后挖掘结果会全部丢失）

SQL> create table tt as select sql_undo,sql_redo from v$logmnr_contents where table_name='T' and operation='UPDATE';

7、停止挖掘

SQL> execute dbms_logmnr.end_logmnr();

PL/SQL procedure successfully completed.

8、关闭参数

SQL> alter system reset utl_file_dir scope=spfile;

System altered.

1、DDL没有undo语句

2、DML有undo,也有redo

3、加密的数据不能分析