logminer分析在线redo log

环境:
OS:Centos 7
DB:11.2.0.4 2节点RAC

 

1.查看当前的日志文件主组

SQL> select t1.THREAD#,t1.GROUP#, t1.STATUS, t2.MEMBER, t2.TYPE
  2    from v$log t1
  3   inner join v$logfile t2
  4      on t1.GROUP# = t2.GROUP#
  5     and t1.STATUS = 'CURRENT';

   THREAD#     GROUP# STATUS           MEMBER                                                                                                                              TYPE
---------- ---------- ---------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------
         1          2 CURRENT          +DATA/slnngk/onlinelog/group_2.264.1082700037                                                                                       ONLINE
         1          2 CURRENT          +DATA/slnngk/onlinelog/group_2.265.1082700037                                                                                       ONLINE
         2          3 CURRENT          +DATA/slnngk/onlinelog/group_3.268.1082700189                                                                                       ONLINE
         2          3 CURRENT          +DATA/slnngk/onlinelog/group_3.269.1082700189 

 

2.在其中一个节点上操作(建表和写入数据)
我这里在节点1上操作

 

SQL> connect hxl/oracle
Connected.

SQL> create table tb_aa(id number,name varchar2(32));

Table created.

SQL> insert into tb_aa values(1,'name1');

1 row created.

SQL> insert into tb_aa values(2,'name2');

1 row created.

SQL> commit;

Commit complete.

 

3.添加在线redo日志

connect / as sysdba
declare
begin
dbms_logmnr.add_logfile('+DATA/slnngk/onlinelog/group_2.264.1082700037');
end;

 

4.分析在线日志文件

declare
begin
dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
end;

 

5.查看分析日志(会话有效)

SQL> select sql_redo from v$logmnr_contents where lower(sql_redo) like '%tb_aa%';

SQL_REDO
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
insert into "SYS"."OBJ$"("OBJ#","DATAOBJ#","OWNER#","NAME","NAMESPACE","SUBNAME","TYPE#","CTIME","MTIME","STIME","STATUS","REMOTEOWNER","LINKNAME","FLAGS","OID$","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('88249','88249','84','TB_AA','1',NULL,'2',TO_DATE('10-OCT-21', 'DD-MON-RR'),TO_DATE('10-OCT-21', 'DD-MON-RR'),TO_DATE('10-OCT-21', 'DD-MON-RR'),'1',NULL,NULL,'0',NULL,'6','1','84',NULL,NULL,NULL);
create table tb_aa(id number,name varchar2(32));
insert into "HXL"."TB_AA"("ID","NAME") values ('1','name1');
insert into "HXL"."TB_AA"("ID","NAME") values ('2','name2');
update "SYS"."WRH$_SQLTEXT" set "SQL_TEXT" = 'select * from v$logmnr_contents where lower(sql_redo) like ''%tb_aa%''' where "SNAP_ID" = '159' and "DBID" = '3500931777' and "SQL_ID" = 'c3uu51s8qux1c' and "COMMAND_TYPE" = '3' and "REF_COUNT" IS NULL and ROWID = 'AAABkJAACAAAQ81AAF';

 

6.结束分析

declare
begin
dbms_logmnr.end_logmnr;
end;

 

该方法也可以分析其他db的日志文件,但是只能识别到可读的ddl语句,dml语句因为没有另外库的字典信息,显示的是unkown

declare
begin
dbms_logmnr.add_logfile('/tmp/redo03.log'); ##另外机器拷贝过来的日志文件
end;


declare
begin
dbms_logmnr.start_logmnr(options=>0);
end;

select sql_redo from v$logmnr_contents
where lower(sql_redo) like '%tb_test%' or lower(sql_redo) like '%200454%'

200454是该表的object_id
只能获取到ddl语句