数据库进阶系列之三:使用Logminer解析Oracle日志

有时候我们会被问到一个数据库对象的创建时间,这简单,可以查user_objects视图,不过如果再深入些,这个表的数据什么时候被什么人修改过?这个在没有工具帮助的情况下是不容易回答的,我们只知道这些记录存在于Redo日志中,但这些日志文件并不是人直接可读的,这个时候Logminer就能派上用场了。下面简单举例(实验环境:Windows Vista + Oracle 11.1.0.7.0):

H:\>sqlplus / as sysdba
SQL> alter database add supplemental log data;
/*该命令用于启动supplemental logging功能,以使Logminer获得更加详细的信息。如果该功能未启用,Logminer解析结果中的username,session_info等信息都被置为‘UNKNOWN’,这样我们就无从得知操作是由哪个用户执行的了。另外,也可能导致某些操作无法解析出来,比如在测试中我发现如果该功能未启用,而我更新的表是一张未设置主键的表,那么在解析结果中我始终找不到对应的UPDATE操作。欲禁用此项功能:alter database drop supplemental log data;*/
SQL> shutdown immediate
SQL> startup
SQL> alter system switch logfile;
/*将日志切换到下一组以便在解析之后可以使用时间条件进行过滤,以免日志记录太多看花眼*/
SQL> exit

/*以下执行一些简单的操作,这些操作会被记录到切换过后的Redo日志中*/
H:\>sqlplus morven3/morven3
SQL> create table tab1(fid number,fval varchar(20));
SQL> insert into tab1 values(1,'abc');
SQL> /
SQL> /
SQL> /
SQL> commit;
SQL> update tab1 set fval='xyz' where fid=1;
SQL> commit;
SQL> delete from tab1;
SQL> commit;
SQL> exit

H:\>sqlplus / as sysdba
SQL> select * from v$log where status='CURRENT';
/*查找当前使用的日志的组号*/
SQL> SELECT MEMBER from v$logfile where group#=3;
/*根据查到的当前日志组号,查找日志文件,得到日志文件路径,如“C:\DATABASE\ORADATA\SIDORCL\REDO03.LOG”*/
SQL> exec dbms_logmnr.add_logfile('C:\DATABASE\ORADATA\SIDORCL\REDO03.LOG',dbms_logmnr.new);
/*向Logminer指定要解析的日志文件*/
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
/*向Logminer指定使用当前数据库的数据字典进行解析(只有根据数据字典才能将对象ID解析成对象名称,增加可读性)*/
SQL> select t.* from v$logmnr_contents t where timestamp >sysdate-10/24/60 and username='MORVEN3';
/*通过上面几个步骤,动态视图v$logmnr_contents就有了解析出来的日志记录。这里你可以简地通过时间过滤一下,比如像上面的查找10分钟内的操作,当然10分钟内可能还是有不少日志记录,你可以加其它的过滤条件,比如用户名。根据上面的语句,我们查到13条记录,可以看“SQL_REDO”字段,这些记录分别是:
create table tab1(fid number,fval varchar(20));
insert into "MORVEN3"."TAB1"("FID","FVAL") values ('1','abc');
insert into "MORVEN3"."TAB1"("FID","FVAL") values ('1','abc');
insert into "MORVEN3"."TAB1"("FID","FVAL") values ('1','abc');
insert into "MORVEN3"."TAB1"("FID","FVAL") values ('1','abc');
update "MORVEN3"."TAB1" set "FVAL" = 'xyz' where "FVAL" = 'abc' and ROWID = 'AAARJ1AAFAAAACFAAA';
update "MORVEN3"."TAB1" set "FVAL" = 'xyz' where "FVAL" = 'abc' and ROWID = 'AAARJ1AAFAAAACFAAB';
update "MORVEN3"."TAB1" set "FVAL" = 'xyz' where "FVAL" = 'abc' and ROWID = 'AAARJ1AAFAAAACFAAC';
update "MORVEN3"."TAB1" set "FVAL" = 'xyz' where "FVAL" = 'abc' and ROWID = 'AAARJ1AAFAAAACFAAD';
delete from "MORVEN3"."TAB1" where "FID" = '1' and "FVAL" = 'xyz' and ROWID = 'AAARJ1AAFAAAACFAAA';
delete from "MORVEN3"."TAB1" where "FID" = '1' and "FVAL" = 'xyz' and ROWID = 'AAARJ1AAFAAAACFAAB';
delete from "MORVEN3"."TAB1" where "FID" = '1' and "FVAL" = 'xyz' and ROWID = 'AAARJ1AAFAAAACFAAC';
delete from "MORVEN3"."TAB1" where "FID" = '1' and "FVAL" = 'xyz' and ROWID = 'AAARJ1AAFAAAACFAAD';
另外,通过“SESSION_INFO”字段,我们还能定位出具体是哪个用户在哪台机器上执行的操作,我们可以看一个“SESSION_INFO”的例子:
login_username=MORVEN3 client_info= OS_username=domain1\hmy Machine_name=domain1\EV001EEC1FF000 OS_terminal=EV001EEC1FF000 OS_process_id=4064:2560 OS_program_name=sqlplus.exe
v$logmnr_contents视图中还有其它一系列的字段,SEG_TYPE_NAME,TABLE_SPACE,SQL_UNDO等等*/
SQL> exec dbms_logmnr.end_logmnr
/*退出Logminer*/

 

posted @ 2010-04-26 16:28  Morven.Huang  阅读(1951)  评论(1编辑  收藏  举报