Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, razvan.

Asked: June 23, 2000 - 4:28 am UTC

Last updated: March 29, 2009 - 10:29 am UTC

Version: 7.3.2

Viewed 10K+ times! This question is

You Asked

hello tom,

can you explain me what does the following statement and in what case should i utilise this:

alter database clear unachived
logfile group 1;

alter database clear unarchived
logfile group 1
unrecoverable datafile;

i seem it does the same things as does the alter database with
resetlogs option.


thanks a lot
razvan

and Tom said...

Straight from the docs on this one:

<quote ref=sql ref manual>
CLEAR LOGFILE

reinitializes an online redo log, optionally without archiving the redo log. CLEAR LOGFILE is similar to adding and dropping a redo log, except that the statement may be issued even if there are only two logs for the thread and also may be issued for the
current redo log of a closed thread.

UNARCHIVED You must specify UNARCHIVED if you want to reuse a redo log that was not archived.

WARNING: Specifying UNARCHIVED makes backups unusable if
the redo log is needed for recovery.
Do not use CLEAR LOGFILE to clear a log needed for media recovery. If it is necessary to clear a log containing redo after the database checkpoint, you must first perform
incomplete media recovery. The current redo log of an open thread can be cleared. The current log of a closed thread can be cleared by switching logs in the closed thread.

If the CLEAR LOGFILE statement is interrupted by a system or instance failure, then the database may hang. If this occurs, reissue the statement after the database is restarted. If
the failure occurred because of I/O errors accessing one member of a log group, then that member can be dropped and other members added.

UNRECOVERABLE DATAFILE
You must specify UNRECOVERABLE DATAFILE if you have taken
the datafile offline with the database in ARCHIVELOG mode (that is, you specified ALTER DATABSE ... DATAFILE OFFLINE without the
DROP keyword), and if the unarchived log to be cleared is needed to recover the datafile before bringing it back online. In this case, you must drop the datafile and the entire tablespace once the CLEAR LOGFILE statement completes.

</quote>

So, that explains what it does. As to when you would use it (with extreme caution)--

o you might use it in some cases to downgrade a database if the version you are downgrading from has changed the redo log format (hence making that incompatible with the prior release)

o If an online redo log file has been corrupted while the database is open, the 'alter database clear logfile' command can be used to clear the files without the database having to be shut down.

o Your redo log was "inadvertantly" dropped at the OS level.


Rating

  (15 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Status of log files

Anil Pant, December 08, 2003 - 2:54 am UTC

The v$logfile shows the following status :
Invalid - file is inaccessible.
Stale - contents are incomplete
Deleted - file is no longer used
Blank - file is in use.
Pls correct me if Im wrong.

Deleted means when we drop the logfile though the file is deleted physically for Oracle its deleted.
Blank is when LGWR is currently writing that logfile.
Also pls explain. Invalid and Stale. When is file inaccessible.

The v$log has following status :
Active / Inactive / Current / Clearing / Clearing_Current / Invalidated / Unused
Pls tell me what is the difference among Clearing, Clearing_Current and Unused ?

Am I correct if I say when COMMIT is done LGWR is invoked and it writes Redolog entries. Is there anything other than this happens ?Im speaking from Oracle 9 point of view.

Tom Kyte
December 08, 2003 - 6:42 am UTC

see
</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96536/ch399.htm#1117308 <code>

the v$ tables are documented in that guide, that is specifically for v$log

when you commit, lgwr flushes the redo log buffer and returns a status back to you (success or failure)

LogFile Status - DELETED

Jayesh, April 16, 2004 - 12:40 pm UTC

When do we get the status of a log file as deleted? CAn u give the scenario?

LogFile

Jayesh, April 24, 2004 - 3:58 am UTC

When do we get the status of a log file as deleted? CAn u give the scenario?

Tom Kyte
April 26, 2004 - 5:02 am UTC

no, i cannot. never have seen it, not able to get it "to happen". It might be there for legacy reasons.

when it is used

Reader, June 07, 2004 - 9:43 pm UTC

alter database clear logfile group 1

when I issue this command it tells me that the logfile is required for crash recovery and cannot be cleared.I wish that it should not be used for crash recovery because of corrupt blocks.

Is there a way to forcibly clear a logfile .
Or else when it can be used.


Thanks


Tom Kyte
June 08, 2004 - 8:07 am UTC 

you cannot wish anything away.  It is saying "this redo log is protecting blocks that are in the cache"

that logfile might be the current logfile (cannot clear that)
that logfile might contain redo for dirty blocks in the buffer cache.


Consider:

ops$tkyte@ORA9IR2> select * from v$log;
 
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
         1          1        333  104857600          1 NO  CURRENT
   8.2047E+12 07-JUN-04
 
         3          1        332  104857600          1 NO  INACTIVE
   8.2047E+12 07-JUN-04
 
 
ops$tkyte@ORA9IR2> alter database clear logfile group 1;
alter database clear logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of thread 1
ORA-00312: online log 1 thread 1: '/home/ora9ir2/oradata/ora9ir2/redo01.log'
 
<b>there we cannot clear it because it is current, so we make it not be current:</b>
 
ops$tkyte@ORA9IR2> alter system switch logfile;
 
System altered.
 
ops$tkyte@ORA9IR2> alter database clear logfile group 1;
alter database clear logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of thread 1
ORA-00312: online log 1 thread 1: '/home/ora9ir2/oradata/ora9ir2/redo01.log'
 
<b>now we cannot clear it because of dirty blocks in the cache, so we get rid of them</b>
 
ops$tkyte@ORA9IR2> alter system checkpoint
  2  /
 
System altered.
 
ops$tkyte@ORA9IR2> alter database clear logfile group 1;
 
Database altered.
 
ops$tkyte@ORA9IR2>
<b>and we can clear it.</b>


but I'm concerned here -- what blocks are corrupt and why do you believe that clearing this redo log file would fix it?

log status during transaction

Shruti, August 31, 2004 - 8:19 am UTC 

I would like to know about the various statuses that redo logfile can have at any time during transaction

I read that the log status
UNUSED - is asigned to the online redo log which has never been written to, 
i.e. its the state of a redo log that was just added, or just after a 
RESETLOGS when it is not the current redo log.

Does this mean when a log switch occurs it is treated similarly as RESETLOGS command?

INVALIDATED - Archived the current redo log without a log switch

I'm unable to understand this state. Without a log switch how can the logfile be archived

Also intriguing is the scenario which occured during a test insert operation (inserted 65536 ercords into a table)
created table as:
create table curshr (id varchar2(10), direction varchar2(5), datetime timestamp default systimestamp);
inserted records as 
insert into curshr(id, direction) values ('1', 'east');
insert into curshr(id, direction) values ('2', 'west');
insert into curshr(id, direction) values ('3', 'north');
insert into curshr(id, direction) values ('4', 'south');
insert into curshr(id, direction) (select id+4, direction from curshr);
... so on till ... 65536 rows created.
See below, these are the three outputs of v$log
1) before the insert was executed in a seperate window
2) during the insertion was ongoing
3) after the insertion completed

The second output shows the status as UNUSED and INVALIDATED with no logfile as CURRENT
Can we explain this?

The output of v$log is as shown below for the three timings

SQL> / (BEFORE INSERT)

    GROUP#    THREAD#  SEQUENCE# ARCHIV STATUS       FIRST_CHANGE# 
---------- ---------- ---------- ------ ------------ ------------- 
         1          1        121 NO     ACTIVE       755982        
         2          1        122 NO     CURRENT      756158        
         3          1        120 YES    ACTIVE       755802        

SQL> / (DURING INSERT)

    GROUP#    THREAD#  SEQUENCE# ARCHIV STATUS       FIRST_CHANGE# 
---------- ---------- ---------- ------ ------------ ------------- 
         1          1          0 YES    UNUSED       0             
         2          1        122 NO     ACTIVE       756158        
         3          1        123 NO     INVALIDATED  756320        


SQL> / (AFTER INSERT)

    GROUP#    THREAD#  SEQUENCE# ARCHIV STATUS       FIRST_CHANGE# 
---------- ---------- ---------- ------ ------------ ------------- 
         1          1        124 YES    ACTIVE       756466        
         2          1        125 NO     CURRENT      756607        
         3          1        123 YES    INACTIVE     756320

Tom Kyte
August 31, 2004 - 1:08 pm UTC 

it'll be normal for them to be either active, current or inactive. 

a log switch is not at all anything like or related to a resetlogs -- that is what you do after an incomplete recovery.
                                                                                
                                                                                
forget about "transactions" for a moment, they are events that just are
always happening (even if your database is "idle", it is never idle
backgrounds are always doing things)
                                                                                
it would be normal for a redo log file to be
                                                                                
active -- has stuff in it that is needed for instance recovery if we crashed
right now.
                                                                                
current -- where stuff is being written to right now


                                                                                
inactive -- was used at some point, but isn't needed for instance recovery
anymore (all blocks it was protecting have been flushed to disk by a
checkpoint) and isn't the current redo log file...
                                                                                
                                                                                
                                                                                
All other status would be "not normal" and after some period of activity
would become "normal"



I can only reproduce your findings with someone running an 

alter system archive log current;

in another session -- if I don't, i get current, inactive...

My test was:

ops$tkyte@ORA9IR2> create table t ( dt timestamp, group# int, status varchar2(40) );
 
Table created.
 
ops$tkyte@ORA9IR2> create table stop(x int);
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2          l_n number;
  3  begin
  4          loop
  5                  insert into t select systimestamp, group#, status from v$log;
  6                  commit;
  7                  select count(*) into l_n from stop;
  8                  exit when l_n > 0;
  9          end loop;
 10  end;
 11  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select status, count(*) from t group by status;
 
STATUS                                     COUNT(*)
---------------------------------------- ----------
ACTIVE                                         7680
CURRENT                                       20381
INACTIVE                                      12701
INVALIDATED                                       2
UNUSED                                            2
 


If I just let that run for a while, nothing but active/current/inactive.  If I do this however:

ops$tkyte@ORA9IR2> alter system archive log current;
 
System altered.
 
ops$tkyte@ORA9IR2> /
 
System altered.
 
ops$tkyte@ORA9IR2> insert into stop values ( 1 );
 
1 row created.
 
ops$tkyte@ORA9IR2> commit;
 
Commit complete.
 


So, maybe you were doing that in another session -- and then the definition of "invalidated" says it all...

Thanks !!

Shruti, September 01, 2004 - 12:47 am UTC

Thanks a lot for this reply.

I can understand that this was difficult to simulate.

I was not doing
alter system archive log current;
rather I was just inserting into a table in one window and querying the v$log in another.
Maybe there was just a miniscule chance of getting such an output during a logswitch occurring through a transaction.

Since the insert of 65536 rows took time I was able to peek into v$log which must have been in a highly transitional state.
I forgot to tell you that I had not committed in several of previous transactions when this v$log output was seen.
Could this point to a reason?

You could try to insert as:
insert into tablename select * from tablename;
to quickly reach the higher number of inserts which would be slow enough to let you peek into the v$log in second window.

With Regards,
./shruti


Tom Kyte
September 01, 2004 - 8:00 am UTC

INVALIDATED - Archived the current redo log without a log switch


I don't think so -- are you on a single user system

Its a test Database

Shruti, September 02, 2004 - 12:38 am UTC

This is a test database for exploring 10g.
It was only me who was connected to this databse when I above observations were seen. Rather its me only who works on this windoze 2K box.

Does this affect the Redo Log Status .... !!! ??

With Regards,
./shruti

Tom Kyte
September 02, 2004 - 8:05 am UTC

it should not, i do not observe the same "reaction"

Redo Log Error...

A reader, September 10, 2004 - 6:33 pm UTC

Hi Tom,
Today, when I tried to start my Oracle 9.2 database on NT, I got error:
"ORA-00333 redo log read error block 8194 count 8192",
and databse is not opened.
Could you please help me what to do? it is just a sandpit database to test few cases.
With Kind Regards

Tom Kyte
September 10, 2004 - 7:46 pm UTC

[tkyte@tkyte-pc-isdn tkyte]$ oerr ora 333
00333, 00000, "redo log read error block %s count %s"
// *Cause: An IO error occurred while reading the log described in the
// accompanying error.
// *Action: Restore accessibility to file, or get another copy of the file.


i don't see the entire stack, so cannot really comment further.

Ops...

A reader, September 10, 2004 - 11:21 pm UTC

Hi Tom,
This is not really helpful. Normally, you are ***always*** helpful or ask for more information.
I hope you are not in bad mood...
Regards

Tom Kyte
September 11, 2004 - 8:16 am UTC

which part of "i don't see the error stack" wasn't clear?


do you see in the error message where it says:

An IO error occurred while reading the log described
in the accompanying error.

well, what what that accompanying error

and truth be told, this Cause/Action thing does sort of have "advice" in it.

From where do I start?

A reader, September 11, 2004 - 4:58 pm UTC

Hi Tom,
Thanks for your un-forgetable help. Please let me know, from where should I start to resolve this error?
Kind Regards

Tom Kyte
September 11, 2004 - 6:04 pm UTC

by giving us a cut and paste of the entire error stack?

or by following the "action" in the error message above...

Here the log from alert and trace

A reader, September 12, 2004 - 5:59 am UTC 

Hi Tom,
The log files are as below:

SQL> alter database open
  2  ;
alter database open
*
ERROR at line 1:
ORA-00333: redo log read error block 8194 count 8192


SQL> 

Oracle Alert:
Sun Sep 12 19:12:58 2004
Errors in file f:\oracle\admin\test\udump\test_ora_489.trc:
ORA-00333: redo log read error block 8194 count 8192
ORA-00312: online log 1 thread 1: 'E:\ORACLE9I\ORADATA\TEST\REDO01.LOG'
ORA-27091: skgfqio: unable to queue I/O
ORA-27070: skgfdisp: async read/write failed
OSD-04006: ReadFile() failure, unable to read from file
O/S-Error: (OS 23) Data error (cyclic redundancy check)

Sun Sep 12 19:12:58 2004
ORA-333 signalled during: alter database test open...
__________________________________________________
trace file:
Dump file f:\oracle\admin\test\udump\test_ora_489.trc
Sun Sep 12 19:12:47 2004
ORACLE V9.2.0.1.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows NT Version 4.0 Service Pack 6, CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Windows NT Version 4.0 Service Pack 6, CPU type 586
Instance name: test

Redo thread mounted by this instance: 1

Oracle process number: 12

Windows thread id: 489, image: ORACLE.EXE


*** SESSION ID:(9.3) 2004-09-12 19:12:47.000
ORA-00333: redo log read error block 8194 count 8192
ORA-00312: online log 1 thread 1: 'E:\ORACLE9I\ORADATA\test\REDO01.LOG'
ORA-27070: skgfdisp: async read/write failed
OSD-04016: Error queuing an asynchronous I/O request.
O/S-Error: (OS 23) Data error (cyclic redundancy check)
ORA-00333: redo log read error block 8194 count 8192
ORA-00312: online log 1 thread 1: 'E:\ORACLE9I\ORADATA\test\REDO01.LOG'
ORA-27091: skgfqio: unable to queue I/O
ORA-27070: skgfdisp: async read/write failed
OSD-04006: ReadFile() failure, unable to read from file
O/S-Error: (OS 23) Data error (cyclic redundancy check)
ORA-00333: redo log read error block 8194 count 8192
ORA-00312: online log 1 thread 1: 'E:\ORACLE9I\ORADATA\test\REDO01.LOG'
ORA-27091: skgfqio: unable to queue I/O
ORA-27070: skgfdisp: async read/write failed
OSD-04006: ReadFile() failure, unable to read from file
O/S-Error: (OS 23) Data error (cyclic redundancy check)
ORA-00333: redo log read error block 8194 count 8192
ORA-00312: online log 1 thread 1: 'E:\ORACLE9I\ORADATA\test\REDO01.LOG'
ORA-27091: skgfqio: unable to queue I/O
ORA-27070: skgfdisp: async read/write failed
OSD-04006: ReadFile() failure, unable to read from file
O/S-Error: (OS 23) Data error (cyclic redundancy check)
_____________
Regards

Tom Kyte
September 12, 2004 - 11:27 am UTC

OSD-04006: ReadFile() failure, unable to read from file
O/S-Error: (OS 23) Data error (cyclic redundancy check)


You have a bad disk.


Unless you have mirrored these online redo log files (if you did, copy good over bad)

Or you have a good backup that you can do a cancel based recovery on

You probably will be writing this test database "off" and getting rid of your current disk and replacing it with a functional one (you'll be doing that last part regardless).



This is why mirroring redo logs on multiple separate devices, doing hot backups, is relevant. You need to perform MEDIA recovery but probably don't have the infrastructure in place to do that.

ACTIVE REDO LOG

Deepak, May 15, 2007 - 9:12 am UTC

Hi Tom,

As always I need your help to get my concepts right...

You have mentioned in one of your replies that in this thread
"active -- has stuff in it that is needed for instance recovery if we crashed right now."


I have the following question on the above statement....

When one log group is in ACTIVE state there must be a CURRENT log group as well. When the last log switch had happened the system might have done a check point, therefore all the dirty blocks from the db cache would have been flushed to the datafile.

Then why do we need the ACTIVE redo log group for instance recovery?



Tom Kyte
May 15, 2007 - 8:56 pm UTC

therefore all dirty blocks would be in the PROCESS of being written to disk since a checkpoint doesn't happen instantaneously, but can take a rather long time.

ALTER DATABASE CLEAR LOGFILE UNARCHIVED GROUP

Aimee, June 06, 2007 - 7:13 pm UTC

Hi Tom,

I am in the process of forming our recovery procedure for 10g database and one of them is "recovering from lossing an inactiver but not-yet-archived redo".

Reference Oracle Doc: http://download-west.oracle.com/docs/cd/B19306_01/backup.102/b14191/recoscen008.htm#sthref1906

However, I encountered ORA-02236: invalid file name when issued command ALTER DATABASE CLEAR LOGFILE UNARCHIVED GROUP 1;

The following are the steps I went through in my test case:

1. DB is open and in read-write state

2. Check the status of the redo files
SQL> SELECT GROUP#, MEMBERS, STATUS, ARCHIVED FROM V$LOG;

GROUP# MEMBERS STATUS ARC
---------- ---------- ---------------- ---
1 2 INACTIVE NO
2 2 INACTIVE NO
3 2 CURRENT NO

3. remove all member of redo group 1 to simulate the lost of all inactive but not-yet-archived redo group.
SQL> SELECT GROUP#, STATUS, MEMBER FROM V$LOGFILE;

GROUP# STATUS MEMBER
---------- ------- ------------------------------------------------------------
1 /u01/oradata/SOLO_TST/redoSOLO_TST01a.log
1 /u01/oradata/SOLO_TST/redoSOLO_TST01b.log
2 /u01/oradata/SOLO_TST/redoSOLO_TST02a.log
2 /u01/oradata/SOLO_TST/redoSOLO_TST02b.log
3 /u01/oradata/SOLO_TST/redoSOLO_TST03a.log
3 /u01/oradata/SOLO_TST/redoSOLO_TST03b.log

6 rows selected.


SQL> ! rm /u01/oradata/SOLO_TST/redoSOLO_TST01?.log
SQL> ! ls /u01/oradata/SOLO_TST/redoSOLO_TST01?.log
ls: /u01/oradata/SOLO_TST/redoSOLO_TST01?.log: No such file or directory

4. try to clear the log:
SQL> ALTER DATABASE CLEAR LOGFILE UNARCHIVED GROUP 1;
ALTER DATABASE CLEAR LOGFILE UNARCHIVED GROUP 1
*
ERROR at line 1:
ORA-02236: invalid file name

5. since, the db is open and the doc mentioned about performing clear log in mounted mode with "startup mount", so I did a shutdown abort and startup mount
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 138413648 bytes
Database Buffers 142606336 bytes
Redo Buffers 2973696 bytes
Database mounted.

6. then try to clear logfile unarchived and then again the command failed.

SQL> ALTER DATABASE CLEAR LOGFILE UNARCHIVED GROUP 1;
ALTER DATABASE CLEAR LOGFILE UNARCHIVED GROUP 1
*
ERROR at line 1:
ORA-02236: invalid file name


7. and confirmed that none of the datafiles is offline:
SQL> select FILE#,status from v$datafile;

FILE# STATUS
---------- -------
1 SYSTEM
2 ONLINE
3 ONLINE
4 ONLINE
5 ONLINE
6 ONLINE
7 ONLINE
8 ONLINE
9 ONLINE
10 ONLINE
11 ONLINE

FILE# STATUS
---------- -------
12 ONLINE
13 ONLINE
14 ONLINE
15 ONLINE
16 ONLINE

16 rows selected.


Have I done anything wrong?

I am using 10.2.0.1 standard edition standalone database.

Thanks for your help in advance,
Aimee


Tom Kyte
June 07, 2007 - 2:38 pm UTC

clear logfile is a command to clear an existing logfile.

the log files no longer exist

hence, there is nothing to clear.


you are looking for resetlogs.

ops$ora10gr2%ORA10GR2> SELECT GROUP#, MEMBERS, STATUS, ARCHIVED FROM V$LOG;

    GROUP#    MEMBERS STATUS           ARC
---------- ---------- ---------------- ---
         1          1 INACTIVE         NO
         2          1 CURRENT          NO
         3          1 INACTIVE         NO

ops$ora10gr2%ORA10GR2>
ops$ora10gr2%ORA10GR2> SELECT GROUP#, STATUS, MEMBER FROM V$LOGFILE;

    GROUP# STATUS
---------- -------
MEMBER
-------------------------------------------------------------------------------
         3 STALE
/home/ora10gr2/oradata/ora10gr2/redo03.log

         2
/home/ora10gr2/oradata/ora10gr2/redo02.log

         1
/home/ora10gr2/oradata/ora10gr2/redo01.log


ops$ora10gr2%ORA10GR2> !rm /home/ora10gr2/oradata/ora10gr2/redo03.log

ops$ora10gr2%ORA10GR2> !rm /home/ora10gr2/oradata/ora10gr2/redo01.log

ops$ora10gr2%ORA10GR2> !ls /home/ora10gr2/oradata/ora10gr2/redo0*.log
/home/ora10gr2/oradata/ora10gr2/redo02.log


ops$ora10gr2%ORA10GR2> connect / as sysdba
Connected.
sys%ORA10GR2> shutdown abort;
ORACLE instance shut down.
sys%ORA10GR2> startup
ORACLE instance started.

Total System Global Area  603979776 bytes
Fixed Size                  1262248 bytes
Variable Size             448793944 bytes
Database Buffers          150994944 bytes
Redo Buffers                2928640 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/home/ora10gr2/oradata/ora10gr2/redo01.log'


sys%ORA10GR2> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery


sys%ORA10GR2> recover until cancel;
Media recovery complete.
sys%ORA10GR2> alter database open resetlogs;

Database altered.

sys%ORA10GR2> !ls /home/ora10gr2/oradata/ora10gr2/redo0*.log
/home/ora10gr2/oradata/ora10gr2/redo01.log  /home/ora10gr2/oradata/ora10gr2/redo02.log  /home/ora10gr2/oradata/ora10gr2/redo03.log


CLEAR LOGFILE (UNARCHIVED) commands

Aimee Lin, June 08, 2007 - 12:33 am UTC

Thanks very much for the respose.

Quoting from your previous comment "If an online redo log file has been corrupted while the database is open, the 'alter
database clear logfile' command can be used to clear the files without the database having to be shut down."

As the purpose of clear logfile is to fix "corrupted/damaged" redo log group without having to shutdown database, I continue to do more testing to ensure I understand the use of clear logfile command:

Test Case 2: Repair the corrupted online redo log group that's archived and inactive

1. Targetting group 1 in this test case
SQL> SELECT GROUP#, MEMBERS, STATUS, ARCHIVED FROM V$LOG;

GROUP# MEMBERS STATUS ARC
---------- ---------- ---------------- ---
1 2 INACTIVE YES
2 2 CURRENT NO
3 2 INACTIVE YES

SQL> SELECT GROUP#, STATUS, MEMBER FROM V$LOGFILE;

GROUP# STATUS MEMBER
---------- ------- ------------------------------------------------------------
1 /u01/oradata/SOLODBA/redoSOLODBA01a.log
1 /u01/oradata/SOLODBA/redoSOLODBA01b.log
2 /u01/oradata/SOLODBA/redoSOLODBA02a.log
2 /u01/oradata/SOLODBA/redoSOLODBA02b.log
3 /u01/oradata/SOLODBA/redoSOLODBA03a.log
3 /u01/oradata/SOLODBA/redoSOLODBA03b.log

2. corrupt all members of redo group1 (/u01/oradata/SOLODBA/redoSOLODBA01a.log & /u01/oradata/SOLODBA/redoSOLODBA01b.log) by using vi to add a few characters
3. use clear logfile to fix damaged redo log file as according to oracle doc (Clearing Inactive, Archived Redo) -- http://download-west.oracle.com/docs/cd/B19306_01/backup.102/b14191/recoscen008.htm#sthref1906
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;

Database altered.

4.
SQL> SELECT GROUP#, MEMBERS, STATUS, ARCHIVED FROM V$LOG;

GROUP# MEMBERS STATUS ARC
---------- ---------- ---------------- ---
1 2 UNUSED YES
2 2 CURRENT NO
3 2 INACTIVE YES

SQL> alter system switch logfile;

System altered.

SQL> SELECT GROUP#, MEMBERS, STATUS, ARCHIVED FROM V$LOG;

GROUP# MEMBERS STATUS ARC
---------- ---------- ---------------- ---
1 2 CURRENT NO
2 2 ACTIVE YES
3 2 INACTIVE YES
SQL> alter system switch logfile;

at this point, I start seeing the following errors in the alert log:
=====================================================================
Completed: ALTER DATABASE CLEAR LOGFILE GROUP 1
Fri Jun 8 14:56:36 2007
Thread 1 advanced to log sequence 5
Current log# 1 seq# 5 mem# 0: /u01/oradata/SOLODBA/redoSOLODBA01a.log
Current log# 1 seq# 5 mem# 1: /u01/oradata/SOLODBA/redoSOLODBA01b.log
Fri Jun 8 14:57:03 2007
Thread 1 advanced to log sequence 6
Current log# 3 seq# 6 mem# 0: /u01/oradata/SOLODBA/redoSOLODBA03a.log
Current log# 3 seq# 6 mem# 1: /u01/oradata/SOLODBA/redoSOLODBA03b.log
Fri Jun 8 14:57:03 2007
Errors in file /opt/oracle/admin/SOLODBA/bdump/solodba_arc0_13023.trc:
ORA-00314: log 1 of thread 1, expected sequence# 5 doesn't match 0
ORA-00312: online log 1 thread 1: '/u01/oradata/SOLODBA/redoSOLODBA01b.log'
ORA-00314: log 1 of thread 1, expected sequence# 5 doesn't match 0
ORA-00312: online log 1 thread 1: '/u01/oradata/SOLODBA/redoSOLODBA01a.log'
Fri Jun 8 14:57:03 2007
Errors in file /opt/oracle/admin/SOLODBA/bdump/solodba_arc0_13023.trc:
ORA-00314: log 1 of thread 1, expected sequence# 5 doesn't match 0
ORA-00312: online log 1 thread 1: '/u01/oradata/SOLODBA/redoSOLODBA01b.log'
ORA-00314: log 1 of thread 1, expected sequence# 5 doesn't match 0
ORA-00312: online log 1 thread 1: '/u01/oradata/SOLODBA/redoSOLODBA01a.log'
Fri Jun 8 14:57:03 2007
ARC0: Failed to archive thread 1 sequence 5 (0)
ARCH: Archival stopped, error occurred. Will continue retrying
Fri Jun 8 14:57:03 2007
ORACLE Instance SOLODBA - Archival Error
Fri Jun 8 14:57:03 2007
ORA-16038: log 1 sequence# 5 cannot be archived
ORA-00312: online log 1 thread 1: '/u01/oradata/SOLODBA/redoSOLODBA01a.log'
ORA-00312: online log 1 thread 1: '/u01/oradata/SOLODBA/redoSOLODBA01b.log'
=====================================================================

At the end, I had to drop the log group to resolve the above siuation.

I don't think it's correct that I am getting the above error messages after clear logfile -- although drop logfile is okay at this case since the logfile group is archived.
Could you please demostrate the correct use of clear log file (ALTER DATABASE CLEAR LOGFILE UNARCHIVED GROUP <group number> and ALTER DATABASE CLEAR LOGFILE GROUP <group number>) if possible as you can see I didn't any luck from the above two test cases including the one in the post? thanks heaps.

So am I correct by saying the following:

- inactive archived redo group:
drop damaged or missing log files to resolve the issue (provided we have more than two redo groups)

or use clear logfile -- look forward to see your example :)

- inactive unarchived redo group:

have to use clear logfile unarchived to resolve the issue -- look forward to see your example :)

Thanks for your help in advance,
Aimee




Tom Kyte
June 09, 2007 - 10:22 am UTC

the alert is telling you you have utter garbage and need to backup.

if you actually get a corrupt online redo log, you have some huge problem (media related typically) - you'll be moving those log files in all probability. Corrupting them with vi isn't something that is going to happen naturally.


A reader, March 24, 2009 - 8:20 am UTC

03/24/2009 05:06 PM <DIR> .
03/24/2009 05:06 PM <DIR> ..
03/24/2009 05:03 PM 7,061,504 CONTROL01.CTL
03/24/2009 05:03 PM 7,061,504 CONTROL02.CTL
03/24/2009 05:03 PM 7,061,504 CONTROL03.CTL
03/24/2009 05:02 PM 2,147,491,840 DSETHI.DBF
03/24/2009 05:01 PM 7,872,000 REDO00
03/24/2009 04:42 PM 52,429,312 REDO01.LOG
03/24/2009 04:42 PM 52,429,312 REDO02.LOG
03/24/2009 04:42 PM 52,429,312 REDO03.LOG
03/24/2009 05:02 PM 241,180,672 SYSAUX01.DBF
03/24/2009 05:02 PM 492,838,912 SYSTEM01.DBF
03/24/2009 04:41 PM 20,979,712 TEMP01.DBF
03/24/2009 05:02 PM 26,222,592 UNDOTBS01.DBF
03/24/2009 05:02 PM 5,251,072 USERS01.DBF
13 File(s) 3,120,309,248 bytes
2 Dir(s) 22,661,611,520 bytes free
Tom Kyte
March 29, 2009 - 10:29 am UTC

very pretty...

but...

so? why did you put that there?
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.