Friday, May 10, 2013

ORA-16014 and All Archive destinations made inactive due to error 333 on windows

ISSUE:
Database hung and no external connection is allowed.

CAUSE:
The only member of one of inactive online redo log groups is corrupted. Archiver can not archive it to disk completely but stopped partially.

ERROR MESSAGES:


ERROR:
ORA-00257: archiver error. Connect internal only, until freed.


SQL> alter system switch logfile;   # it hung


SQL> alter system archive log all;
alter system archive log all
*
ERROR at line 1:
ORA-16020: fewer destinations available than specified by
LOG_ARCHIVE_MIN_SUCCEED_DEST



SQL> alter system archive log current;
alter system archive log current
*
ERROR at line 1:
ORA-16038: log 1 sequence# 6484 cannot be archived
ORA-00333: redo log read error block  count
ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\TEST\REDO01.LOG'

ALERT LOG MESSAGE:

ARC2: All Archive destinations made inactive due to error 333
Committing creation of archivelog 'D:\ORACLE\ORADATA\TEST\ARCHIVE\TEST_ARC0000047371_07852571900001.ARC' (error 333)

SOLUTION:
Clear the corrupted log group and make a full backup. All old backup is unusable to bring the database back   beyond the corrupted point.

Identify problem log group:
Found sequence # from alert log and map it with output of " select * from v$log "

Command:
alter database clear unarchived logfile group 5;


RCA:
Need run blow command to see if redo log is corrupted.
alter system dump logfile '<logfileL_filename>'; 

Reference:
http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10739/onlineredo.htm#i1006568
http://www.dba-oracle.com/t_ora_16014_log_sequence_number_not_archived_no_available_destinations.htm
https://forums.oracle.com/forums/thread.jspa?threadID=839923&tstart=0



1 comment: