Active status of redo log

ACTIVE/INACTIVE is about having data changes (dirty buffers) protected by redo in that particular redolog file still not flushed to disk.
Whether that redolog has been archived (copied away) or not, is a different matter, they are different things. Note that Oracle doesn’t allow LGWR to overwrite a redolog file which is ACTIVE or hasn’t been archived yet (in case when having the database in archivelog mode).
Some more details:

ACTIVE status means that there are still some buffers in buffer cache which haven’t been flushed to disk since a modification done when that log was CURRENT. A more formal explanation would be that the checkpoint position (the redo byte address (RBA) of the redo thread up to which all the corresponding dirty buffers have been flushed) has not passed beyond that logs end yet, thus some blocks of that redo log would be still needed for recovery in case of an instance crash.

Whether the log is already archived or not doesn’t matter here, when the instance crashes, Oracle needs some blocks from that redolog. Archivelog is just an archived copy of the redolog, so you could use either the online or achive log for the recovery, it’s the same data in there (Oracle reads the log/archivelog file header when it tries to use it for recovery and validates whether it contains the changes (RBA range) in it what it needs).

During instance startup after instance crash, Oracle uses controlfiles to check where the incremental checkpoint position (RBA), opens up the corresponding (active) redolog and starts rolling forward from that RBA stated in the controlfile. So, it may apply only a part of that logfile if the controlfile (with the incremental checkpoint position recorded in it) is available.

Should the controlfiles go missing (or you recover with using backup controlfile option for some reason), then the incremental checkpoint progress records would not be used and Oracle reads the starting point of the recovery from datafile headers instead.

Should an ACTIVE redolog go missing, but you have a successfully archived copy of it, you can use that for recovering the database instead.

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

 #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...)


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

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

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

now it is not only not needed for crash recovery, it is also archived

Reference:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:69959697198021
https://community.oracle.com/thread/1108495?start=0&tstart=0

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s