logminer

logminer包括2个包：DBMS_LOGMNR和DBMS_LOGMNR_D，可以分析redo  log  file，也可以分析归档后的archive log file,在分析的过程中一般要先生成数据字典文件后才实用，对于oracle 10g，还

可以实用在线数据字典。
    logminer也可以分析其他db的redo log file，但是要实用redo log file所在db的数据字典，另外OS最好也一样，block size要求相同。

logminer的安装：
创建DBMS_LOGMNR：
SQL> @$ORACLE_HOME/rdbms/admin/dbmslm.sql

Package created.


Grant succeeded.

创建DBMS_LOGMNR_D
SQL> @$ORACLE_HOME/rdbms/admin/dbmslmd.sql

Package created.

logminer实验
首先检查一下supplemental logging是否开启
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

SUPPLEME
--------
NO

没有开启，将其启动

SQL> alter database add supplemental log data;

Database altered.

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

SUPPLEME
--------
YES

如果是YES或者IMPLICIT则表明已经生效了，否则需要重新启动。

SQL> show parameter utl

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines               string
utl_file_dir                         string
SQL> create table scott.test(id number, name varchar2(20));

Table created.

SQL> insert into scott.test values(1,'a');

1 row created.

SQL> commit;

Commit complete.

SQL> alter table scott.test add (string varchar2(20));

Table altered.

SQL> insert into scott.test values(2,'b','c');

1 row created.

SQL> commit;

Commit complete.

SQL> delete from scott.test where id = 1;

1 row deleted.

SQL> commit;

归档当前日志

SQL> alter system switch logfile;
System altered.

通过查询v$archived_log视图，得知归档日志文件名字为+DG1/devdb/1_14_768042025.dbf。

使用源数据库数据字典

添加要分析的日志文件
SQL> execute dbms_logmnr.add_logfile(logfilename=>'+DG1/devdb/1_14_768042025.dbf',options=>dbms_logmnr.new); 

PL/SQL procedure successfully completed.

继续添加的话，将options=>dbms_logmnr.addfile.
移除的话，使用dbms_logmnr.removefile。

启动logminer
SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.

查看分析结果

SQL> select username,scn,timestamp,sql_redo from v$logmnr_contents where seg_name='TEST';

USERNAME                              SCN TIMESTAMP
------------------------------ ---------- ---------
SQL_REDO
--------------------------------------------------------------------------------
                                  1561619 20-DEC-11
create table scott.test(id number, name varchar2(20));

                                  1561655 20-DEC-11
insert into "SCOTT"."TEST"("COL 1","COL 2") values (HEXTORAW('c102'),HEXTORAW('6
1'));

                                  1561757 20-DEC-11
alter table scott.test add (string varchar2(20));

USERNAME                              SCN TIMESTAMP
------------------------------ ---------- ---------
SQL_REDO
--------------------------------------------------------------------------------

                                  1561809 20-DEC-11
insert into "SCOTT"."TEST"("ID","NAME","STRING") values ('2','b','c');

                                  1561847 20-DEC-11
delete from "SCOTT"."TEST" where "ID" = '1' and "NAME" = 'a' and "STRING" IS NUL
L and ROWID = 'AAAM6hAAEAAAAGIAAA';


结束logminer

SQL> execute dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.

使用logminer字典文件

建立字典文件

SQL> alter system set utl_file_dir='/u01/logminer' scope = spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  314572800 bytes
Fixed Size                  1219160 bytes
Variable Size             113247656 bytes
Database Buffers          197132288 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.

SQL> show parameter utl

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines               string
utl_file_dir                         string      /u01/logminer

SQL> execute dbms_logmnr_d.build('dictionary.ora','/u01/logminer',dbms_logmnr_d.store_in_flat_file); 

PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.add_logfile(logfilename=>'+DG1/devdb/1_19_768042025.dbf',options=>dbms_logmnr.new); 

PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.start_logmnr(dictfilename=>'/u01/logminer/dictionary.ora',options=>dbms_logmnr.ddl_dict_tracking);

PL/SQL procedure successfully completed.

SQL> col username format a10

SQL> col sql_redo format a50 
 
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; 
SQL> select username,scn,timestamp,sql_redo from v$logmnr_contents  where lower(sql_redo) like '%table%';
USERNAME        SCN TIMESTAMP SQL_REDO
-------- ---------- --------- --------------------------------------------------
            1561619 20-DEC-11 create table scott.test(id number, name varchar2(2
                              0));

            1561757 20-DEC-11 alter table scott.test add (string varchar2(20));


结束logminer

SQL> execute dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.