tsingsong99

LogMiner的错误问题ORA-01347
数据库ORACLE9.2
平台:WIN2000
数据库是归档的
EXECUTE dbms_logmnr_d.build('ass.ora','d:\',options =>; dbms_logmnr_d.store_in_flat_file);

EXECUTE dbms_logmnr.add_logfile(LogFileName=>;'d:\Oracle\ora92\rdbms\ARC00008.001',Options=>;dbms_logmnr.new);

EXECUTE dbms_logmnr.start_logmnr(DictFileName=>;'d:\ass.ora',Options=>;dbms_logmnr.ddl_dict_tracking);

都OK
但我select username from v$logmnr_contents;
或
SELECT SQL_REDO from v$logmnr_contents WHERE USERNAME='ASS';
等的时候就报ORA-01347: 未找到补充日志数据
看了文档资料说这个SESSION有问题说再开个就好了
但我重新开个SESSION做错误依旧
谢谢

tsingsong99

过程如下:
##在pfile中补充参数utl_file_dir
*.utl_file_dir='/oracle/admin/orcl/udump/'
~
~
"initorcl.ora" 31L, 1009C written
[oracle@wapdb dbs]$ sqlplus '/ as sysdba'

SQL*Plus: Release 9.2.0.5.0 - Production on Tue Jan 4 14:46:15 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to an idle instance.

SQL>; create spfile from pfile;

File created.

SQL>; startup
ORACLE instance started.

Total System Global Area  236000424 bytes
Fixed Size                   451752 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
SQL>;
SQL>;
SQL>;
SQL>; show parameter utl_file_dir

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string      /oracle/admin/orcl/udump/
SQL>;
SQL>;
SQL>; EXECUTE DBMS_LOGMNR_D.BUILD('test_logdict.ora','/oracle/admin/orcl/udump/');

PL/SQL procedure successfully completed.

SQL>; select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
         1          1        116  104857600          1 NO  CURRENT
     33068552 04-JAN-05

         2          1        114  104857600          1 NO  INACTIVE
     32454851 02-JAN-05

         3          1        115  104857600          1 NO  INACTIVE
     32788870 03-JAN-05


SQL>; EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/oracle/oradata/orcl/redo01.log',DBMS_LOGMNR.NEW);

PL/SQL procedure successfully completed.


SQL>;
SQL>;
SQL>; EXECUTE DBMS_LOGMNR.START_LOGMNR(DICTFILENAME=>;'/oracle/admin/orcl/udump/test_logdict.ora',STARTSCN=>;33068552,ENDSCN=>;43068552, OPTIONS=>;DBMS_LOGMNR.DDl_DICT_TRACKING);

PL/SQL procedure successfully completed.

SQL>;
SQL>; SELECT SCN,SEG_OWNER,SEG_NAME,SQL_REDO,REDO_VALUE,SQL_UNDO,UNDO_VALUE from v$logmnr_contents WHERE seg_name=upper('mo_wap_dlfobjects');
SELECT SCN,SEG_OWNER,SEG_NAME,SQL_REDO,REDO_VALUE,SQL_UNDO,UNDO_VALUE from v$logmnr_contents WHERE seg_name=upper('mo_wap_dlfobjects')
                                                                           *
ERROR at line 1:
ORA-01347: Supplemental log data no longer found


SQL>;
SQL>;

sshd

for DDL_DICT_TRACKING option you will need to enable "Supplemental Logging" to avoid this error.

solution: 1) remove the DDL_DICT_TRACKING option
or
2)
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
Note: it has a significant database performance impact.

to disable it:
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

tsingsong99

谢谢大侠的解答,
能否把方法2)详细解释一下??
谢谢!

tsingsong99

----------------其他两个错误
SQL>; EXECUTE DBMS_LOGMNR_D.BUILD('test_logdict.ora','/oracle/admin/orcl/udump/');

PL/SQL procedure successfully completed.

SQL>; EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/oracle/oradata/orcl/redo01.log',DBMS_LOGMNR.NEW);

PL/SQL procedure successfully completed.

SQL>; EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/oracle/oradata/orcl/redo02.log',DBMS_LOGMNR.ADDFILE);

PL/SQL procedure successfully completed.

SQL>; EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/oracle/oradata/orcl/redo03.log',DBMS_LOGMNR.ADDFILE);

PL/SQL procedure successfully completed.

SQL>; EXECUTE DBMS_LOGMNR.START_LOGMNR(DICTFILENAME=>;'F:\oracle\admin\ZJHDB\udump\test_logdict.ora');
BEGIN DBMS_LOGMNR.START_LOGMNR(DICTFILENAME=>;'F:\oracle\admin\ZJHDB\udump\test_logdict.ora'); END;

*
ERROR at line 1:
ORA-01284: file  cannot be opened
ORA-06512: at "SYS.DBMS_LOGMNR", line 53
ORA-06512: at line 1


SQL>; EXECUTE DBMS_LOGMNR.START_LOGMNR(DICTFILENAME=>;'F:\oracle\admin\ZJHDB\udump\test_logdict.ora');

PL/SQL procedure successfully completed.

SQL>; SELECT SQL_REDO,SQL_UNDO from v$logmnr_contents WHERE seg_name=upper('test_log_miner');
SELECT SQL_REDO,SQL_UNDO from v$logmnr_contents WHERE seg_name=upper('test_log_miner')
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel


SQL>;

tsingsong99

----------------其他两个错误
SQL>; EXECUTE DBMS_LOGMNR_D.BUILD('test_logdict.ora','/oracle/admin/orcl/udump/');

PL/SQL procedure successfully completed.

SQL>; EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/oracle/oradata/orcl/redo01.log',DBMS_LOGMNR.NEW);

PL/SQL procedure successfully completed.

SQL>; EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/oracle/oradata/orcl/redo02.log',DBMS_LOGMNR.ADDFILE);

PL/SQL procedure successfully completed.

SQL>; EXECUTE DBMS_LOGMNR.ADD_LOGFILE('/oracle/oradata/orcl/redo03.log',DBMS_LOGMNR.ADDFILE);

PL/SQL procedure successfully completed.

SQL>; EXECUTE DBMS_LOGMNR.START_LOGMNR(DICTFILENAME=>;'F:\oracle\admin\ZJHDB\udump\test_logdict.ora');
BEGIN DBMS_LOGMNR.START_LOGMNR(DICTFILENAME=>;'F:\oracle\admin\ZJHDB\udump\test_logdict.ora'); END;

*
ERROR at line 1:
ORA-01284: file  cannot be opened
ORA-06512: at "SYS.DBMS_LOGMNR", line 53
ORA-06512: at line 1


SQL>; EXECUTE DBMS_LOGMNR.START_LOGMNR(DICTFILENAME=>;'F:\oracle\admin\ZJHDB\udump\test_logdict.ora');

PL/SQL procedure successfully completed.

SQL>; SELECT SQL_REDO,SQL_UNDO from v$logmnr_contents WHERE seg_name=upper('test_log_miner');
SELECT SQL_REDO,SQL_UNDO from v$logmnr_contents WHERE seg_name=upper('test_log_miner')
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel


SQL>;

tsingsong99

是不是说"补充日志"是一种追加了更多信息的日志,
要使用DDL_DICT_TRACKING ,则必须现将数据库设置为 补充日志 ,
然后对这些日志进行 DDL_DICT_TRACKING  的 LogMiner ?