Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Satheesh Babu.S.

Asked: August 12, 2006 - 9:56 pm UTC

Last updated: August 16, 2006 - 8:21 am UTC

Version: 9i

Viewed 10K+ times! This question is

You Asked

Tom,
What are all the reason for redolog file status to remain "ACTIVE"? For eg., if the redolog file is not archived yet, if the checkpoint in respect to that redo log is not completed, apart from this anything else you can think off?
Also at what point of time redolog status will change to "INACTIVE", i believe it is immediately after the redo log is archived. Am i correct? Is there anything else oracle checks after it is archived and before the status become "INACTIVE".

Regards,
Satheesh Babu.S

and Tom said...

Actually - you listed too many reasons.

</code> http://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_1150.htm#sthref3725 <code>

the redo is needed for crash recovery - that is the only reason really. Archival doesn't come into play here.

Rating

  (4 ratings)

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

Comments

transaction-consistent state

Arindam Mukherjee, August 14, 2006 - 9:43 am UTC

Respected Mr. Tom,

On reading the above question and answer, I could not resist myself to ask you one most common thing that still scares me and makes me feel ashamed.

Well, after commit, SCN is generated and it is stored in generally in On Line Redo Log file. When DBWR starts writing, all data file headers in that DATABASE have same SCN i.e. transaction-consistent state. This is also explained as - all read/write datafiles and control files are checkpointed with respect to the same system change number (SCN).

It’s my first question – What is Checkpoint? My answer is - The event when all modified database buffers in the SGA are written to the datafiles by DBWn.

If my answer is okay, then the next part leaves me in a daze.

Quote from Oracle 9i Backup and Recovery Concepts - "Oracle makes the control files and datafiles consistent to the same SCN during a database checkpoint. The only tablespaces in a consistent backup that are allowed to have older SCNs are read-only and offline normal tablespaces, which are still consistent with the other datafiles in the backup because no changes have been made to them."

In addition to when I bring any tablespace online that was taken out offline normal, the database is still in transaction-consistent state

This is my question - how it is possible when some datafiles have current SCN and some have Old SCN but we say the database is in “transaction-consistent state”?


Tom Kyte
August 14, 2006 - 11:49 am UTC

well, it is not "when dbwr" starts writing - and it is not an atomic operation (to update all data file headers) - and the SCN recorded there is sort of a worst case (some of the blocks in the file may be much "newer" than that)

A checkpoint might not hit ALL dirty buffers in the cache (else a checkpoint would have to STOP everything going on, we'd never be able to get all of the blocks out if you keep creating MORE)

Think of a checkpoint like a line drawn in the sand. It is a place, a marker. We we have checkpointed up to "X" (the line in the sand) we know we don't need anything (redo) on the other side of that line to perform crash recovery.

Think of checkpoint like you would a staff meeting. Everyone is brought relatively up to date (work continues while you meet though! so by the time you leave this meeting, your information is stale already).

A checkpoint is simply "a marker" - "a line drawn in the sand over which you do not have to cross to get back in business (after a crash)"


don't over analyze this too much - the database is constantly in-flux, the files are never really "consistent", they are by design fuzzy always while the database is open. Always fuzzy - but caught up to "at least this time (scn)".

the checkpoint marker is a "worst case" line, there will be block images in files that post date this line.

Please clarify...

Satheesh Babu.S, August 15, 2006 - 1:25 am UTC

Tom,
Let us assume, checkpoint on a redo log file is completed. But however archive of this redo log is not yet completed. In this scenario, what will be status of redo log? Will be "ACTIVE" or "INACTIVE"

Regards,
Satheesh Babu.S

Tom Kyte
August 15, 2006 - 7:53 am UTC 

there is an "archived" column in that view.

active means "we need it for crash recovery"

if the checkpoint signaled by the switch logfile has completed (there is no such thing really as a "checkpoint ON a redo log file") we no longer need that log file for crash recovery.

that is independent of whether it was archived or not yet.


ops$tkyte%ORA9IR2> select group#, thread#, sequence#, status, archived from v$log;

    GROUP#    THREAD#  SEQUENCE# STATUS           ARC
---------- ---------- ---------- ---------------- ---
         1          1         17 INACTIVE         YES
         2          1         18 INACTIVE         NO
         3          1         19 CURRENT          NO

ops$tkyte%ORA9IR2> alter system switch logfile;

System altered.

ops$tkyte%ORA9IR2> select group#, thread#, sequence#, status, archived from v$log;

    GROUP#    THREAD#  SEQUENCE# STATUS           ARC
---------- ---------- ---------- ---------------- ---
         1          1         20 CURRENT          NO
         2          1         18 INACTIVE         NO
         3          1         19 ACTIVE           NO

<b> #19 is 'active', needed for crash recovery, it is not yet archived (i have log archive start = false and am using 9i so it still has meaning...)
</b>

ops$tkyte%ORA9IR2> alter system checkpoint;

System altered.

ops$tkyte%ORA9IR2> select group#, thread#, sequence#, status, archived from v$log;

    GROUP#    THREAD#  SEQUENCE# STATUS           ARC
---------- ---------- ---------- ---------------- ---
         1          1         20 CURRENT          NO
         2          1         18 INACTIVE         NO
         3          1         19 INACTIVE         NO

<b> #19 is no longer active since we checkpointed and waited for the checkpoint to complete.  It is still not archived however</b>

ops$tkyte%ORA9IR2> alter system archive log all;

System altered.

ops$tkyte%ORA9IR2> select group#, thread#, sequence#, status, archived from v$log;

    GROUP#    THREAD#  SEQUENCE# STATUS           ARC
---------- ---------- ---------- ---------------- ---
         1          1         20 CURRENT          NO
         2          1         18 INACTIVE         YES
         3          1         19 INACTIVE         YES

<b>now it is not only not needed for crash recovery, it is also archived</b>

Great!!!

Satheesh Babu.S, August 15, 2006 - 10:44 pm UTC

Thanks Tom. So status becomes "INACTIVE" even if it is not archived. But oracle allows us to drop the redolog which is "INACTIVE". Does this not mean, oracle allows us to delete a redo log file which is not archived yet? There by breaking the chain.

Regards,
Satheesh Babu.S

Tom Kyte
August 16, 2006 - 8:21 am UTC 

give it a try, see what happens.


ops$tkyte%ORA9IR2> alter database drop logfile '/tmp/foo.log';
alter database drop logfile '/tmp/foo.log'
*
ERROR at line 1:
ORA-00350: log 4 of thread 1 needs to be archived
ORA-00312: online log 4 thread 1: '/tmp/foo.log'

good for basic understanding all the stuff

blaimschein, November 18, 2006 - 7:57 pm UTC

good explanation how archiving works.


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.