oracle Logminer 日志挖掘
Logminer
--本机挖掘测试
1，创建测试表
create table a.test1 (NAME varchar2(20), ID number);
insert into a.test1 values('x','1');
insert into a.test1 values('xx','2');
insert into a.test1 values('xxx','3');
insert into a.test1 values('xxxx','4');
insert into a.test1 values('xxxxx','5');
insert into a.test1 values('xxxxxx','6');
commit;
update a.test1 set name = 'xxxx' where id =3;
commit;
delete from a.test1 where id=5;
commit;
truncate table a.test1;
2,切换日志
alter system switch logfile;
alter system switch logfile;
3,检查归档
mysql> select * from (select name from v$archived_log where name like '%archive%' order by SEQUENCE# desc) where rownum <3;
NAME
--------------------------------------------------------------------------------
+ARCH/zdb/archivelog/2020_07_16/thread_1_seq_120916.883.1045906869
+ARCH/zdb/archivelog/2020_07_16/thread_1_seq_120915.408.1045906867
4,添加日志
execute dbms_logmnr.add_logfile(logfilename=>'+ARCH/zdb/archivelog/2020_07_16/thread_1_seq_120916.883.1045906869',options=>dbms_logmnr.new);
execute dbms_logmnr.add_logfile(logfilename=>'+ARCH/zdb/archivelog/2020_07_16/thread_1_seq_120915.408.1045906867',options=>DBMS_LOGMNR.ADDFILE);
5,开始挖掘
execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
6,查看
set linesize 300 pagesize 200
select to_char(timestamp,'yyyy-mm-dd hh24:mm:ss'),
operation,
sql_redo
from v$logmnr_contents
where table_name = 'TEST1';
2020-07-16 09:07:17 DDL
create table a.test1 (NAME varchar2(20), ID number);
2020-07-16 09:07:27 INSERT
insert into "A"."TEST1"("NAME","ID") values ('x','1');
2020-07-16 09:07:27 INSERT
insert into "A"."TEST1"("NAME","ID") values ('xx','2');
2020-07-16 09:07:27 INSERT
insert into "A"."TEST1"("NAME","ID") values ('xxx','3');
2020-07-16 09:07:46 INSERT
insert into "A"."TEST1"("NAME","ID") values ('xxxx','4');
2020-07-16 09:07:12 INSERT
insert into "A"."TEST1"("NAME","ID") values ('xxxxx','5');
2020-07-16 09:07:12 INSERT
insert into "A"."TEST1"("NAME","ID") values ('xxxxxx','6');
2020-07-16 09:07:38 UPDATE
update "A"."TEST1" set "NAME" = 'xxxx' where "NAME" = 'xxx' and "ID" = '3' and ROWID = 'AABft3AAEAAAjs0AAC';
2020-07-16 09:07:46 DELETE
delete from "A"."TEST1" where "NAME" = 'xxxxx' and "ID" = '5' and ROWID = 'AABft3AAEAAAjs0AAE';
2020-07-16 09:07:35 DDL
truncate table a.test1;
10 rows selected.
7,结束挖掘
EXECUTE DBMS_LOGMNR.END_LOGMNR();
--异机挖掘测试
1,本机生成数据字典
09:57:19 SYS@zwdb1(zwdb1)> EXECUTE DBMS_LOGMNR_D.BUILD (OPTIONS => DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
PL/SQL procedure successfully completed.
09:58:43 SYS@zwdb1(zwdb1)> SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN='YES';--开始的日志文件
NAME
-----------------------------------------------------------------------------
+ARCH/zdb/archivelog/2020_07_16/thread_1_seq_120919.835.1045907913
09:59:05 SYS@zwdb1(zwdb1)> SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_END='YES';--结束的日志文件
NAME
------------------------------------------------------------------------------
+ARCH/zdb/archivelog/2020_07_16/thread_1_seq_120920.326.1045907913
2，拷贝数据字典和归档到异机
[grid~]$ asmcmd
ASMCMD> cd +ARCH/zdb/archivelog/2020_07_16
ASMCMD> ls
thread_1_seq_120915.408.1045906867
thread_1_seq_120916.883.1045906869
thread_1_seq_120917.695.1045907911
thread_1_seq_120918.379.1045907911
thread_1_seq_120919.835.1045907913
thread_1_seq_120920.326.1045907913
thread_2_seq_45469.272.1045907901
thread_2_seq_45470.655.1045907903
--注意，此处拷贝到的目录，grid用户要用写的权限
cp +ARCH/zdb/archivelog/2020_07_16/thread_1_seq_120916.883.1045906869 /home/oracle/nfs
cp +ARCH/zdb/archivelog/2020_07_16/thread_1_seq_120915.408.1045906867 /home/oracle/nfs
cp +ARCH/zdb/archivelog/2020_07_16/thread_1_seq_120919.835.1045907913 /home/oracle/nfs
cp +ARCH/zdb/archivelog/2020_07_16/thread_1_seq_120920.326.1045907913 /home/oracle/nfs
ASMCMD> cp +ARCH/zdb/archivelog/2020_07_16/thread_1_seq_120916.883.1045906869 /home/oracle/nfs
copying +ARCH/zdb/archivelog/2020_07_16/thread_1_seq_120916.883.1045906869 -> /home/oracle/nfs/thread_1_seq_120916.883.1045906869
ASMCMD> cp +ARCH/zdb/archivelog/2020_07_16/thread_1_seq_120915.408.1045906867 /home/oracle/nfs
copying +ARCH/zdb/archivelog/2020_07_16/thread_1_seq_120915.408.1045906867 -> /home/oracle/nfs/thread_1_seq_120915.408.1045906867
ASMCMD> cp +ARCH/zdb/archivelog/2020_07_16/thread_1_seq_120919.835.1045907913 /home/oracle/nfs
copying +ARCH/zdb/archivelog/2020_07_16/thread_1_seq_120919.835.1045907913 -> /home/oracle/nfs/thread_1_seq_120919.835.1045907913
ASMCMD> cp +ARCH/zdb/archivelog/2020_07_16/thread_1_seq_120920.326.1045907913 /home/oracle/nfs
copying +ARCH/zdb/archivelog/2020_07_16/thread_1_seq_120920.326.1045907913 -> /home/oracle/nfs/thread_1_seq_120920.326.1045907913
cd /home/oracle/nfs
chown oracle:oinstall thread*
scp /home/oracle/nfs/thread* 12.1.16.23:/home/oracle/lj
/home/oracle/lj/thread_1_seq_120916.883.1045906869
/home/oracle/lj/thread_1_seq_120915.408.1045906867
/home/oracle/lj/thread_1_seq_120919.835.1045907913
/home/oracle/lj/thread_1_seq_120920.326.1045907913
---或者使用 RMAN 进行copy
RMAN> copy archivelog '+ARCH/psdb/archivelog/2020_06_14/thread_2_seq_16087.1656.1043088111' to '/home/oracle/test/thread_2_seq_16087.dbf';
3，添加日志和数据字典
logminer加载日志：
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '第一个文件', OPTIONS => DBMS_LOGMNR.NEW);--数据字典日志文件
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '之后的文件', OPTIONS => DBMS_LOGMNR.ADDFILE);--数据字典日志文件，加载开始到结束的全部日志文件
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '需要挖掘的日志文件', OPTIONS => DBMS_LOGMNR.ADDFILE);--添加需要挖掘的
--示例实操
execute dbms_logmnr.add_logfile(logfilename=>'/home/oracle/lj/thread_1_seq_120916.883.1045906869',options=>dbms_logmnr.new);
execute dbms_logmnr.add_logfile(logfilename=>'/home/oracle/lj/thread_1_seq_120915.408.1045906867',options=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'/home/oracle/lj/thread_1_seq_120919.835.1045907913',options=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'/home/oracle/lj/thread_1_seq_120920.326.1045907913',options=>dbms_logmnr.addfile);
查看添加的日志列表
select filename from V$LOGMNR_LOGS;
4,开始挖掘
execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.DICT_FROM_REDO_LOGS);
5,查询
set linesize 300 pagesize 200
select to_char(timestamp,'yyyy-mm-dd hh24:mm:ss'),
operation,
sql_redo
from v$logmnr_contents
where table_name = 'TEST1';
2020-07-16 09:07:17 DDL
create table a.test1 (NAME varchar2(20), ID number);
2020-07-16 09:07:27 INSERT
insert into "A"."TEST1"("NAME","ID") values ('x','1');
2020-07-16 09:07:27 INSERT
insert into "A"."TEST1"("NAME","ID") values ('xx','2');
2020-07-16 09:07:27 INSERT
insert into "A"."TEST1"("NAME","ID") values ('xxx','3');
2020-07-16 09:07:46 INSERT
insert into "A"."TEST1"("NAME","ID") values ('xxxx','4');
2020-07-16 09:07:12 INSERT
insert into "A"."TEST1"("NAME","ID") values ('xxxxx','5');
2020-07-16 09:07:12 INSERT
insert into "A"."TEST1"("NAME","ID") values ('xxxxxx','6');
2020-07-16 09:07:38 UPDATE
update "A"."TEST1" set "NAME" = 'xxxx' where "NAME" = 'xxx' and "ID" = '3' and ROWID = 'AABft3AAEAAAjs0AAC';
2020-07-16 09:07:46 DELETE
delete from "A"."TEST1" where "NAME" = 'xxxxx' and "ID" = '5' and ROWID = 'AABft3AAEAAAjs0AAE';
2020-07-16 09:07:35 DDL
truncate table a.test1;
10 rows selected.
---和同机操作结果一致
--也可以把挖取结果保存到指定表格中： create table logtab as select * from v$logmnr_contents;
6,结束挖掘
EXECUTE DBMS_LOGMNR.END_LOGMNR();
挖掘查询结果：
SELECT scn,seg_owner,operation,timestamp,status,sql_redo,seg_name FROM v$logmnr_contents
set lines 230
col TIMESTAMP for a23
col OPERATION for a10
col USERNAME for a10
col OS_USERNAME for a10
col MACHINE_NAME for a20
col STATUS for a15
col SQL_REDO for a60
select TIMESTAMP,OPERATION,USERNAME,OS_USERNAME,MACHINE_NAME,STATUS,SQL_REDO from v$logmnr_contents where table_name='ABC';
---附加
Logminer依赖于2个包：DBMS_LOGMNR和DBMS_LOGMNR_D，Oracle 11g默认已安装
Logminer 基本使用步骤
<1>. Specify a LogMiner dictionary. 指定Logminer字典
<2>. Specify a list of redo log files for analysis. 指定需要挖掘的redo或者archivelog日志文件
<3>. Start LogMiner. 开始日志挖掘
<4>. Request the redo data of interest. 查询V$LOGMNR_CONTENTS获取挖掘的结果
<5>. End the LogMiner session. 结束日志挖掘
SELECT NAME FROM V$ARCHIVED_LOGWHERE FIRST_TIME between to_date('2018-07-06 08:20:00','yyyy-mm-dd hh24:mi:ss')
and to_date('2018-07-06 09:40:00','yyyy-mm-dd hh24:mi:ss') order by 1;
3.指定Logminer字典 (三选一)
Oracle 11g 指定Logminer字典有三种方法
1.Using the Online Catalog 使用在线目录
2.Extracting a LogMiner Dictionary to the Redo Log Files 抽取字典到redo日志文件中
3.Extracting the LogMiner Dictionary to a Flat File 抽取字典到平面文件中（需要设置UTL_FILE_DIR参数，重启数据库，不推荐）
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
或者
EXECUTE DBMS_LOGMNR_D.BUILD( OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
或者
EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora', '/oracle/database/', DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);
把挖取结果保存到指定表格中： create table logtab as select * from v$logmnr_contents;
--
dbms_logmnr_d.build 创建一个数据字典文件
Dbms_logmnr.add_logfile 在类表中增加日志文件以供分析
Dbms_logmnr.start_logmnr 使用一个可选的字典文件和前面确定要分析日志文件来启动LogMiner
Dbms_logmnr.end_logmnr 停止LogMiner分析
V$logmnr_dictionary 显示用来决定对象ID名称的字典文件的信息
V$logmnr_logs 在LogMiner启动时显示分析的日志列表
V$logmnr_contents LogMiner启动后，可以使用该视图在SQL提示符下输入SQL语句来查询重做日志的内容