logmnr(二) dictory模式的用法
Mar282012
书接上文，上文介绍了LOGMNR的DICT_FROM_ONLIE_CATALOG模式的用法（详见http://www.dbdream.com.cn/2013/03/19/logmnr%E4%B8%89dict_from_redo_logs%E6%A8%A1%E5%BC%8F/）,这次说下LOGMNR的DICTORY模式的用法，这种方法也很常用，功能也很强大，这种方式不仅可以分析当前数据库的ONLINE REDO LOG和ARCHIVE LOG，还可以分析其他数据库的归档日志，使用这种方式需要先建立一个字典文件，操作起来也比较麻烦，依赖性也较高，需要设置数据库的UTL_FILE_DIR参数，UTL_FILE_DIR参数默认没有设置，而且该参数是静态参数，设置该参数需要重启数据库才可以生效。
本实验环境为OEL5.4，ORACLE11gR2
SQL> show parameter utl_file_dir NAME TYPE VALUE --------------- ---------- ------- utl_file_dir string SQL> alter system set utl_file_dir='/home/oracle/utltest' scope=spfile; System altered. SQL> shutdown immediate SQL> startup SQL> show parameter utl_file_dir NAME TYPE VALUE -------------- ----------- ------------------------ utl_file_dir string /home/oracle/utltest
一、LOGMNR的DICTORY模式挖掘本地数据库的ONLINE REDO LOG的用法。
1.创建数据字典。
SQL> exec dbms_logmnr_d.build(dictionary_filename=>'dict.ora',dictionary_location=>'/home/oracle/utltest',options=>dbms_logmnr_d.store_in_flat_file); PL/SQL procedure successfully completed.
此时在/home/oracle/utl_dbdream目录下就会生成一个名字为dict.ora的文件。
[oracle@stream ~]$ cd /home/oracle/utltest [oracle@stream utltest]$ ls dict.ora [oracle@stream utltest]$ du -sh dict.ora 35M dict.ora
2.查看数据库的REDO日志路径。
SQL> select member from v$logfile; MEMBER ------------------------------------------------ /u01/app/oracle/oradata/stream/STREAM/redo03.log /u01/app/oracle/oradata/stream/STREAM/redo02.log /u01/app/oracle/oradata/stream/STREAM/redo01.log
3.添加日志文件。
SQL> exec dbms_logmnr.add_logfile(logfilename=> '/u01/app/oracle/oradata/stream/STREAM/redo01.log', options=>dbms_logmnr.new); PL/SQL procedure successfully completed. SQL> exec dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/stream/STREAM/redo02.log',options=>dbms_logmnr.addfile); PL/SQL procedure successfully completed. SQL> exec dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/stream/STREAM/redo03.log',options=>dbms_logmnr.addfile); PL/SQL procedure successfully completed.
4.开始分析。
SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/utltest/dict.ora'); PL/SQL procedure successfully completed.
5.查看分析的日志信息。
SQL> select timestamp,sql_redo from v$logmnr_contents where table_name='EMP'; SQL_REDO --------------------------------------------------------- update "SCOTT"."EMP" set "EMPNO" = '7788' where "EMPNO" = '7788' and ROWID = 'AAAR6+AAEAAAACXAAH';
6.关闭LOGMNR
SQL> exec dbms_logmnr.end_logmnr; PL/SQL procedure successfully completed.
二、LOGMNR的DICTORY模式挖掘本地数据库的ARCHIVE LOG的用法。
和LOGMNR的DICT_FROM_ONLINE_CATALOG模式一样，使用LOGMNR的DICTORY模式挖掘本地数据库的归档日志和挖掘ONLINE REDO LOG的用法几乎相同。
1.创建数据字典
如果操作前没有创建上文的dict.ora字典文件，需先创建这个字典文件。
SQL> exec dbms_logmnr_d.build(dictionary_filename=>'dict.ora',dictionary_location=>'/home/oracle/utltest',options=>dbms_logmnr_d.store_in_flat_file); PL/SQL procedure successfully completed.
2.添加归档日志
要确保要分析的信息在添加的归档日志内，否则可能挖掘不到想要的信息。
SQL> exec dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/flash_recovery_area/STREAM/archivelog/2012_03_21/o1_mf_1_13_7plvx3bc_.arc',options=>dbms_logmnr.new); PL/SQL procedure successfully completed. SQL> exec dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/flash_recovery_area/STREAM/archivelog/2012_03_21/o1_mf_1_14_7plw0bgo_.arc',options=>dbms_logmnr.addfile); PL/SQL procedure successfully completed.
3.开始挖掘
SQL> execute DBMS_LOGMNR.START_LOGMNR(DICTFILENAME =>'/home/oracle/utltest/dict.ora'); PL/SQL procedure successfully completed.
4.查询挖掘的日志信息
SQL> select timestamp,sql_redo from v$logmnr_contents where table_name='T_TEST';
SQL_REDO
----------------------------------------------------------------
create table t_test(id number,name varchar2(15));
insert into "SCOTT"."T_TEST"("ID","NAME") values ('1','stream');
insert into "SCOTT"."T_TEST"("ID","NAME") values ('2','dbdream');
update "SCOTT"."T_TEST" set "NAME" = 'streamsong' where "NAME" =
'stream' and ROWID = 'AAAR7fAAEAAAADXAAA';
delete from "SCOTT"."T_TEST" where "ID" = '1' and "NAME" =
'streamsong' and ROWID = 'AAAR7fAAEAAAADXAAA';
delete from "SCOTT"."T_TEST" where "ID" = '2' and "NAME" =
'dbdream' and ROWID = 'AAAR7fAAEAAAADXAAB';
5.结束LOGMNR挖掘
SQL> exec dbms_logmnr.end_logmnr; PL/SQL procedure successfully completed.
三、分析其他数据库的归档日志
使用LOGMNR的DICTORY模式的好处就是可以分析其他数据库的日志信息，但是在将其他数据库的日志拷到本地数据库服务器的同时，还需要拷贝那个数据库创建的字典文件。
1.创建测试表
SQL> conn scott/tiger
Connected.
SQL> create table t_test as select * from dept;
Table created.
SQL> select * from t_test;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
2.对测试表做DML操作
SQL> update t_test set deptno=30 where dname='SALES'; 1 row updated. SQL> commit; Commit complete.
3.创建字典文件
SQL> exec dbms_logmnr_d.build(dictionary_filename=>'dict_dbdream.ora',dictionary_location=>'/home/oracle/utl_dbdream',options=>dbms_logmnr_d.store_in_flat_file); PL/SQL procedure successfully completed.
4.切换日志，使REDO日志归档
SQL> conn / as sysdba Connected. SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> / System altered.
5.将归档日志和字典文件拷贝到测试数据库服务器
[oracle@dbdream ~]$ scp /u01/app/oracle/flash_recovery_area/DBDREAM/ archivelog/2012_03_27/* 192.168.78.200:/home/oracle/utltest oracle@192.168.78.200's password: o1_mf_1_54_7q2wdhqp_.arc 100% 403KB 403.0KB/s 00:00 o1_mf_1_55_7q2wdjqh_.arc 100% 1024 1.0KB/s 00:00 o1_mf_1_56_7q2wdmn6_.arc 100% 2560 2.5KB/s 00:00 [oracle@dbdream utl_dbdream]$ scp dict_dbdream.ora 192.168.78.200:/home/oracle/utltest oracle@192.168.78.200's password: dict_dbdream.ora 100% 30MB 29.9MB/s 00:01
以上5步骤的操作是在主机名和数据库实例名为dbdream的数据库服务器操作，以下部分是在主机名和数据库实例名为stream的数据库服务器上操作。
将远程数据库的归档和字典文件拿到之后，就可以使用LOGMNR的DICTORY方式对这些归档日志进行分析，方法和分析本地日志的方法几乎相同，有一点区别就是在本地数据库也就是做分析的数据库不需要创建字典文件了，因为已经将远程的字典文件拷贝过来了，即使创建了字典文件也不能拿来分析远程的归档日志，分析远程的归档日志必须使用远程数据库自己创建的字典文件。
6.添加远程数据库的归档日志
SQL> exec dbms_logmnr.add_logfile(logfilename=> '/home/oracle/utltest/o1_mf_1_54_7q2wdhqp_.arc', options=>dbms_logmnr.new); PL/SQL procedure successfully completed. SQL> exec dbms_logmnr.add_logfile(logfilename=>'/home/oracle/utltest/o1_mf_1_55_7q2wdjqh_.arc',options=>dbms_logmnr.addfile); PL/SQL procedure successfully completed. SQL> exec dbms_logmnr.add_logfile(logfilename=>'/home/oracle/utltest/o1_mf_1_56_7q2wdmn6_.arc',options=>dbms_logmnr.addfile); PL/SQL procedure successfully completed.
7.开始分析
SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/utltest/dict_dbdream.ora'); PL/SQL procedure successfully completed.
8.查询分析的远程数据库归档日志的信息
SQL> select sql_redo from v$logmnr_contents where table_name ='T_TEST'; SQL_REDO ----------------------------------------------------------------------- create table t_test as select * from dept; update "SCOTT"."T_TEST" set "DEPTNO" = '30' where "DEPTNO" = '30' and ROWID = 'AAACgCAAEAAAAA+AAA';
9.结束LOGMNR操作
SQL> exec dbms_logmnr.end_logmnr; PL/SQL procedure successfully completed.
好麻烦，这种方式基本已经被DICT_FROM_ONLINE_CATALOG模式取代，但是作为DBA，这种方法也得会，万一哪天就遇到这样的需求也不好说，一切皆有可能！
logmnr(二) dictory模式的用法：等您坐沙发呢！
发表评论
