flat files oracle,今天学习了一下oracle的重做日志分析，折腾一天，记下来

第一步、

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;