REDO Log and Checkpoint

I was reading about the data backup and recovery in Oracle DB and in the end , was left with all confusion. So finally decided to start from reading checkpoint/logfile concepts so that it helps in better understanding .

So you can consider this post as :-
Backup and Recovery : Concepts Part 1

REDO LOGFILE
—————–
The Oracle server maintains the redo Oracle log files to minimize the loss of data in the Database in case of an uncontrolled shutdown.

Online redo Oracle log files are filled with redo records. A redo record, also called a redo entry, is made up of a group of change vectors, each of which is a description of a change made to a single block in the database.

For example, if you change a employee name in an employee table, you generate a redo record containing change vectors that describe changes to the data segment block for the table, the rollback segment data block, and the transaction table of the rollback segments.

Two views play an important part here namely , v$log and v$logfile.

v$logfile can be used to find the path of the redo log or their type (online/standby(used for standby database))
For more information on standby redo logs :-
http://www.pythian.com/news/581/oracle-standby-redo-logs/

 
SQL> select group#,type,member from v$logfile;

    GROUP# TYPE  MEMBER
--------------------------------------------------------------------------------
         3 ONLINE /scratch/aime1/app/aime1/oradata/orcl/redo03.log
         2 ONLINE /scratch/aime1/app/aime1/oradata/orcl/redo02.log
         1 ONLINE /scratch/aime1/app/aime1/oradata/orcl/redo01.log

v$log can be used to find out if your redo logs are archived or not with archived column . And also to check the status of the the log files . There are different log status , few of the important ones are:-

1. UNUSED – Online redo log has never been written to. This is the state of a redo log that was just added, or just after a RESETLOGS, when it is not the current redo log.
2. CURRENT – Current redo log. This implies that the redo log is active. The redo log could be open or closed.
3. ACTIVE – Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.
4. INACTIVE – Log is no longer needed for instance recovery. It may be in use for media recovery. It might or might not be archived.
5. INVALIDATED – Archived the current redo log without a log switch.

 
SQL> SELECT group#, sequence#, members, archived, status FROM v$log;

    GROUP#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
         1          7          1 NO  CURRENT
         2          5          1 NO  INACTIVE
         3          6          1 NO  INACTIVE

Also to check which archivelog/no archivelog mode , we can use

 
SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

————————————————————————————-

CHECKPOINT
—————-

A checkpoint performs the following three operations:

1. Every dirty block in the buffer cache is written to the data files. That is, it(DBWR) synchronizes the datablocks in the buffer cache with the datafiles on disk.
2. The latest SCN is written (updated) into the datafile header.
3. The latest SCN is also written to the controlfiles.

Every modification in the database (for example, an update) is given an unique system change number SCN, which is, therefore, an ever increasing integer. The latest SCN is immediately recorded in the controlfile. The modifications are logged in the online redo log by the log writer LGWR, but not necessarily in the datafiles.

v$datafile_header can be used to find out the checkpoint number in the datafiles .

 
SQL> select FILE#, CHECKPOINT_TIME, CHECKPOINT_CHANGE# from V$DATAFILE_HEADER;

     FILE# CHECKPOIN CHECKPOINT_CHANGE#
---------- --------- ------------------
         1 27-AUG-10             829278
         2 27-AUG-10             829278
         3 27-AUG-10             829278
         4 27-AUG-10             829278
         5 27-AUG-10             829278

Latest checkpoint number can be found with the help of v$database

 
SQL> select CHECKPOINT_CHANGE# from v$database;

CHECKPOINT_CHANGE#
------------------
            829278

If the control file is a backup file , checkpoint number in the file can be found with :-

 
SQL> select CONTROLFILE_CHANGE# from v$database;

CONTROLFILE_CHANGE#
-------------------
             830797

————————————————————————————-

REDO Log and Checkpoint
—————————

Now , “alter system switch logfile ” forces a redo log switch to happen . A log switch also occurs when one online redo log has been filled and the next online redo log is going to be filled.

A log switch always triggers a checkpoint (that’s what is written at maximum places) but if you see this carefully .

 
SQL> select FILE#, CHECKPOINT_TIME, CHECKPOINT_CHANGE# from V$DATAFILE_HEADER;

     FILE# CHECKPOIN CHECKPOINT_CHANGE#
---------- --------- ------------------
         1 27-AUG-10             831582
         2 27-AUG-10             831582
         3 27-AUG-10             831582
         4 27-AUG-10             831582
         5 27-AUG-10             831582

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
            831582

SQL> SQL>  SELECT group#, sequence#, members, archived, status FROM v$log;

    GROUP#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
         1         13          1 NO  INACTIVE
         2         14          1 NO  CURRENT
         3         12          1 NO  INACTIVE

SQL>  alter system switch logfile;

System altered.

SQL> SELECT group#, sequence#, members, archived, status FROM v$log;

    GROUP#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
         1         13          1 NO  INACTIVE
         2         14          1 NO  ACTIVE
         3         15          1 NO  CURRENT

SQL>  select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
            831582

SQL>  select FILE#, CHECKPOINT_TIME, CHECKPOINT_CHANGE# from V$DATAFILE_HEADER;

     FILE# CHECKPOIN CHECKPOINT_CHANGE#
---------- --------- ------------------
         1 27-AUG-10             831582
         2 27-AUG-10             831582
         3 27-AUG-10             831582
         4 27-AUG-10             831582
         5 27-AUG-10             831582

There is no change in the checkpoint . Checkpointing in case of log switch happens if there are dirty buffers to be written to the datafile or in other words , SCN in the datafile header and in the control file don’t match . Here since they were same , no checkpointing occurred even though log was switched.

Also , if we do a checkpointing , log switch doesn’t happen but triggering a checkpoint brings the log files in “INACTIVE” state since they are no longer needed in instance recovery (may be needed in the media recovery) ( as it is written on the datafiles).

 
SQL>  SELECT group#, sequence#, members, archived, status FROM v$log;

    GROUP#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
         1         13          1 NO  CURRENT
         2         11          1 NO  INACTIVE
         3         12          1 NO  INACTIVE

SQL> alter system switch logfile;

System altered.

SQL>  SELECT group#, sequence#, members, archived, status FROM v$log;

    GROUP#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
         1         13          1 NO  ACTIVE
         2         14          1 NO  CURRENT
         3         12          1 NO  INACTIVE
   
SQL> alter system checkpoint;

System altered.

SQL>  SELECT group#, sequence#, members, archived, status FROM v$log;

    GROUP#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
         1         13          1 NO  INACTIVE
         2         14          1 NO  CURRENT
         3         12          1 NO  INACTIVE
   
-------------------------------------------------------------------------------------
6 COMMENTS
  1. Jayant / 28 Aug 2010

    awesome parul ! loved the checkpoint the redo log switch part .. nicely explained :)

  2. Parul Garg / 28 Aug 2010

    Thankyou :)

  3. Aman Sharma / 06 Sep 2010

    Parul,

    The reason that the datafiles are not updated with the latest change is not because that there is nothing to write but because this is considered as a “Thread checkpoint” and not a “Full Checkpoint”. Even though there is no change reported in the checkpoint information from the V$datafile, the statistics of the database, “background checkpoint completed and started” .

    See below, where I am switching in one session and checking the statistics in another session.

    Session1>>
    SQL> alter system switch logfile;

    System altered.

    SQL> select checkpoint_change# from V$datafile;

    CHECKPOINT_CHANGE#
    ——————
    4319036
    4319036
    4319036
    4319036
    4319036
    4319036
    4319036

    7 rows selected.

    SQL> alter system switch logfile;

    System altered.

    SQL> select checkpoint_change# from V$datafile;

    CHECKPOINT_CHANGE#
    ——————
    4319036
    4319036
    4319036
    4319036
    4319036
    4319036
    4319036

    7 rows selected.

    Session2>>
    SQL> select name, value from V$sysstat
    2 where lower(name) like ‘%checkpoint%’;

    NAME VALUE
    —————————————————————- ———-
    physical writes non checkpoint 3070
    DBWR checkpoint buffers written 3623
    DBWR thread checkpoint buffers written 0
    DBWR tablespace checkpoint buffers written 0
    DBWR parallel query checkpoint buffers written 0
    DBWR checkpoints 41
    background checkpoints started 2
    background checkpoints completed 2

    8 rows selected.

    SQL> /

    NAME VALUE
    —————————————————————- ———-
    physical writes non checkpoint 3071
    DBWR checkpoint buffers written 3628
    DBWR thread checkpoint buffers written 0
    DBWR tablespace checkpoint buffers written 0
    DBWR parallel query checkpoint buffers written 0
    DBWR checkpoints 42
    background checkpoints started 3
    background checkpoints completed 2

    8 rows selected.

    SQL> /

    NAME VALUE
    —————————————————————- ———-
    physical writes non checkpoint 3071
    DBWR checkpoint buffers written 3628
    DBWR thread checkpoint buffers written 0
    DBWR tablespace checkpoint buffers written 0
    DBWR parallel query checkpoint buffers written 0
    DBWR checkpoints 43
    background checkpoints started 4
    background checkpoints completed 2

    8 rows selected.

    SQL> l
    1 select name, value from V$sysstat
    2* where lower(name) like ‘%checkpoint%’
    SQL>

    The other point is correct that when there would be some dirty buffers, there would be an update of the checkpoint in the datafile headers as well with the switch of the logfile.

    Aman….

  4. Parul Garg / 07 Sep 2010

    @Aman :- Thankyou for your clarification on the “Thread Checkpoint” .

  5. Libby / 31 Oct 2011

    Very well explained. I simply loved this post. Both the concepts are clarified in such a simple way. I will definitely share this post with all my friends too. Thanks and Keep up the good work.
    sap upgrade transactions

  6. Parul Garg / 01 Nov 2011

    Thanks :) and thanks for sharing too .. :)

Leave a Comment




*