ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > LogMiner日志挖掘

LogMiner日志挖掘

原创 Oracle 作者：tiny_cion 时间：2020-11-19 15:05:03 1 删除 编辑

一个典型的LogMiner的操作包含如下步骤：

1、进行初始化设置：开启附加日志，设置LogMiner的表空间，设置UTL_FILE_DIR静态参数的值

2、提取一个字典：将字典文件直接使用Online Catalog（看一），提取为Flat File（看二）或Redo日志（看三）

3、指定需要分析的Redo日志文件：利用DBMS_LOGMNR.ADD_LOGFILE来添加日志；

4、开始LogMiner：执行DBMS_LOGMNR.START_LOGMNR来启动LogMiner；

5、查询V$LOGMNR_CONTENTS视图；

6、结束LogMiner：通过执行EXECUTE DBMS_LOGMNR.END_LOGMNR来结束分析。

默认情况下，Oracle 10g以上已经安装了LogMiner工具。若是没有安装，则可以运行下面两个脚本：

$ORACLE_HOME/rdbms/admin/dbmslm.sql

$ORACLE_HOME/rdbms/admin/dbmslmd.sql

一、没开归档的情况下，直接使用online catalog LogMiner

1.开启补充日志
SQL> alter database add supplemental log data;   

2.查看日志组
SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/xdb/redo01.log
/u01/app/oracle/oradata/xdb/redo02.log
/u01/app/oracle/oradata/xdb/redo03.log

3.LogMiner日志组，注意第一个要写new，后面的写addfile
SQL> execute dbms_logmnr.add_logfile('/u01/app/oracle/oradata/xdb/redo01.log',dbms_logmnr.new);

PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.add_logfile('/u01/app/oracle/oradata/xdb/redo02.log',dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.add_logfile('/u01/app/oracle/oradata/xdb/redo03.log',dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

开始挖掘，使用online_catalog
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog+dbms_logmnr.committed_data_only);

PL/SQL procedure successfully completed.

When you specify the COMMITTED_DATA_ONLY option, LogMiner groups together all DML operations that belong to the same transaction. Transactions are

returned in the order in which they were committed.

4.验证如下

SQL> select sql_redo,sql_undo from v$logmnr_contents where table_name='T1';

SQL_REDO
--------------------------------------------------------------------------------
SQL_UNDO
--------------------------------------------------------------------------------
ALTER TABLE "T1"."T1" RENAME TO "BIN$s9/jzkW9CjvgUzwFqMAF1Q==$0" ;


drop table t1 AS "BIN$s9/jzkW9CjvgUzwFqMAF1Q==$0" ;


select sql_redo,sql_undo from v$logmnr_contents where table_name='T1' and OPERATION='DROP';


二、开启归档的情况下，提取字典为Flat File

1.开启补全日志
SQL> alter database add supplemental log data;

2.找出需要挖掘的归档日志文件路径
SQL>select name from v$ARCHIVED_LOG WHERE FIRST_TIME BETWEEN TO_DATE('2020-11-12 14:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2020-11-12

14:40:00','yyyy-mm-dd hh24:mi:ss');
/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_84_1056076841.dbf
/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_85_1056076841.dbf
/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_86_1056076841.dbf
/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_87_1056076841.dbf
/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_88_1056076841.dbf
/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_89_1056076841.dbf

3.将字典文件提取为一个Flat File（平面文件）
SQL> alter system set utl_file_dir='/home/oracle' scope=spfile;
SQL> EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora','/home/oracle',DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);

PL/SQL procedure successfully completed.

4.添加归档日志
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_84_1056076841.dbf',DBMS_LOGMNR.NEW);

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_85_1056076841.dbf',DBMS_LOGMNR.ADDFILE);

PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_86_1056076841.dbf',DBMS_LOGMNR.ADDFILE);

PL/SQL procedure successfully completed.
SQL>  EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_88_1056076841.dbf',DBMS_LOGMNR.ADDFILE);

PL/SQL procedure successfully completed.

SQL>  EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_88_1056076841.dbf',DBMS_LOGMNR.ADDFILE);
BEGIN DBMS_LOGMNR.ADD_LOGFILE('/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_88_1056076841.dbf',DBMS_LOGMNR.ADDFILE); END;

*
ERROR at line 1:
ORA-01289: cannot add duplicate logfile
/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_88_1056076841.dbf
ORA-06512: at "SYS.DBMS_LOGMNR", line 68
ORA-06512: at line 1
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_87_1056076841.dbf',DBMS_LOGMNR.ADDFILE);

PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_89_1056076841.dbf',DBMS_LOGMNR.ADDFILE);

5.查看添加的日志列表
SQL> SELECT FILENAME FROM V$LOGMNR_LOGS;

FILENAME
--------------------------------------------------------------------------------
/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_84_1056076841.dbf
/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_85_1056076841.dbf
/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_86_1056076841.dbf
/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_87_1056076841.dbf
/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_88_1056076841.dbf
/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_89_1056076841.dbf

6.开始挖掘
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(DICTFILENAME=>'/home/oracle/dictionary.ora');

PL/SQL procedure successfully completed.

7.查看挖掘并保存所有结果到指定表中

SQL>SELECT (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID,
   USERNAME, SQL_REDO FROM V$LOGMNR_CONTENTS WHERE USERNAME != 'SYS'
   AND SEG_OWNER IS NULL OR SEG_OWNER NOT IN ('SYS', 'SYSTEM');

SQL> create table logminer_tab as select * from v$logmnr_contents;

8.结束LogMiner
SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR;

三、抽取字典到redo日志文件中

1.开启补全日志
SQL> alter database add supplemental log data;

2.找出需要挖掘的归档日志文件路径

SQL>select name from v$ARCHIVED_LOG WHERE FIRST_TIME BETWEEN TO_DATE('2020-11-12 14:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2020-11-12

14:40:00','yyyy-mm-dd hh24:mi:ss');
/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_84_1056076841.dbf
/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_85_1056076841.dbf
/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_86_1056076841.dbf
/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_87_1056076841.dbf
/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_88_1056076841.dbf
/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_89_1056076841.dbf

3.抽取字典到redo中

SQL>EXECUTE DBMS_LOGMNR_D.BUILD( OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
PL/SQL procedure successfully completed.

4.LogMiner添加归档日志文件

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_85_1056076841.dbf',DBMS_LOGMNR.ADDFILE);

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_86_1056076841.dbf',DBMS_LOGMNR.ADDFILE);

PL/SQL procedure successfully completed.

5.查看添加的日志列表
SQL> select filename from v$logmnr_logs;

6.开始挖掘

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

PL/SQL procedure successfully completed.

7.获取挖掘结果

SELECT (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID,
   USERNAME, SQL_REDO FROM V$LOGMNR_CONTENTS WHERE USERNAME != 'SYS'
   AND SEG_OWNER IS NULL OR SEG_OWNER NOT IN ('SYS', 'SYSTEM');

SQL> create table logminer_tab as select * from v$logmnr_contents;

8.结束LogMiner

SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR;

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30654353/viewspace-2735265/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 监听报错处理小记
下一篇： spm执行计划的绑定
请登录后发表评论 登录
全部评论
tiny_cion

注册时间：2015-12-23

  • 博文量
    32
  • 访问量
    37530

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593