Oracle 11g logminer解析redo日志

清疚 2022-09-26 15:59 339阅读 0赞

下面的示例是分析在线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

identified by "LOGMINER"

default tablespace LCAM\_PUB\_TBS --表空间依据当前数据库的情况而定

temporary tablespace TEMP

profile DEFAULT;

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

--------

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\#;

STATUS           MEMBER

\---------------- --------------------------------------------------

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','yyyy-mm-dd hh24:mi:ss'),DictFileName => '/oracle/oradata/oradb11/LOGMNR/dictionary.ora');

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

Drop table logminer\_t purge;

Create table logminer\_t  as select \* from V$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

From logminer\_t s

where s.SEG\_OWNER = 'L\_PUB'

and s.table\_name = 'TEST'

order by scn;

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

![viewdownfile_f_97C391B16879DAB4144B6CB67B77A31A4149C43B5DE76CADC9BEE885D40DAD37A4B20CD1CE2324BA8737DA34128A2A2BFFF0A380930DF980CDF705698C0F1441126E95A9E6D1E19AB8C21C27B424A78D7201FACC19F87C2923CC43B2A07A4BE867B4481399134108411106E6ADC7F297FA5F3D5F476FB3C8C2673D2E973BC67E_sid_WKyAVeW1kSmUPitz][]

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

with co\_scn as(

select start\_scn,commit\_scn

from logminer\_t s

where s.start\_scn is not null

and s.commit\_scn is not null),

operate\_scn as(

Select scn,s.sql\_redo  From logminer\_t s

where s.SEG\_OWNER = 'L\_PUB'

and s.table\_name = 'TEST'

)

Select scn,sql\_redo

From operate\_scn s, co\_scn co

where s.scn >= co.start\_scn

and s.scn <= co.commit\_scn;

![viewdownfile_f_97C391B16879DAB4144B6CB67B77A31A4149C43B5DE76CADC9BEE885D40DAD37A4B20CD1CE2324BA8737DA34128A2A2BFFF0A380930DF980CDF705698C0F1441126E95A9E6D1E19AB8C21C27B424A78D7201FACC19F87C2923CC43B2A07A4BE867B4481399134108411106E6ADC7F2975C8234C141D84FF9C2673D2E973BC67E_sid_WKyAVeW1kSmUPitz][]

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

select S.SCN,

S.TIMESTAMP,

S.OPERATION,

S.ROLLBACK,

S.SEG\_OWNER,

S.SEG\_NAME,

S.TABLE\_NAME,

S.SQL\_REDO from logminer\_t s where pxid in

(select pxid from logminer\_t where rollback=1 and SEG\_OWNER = 'LCAM\_PUB')

order by scn;

![viewdownfile_f_97C391B16879DAB4144B6CB67B77A31A4149C43B5DE76CADC9BEE885D40DAD37A4B20CD1CE2324BA8737DA34128A2A2BFFF0A380930DF980CDF705698C0F1441126E95A9E6D1E19AB8C21C27B424A78D7201FACC19F87C2923CC43B2A07A4BE867B4481399134108411106E6ADC7F2974835ABB193B39AC7C2673D2E973BC67E_sid_WKyAVeW1kSmUPitz][]

[viewdownfile_f_97C391B16879DAB4144B6CB67B77A31A4149C43B5DE76CADC9BEE885D40DAD37A4B20CD1CE2324BA8737DA34128A2A2BFFF0A380930DF980CDF705698C0F1441126E95A9E6D1E19AB8C21C27B424A78D7201FACC19F87C2923CC43B2A07A4BE867B4481399134108411106E6ADC7F297FA5F3D5F476FB3C8C2673D2E973BC67E_sid_WKyAVeW1kSmUPitz]: /images/20220708/a1c8f0a4948a405aa476b7cbb74e3f61.png
[viewdownfile_f_97C391B16879DAB4144B6CB67B77A31A4149C43B5DE76CADC9BEE885D40DAD37A4B20CD1CE2324BA8737DA34128A2A2BFFF0A380930DF980CDF705698C0F1441126E95A9E6D1E19AB8C21C27B424A78D7201FACC19F87C2923CC43B2A07A4BE867B4481399134108411106E6ADC7F2975C8234C141D84FF9C2673D2E973BC67E_sid_WKyAVeW1kSmUPitz]: /images/20220708/8e5a494b671d4c8b91d727de116ee286.png
[viewdownfile_f_97C391B16879DAB4144B6CB67B77A31A4149C43B5DE76CADC9BEE885D40DAD37A4B20CD1CE2324BA8737DA34128A2A2BFFF0A380930DF980CDF705698C0F1441126E95A9E6D1E19AB8C21C27B424A78D7201FACC19F87C2923CC43B2A07A4BE867B4481399134108411106E6ADC7F2974835ABB193B39AC7C2673D2E973BC67E_sid_WKyAVeW1kSmUPitz]: /images/20220708/ffc57ef475384e62832cb282d3e5eb3a.png