ORA-16014: log 2 sequence# 12 not archived, no available destinations

Mon May 12 16:43:32 2008

ORA-16014: log 2 sequence# 12 not archived, no available destinations

ORA-00312: online log 2 thread 1: '/apps/oracle/oradata/OCP10G/redo02.log'

Mon May 12 16:43:32 2008

Errors in file /apps/oracle/admin/OCP10G/bdump/ocp10g_arc0_10273.trc:

ORA-16014: log 2 sequence# 12 not archived, no available destinations

ORA-00312: online log 2 thread 1: '/apps/oracle/oradata/OCP10G/redo02.log'

Mon May 12 16:43:51 2008

Read of flashback database logfile 33, block 685 found corrupted data.

See trace file for more information.

Reread of flashback database logfile 33, block 685 found same corrupted data

Read of flashback database logfile 33, block 685 found corrupted data.

See trace file for more information.
Reread of flashback database logfile 33, block 685 found same corrupted data



Above error observed in my test database, verified that disk space still has enough space.

-- only 3 groups of redo log;
--the 2nd group looks corrupted and can't be resued.

alter system archive log all to '/home/oracle/';


SQL> alter database clear unarchived logfile group 2;

Database altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 18 52428800 1 NO CURRENT
1402127 12-MAY-08

2 1 0 52428800 1 YES UNUSED
1311896 29-APR-08

3 1 17 52428800 1 YES INACTIVE
1401025 12-MAY-08


SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 21 52428800 1 NO CURRENT
1405111 12-MAY-08

2 1 19 52428800 1 YES ACTIVE
1405094 12-MAY-08

3 1 20 52428800 1 YES ACTIVE
1405099 12-MAY-08


SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 27 52428800 1 YES ACTIVE
1405140 12-MAY-08

2 1 28 52428800 1 NO CURRENT
1406092 12-MAY-08

3 1 26 52428800 1 YES INACTIVE
1405138 12-MAY-08



REF: http://www.dbforums.com/archive/index.php/t-1119443.html

http://www.databasedesign-resource.com/oracle-log-files.html



Review: This is a rare encountered scenario of redo log corrupted.

Clear A Log File If It Has Become Corrupt And Avoid Archiving

ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP ;

another relevant command is

1. This statement overcomes two situations where dropping redo logs is not possible: If there are only two log groups and if the corrupt redo log file belongs to the current group:
ALTER DATABASE CLEAR LOGFILE GROUP 4;


Clearing Online Redo Log Files
Under certain circumstances, a redo log group member (or all members of a log group) can
become corrupted. To solve this problem, you can drop and re-add the log file group or group
member. It is much easier, however, to use the ALTER DATABASE CLEAR LOGFILE command. The
following example clears the contents of redo log group 3 in the database:
ALTER DATABASE CLEAR LOGFILE GROUP 3;
Another distinct advantage of this command is that you can clear a log group even if the database
has only two log groups and only one member in each group. You can also clear a log
group member even if it has not been archived by using the UNARCHIVED keyword. In this case,
it is advisable to do a full database backup at the earliest convenience, because the unarchived
redo log file is no longer usable for database recovery.