Oracle 11g logminer解析redo日志

下面的示例是分析在线redo日志，分析归档redo日志过程也是如此。toad 里面也集成了logminer的功能。

sql> SELEct * from v$version;
BAnnER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
pl/sql Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

1.用sys账号建立一个logminer的用户，授予sysdba的权限

create user LOGmineR

IDentifIEdby "LOGmineR"

defaulttablespace LCAM_PUB_TBS--表空间依据当前数据库的情况而定

temporarytablespace TEMP

profileDEFAulT;

grant connect to LOGmineR;

grant resource to LOGmineR;

grant sysdba to LOGmineR;

2.用logminer用户执行两个Oracle脚本，生成一些logminer的package,确保数据库打开辅助日志

sql> @F:\app\administrator\product\11.2.0\dbhome_1\RDBMS\admin\dbmslm.sql

sql> @F:\app\administrator\product\11.2.0\dbhome_1\RDBMS\admin\dbmslmd.sql

sql> SELEct SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEME

--------

NO

sql> alter database add supplemental log data;

sql> SELEct SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEME

--------

YES

3.用logminer用户设置utl_file_dir，并且确定有这个目录存在，有必要建一下文件夹LOGMNR

alter system set utl_file_dir='/Oracle/oradata/oradb11/LOGMNR' scope=spfile;

4. 查看归档redo组

Status为current为当前启用的在线日志，为了试验简洁，我只分析当前的日志。

sql> SELEct l.STATUS,s.MEMBER from v$log l,v$logfile s where l.GROUP# = s.GROUP#;

STATUSMEMBER

---------------- --------------------------------------------------

INACTIVE/Oracle/oradata/oradb11/redo04.log

CURRENT/Oracle/oradata/oradb11/redo05.log

INACTIVE/Oracle/oradata/oradb11/redo06.log

5. 在L_PUB上执行测试场景的脚本（自行找测试用户）

记录操作开始时间和结束时间。

SELEct sysdate from dual;--2017/3/6 11:00:19

create table test(id number,name varchar2(100));

insert into test values(1,'张三');

insert into test values(2,'李四');

commit;

insert into test values(3,'王五');

rollBACk;

insert into test values(4,'赵六');

insert into test values(5,'冯七');

insert into test values(6,'刘八');

insert into test values(7,'廖九');

commit;

update test set name='刘八八' where ID=6;

commit;

delete from test where ID=7;

rollBACk;

delete from test where ID=1;

commit;

SELEct sysdate from dual;--2017/3/6 11:02:05

6.用logminer用户生成数据字典,需要保证Dictionary_LOCATIOn所指定的目录存在

execute DBMS_logmnr_d.build(Dictionary_filename =>'Dictionary.ora',Dictionary_LOCATIOn =>'/Oracle/oradata/oradb11/LOGMNR');

7. 用logminer用户添加分析的redo日志
第一个日志options=>DBMS_logmnr.new，后面的options=>DBMS_logmnr.addfile。

exec DBMS_logmnr.add_logfile(logfilename=>'/Oracle/oradata/oradb11/redo05.log',options=>DBMS_logmnr.new);

DBMS_logmnr.add_logfile(logfilename=>'/Oracle/oradata/oradb11/redo06.log',options=>DBMS_logmnr.addfilE);

execute DBMS_logmnr.add_logfile(logfilename=>'/Oracle/oradata/oradb11/redo06.log',options=>DBMS_logmnr.addfilE);

8. 用logminer用户启动logminer

启动的方式有多种：

全分析，execute DBMS_logmnr.start_logmnr(Dictfilename=>'/Oracle/oradata/oradb11/LOGMNR/Dictionary.ora');

按时间段来分析，execute DBMS_logmnr.start_logmnr(startTime => to_date('2017-03-06 11:00:18','yyyy-mm-dd hh24:mi:ss'),endTime => to_date('2017-03-06 11:02:06',Dictfilename => '/Oracle/oradata/oradb11/LOGMNR/Dictionary.ora');

9. 用logminer用户logminer分析处理的结果只有本session能看到，可以先用表把数据记录然后分析

drop table logminer_t purge;

create table logminer_tas SELEct * fromv$LOGMNR_CONTENTS;

10. 用logminer用户分析后释放内存

execute DBMS_logmnr.end_logmnr;

11. 用logminer用户可以慢慢分析

SELEct s.SCN,

s.start_scn,

s.COMMIT_SCN,

s.timestAMP,

s.START_timestAMP,

s.COMMIT_timestAMP,

s.OPERATION,

s.RolLBACK,

s.SEG_owneR,

s.SEG_name,

s.table_name,

s.table_SPACE,

s.sql_REDO,

s.sql_UNDO

Fromlogminer_ts

where s.SEG_owneR = 'L_PUB'

ands.table_name = 'TEST'

order by scn;

我们可以捕获到执行的sql：

以下是提交事务的sql,同步的时候可以用到:

with co_scn as(

SELEct start_scn,commit_scn

fromlogminer_t s

where s.start_scn is not null

ands.commit_scn is not null),

operate_scn as(

SELEct scn,s.sql_redoFrom logminer_t s

where s.SEG_owneR = 'L_PUB'

ands.table_name = 'TEST'

)

SELEct scn,sql_redo

Fromoperate_scn s,co_scn co

where s.scn >= co.start_scn

ands.scn <= co.commit_scn;

以下是回滚的sql，同步时可以忽略：

SELEct s.SCN,

s.sql_REDOfrom logminer_t s where pxID in

(SELEct pxID from logminer_t where rollBACk=1 and SEG_owneR = 'LCAM_PUB')

order by scn;