| Log Miner简介
Log Miner是Oracle自Oracle 8i以后推出的一个可以分析数据库redo log和archivelog内容的工具，可以通过日志分析所有对数据库的DDL和DML操作，也可以分析出操作的时间与操作时的SCN和进行操作的机器，对于DML操作还可以查询出还原操作的sql。
| Log Miner组成
源数据库 产生LogMiner分析的所有重做日志文件的数据库
挖掘数据库 是执行LogMiner分析时使用的数据库。
LogMiner数据字典 是LogMiner使用字典将内部对象标识符和数据类型转换为可读数据。如果没有字典，Log Miner分析的结果会显示为二进制数据。
| Log Miner数据字典选项
当LogMiner分析重做数据时，需要一个数据字典将日志的对象ID转换为可读数据。LogMiner提供了三个使用数据字典的方式。
1、使用在线目录( Online Catalog)
使用catalog的数据字典，必须在源数据库执行。启动命令为：
SQL> execute dbms_logmnr.start_logmnr (
options
=>
dbms_logmnr.dict_from_online_catalog);
2、将LogMiner字典提取到archive log。启动命令为：
SQL> execute dbms_logmnr_d.build(
options
=>
dbms_logmnr_d.store_in_redo_logs);
使用这种操作的
3、将LogMiner字典提取到操作系统文件。启动命令为：
SQL>
execute dbms_logmnr_d.build (
'directory_name'
，
'/xxx/xxx/'
，dbms_logmnr_d.store_in_flat_file);
使用这种方式的话，需要设置utl_file_dir参数，该参数需要重启才能生效。
这个工具使用起来并不复杂。由于将Log Miner数据字典提取到操作系统文件在未设置参数的情况下需要重启数据库，使用场景比较狭隘，所以以下测试场景为使用Online catalog数据字典模式和将字典提取到redo log。
| 测试场景
1、确认数据库开启了补充日志
sys@RAC11G>
select
SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEME
--------
YES
--如果返回结果为
no
，通过以下命令开启
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
注意： 在使用Log Miner分析的日志文件之前，必须启用补充日志。 启用补充日志时，会在重做日志流中记录其他信息。如果不开启，LogMiner的挖掘的一些信息无法正常显示。
2、创建测试表，并做一些DML与DDL操作
sys
@
RAC11G
> create table test1 (NAME varchar2(
20
), ID number);
Table
created
.
sys
@
RAC11G
>
insert
into test1 values(
'x'
,
'1'
);
1
row
created
.
sys
@
RAC11G
>
insert
into test1 values(
'xx'
,
'2'
);
1
row
created
.
sys
@
RAC11G
>
insert
into test1 values(
'xxx'
,
'3'
);
1
row
created
.
sys
@
RAC11G
>
commit
;
Commit
complete
.
sys
@
RAC11G
>
update
test1 set name =
'xxxx'
where id =
3
;
1
row
updated
.
sys
@
RAC11G
>
commit
;
Commit
complete
.
sys
@
RAC11G
>
truncate
table test1;
Table
truncated
.
3、切换归档日志
sys@RAC11G>alter system
switch
logfile;
System altered.
sys@RAC11G>alter system
switch
logfile;
System altered.
--然后查看最后生成的归档日志
sys@RAC11G>select *
from
(select name
from
v$archived_log where name like
'%archive%'
order
by
SEQUENCE
# desc ) where rownum <3;
NAME
---------------------------------------------------------------------------------------------------------------------------------------
+DATADG/rac11g/archivelog/
2018
_10_14/thread_1_seq_293
.847.989533723
+DATADG/rac11g/archivelog/
2018
_10_14/thread_1_seq_292
.846.989533631
4、Log Miner添加需要分析的归档日志
--添加日志
sys@RAC11G> execute dbms_logmnr.add_logfile(logfilename=>
'+DATADG/rac11g/archivelog/2018_10_14/thread_1_seq_293.847.989533723'
,options=>dbms_logmnr.new);
PL/SQL
procedure
successfully
completed
.
--添加多个日志，使用
DBMS_LOGMNR
.
ADDFILE
选项
sys
@
RAC11G
>
execute
dbms_logmnr
.
add_logfile
(logfilename=>
'+DATADG/rac11g/archivelog/2018_10_14/thread_1_seq_292.846.989533631'
,options=>DBMS_LOGMNR.ADDFILE)
;
PL/SQL
procedure
successfully
completed
.
5、启动Log Miner
sys@RAC11G>execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL
procedure
successfully
completed
.
6、通过视图v$logmnr_contents进行分析结果查询
v$logmnr_contents只有在开启了Log Miner后才可以进行查询
select
to_char(
timestamp
,
'yyyy-mm-dd hh24:mm:ss'
),
operation,
username,
SESSION_INFO,
sql_redo
from
v$logmnr_contents
where
table_name =
'TEST1'
;
TO_CHAR(TIMESTAMP,' OPERATION USERNAME SESSION_INFO SQL_REDO
------------------- -------------------------------- --------------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
2018-10-14 22:10:50 DDL SYS login_username=SYS client_info= OS_username=oracle Machine_name=11gnode1 OS
create
table
test1 (
NAME
varchar2(
20
),
ID
number
);
_terminal=pts/5 OS_process_id=17273 OS_program_name=sqlplus@11gnode1 (TNS V
1-V3)
2018-10-14 22:10:29
INSERT
SYS
login_username=
SYS
client_info= OS_username=
oracle
Machine_name=
11
gnode1 OS
insert
into
"SYS"
.
"TEST1"
(
"NAME"
,
"ID"
)
values
(
'x'
,
'1'
);
_terminal=pts/5 OS_process_id=17273 OS_program_name=sqlplus@11gnode1 (TNS V
1-V3)
2018-10-14 22:10:33
INSERT
SYS
login_username=
SYS
client_info= OS_username=
oracle
Machine_name=
11
gnode1 OS
insert
into
"SYS"
.
"TEST1"
(
"NAME"
,
"ID"
)
values
(
'xx'
,
'2'
);
_terminal=pts/5 OS_process_id=17273 OS_program_name=sqlplus@11gnode1 (TNS V
1-V3)
2018-10-14 22:10:37
INSERT
SYS
login_username=
SYS
client_info= OS_username=
oracle
Machine_name=
11
gnode1 OS
insert
into
"SYS"
.
"TEST1"
(
"NAME"
,
"ID"
)
values
(
'xxx'
,
'3'
);
_terminal=pts/5 OS_process_id=17273 OS_program_name=sqlplus@11gnode1 (TNS V
1-V3)
2018-10-14 22:10:51
UPDATE
SYS
login_username=
SYS
client_info= OS_username=
oracle
Machine_name=
11
gnode1 OS
update
"SYS"
.
"TEST1"
set
"NAME"
=
'xxxx'
where
"NAME"
=
'xxx'
and
ROWID
=
'
_terminal=pts/5 OS_process_id=17273 OS_program_name=sqlplus@11gnode1 (TNS V AAAE6eAABAAAKHBAAC'
;
1-V3)
2018-10-14 22:10:12 DDL SYS login_username=SYS client_info= OS_username=oracle Machine_name=11gnode1 OS
truncate
table
test1;
_terminal=pts/5 OS_process_id=17273 OS_program_name=sqlplus@11gnode1 (TNS V
1-V3)
.
7、关闭Log Miner
EXECUTE
DBMS_LOGMNR.END_LOGMNR（）;
注意： Log Miner的数据只存在PGA中，如果查询的会话断开连接，Log Miner也会随之关闭。
8、将数据字典提取到redo log
sys@RAC11G>EXECUTE DBMS_LOGMNR_D.BUILD (OPTIONS => DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
PL/SQL
procedure
successfully
completed
.
根据字典的大小，它可能包含在多个归档文件中。如果已归档相关的重做日志文件，则可以找出包含提取的字典的开头和结尾的归档日志。可以查询
V
$
ARCHIVED_LOG
视图
sys
@
RAC11G
>
SELECT
NAME
FROM
V
$
ARCHIVED_LOG
WHERE
DICTIONARY_BEGIN
='
YES
';
NAME
---------------------------------------------------------------------------------------------------------------------------------------
+DATADG/rac11g/archivelog/
2018
_10_15/thread_1_seq_308.
869.989570647
sys@RAC11G>SELECT
NAME
FROM V$ARCHIVED_LOG WHERE DICTIONARY_END=
'YES'
;
NAME
---------------------------------------------------------------------------------------------------------------------------------------
+DATADG/rac11g/archivelog/
2018
_10_15/thread_1_seq_309.
871.989570649
9、添加包含数据字典的归档日志以及需要分析的归档日志
sys@RAC11G>execute dbms_logmnr.add_logfile(logfilename=>
'+DATADG/rac11g/archivelog/2018_10_15/thread_1_seq_308.869.989570647'
,options=>dbms_logmnr.new);
PL/SQL
procedure
successfully
completed
.
sys
@
RAC11G
>
execute
dbms_logmnr
.
add_logfile
(logfilename=>
'+DATADG/rac11g/archivelog/2018_10_15/thread_1_seq_309.871.989570649'
,options=>dbms_logmnr.addfile)
;
PL/SQL
procedure
successfully
completed
.
sys
@
RAC11G
>
execute
dbms_logmnr
.
add_logfile
(logfilename=>
'+DATADG/rac11g/archivelog/2018_10_14/thread_1_seq_293.847.989533723'
,options=>dbms_logmnr.addfile)
;
PL/SQL
procedure
successfully
completed
.
sys
@
RAC11G
>
execute
dbms_logmnr
.
add_logfile
(logfilename=>
'+DATADG/rac11g/archivelog/2018_10_14/thread_1_seq_292.846.989533631'
,options=>dbms_logmnr.addfile)
;
PL/SQL
procedure
successfully
completed
.
10、启动Log Miner
sys@RAC11G> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.DICT_FROM_REDO_LOGS);
PL/SQL
procedure
successfully
completed
.
11、通过视图v$logmnr_contents进行分析结果查询
select
to_char(
timestamp
,
'yyyy-mm-dd hh24:mm:ss'
),
operation,
username,
SESSION_INFO,
sql_redo
from
v$logmnr_contents
7
where
table_name =
'TEST1'
;
TO_CHAR(TIMESTAMP,' OPERATION USERNAME
------------------- -------------------------------- ------------------------------
SESSION_INFO
---------------------------------------------------------------------------------------------------------------------------------------
SQL_REDO
---------------------------------------------------------------------------------------------------------------------------------------
2018-10-14 22:10:50 DDL SYS
login_username=SYS client_info= OS_username=oracle Machine_name=11gnode1 OS_terminal=pts/5 OS_process_id=17273 OS_program_name=sqlplus@
11gnode1 (TNS V1-V3)
create
table
test1 (
NAME
varchar2(
20
),
ID
number
);
2018-10-14 22:10:29
INSERT
SYS
login_username=
SYS
client_info= OS_username=
oracle
Machine_name=
11
gnode1 OS_terminal=pts/
5
OS_process_id=
17273
OS_program_name=sqlplus@
11
gnode1 (TNS V1-V3)
insert
into
"SYS"
.
"TEST1"
(
"COL 1"
,
"COL 2"
)
values
(HEXTORAW(
'78'
),HEXTORAW(
'c102'
));
2018-10-14 22:10:33
INSERT
SYS
login_username=
SYS
client_info= OS_username=
oracle
Machine_name=
11
gnode1 OS_terminal=pts/
5
OS_process_id=
17273
OS_program_name=sqlplus@
11
gnode1 (TNS V1-V3)
insert
into
"SYS"
.
"TEST1"
(
"COL 1"
,
"COL 2"
)
values
(HEXTORAW(
'7878'
),HEXTORAW(
'c103'
));
2018-10-14 22:10:37
INSERT
SYS
login_username=
SYS
client_info= OS_username=
oracle
Machine_name=
11
gnode1 OS_terminal=pts/
5
OS_process_id=
17273
OS_program_name=sqlplus@
11
gnode1 (TNS V1-V3)
insert
into
"SYS"
.
"TEST1"
(
"COL 1"
,
"COL 2"
)
values
(HEXTORAW(
'787878'
),HEXTORAW(
'c104'
));
2018-10-14 22:10:51
UPDATE
SYS
login_username=
SYS
client_info= OS_username=
oracle
Machine_name=
11
gnode1 OS_terminal=pts/
5
OS_process_id=
17273
OS_program_name=sqlplus@
11
gnode1 (TNS V1-V3)
update
"SYS"
.
"TEST1"
set
"COL 1"
= HEXTORAW(
'78787878'
)
where
"COL 1"
= HEXTORAW(
'787878'
)
and
ROWID
=
'AAAE6eAABAAAKHBAAC'
;
2018-10-14 22:10:12 DDL SYS
login_username=SYS client_info= OS_username=oracle Machine_name=11gnode1 OS_terminal=pts/5 OS_process_id=17273 OS_program_name=sqlplus@
11gnode1 (TNS V1-V3)
truncate
table
test1;
| Log Miner的一些限制
当然，这个工具也有一定的限制
源数据库与挖掘数据库
源数据库和挖掘数据库都必须在同一操作系统平台上运行。
挖掘数据库可以与源数据库相同或完全独立。
挖掘数据库必须运行与源数据库相同的版本或更高版本的Oracle数据库软件。
挖掘数据库必须使用源数据库使用的相同字符集（或字符集的超集）。
Log Miner 数据字典
数据字典必须由源数据库生成。
归档日志
每次分析的所有归档日志必须由相同的源数据生成。
必须与同一数据库关联RESETLOGS SCN。
必须来自8i或更高版本的Oracle数据库。
这些限制都不算苛刻，如果灵活使用这个工具的话，可以使归档日志利用最大化，在关键时间有非常大的作用。
| 作者简介
陈康，沃趣科技数据库技术专家
主要参与公司产品实施、测试、维护以及优化。
