oracle logmnr日志挖掘一例(二)
drop table t AS "BIN$wWGJJd0UCrDgRAAMKRcWyg==$0"
;
1823953 01-JUN-12 DEX T T purge table "BIN$wWGJJd0UCrDgRAAMKRc
Wyg==$0" ;
Wyg==$0" ;
如果发现username , os_username , machine_name 等为unknow 或者 null 的状态 这是一个bug 当你
重复执行同一个logmnr使用相同的参数的时候就可能会出现这种状况（第一次不会）BUG:10129774
重复执行同一个logmnr使用相同的参数的时候就可能会出现这种状况（第一次不会）BUG:10129774
链接地址 https://support.oracle.com/CSP/main/article cmd=show&type=BUG&id=10129774
打补丁 10129774
或者提前使用补充日志
我们再来试一下
SQL> alter database add supplemental log data;
Database altered.
SQL> set time on
15:00:41 SQL>
15:01:16 SQL> select log_mode, supplemental_log_data_min from v$database;
LOG_MODE SUPPLEME
------------ --------
ARCHIVELOG YES
15:02:18 SQL> create table t (x int primary key ) ;
Table created.
15:02:32 SQL> drop table t purge ;
Table dropped.
begin
dbms_logmnr.add_logfile(logfilename=>'/gwrs/PROD/archivelog/2012_06_01/o1_mf_2_36_7wjtbo2y
_.arc',options=>dbms_logmnr.new) ;
_.arc',options=>dbms_logmnr.new) ;
dbms_logmnr.add_logfile(logfilename=>'/gwrs/PROD/archivelog/2012_06_01/o1_mf_1_46_7wjtbsns
_.arc',options=>dbms_logmnr.addfile) ;
dbms_logmnr.start_logmnr(dictfilename=>'/gwrs/logmnr/dict.ora');
end ;
/
create table log_tmp tablespace users as select * from v$logmnr_contents ;
15:15:59 SQL> set lines 150
15:13:15 SQL> col username for a20
15:15:50 SQL> col os_username for a10
15:15:50 SQL> col machine_name for a10
15:15:50 SQL> col sql_redo for a50
15:15:51 SQL> select username , os_username , machine_name , sql_redo from logmnr_tmp where table_name='T' ; www.2cto.com
USERNAME OS_USERNAM MACHINE_NA SQL_REDO
-------------------- ---------- ---------- --------------------------------------------------
DEX oracle sol1 create table t (x int primary key ) ;
DEX oracle sol1 drop table t purge ;
ok 了， 再执行一次看一下bug是否依旧存在。
15:16:11 SQL> exec dbms_logmnr.end_logmnr() ;
begin
dbms_logmnr.add_logfile(logfilename=>'/gwrs/PROD/archivelog/2012_06_01/o1_mf_2_36_7wjtbo2y
_.arc',options=>dbms_logmnr.new) ;
_.arc',options=>dbms_logmnr.new) ;
dbms_logmnr.add_logfile(logfilename=>'/gwrs/PROD/archivelog/2012_06_01/o1_mf_1_46_7wjtbsns
_.arc',options=>dbms_logmnr.addfile) ; www.2cto.com
_.arc',options=>dbms_logmnr.addfile) ; www.2cto.com
dbms_logmnr.start_logmnr(dictfilename=>'/gwrs/logmnr/dict.ora');
end ;
/
15:17:53 SQL> select username , os_username , machine_name , sql_redo from v$logmnr_cont
ents where table_name='T' ;
ents where table_name='T' ;
USERNAME OS_USERNAM MACHINE_NA SQL_REDO
-------------------- ---------- ---------- --------------------------------------------------
DEX oracle sol1 create table t (x int primary key ) ;
DEX oracle sol1 drop table t purge ;
ok 没有问题。
作者 renfengjun