Checking for Oracle archive log corruption [ID 1268590.1]

日誌挖掘可以驗證歸檔日誌是否損壞。

Goal

EXTRACT is hung reading an archive log. OGG trace and o/s truss outputs show EXTRACT repeatedly accessing the next archive log but not progressing or generating an error. How can I verify that the log is not corrupted?

Solution

Try processing that archive log using logminer and see if logminer can access the file header info:

SQL> exec
dbms_logmnr.add_logfile('/.arc');
SQL> exec dbms_logmnr.start_logmnr;
SQL> select count(1) from v$logmnr_contents;

When the archive file is good, you will be able to execute logmnr and select from the view:

os> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Dec 7 09:51:58 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> exec dbms_logmnr.add_logfile('/rdbms/oracle/ora1022i/64/admin/ora1022a/arch/1_mf_1_879_56dggmtf_.arc');

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.start_logmnr;

PL/SQL procedure successfully completed.

SQL> select count(1) from v$logmnr_contents;

COUNT(1)
----------
18466


If the archive is bad, logmnr will not be able to access it:


SQL> exec dbms_logmnr.add_logfile('/rdbms/oracle/ora1022i/64/admin/ora1022a/arch/
wqbsqa01_652800616_1_4265.arc');
BEGIN dbms_logmnr.add_logfile('/rdbms/oracle/ora1022i/64/admin/ora1022a/arch/wqbsqa01_652800616_1_4265.arc'); END;

*
ERROR at line 1:
ORA-01284: file
/rdbms/oracle/ora1022i/64/admin/ora1022a/arch/wqbsqa01_652800616_1_4265.arc cannot be opened
ORA-00308: cannot open archived log
'/rdbms/oracle/ora1022i/64/admin/ora1022a/arch/wqbsqa01_652800616_1_4265.arc'
ORA-27047: unable to read the header block of file
OSD-04001: invalid logical block size (OS 3899694081)
ORA-06512: at "SYS.DBMS_LOGMNR", line 68
ORA-06512: at line 1[@more@]