ITPub博客

首页 > IT基础架构 > 网络安全 > logminer

logminer

原创 网络安全 作者:guyuexue 时间:2007-11-22 10:56:25 0 删除 编辑
logminer
[准备工作]
1. Package file
oracle dba user ( for prepare )
[8.1.5] svrmgrl> @?/rdbms/admin/dbmslogmnrd.sql

[8.1.6] svrmgrl> @?/rdbms/admin/dbmslm.sql
@?/rdbms/admin/dbmslmd.sql


2. Dictionary file build
. initSID.ora file change
[NT] utl_file_dir = *
[UNIX] utl_file_dir = /oracle/logs <- Dictionary file directory
( or utl_file_dir = * )
. SQL> call dbms_logmnr_d.build('dir_file','/oracle/logs');
eg. call dbms_logmnr_d.build('dif_file','/oracle/DSD/saparch/logminer');
---------A ------------B

- A : Dictionary file name (just as dif_file)
- B : Dictionary file directory. initSID.ora (just as /oracle/DSD/saparch/logminer)

3. Redo log/Archive
. SQL> select a.group#, a.sequence#, b.member
from v$log a, v$logfile b
where a.group# = b.group#
and a.status = 'CURRENT'; => log file

. SQL> ARCHIVE LOG LIST => Archive file
. SQL> call dbms_logmnr.add_logfile('/oradata/redo01.log',1); "
SQL> call dbms_logmnr.add_logfile('/oradata/redo01.log',3); "
SQL> call dbms_logmnr.add_logfile('/oradata/redo01.log',2); "
eg.
call dbms_logmnr.add_logfile('/oracle/DSD/origlogA/log_g13m1.dbf',1);
call dbms_logmnr.add_logfile('/oracle/DSD/origlogA/log_g13m1.dbf',3);
call dbms_logmnr.add_logfile('/oracle/DSD/origlogA/log_g13m1.dbf',2);

4. Log miner start
. SQL> execute dbms_logmnr.start_logmnr(DictFileName=>'/oracle/logs/dir_file');
execute dbms_logmnr.start_logmnr(DictFileName=>'/oracle/DSD/saparch/logminer');
-----------C ---------------------D
- C : Parameter
StartSCN (default 0)
EndSCN (0)
StartTime=>to_date('2002/04/20 13:00:00','yyyy/mm/dd hh24:mi:ss)
EndTime (01-JAN-2988)
DictFileName (NULL)
Option (0)
- D : /oracle/DSD/saparch/logminer/dif_file (full directory and name)

5. Log file
. SQL> select seg_owner, seg_name, operation,
to_char(timestamp,'yyyy/mm/dd hh24:mi:ss') "Time"
sql_redo, sql_undo
from v$logmnr_contents
where seg_owner = 'SAPR3'
and seg_name = 'DEPT'
and operation = 'DELETE';

- Redo SQL. Undo SQL

. View
V$LOGMNR_DICTIONARY : dictionary file
V$LOGMNR_PARAMETERS : Logminer setting parameter
V$LOGMNR_LOGS : redo log file
V$LOGMNR_CONTENTS : redo log file


6.
. SQL> call dbms_logmnr.end_logmnr();








---------------------------------------------
DSP 上设置
select a.group#, a.sequence#, b.member
from v$log a, v$logfile b
where a.group# = b.group#
and a.status = 'CURRENT';

call dbms_logmnr.add_logfile('/oracle/DSD/origlogA/log_g13m1.dbf',1);
call dbms_logmnr.add_logfile('/oracle/DSD/origlogA/log_g13m1.dbf',3);
call dbms_logmnr.add_logfile('/oracle/DSD/origlogA/log_g13m1.dbf',2);

execute dbms_logmnr.start_logmnr(DictFileName=>'/oracle/DSD/saparch/logminer/dif_file');

select seg_owner, seg_name, operation,
to_char(timestamp,'yyyy/mm/dd hh24:mi:ss') "Time" ,
sql_redo, sql_undo
from v$logmnr_contents
where seg_owner = 'SAPR3'
and seg_name = 'ZTEST'
and operation = 'DELETE';

select sql_undo
from v$logmnr_contents
where seg_owner = 'SAPR3'
and seg_name = 'ZTEST'
and operation = 'DELETE';


------------------------------------
用SYS用户登录
1.execute dbms_logmnr_d.build(dictionary_filename=>'dict.ora',dictionary_location=>'/oracle/logs');
定义文件的名字;LOGMNR的路径

2.execute dbms_logmnr.add_logfile('/oracle/arch/1_30.dbf',dbms_logmnr.new);
execute dbms_logmnr.add_logfile('/oracle/arch/1_31.dbf',dbms_logmnr.addfile);
添加archive 文件到 分析里,第一个用NEW 其他的ADDFILE

3.execute dbms_logmnr.start_logmnr(dictFileName => '/oracle/logs/dict.ora');
分析archive file

4.从 V$logmnr_contents 里选取
select SQL_UNDO from V$logmnr_contents
where seg_name='TEST2';


---------------------------------------
begin
dbms_logmnr_d.build('logmnrSCM.dic','/oracle/logs');
end;
/

begin
dbms_logmnr.add_logfile('/oracle/SMP/saparch/SMParch1_267027.dbf',dbms_logmnr.new);
dbms_logmnr.add_logfile('/oracle/SMP/saparch/SMParch1_267028.dbf',dbms_logmnr.addfile);
dbms_logmnr.add_logfile('/oracle/SMP/saparch/SMParch1_267029.dbf',dbms_logmnr.addfile);
dbms_logmnr.add_logfile('/oracle/SMP/saparch/SMParch1_267030.dbf',dbms_logmnr.addfile);
end;
/
begin
dbms_logmnr.start_logmnr(dictFileName => '/oracle/SMP/saparch/logmnrSMP.dic');
end;
/


------------------------------------
2004年8月23日恢复ZS003

1)execute dbms_logmnr_d.build(dictionary_filename=>'dict.ora',dictionary_location=>'/oracle/DSP/saparch/logminer');

2)execute dbms_logmnr.add_logfile('/oracle/DSP/saparch/DSParch1_262259.dbf',dbms_logmnr.new);

3)execute dbms_logmnr.start_logmnr(dictFileName => '/oracle/DSP/saparch/logminer/dict.ora');

4)select SQL_UNDO from V$logmnr_contents
where seg_name='ZS003'
AND SQL_UNDO LIKE 'insert%'



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

2005.2 实验记录
9i中 logmnr的使用

[准备]
1.Package file
@oracle_home/rdbms/admin/dbmslmd.sql

2.Dictionary file build
.initSID.ora file change
utl_file_dir = /oracle/logs <- Dictionary file directory

[logmnr]
1.dic.ora 文件生成
execute dbms_logmnr_d.build(dictionary_filename=>'dict.ora',dictionary_location=>'/oracle/ANT/data4/logmnr');

2.添加需要使用的log文件
execute dbms_logmnr.add_logfile(logfilename=>'/oracle/ANT/arch/archANT161.log', options=>dbms_logmnr.new);
execute dbms_logmnr.add_logfile(logfilename=>'/oracle/ANT/arch/archANT162.log', options=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'/oracle/ANT/arch/archANT163.log', options=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'/oracle/ANT/arch/archANT160.log', options=>dbms_logmnr.addfile);
3.开始进行分析
execute dbms_logmnr.start_logmnr(DictFileName=>'/oracle/ANT/data4/logmnr/dict.ora');


select username ,operation,sql_redo
from v$logmnr_contents
where operation='DDL'


SELECT SEG_OWNER,SEG_NAME,OPERATION,TO_CHAR(TIMESTAMP,'YYYY/MM/DD HH24:MI:SS')"TIME"
SQL_REDO,SQL_UNDO
FROM V$LOGMNR_CONTENTS
WHERE SEG_OWNER='SAHRIS'
AND SEG_NAME = 'KAOKE'
AND OPERATION='DELETE'


select sql_undo from v$logmnr_contents
where SEG_NAME = 'KAOKE'
AND seg_owner='SAHRIS'
AND OPERATION='DELETE'

4.dbms_logmnr.end_logmnrA
[@more@]

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/503782/viewspace-983266/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
  • 博文量
    87
  • 访问量
    529902