不仅如此,日志中记录的信息还包括:数据库的更改历史、更改类型(INSERT、UPDATE、DELETE、DDL等)、更改对应的SCN号、以及执行这些操作的用户信息等,LogMiner在分析日志时,将重构等价的SQL语句和UNDO语句(分别记录在V$LOGMNR_CONTENTS视图的SQL_REDO和SQL_UNDO中)。这里需要注意的是等价语句,而并非原始SQL语句,例如:我们最初执行的是“delete a where c1 <>'cyx';”,而LogMiner重构的是等价的6条DELETE语句。所以我们应该意识到V$LOGMNR_CONTENTS视图中显示的并非是原版的现实,从数据库角度来讲这是很容易理解的,它记录的是元操作,因为同样是“delete a where c1 <>'cyx';”语句,在不同的环境中,实际删除的记录数可能各不相同,因此记录这样的语句实际上并没有什么实际意义,LogMiner重构的是在实际情况下转化成元操作的多个单条语句。
另外由于Oracle重做日志中记录的并非原始的对象(如表以及其中的列)名称,而只是它们在Oracle数据库中的内部编号(对于表来说是它们在数据库中的对象ID,而对于表中的列来说,对应的则是该列在表中的排列序号:COL 1, COL 2 等),因此为了使LogMiner重构出的SQL语句易于识别,我们需要将这些编号转化成相应的名称,这就需要用到数据字典(也就说LogMiner本身是可以不用数据字典的,详见下面的分析过程),LogMiner利用DBMS_LOGMNR_D.BUILD()过程来提取数据字典信息。
delete from "C"."A" where "C1" = ‘gototop’ and ROWID = 'AAABg1AAFAAABQaAAH';
如果没有字典文件,LogMiner分析出来的结果将是:
delete from "UNKNOWN"."OBJ# 6197" where "COL 1" = HEXTORAW('d6a7d4ae') and ROWID
= 'AAABg1AAFAAABQaAAH';
ERROR at line 1:
ORA-01308: initialization parameter utl_file_dir is not set
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 923
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 1938
ORA-06512: at line 1
需要注意的是,在oracle817 for Windows版中会出现以下错误:
14:26:05 SQL> execute dbms_logmnr_d.build('oradict.ora','c:oracleadminoralog');
BEGIN dbms_logmnr_d.build('oradict.ora','c:oracleadminoralog'); END;
*
ERROR at line 1:
ORA-06532: Subscript outside of limit
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 793
ORA-06512: at line 1
解决办法:
编辑"$ORACLE_HOME/rdbms/admindbmslmd.sql"文件,把其中的
TYPE col_desc_array IS VARRAY(513) OF col_description;
改成:
TYPE col_desc_array IS VARRAY(700) OF col_description;
保存文件,然后执行一遍这个脚本:
15:09:06 SQL> @c:oracleora81rdbmsadmindbmslmd.sql
Package created.
Package body created.
No errors.
Grant succeeded.
然后重新编译DBMS_LOGMNR_D包:
15:09:51 SQL> alter package DBMS_LOGMNR_D compile body;
Package body altered.
之后重新执行dbms_logmnr_d.build即可:
15:10:06 SQL> execute dbms_logmnr_d.build('oradict.ora','c:oracleadminoralog');
PL/SQL procedure successfully completed.
ORA-01284: file /data6/cyx/logmnr/arch_1_163570.arc cannot be opened
ORA-00308: cannot open archived log '/data6/cyx/logmnr/arch_1_163570.arc'
ORA-27048: skgfifi: file header information is invalid
ORA-06512: at "SYS.DBMS_LOGMNR", line 63
ORA-06512: at line 1
SQL> select timestamp,sql_redo from v$logmnr_contents2
where upper(sql_redo) like '%CREATE%';
TIMESTAMP
-------------------
SQL_REDO
-------------------------
2003-09-21 10:01:55
create table t (c1 number);
create or replace trigger on_logon_trigger
after logon on database
begin
dbms_application_info.set_client_info(sys_context('userenv', 'ip_address'));
end;
/