LogMiner 示例

LogMiner 示例

 

在做实验之前，检查下suppplemental logging：

 

 

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

SUPPLEME

--------

YES

 

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

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

 

4.1 使用源数据库数据字典（Online Catalog）来分析DML操作

 

1、先进行DML和DDL的操作，以便下面分析。

SQL> conn /as sysdba

已连接。

SQL> show parameter utl;

NAME                                 TYPE        VALUE                         

------------------------------------ ----------- --------

create_stored_outlines               string                                    

utl_file_dir                         string   

SQL> insert into scott.dept  values('80','Dave','AnQing');

已创建 1 行。

SQL> update scott.dept set loc='shang hai' where deptno=70;

已更新 1 行。

SQL> commit;

提交完成。

SQL> delete from scott.dept where deptno=40;

已删除 1 行。

SQL> commit;

提交完成。

SQL> alter table scott.dept add(phone varchar2(32));

表已更改。

SQL> insert into scott.dept values(50,'David','Dai','13888888888');

已创建 1 行。

SQL> commit;

提交完成。

SQL> alter table scott.dept add(address varchar2(300));

表已更改。

 

2、把在线重做日志变成归档日志，这样分析归档日志就可以了

SQL> alter system switch logfile;

系统已更改。

 

3、建立日志分析列表：

 

----添加要分析的日志文件

SQL> execute dbms_logmnr.add_logfile(logfilename=>'D:/oracle/arch/TEST/ARCHIVELOG/2009_08_25/O1_MF_1_32_597FQD7B_.ARC',options=>dbms_logmnr.new);

PL/SQL 过程已成功完成。

---继续填加，用dbms_logmnr.removefile可以删除

SQL> execute dbms_logmnr.add_logfile(logfilename=>'D:/oracle/arch/TEST/ARCHIVELOG/2009_08_25/O1_MF_1_30_597B5P7B_.ARC',options=>dbms_logmnr.addfile);

PL/SQL 过程已成功完成。

 

4、启动LogMiner

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

PL/SQL 过程已成功完成。

 

5、查看日志分析结果：

SQL> col username format a8

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 seg_name='DEPT';

USERNAME        SCN TIMESTAMP           SQL_REDO

-------- ---------- ------------------- -----------------------------------

            1645927 2009-08-25 16:54:56 delete from "SCOTT"."DEPT" where "DEPTNO" = '40' and "DNAME" = 'OPERATIONS' and "LOC" = 'BOSTON' and "PHONE" IS NULL and "ADDRESS" IS                                           NULL and ROWID = 'AAAMfNAAEAAAAAQAAD';

SYS         1645931 2009-08-25 16:54:57 alter table scott.dept add(phone varchar2(32)) ;

SYS         1645992 2009-08-25 16:56:33 alter table scott.dept add(address varchar2(300)) ;

 

6、结束LogMiner

SQL> execute dbms_logmnr.end_logmnr;

PL/SQL 过程已成功完成。

 

 

4.2 摘取LogMiner字典到字典文件分析DDL操作

 

1、进行DDL操作，以便分析

SQL> conn scott/admin

已连接。

SQL> drop table emp;

表已删除。

SQL> drop table dept;

表已删除。

SQL> conn /as sysdba

已连接。

SQL> alter system switch logfile;

系统已更改。

 

2、使用字典文件，请查看数据库是否配置utl_file_dir，这个参数为字典文件的目录。配置该参数后，需要重启数据库

SQL> show user;

USER 为 "SYS"

SQL> show parameter utl;

NAME                                 TYPE        VALUE                         

------------------------------------ ----------- ------------

create_stored_outlines               string                                    

utl_file_dir                         string                          

SQL> alter system set utl_file_dir='D:/oracle/logminer' scope=spfile;

System altered.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

SQL> show parameter utl

NAME                                 TYPE        VALUE                         

------------------------------------ ----------- -----------

create_stored_outlines               string                                    

utl_file_dir                         string      D:/oracle/logminer     

 

3、建立字典文件：

SQL> execute dbms_logmnr_d.build ('dict.ora','D:/oracle/logminer',dbms_logmnr_d.store_in_flat_file);

PL/SQL 过程已成功完成。

 

4、建立日志分析列表：

SQL> execute dbms_logmnr.add_logfile(logfilename=>'D:/oracle/arch/TEST/ARCHIVELOG/2009_08_25/O1_MF_1_32_597FQD7B_.ARC',options=>dbms_logmnr.new);

PL/SQL 过程已成功完成。

 

SQL> execute dbms_logmnr.add_logfile(logfilename=>'D:/oracle/arch/TEST/ARCHIVELOG/2009_08_25/O1_MF_1_30_597B5P7B_.ARC',options=>dbms_logmnr.addfile);

PL/SQL 过程已成功完成。

 

5、启动LogMiner

SQL> execute dbms_logmnr.start_logmnr(dictfilename=>'D:/oracle/logminer/dict.ora',options=>dbms_logmnr.ddl_dict_tracking);

PL/SQL 过程已成功完成。

 

6、查询分析日志结果：

 

SQL> select username,scn,timestamp,sql_redo from v$logmnr_contents  where lower(sql_redo) like '%table%';

USERNAME        SCN TIMESTAMP      SQL_REDO

-------- ---------- -------------- -----------------------------------

            1647538 25-8月 -09     ALTER TABLE "SCOTT"."EMP" RENAME CONSTRAINT "PK_EMP" TO "BIN$f/mFjN+nTmaYjrb17YU80w==$0" ;

            1647550 25-8月 -09     ALTER TABLE "SCOTT"."EMP" RENAME TO "BIN$E5UujHaTR+uItpLtzN0Ddw==$0" ;

            1647553 25-8月 -09     drop table emp AS "BIN$E5UujHaTR+uItpLtzN0Ddw==$0" ;

            1647565 25-8月 -09     ALTER TABLE "SCOTT"."DEPT" RENAME CONSTRAINT "PK_DEPT" TO "BIN$3imFL+/1SqONFCB7LoPcCg==$0" ;

            1647571 25-8月 -09     ALTER TABLE "SCOTT"."DEPT" RENAME TO "BIN$kYKBLvltRb+vYaT6RkaRiA==$0";

            1647574 25-8月 -09     drop table dept AS "BIN$kYKBLvltRb+vYaT6RkaRiA==$0" ;                        

                                              

或者其他的查询：

 

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

USERNAME TIMESTAMP           SQL_REDO                                          

-------- ------------------- --------------------------------------------------

USERNAME        SCN TIMESTAMP      SQL_REDO

-------- ---------- -------------- -----------------------------------

SYS         1647487 25-8月 -09     set transaction read write;

SYS         1647488 25-8月 -09     alter user scott account unlock;

SYS         1647490 25-8月 -09     Unsupported

SYS         1647492 25-8月 -09     commit;

                         

7、结束LogMiner

SQL> execute dbms_logmnr.end_logmnr;

PL/SQL 过程已成功完成。

 

       注意，v$logmnr_contents内容保存了日志的内容，只在当前会话有效，如果想长期保存分析，可以在当前会话用create table tablename as select * from v$logmnr_contents语句来持久保存。