环境:
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语句
