Re: Oracle Doc Error

danielroy10junk_at_hotmail.com (Daniel Roy) wrote in message news:<3722db.0401211957.eb6c17d_at_posting.google.com>...
> Guys,
> I was going through the chapter 7 of the Admin guide 9.2 on
> tahiti.oracle.com, about the redo logs. On that page, Oracle states
> that "If you need to drop the current group, first force a log switch
> to occur.". This looked fishy to me, so I decided to test it:
>
> SQL> select * from v$log;
>
> GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
> ---------- ---------- ---------- ---------- ---------- ---
> ----------------
> FIRST_CHANGE# FIRST_TIM
> ------------- ---------
> 1 1 181 104857600 1 NO INACTIVE
> 7112405 19-JAN-04
>
> 2 1 182 104857600 1 NO CURRENT
> 7135173 21-JAN-04
>
> 3 1 180 104857600 1 NO INACTIVE
> 7112401 19-JAN-04
>
>
> 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 181 104857600 1 NO INACTIVE
> 7112405 19-JAN-04
>
> 2 1 182 104857600 1 NO ACTIVE
> 7135173 21-JAN-04
>
> 3 1 183 104857600 1 NO CURRENT
> 7135646 21-JAN-04
>
>
> SQL> alter database drop logfile group 2;
> alter database drop logfile group 2
> *
> ERROR at line 1:
> ORA-01624: log 2 needed for crash recovery of thread 1
> ORA-00312: online log 2 thread 1:
> 'C:\ORA9201\ORADATA\OEMREP\REDO02.LOG'
>
>
> SQL> alter system checkpoint;
>
> System altered.
>
> SQL> alter database drop logfile group 2;
>
> Database altered.
>
> As you can see, it seems to me that before being able to drop a log
> group, a log switch must of course have occurred. But a checkpoint up
> to at least the end of the log group must also have occurred! Is my
> counter-example correct, or I'm missing something?
>
> Daniel

I believe that "alter system switch logfile;" returns control to the calling process/thread immediately.

Try issuing the checkpoint first and repeat.

I believe that "alter system checkpoint;" does not return control to the calling process/thread immediately.

That would describe the behavior you are experiencing.

See, the value in the column "STATUS" is active. That implies that the log is still being archived. Check in v$archived_log for when that log has been archived. (you may want to set your nls_date_format to something that has a resolution in seconds, such as "alter session set nls_date_format='RRRRMMDD HH24:MI:SS';".

Multiplex your redo logs, so that if one file is hosed, your stream of archived redo logs is not interrupted. I can't tell if the log groups have more than one member, as you only displayed the output from v$log, not v$logfile.

hth.

Pd Received on Thu Jan 22 2004 - 00:32:12 CST