logmnr使用
logminer 工具的使用
Oracle LogMiner 是Oracle公司从产品8i以后提供的一个实际非常有用的分析工具，使用该工具可以轻松获得Oracle 重作日志文件（归档日志文件）中的具体内容，特别是，该工具可以分析出所有对于数据库操作的DML（insert、update、delete等）语句，另外还可分析得到一些必要的回滚SQL语句。该工具特别适用于调试、审计或者回退某个特定的事务。
LogMiner分析工具实际上是由一组PL/SQL包和一些动态视图（Oracle8i内置包的一部分）组成，它作为Oracle数据库的一部分来发布，是8i产品提供的一个完全免费的工具。但该工具和其他Oracle内建工具相比使用起来显得有些复杂，主要原因是该工具没有提供任何的图形用户界面（GUI）。本文将详细介绍如何安装以及使用该工具。
一、LogMiner的用途
日志文件中存放着所有进行数据库恢复的数据，记录了针对数据库结构的每一个变化，也就是对数据库操作的所有DML语句。
在Oracle
8i之前，Oracle没有提供任何协助数据库管理员来读取和解释重作日志文件内容的工具。系统出现问题，对于一个普通的数据管理员来讲，唯一可以作的工作就是将所有的log文件打包，然后发给Oracle公司的技术支持，然后静静地等待Oracle
公司技术支持给我们最后的答案。然而从8i以后，Oracle提供了这样一个强有力的工具-LogMiner。
LogMiner 工具即可以用来分析在线，也可以用来分析离线日志文件，即可以分析本身自己数据库的重作日志文件，也可以用来分析其他数据库的重作日志文件。
总的说来，LogMiner工具的主要用途有：
1． 跟踪数据库的变化：可以离线的跟踪数据库的变化，而不会影响在线系统的性能。
2． 回退数据库的变化：回退特定的变化数据，减少point-in-time recovery的执行。
3． 优化和扩容计划：可通过分析日志文件中的数据以分析数据增长模式。
二、安装LogMiner
如果未安装过提示无法使用这个dbms包，则可以用SYSDBA登陆，然后依次执行：
@$Oracle_HOME\rdbms\admin\dbmslm.sql;
@$ORACLE_HOME\rdbms\admin\dbmslmd.sql;
第一个脚本用来创建DBMS_LOGMNR包，该包用来分析日志文件。
第二个脚本用来创建DBMS_LOGMNR_D包，该包用来创建数据字典文件。
三、开始
3.1 环境介绍：
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
3.2 设置参数utl_file_dir
[oracle@Oracle11g ~]$ mkdir -p /u01/logmnr_more
SYS @ prod
> show parameter utl_file_dir ;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir string
SYS @ prod > alter system set utl_file_dir='/u01/logmnr_more' scope=spfile;
System altered.
SYS
@ prod >startup
force
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 79693200 bytes
Database Buffers 230686720 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SYS @ prod >show parameter utl_file_dir ;
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
utl_file_dir string /u01/logmnr_more
——建立数据字典文件dict.ora
SYS @ prod >execute dbms_logmnr_d.build('dict.ora','/u01/logmnr_more',dbms_logmnr_d.store_in_flat_file);
PL/SQL procedure successfully completed.
3.3 对redo log 进行挖掘，找出在某个时间点所作的DDL 或DML 操作（包括：时间点、datablock scn 、sql语句)
1) 对DML 分析
SQL> delete from owner.emp;
3 rows deleted.
SQL> commit;
Commit complete.
SQL> insert into owner.emp select * from scot.emp;
SQL> commit;
Commit complete.
SYS @ prod > select * from owner.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
——查看当前日志组
SYS @ prod > select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 7 52428800 512 1 YES ACTIVE 1038204 16-SEP-14 1049618 05-NOV-17
2 1 8 52428800 512 1 NO CURRENT 1049618 05-NOV-17 2.8147E+14
3 1 6 52428800 512 1 YES INACTIVE 1016929 16-SEP-14 1038204 16-SEP-14
SQL> alter system archive log current; ——使当前日志组归档
System altered.
SYS @ prod > select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 7 52428800 512 1 YES ACTIVE 1038204 16-SEP-14 1049618 05-NOV-17
2 1 8 52428800 512 1 YES ACTIVE 1049618 05-NOV-17 1050691 05-NOV-17
3 1 9 52428800 512 1 NO CURRENT 1050691 05-NOV-17 2.8147E+14
2) 启用logmnr
——添加database补充日志
SYS @ prod > alter database add supplemental log data; ——不添加后面会出错
Database altered.
——查询日志（归档日志和当前日志)
SYS @ prod > select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 7 52428800 512 1 YES ACTIVE 1038204 16-SEP-14 1049618 05-NOV-17
2 1 8 52428800 512 1 YES ACTIVE 1049618 05-NOV-17 1050691 05-NOV-17
3 1 9 52428800 512 1 NO CURRENT 1050691 05-NOV-17 2.8147E+14
SQL> select member from v$logfile;——当前日志
MEMBER
--------------------------------------------------
/u01/app/oracle/oradata/prod/redo01.log
/u01/app/oracle/oradata/prod/redo02.log
/u01/app/oracle/oradata/prod/redo03.log
SQL> select name from v$archived_log;——查看归档日志信息
NAME
-------------------
/u01/arch_dir/prod/1_7_858439997.dbf
/u01/arch_dir/prod/1_8_858439997.dbf
——添加日志，分析【添加的日志应该是切换前的日志，归档日志是最后一个日志】
SYS @ prod > execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/prod/redo02.log',options=>dbms_logmnr.new);
PL/SQL procedure successfully completed.
SYS @ prod > execute dbms_logmnr.add_logfile(logfilename=>'/u01/arch_dir/prod/1_7_858439997.dbf',options=>dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
——执行logmnr 分析
SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
——查询分析结果
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> col username for a10
SQL> col timestamp for a10
SQL> col sql_redo for a100
SQL> select username,scn,to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') time1 ,sql_redo from v$logmnr_contents where username ='OWNER' order by time1 desc
USERNAME SCN TIMESTAMP SQL_REDO
--------------- ---------- ------------------- --------------------------------------------------
OWNER 1043876 2017-11-04 22:03:40
insert into "OWNER"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7499','ALLEN','SALESMAN','7698',TO_DATE('1981-02-20 00:00:00', 'yyyy-mm-dd hh24:mi:ss'),'1600','300','30');
OWNER 1043876 2017-11-04 22:03:40
insert into "OWNER"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7521','WARD','SALESMAN','7698',TO_DATE('1981-02-22 00:00:00', 'yyyy-mm-dd hh24:mi:ss'),'1250','500','30');
OWNER 1043876 2017-11-04 22:03:40
insert into "OWNER"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7566','JONES','MANAGER','7839',TO_DATE('1981-04-02 00:00:00', 'yyyy-mm-dd hh24:mi:ss'),'2975',NULL,'20');
OWNER 1043876 2017-11-04 22:03:40
insert into "OWNER"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7654','MARTIN','SALESMAN','7698',TO_DATE('1981-09-28 00:00:00', 'yyyy-mm-dd hh24:mi:ss'),'1250','1400','30');
——结束日志分析
SQL> execute dbms_logmnr.end_logmnr;
PL/SQL procedure successfully completed.
2) 对DDL 操作分析
SCOTT @ prod > drop table tb01;
Table dropped.
SCOTT @ prod > create table tb01(id int) tablespace users;
Table created.
SCOTT @ prod > insert into tb01 values(1);
1 row created.
SCOTT @ prod > commit;
Commit complete.
——设置logmnr 参数，存放数据字典文件
——查看日志信息
SQL> select name,sequence# from v$archived_log;
/u01/app/oracle/flash_recovery_area/PROD/archivelog/2013_03_01/o1_mf_1_44_8lz8xwqz_.arc
44
/u01/app/oracle/flash_recovery_area/PROD/archivelog/2013_03_01/o1_mf_1_45_8lz9fqxg_.arc
45
/u01/app/oracle/flash_recovery_area/PROD/archivelog/2013_03_01/o1_mf_1_46_8lzb48og_.arc
46
SYS @ prod > select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 46 52428800 1 YES INACTIVE 873198 01-MAR-13
2 1 47 52428800 1 NO CURRENT 893931 01-MAR-13
3 1 45 52428800 1 YES INACTIVE 872875 01-MAR-13
SYS @ prod > select group#,member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
1 /u01/app/oracle/oradata/prod/redo01.log
2 /u01/app/oracle/oradata/prod/redo02.log
3 /u01/app/oracle/oradata/prod/redo03.log
——添加日志分析
SYS @ prod > execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/prod/redo02.log',options=>dbms_logmnr.new);
PL/SQL procedure successfully completed.
SYS @ prod > execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/flash_recovery_area/PROD/archivelog/2013_03_01/o1_mf_1_46_8lzb48og_.arc',options=>dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
——执行分析
SYS @ prod >execute dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logmnr/dict.ora',options=>dbms_logmnr.ddl_dict_tracking);
PL/SQL procedure successfully completed.
——查看分析结果
SYS @ prod >alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SYS @ prod >select username,scn,timestamp,sql_redo from v$logmnr_contents where username='SCOTT' and lower(sql_redo) like '%table%';
USERNAME SCN TIMESTAMP SQL_REDO
---------- ---------- ------------------- --------------------------------------------------
SCOTT 851229 2012-03-23 15:45:22 drop table tb01 purge;
SCOTT 851264 2012-03-23 15:45:45 create table tb01(id int) tablespace test;
SQL> execute dbms_logmnr.end_logmnr;
PL/SQL procedure successfully completed.
***********************************以上接案例2*****************************
