oracle-logminer

 

LogMiner工具实际上是由两个新的PL/SQL内建包（(DBMS_LOGMNR 和 DBMS_ LOGMNR_D）和四个V$动态性能视图（视图是在利用过程DBMS_LOGMNR.START_LOGMNR启动LogMiner时创建）组成。
在使用LogMiner工具分析redo log文件之前，可以使用DBMS_LOGMNR_D 包将数据字典导出为一个文本文件。
该字典文件是可选的，但是如果没有它，LogMiner解释出来的语句中关于数据字典中的部分（如表名、列名等）和数值都将是16进制的形式，我们是无法直接理解的


一、logminer的用途
日志文件中存放着所有进行数据库恢复的数据，记录了针对数据库结构的每一个变化，也就是对数据库操作的所有DML语句。logminer 工具即可以用来分析在线，也可以用来分析离线日志文件，即可以分析本身自己数据库的重作日志文件，也可以用来分析其他数据库的重作日志文件。总的说来，logminer工具的主要用途有：
　1．跟踪数据库的变化：可以离线的跟踪数据库的变化，而不会影响在线系统的性能。
　2．回退数据库的变化：回退特定的变化数据，减少point-in-time recovery的执行。
　3．优化和扩容计划：可通过分析日志文件中的数据以分析数据增长模式。

二、安装logminer
要安装logminer工具，必须首先要运行下面这样两个脚本：
　l. $ORACLE_HOME/rdbms/admin/dbmslm.sql
　2. $ORACLE_HOME/rdbms/admin/dbmslmd.sql
这两个脚本必须均以SYS用户身份运行。其中第一个脚本用来创建DBMS_LOGMNR包，该包用来分析日志文件。第二个脚本用来创建DBMS_LOGMNR_D包，该包用来创建数据字典文件。
 

operation 指的是操作 ，sql_redo 指的是实际操作，sql_undo 指的是用于取消的相反操作。
需要强调一点的是，视图v$logmnr_contents中的分析结果仅在我们运行过程'dbms_logmrn.start_logmnr'这个会话的生命期中存在。这是因为所有的logminer存储都在PGA内存中，所有其他的进程是看不到它的，同时随着进程的结束，分析结果也随之消失。最后，使用过程DBMS_LOGMNR.END_LOGMNR终止日志分析事务，此时PGA内存区域被清除，分析结果也随之不再存在。

Even after you have successfully called DBMS_LOGMNR.START_LOGMNR and selected from
the V$LOGMNR_CONTENTS view, you can call DBMS_LOGMNR.START_LOGMNR again without
ending the current LogMiner session and specify different options and time or SCN
ranges.

成功调用start并查询后，可以再次调用start而不用结束当前logminer会话,并指定不同的选项和时间或scn。

======================

字典为online_catalog的方式

如果要加入新的要分析的归档日志文件的话，需要结束，重新来一遍
另外如果归档日志文件是不连续的，也可以，在select * from v$logmnr_logs;时会看到下面的
Missing log file(s) for thread 1, sequence(s) 95 to 98

一、创建用户并授权
create user log1 identified by 123456;
grant dba to log1;
grant sysdba to log1;//没有这个权限是不行的
select * from v$pwfile_users;

startup mount;
alter database archivelog;
alter database open;
alter database add supplemental log data;

在使用LogMiner读取归档/在线日志需要按照第2章节进行设置，设置完毕后可以对归档和在线日志进行分析。特别是需要开启LogMiner日志补充模式，如果没有开始LogMiner补充模式将无法查看DDL语句，按照测试结果看，只有开始LogMiner日志补充模式后，才能查看DDL语句，在此之前进行DDL将无法进行查看。

这里如果不打开的话，在分析归档日志的时候，就看不到执行操作的machine、os_name、user_name等等，对分析操作排查问题会产生很大困扰。supplemental logging（扩充日志）在通常情况下，redo log 只记录的进行恢复所必需的信息，但是这些信息对于我们使用redo log进行一些其他应用时是不够的，例如在 redo log中使用rowid唯一标识一行而不是通过Primary key，如果我们在另外的数据库分析这些日志并想重新执行某些dml时就可能会有问题，因为不同的数据库其rowid代表的内容是不同的。在这时候就需要一些额外的信息（columns）加入redo log，这就是supplemental logging。


alter database add supplemental log data;
SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

orapwd FILE='/db/oracle/product/10.2.0/db_1/dbs/orapw+SID'  PASSWORD=oracle   ENTRIES=5  FORCE=y
首先，把初始化参数REMOTE_LOGIN_PASSWORDFILE的值改成EXCLUSIVE
如果还是有问题，可能是缺少密码文件，用orapwd创建密码文件

二、登录
以sysdba权限登录
show user;

三、添加要分析的日志
指定要被分析的redo log files list，分在线与归档两种日志
分析在线（联机）日志
select * from v$logfile;
select * from v$log;
select b.member from v$log a left join v$logfile b
on a.group#=b.group# where a.status='CURRENT';

select t1.GROUP#,t1.STATUS,t2.MEMBER,t2.TYPE from v$log t1
inner join v$logfile t2 on t1.GROUP#=t2.GROUP# and t1.STATUS='CURRENT';

execute dbms_logmnr.add_logfile(logfilename=>'F:\APP\ASUS\ORADATA\ORCLDATA\REDO02.LOG',options=>dbms_logmnr.NEW);
execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/fast_recovery_area/FGY1/archivelog/2017_08_22/o1_mf_1_94_dsq5n0jp_.arc',options=>dbms_logmnr.ADDFILE);

exec dbms_logmnr.remove_logfile('/log/log1.log');

 

添加在线日志文件，添加第一个文件用new参数
BEGIN
dbms_logmnr.add_logfile(logfilename=>'/fra/MY/onlinelog/o1_mf_1_dcz3w3n1_.log',options=>dbms_logmnr.NEW);
END;
/

添加在线日志文件，添加第二个文件用addfile参数
BEGIN
dbms_logmnr.add_logfile(logfilename=>'/fra/MY/archivelog/2017_08_10/o1_mf_1_90_drqhh7r3_.arc',options=>dbms_logmnr.ADDFILE);
END;
/

分析归档日志
archive log list;
alter system switch logfile;
select * from v$archived_log;
SELECT NAME FROM V$ARCHIVED_LOG
WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);
execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/fast_recovery_area/FGY1/archivelog/2017_08_24/o1_mf_1_100_dsvv6o42_.arc',options=>dbms_logmnr.NEW);
execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/fast_recovery_area/FGY1/archivelog/2017_08_22/o1_mf_1_94_dsq5n0jp_.arc',options=>dbms_logmnr.ADDFILE);


四、启动logminer并指定要使用的字典
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

五、开始查询
select * from v$logmnr_contents
where table_space like 'KYC%';
select * from V$SQLCOMMAND;
select * from v$logmnr_logs;
select * from V$LOGMNR_PARAMETERS;
select * from V$logmnr_dictionary;

六、结束会话
EXECUTE DBMS_LOGMNR.END_LOGMNR();

========================

字典为redo log的方式

SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN='YES';
SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_END='YES';

EXECUTE DBMS_LOGMNR_D.BUILD(OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/FGY1/onlinelog/o1_mf_2_dso9q3c6_.log',options=>dbms_logmnr.NEW);
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_redo_logs);

BEGIN
DBMS_LOGMNR.END_LOGMNR;
END;
/

 

archive log list;
EXECUTE DBMS_LOGMNR_D.BUILD(OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
select * from v$archived_log;

The process of extracting the dictionary to the redo log file
字典可能被包含在多个redo log files中
SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN='YES';
SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_END='YES';

 

========================

字典为文件的方式，这个不推荐

create directory logm as '/u01/app/oracle/admin/my/dpdump/';
show parameter utl;
alter system set utl_file_dir='/u01/app/oracle/admin/my/dpdump/' scope=both;

EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora', '/oracle/database/', DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);

begin
dbms_logmnr_d.build(dictionary_filename => 'dictionary.ora', dictionary_location =>'/u01/app/oracle/admin/my/dpdump/');
end;
/

EXECUTE dbms_logmnr.start_logmnr(dictfilename=>'/u01/app/oracle/admin/my/dpdump/dictionary.ora');

EXECUTE DBMS_LOGMNR.END_LOGMNR();

========================

http://www.cnblogs.com/shishanyuan/p/3140440.html

Before you begin using LogMiner, it is important to understand how LogMiner works with the LogMiner dictionary file (or files) and redo log files.

Query the V$LOGMNR_CONTENTS view. (You must have the SELECT ANY TRANSACTION privilege to query this view.)
You must have been granted the EXECUTE_CATALOG_ROLE role to use the LogMiner PL/SQL packages and to query the V$LOGMNR_CONTENTS view.

========================

一般线上数据库，一天的归档日志非常多，所以直接查询v$logmnr_contents就比较消耗资源，会很卡，因为执行查询的时候，只是一个个从已经添加到队列的归档日志进行数据录入到v$logmnr_contents的。
比如，曾经分析过一家传统的公司，oa系统，每天所有的归档日志分析下来到v$logmnr_contents表，总数据量是16410242条记录，
使用create table logminer.Z1 as select * from v$logmnr_contents;
这个临时表logminer.Z1占据了54G的磁盘空间。所以为了check方便，需要通过一些检索字段，比如通过sql_redo来过滤表。
create table logminer.Z_20170319 as select * from v$logmnr_contents t
where t.sql_redo like ‘%UC_USER%’ or t.sql_redo like ‘%uc_user%’;
create table l1_Z1 as select * from v$logmnr_contents;
执行select * from v$logmnr_contents;非常慢，因为是会去归档日志里面查询数据出来然后显示，这个时候你在后台的alert log里面会看到如下信息，从一个个归档日志里面去mining数据出来：
less /u01/app/oracle/diag/rdbms/testdb/testdb/trace/alert_testdb.log

 =============================================

The CONTINUOUS_MINE option requires that the database be mounted and that archiving be enabled.
不用手动添加日志文件了
自动根据过滤条件扫描归档的日志
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
EXECUTE DBMS_LOGMNR.START_LOGMNR( -
STARTTIME => '15-Aug-2017 08:30:00', -
ENDTIME => '20-Aug-2017 08:30:00', -
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
DBMS_LOGMNR.CONTINUOUS_MINE);


begin
DBMS_LOGMNR.START_LOGMNR(
STARTTIME => '2017-08-24 08:30:00',
ENDTIME => '2017-08-25 08:30:00',
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE + DBMS_LOGMNR.PRINT_PRETTY_SQL);
end;
/


也可以跟踪日志来看是将归档日志中的记录插入到相应的表中
tail -f /u01/app/oracle/diag/rdbms/my/my/trace/alert_my.log

select count(*) from v$logmnr_contents;//300百万行
select count(*) from v$logmnr_contents where seg_owner='PERSONAL';//10行
把它另存为临时表，但是存储在sysdba权限的sys用户下，而不是test1用户下
create table t2 as select * from v$logmnr_contents where seg_owner='PERSONAL';
select * from tab;
所以加了个personal.t2
create table personal.t2 as select * from v$logmnr_contents where seg_owner='PERSONAL';
这样就可以脱离会话来日志挖掘了

 

转载于:https://www.cnblogs.com/createyuan/p/7337796.html