ITPub博客

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

LogMiner日志挖掘

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

一个典型的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博客 ” ,链接:http://blog.itpub.net/30654353/viewspace-2735265/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2015-12-23

  • 博文量
    14
  • 访问量
    5626