[Logmnr]对归档日志进行数据挖掘

时间：2022-09-23 08:17:26

1、创建演示数据

SQL> update emp set sal=sal+100 where deptno=10;
SQL> update emp set sal=sal+200 where deptno=20;
SQL> update emp set sal=sal+300 where deptno=30;
SQL> update emp set sal=sal+400 where deptno=40;
SQL> update emp set sal=sal-400 where deptno=10;
2、手动切换归档日志 
SQL> alter system switch logfile;System altered

3、查询归档日志相关信息

SQL> select name,sequence# from v$archived_log;
NAME                                                                              SEQUENCE#
-------------------------------------------------------------------------------- ----------
/u01/oracle/archive/1_34_895068568.dbf                                                   34
/u01/oracle/archive/1_35_895068568.dbf                                                   35
/u01/oracle/archive/1_36_895068568.dbf                                                   36
3 rows selected
4、添加需要进行解析的日志文件 
SQL> exec dbms_logmnr.add_logfile('/u01/oracle/archive/1_34_895068568.dbf',dbms_logmnr.new);PL/SQL procedure successfully completedSQL> exec dbms_logmnr.add_logfile('/u01/oracle/archive/1_35_895068568.dbf',dbms_logmnr.addfile);PL/SQL procedure successfully completedSQL> exec dbms_logmnr.add_logfile('/u01/oracle/archive/1_36_895068568.dbf',dbms_logmnr.addfile);PL/SQL procedure successfully completed
5、使用在线字典进行解析+只查询commit的数据+不显示rowid 
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog+dbms_logmnr.committed_data_only+dbms_logmnr.no_rowid_in_stmt);PL/SQL procedure successfully completed
6、查询解析结果 
SQL> select sql_redo,sql_undo from v$logmnr_contents where table_name='EMP';SQL_REDO                                                      SQL_UNDO------------------------------------------------------------------------------------------------------------------------update "SCOTT"."EMP" set "SAL" = '2472' where "SAL" = '2462' update "SCOTT"."EMP" set "SAL" = '2462' where "SAL" = '2472'update "SCOTT"."EMP" set "SAL" = '5022' where "SAL" = '5012' update "SCOTT"."EMP" set "SAL" = '5012' where "SAL" = '5022'update "SCOTT"."EMP" set "SAL" = '1322' where "SAL" = '1312' update "SCOTT"."EMP" set "SAL" = '1312' where "SAL" = '1322'update "SCOTT"."EMP" set "SAL" = '2572' where "SAL" = '2472' update "SCOTT"."EMP" set "SAL" = '2472' where "SAL" = '2572'update "SCOTT"."EMP" set "SAL" = '5122' where "SAL" = '5022' update "SCOTT"."EMP" set "SAL" = '5022' where "SAL" = '5122'update "SCOTT"."EMP" set "SAL" = '1422' where "SAL" = '1322' update "SCOTT"."EMP" set "SAL" = '1322' where "SAL" = '1422'update "SCOTT"."EMP" set "SAL" = '1020' where "SAL" = '820'  update "SCOTT"."EMP" set "SAL" = '820' where "SAL" = '1020'update "SCOTT"."EMP" set "SAL" = '3195' where "SAL" = '2995' update "SCOTT"."EMP" set "SAL" = '2995' where "SAL" = '3195'update "SCOTT"."EMP" set "SAL" = '3220' where "SAL" = '3020' update "SCOTT"."EMP" set "SAL" = '3020' where "SAL" = '3220'update "SCOTT"."EMP" set "SAL" = '1320' where "SAL" = '1120' update "SCOTT"."EMP" set "SAL" = '1120' where "SAL" = '1320'update "SCOTT"."EMP" set "SAL" = '3220' where "SAL" = '3020' update "SCOTT"."EMP" set "SAL" = '3020' where "SAL" = '3220'update "SCOTT"."EMP" set "SAL" = '1930' where "SAL" = '1630' update "SCOTT"."EMP" set "SAL" = '1630' where "SAL" = '1930'update "SCOTT"."EMP" set "SAL" = '1580' where "SAL" = '1280' update "SCOTT"."EMP" set "SAL" = '1280' where "SAL" = '1580'update "SCOTT"."EMP" set "SAL" = '1580' where "SAL" = '1280' update "SCOTT"."EMP" set "SAL" = '1280' where "SAL" = '1580'update "SCOTT"."EMP" set "SAL" = '3180' where "SAL" = '2880' update "SCOTT"."EMP" set "SAL" = '2880' where "SAL" = '3180'update "SCOTT"."EMP" set "SAL" = '1830' where "SAL" = '1530' update "SCOTT"."EMP" set "SAL" = '1530' where "SAL" = '1830'update "SCOTT"."EMP" set "SAL" = '1280' where "SAL" = '980'  update "SCOTT"."EMP" set "SAL" = '980' where "SAL" = '1280'update "SCOTT"."EMP" set "SAL" = '2172' where "SAL" = '2572' update "SCOTT"."EMP" set "SAL" = '2572' where "SAL" = '2172'update "SCOTT"."EMP" set "SAL" = '4722' where "SAL" = '5122' update "SCOTT"."EMP" set "SAL" = '5122' where "SAL" = '4722'update "SCOTT"."EMP" set "SAL" = '1022' where "SAL" = '1422' update "SCOTT"."EMP" set "SAL" = '1422' where "SAL" = '1022'20 rows selected
7、结束整个Logminer 
SQL> exec dbms_logmnr.end_logmnr();PL/SQL procedure successfully completed


其中第5步也可以使用utl_file_dir（不建议,该参数只是为了兼容9i），具体步骤如下：

①修改utl_file_dir参数，并重启数据库

SQL> alter system set utl_file_dir='/home/oracle/' scope=spfile;
System altered.
SQL> shutdown immediate;
SQL> startup
ORACLE instance started.
②使用dbms_logmnr_d.build建立OS上的字典文件 
SQL> exec dbms_logmnr_d.build('logmnr_dict.ora','/home/oracle/',dbms_logmnr_d.store_in_flat_file);
PL/SQL procedure successfully completed.
③添加归档日志文件 
SQL> exec dbms_logmnr.add_logfile('/u01/oracle/archive/1_32_895068568.dbf',dbms_logmnr.new);PL/SQL procedure successfully completed.SQL> exec dbms_logmnr.add_logfile('/u01/oracle/archive/1_33_895068568.dbf',dbms_logmnr.addfile);PL/SQL procedure successfully completed.SQL> exec dbms_logmnr.add_logfile('/u01/oracle/archive/1_34_895068568.dbf',dbms_logmnr.addfile);PL/SQL procedure successfully completed.
④启用Logmnr分析 
SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logmnr_dict.ora');PL/SQL procedure successfully completed.


标签：

相关文章