第一步、
SQL> show user;
USER is "SYS"
第二步:
SQL> alter system set
utl_file_dir='/u01/oracle/dict' scope=spfile;
System altered.
第三步:
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
第四步:
SQL> startup
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed
Size 1336960 bytes
Variable
Size 293603712 bytes
Database
Buffers 121634816 bytes
Redo
Buffers 6094848 bytes
Database mounted.
Database opened.
第五步:
SQL> show parameter utl_file_dir
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
utl_file_dir string /u01/oracle/dict
第六步(写PL/SQL)
begin
dbms_logmnr_d.build(dictionary_filename=>'dict.ora',
dictionary_location=>'/u01/oracle/dict',
options=>dbms_logmnr_d.store_in_flat_file);
end;
第七步:执行
SQL> /
begin
*
ERROR at line 1:
ORA-01336: specified dictionary file cannot be opened
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 6003
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 6093
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 12
ORA-06512: at line 2
问题原因:在第六步里dictionary_location必须跟utl_file_dir一致,提示
字典文件打不开是因为系统中不存在目录/u01/oracle/dict,文件dict.ora
不用手工创建,系统会自动创建,创建用户就是oracle。
修改:mkdir /u01/oracle/dict,执行没有问题,
SQL> /
PL/SQL procedure successfully completed.
继续第八步:
SQL> select * from v$logfile;
GROUP#
STATUS TYPE MEMBER IS_RECOVER
---------- ---------- ---------- ------------------------------
----------
########## ONLINE /u01/oradata/wilson/redo03.log NO
########## ONLINE /u01/oradata/wilson/redo02.log NO
########## ONLINE /u01/oradata/wilson/redo01.log NO
SQL> begin
2 dbms_logmnr.add_logfile(logfilename=>'/u01/oradata/wilson/redo01.log',
3 options=>dbms_logmnr.new);
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> edit
Wrote file afiedt.buf
1 begin
2 dbms_logmnr.add_logfile(logfilename=>'/u01/oradata/wilson/redo02.log',
3 options=>dbms_logmnr.addfile);
4* end;
SQL> /
PL/SQL procedure successfully completed.
SQL> edit
Wrote file afiedt.buf
1 begin
2 dbms_logmnr.add_logfile(logfilename=>'/u01/oradata/wilson/redo03.log',
3 options=>dbms_logmnr.addfile);
4* end;
SQL> /
PL/SQL procedure successfully completed.
第九步:分析日志
SQL> begin
2 dbms_logmnr.start_logmnr(dictfilename=>'/u01/oradata/dict/dict.ora');
3* end;
SQL> /
PL/SQL procedure successfully completed.
第十步:查看分析结果
SQL> select sql_redo,username,timestamp,sql_undo
from v$logmnr_contents;
第十一步:结束分析
SQL> EXEC dbms_logmnr.end_logmnr;