This discussion is archived
14 Replies Latest reply: Feb 18, 2013 10:02 AM by sunilkumar RSS

checkpoint SCN and checkpoint counter

sunilkumar Newbie
Currently Being Moderated
Dear Experts,

What is the difference between checkpoint SCN and checkpoint counter ?

during startup of a crash database (may be shut abort), it check checkpoint counter of a datafile with control file and if they match it means this is right version!!! and then it check checkpoint SCN of the datafile with the stop SCN in control file for that file and start crash recovery, if the do not match.

I am confused about this checkpoint counter and will it identified that the right file is there or restored ?

Please correct me if I am wrong in the above scenario?

Regards
Sunil Kumar
  • 1. Re: checkpoint SCN and checkpoint counter
    695836 Journeyer
    Currently Being Moderated
    Hi,

    Where did you read such terms?
    Whether you call it checkpoint counter or Checkpoint SCN or checkpoint number, it s all same.
  • 2. Re: checkpoint SCN and checkpoint counter
    Aman.... Oracle ACE
    Currently Being Moderated
    sunil kumar wrote:
    Dear Experts,

    What is the difference between checkpoint SCN and checkpoint counter ?
    Checkpoint SCN is the on-disk SCN, the SCN when the checkpoint write is done to the data file. Checkpoint Counter is simply a count of the number of checkpoint calls made to that datafile and is also recorded in the control file.
    >
    during startup of a crash database (may be shut abort), it check checkpoint counter of a datafile with control file and if they match it means this is right version!!! and then it check checkpoint SCN of the datafile with the stop SCN in control file for that file and start crash recovery, if the do not match.

    I am confused about this checkpoint counter and will it identified that the right file is there or restored ?

    Please correct me if I am wrong in the above scenario?
    I am not sure that what's your question actually.

    Aman....
  • 3. Re: checkpoint SCN and checkpoint counter
    695836 Journeyer
    Currently Being Moderated
    Hi,
    A datafile is checkpointed all the time. How would it matter to oracle how many checkpoint calls were made?

    Which dictionary view provide this information?
  • 4. Re: checkpoint SCN and checkpoint counter
    PavanKumar Guru
    Currently Being Moderated
    Hi Aman,

    Correct me if I'm wrong, the part of consistency of files (CRD) are partially dependent on checkpoint counter along with SCN which is recorded in data file headers (in sync with controlfile). So, during the recovery oracle (recover process) would decide particular data file based on the SCN’s and checkpoint counters of the restored files and the SCN’s and checkpoint counters recorded for each data file in the current control file, which helps to perform the actual recovery of database (applying recover changes).

    - Pavan Kumar N
  • 5. Re: checkpoint SCN and checkpoint counter
    Aman.... Oracle ACE
    Currently Being Moderated
    Yasir Hashmi wrote:
    Hi,
    A datafile is checkpointed all the time. How would it matter to oracle how many checkpoint calls were made?
    As I said, they are matched with the control file to ensure that the count matches and this would be the first of the check in deciding whether recovery is needed or not. Below is an old dump from my notes (as I don't have a db right now with me to do the same) from 102.
    ***************************************************************************
    DATA FILE RECORDS
    ***************************************************************************
     
     unrecoverable SCN: 0x0000.00000000 01/01/1988 00:00:00
     Checkpoint cnt:88 SCN: 0x0000.000e40e7 04/12/2009 00:15:12
     Stop SCN: 0xffff.ffffffff 04/11/2009 17:34:52
     Creation Checkpointed at SCN:  0x0000.00000009 08/30/2005 13:50:22
     thread:0 rba:(0x0.0.0)
    Which dictionary view provide this information?
    I don't know that any view has this information. It's under x$kcvfh . See my last reply about it.

    Aman....

    Edited by: Aman.... on Jan 30, 2013 11:34 PM

    Edited by: Aman.... on Jan 31, 2013 9:14 AM
  • 6. Re: checkpoint SCN and checkpoint counter
    PavanKumar Guru
    Currently Being Moderated
    Hi Aman,

    Can you check x$activeckpt (a quick check, I'm sure it might not be there in it)

    - Thanks
    Pavan Kumar N
  • 7. Re: checkpoint SCN and checkpoint counter
    Aman.... Oracle ACE
    Currently Being Moderated
    I guess you are correct Pavan.

    Aman....
  • 8. Re: checkpoint SCN and checkpoint counter
    PavanKumar Guru
    Currently Being Moderated
    Hi,

    I'm sure this table, doesn't hold the current checkpoint which is reporting in alert log, perhaps the recently completed checkpoint. I researched to track things, but failed to get/know the things which oracle reports current checkpoint information in alert log.

    - Pavan Kumar N
  • 9. Re: checkpoint SCN and checkpoint counter
    Aman.... Oracle ACE
    Currently Being Moderated
    I checked the table Pavan but I am not sure that this is the table that contains this information.

    Aman....
  • 10. Re: checkpoint SCN and checkpoint counter
    Aman.... Oracle ACE
    Currently Being Moderated
    A good cup of coffee can solve things nicely :) . Just figured it out today morning(hopefully) .

    The table name is x$kcvfh . I believe it should be "Kernel Cache V(?) File Header" . It has a column FHCPC which I believe is "File Header Checkpoint Count" . Here is an output from 11201 db,
    SQL> select fhcpc from X$KCVFH;
    
         FHCPC
    ----------
           146
           146
            74
           145
            70
    
    SQL> select name, file# from V$datafile;
    
    NAME
    --------------------------------------------------------------------------------
         FILE#
    ----------
    /u01/app/oracle/oradata/amer/system01.dbf
             1
    
    /u01/app/oracle/oradata/amer/sysaux01.dbf
             2
    
    /u01/app/oracle/oradata/amer/undotbs01.dbf
             3
    
    
    NAME
    --------------------------------------------------------------------------------
         FILE#
    ----------
    /u01/app/oracle/oradata/amer/users01.dbf
             4
    
    /u01/app/oracle/oradata/amer/example01.dbf
             5
    
    
    SQL> 
    And to confirm this, I dumped the file headers of all the files with Level 1. Below is the snipped output of just the file# 5.
    DATA FILE #5: 
      name #9: /u01/app/oracle/oradata/amer/example01.dbf
    creation size=12800 block size=8192 status=0xe head=9 tail=9 dup=1
     tablespace 6, index=6 krfil=5 prev_file=0
     unrecoverable scn: 0x0000.000e0bf8 01/26/2013 06:00:21
     *Checkpoint cnt:70* scn: 0x0000.0019769e 01/31/2013 02:02:29
     Stop scn: 0xffff.ffffffff 01/28/2013 08:47:41
     Creation Checkpointed at scn:  0x0000.000bf5d1 01/25/2013 22:10:00
     thread:1 rba:(0x1.6a2f.10)
    I hope it's correct!

    Aman....
  • 11. Re: checkpoint SCN and checkpoint counter
    Aman.... Oracle ACE
    Currently Being Moderated
    I have tried to put together everything in a blog post. Read and comment(and correct),
    http://blog.aristadba.com/?p=234

    Aman....
  • 12. Re: checkpoint SCN and checkpoint counter
    sunilkumar Newbie
    Currently Being Moderated
    Hi Aman/Experts,

    Sorry for late response, I go through your blog about checkpoint, it was great. But honestly there is still lots of confusion about recovery process.
    Please clear following points:

    I perform a shut abort (Oracle 10g) and after instance down, replaced a datafile from old one(from backup) and start the instance.
    Now how the recovery will progress ?

    As I think, it should be go like this:

    after mount, oracle find that that thread status is open (since database is crashed), will it immediate start crash recovery ? or it will check all datafiles header for checkpoint
    SCN and match with corresponding stop SCN in datafiles and then decide weather to perform crash recovery or media recovery ?

    one more thing when we say start SCN in datafile (which incremented during instance progress) is checkpoint SCN that matched with the stop SCN in the datafile (which again checkpoint SCN)

    Regards
    Sunil Kumar
  • 13. Re: checkpoint SCN and checkpoint counter
    Aman.... Oracle ACE
    Currently Being Moderated
    sunil kumar wrote:
    Hi Aman/Experts,

    Sorry for late response, I go through your blog about checkpoint, it was great. But honestly there is still lots of confusion about recovery process.
    Please clear following points:

    I perform a shut abort (Oracle 10g) and after instance down, replaced a datafile from old one(from backup) and start the instance.
    Now how the recovery will progress ?
    It's not instance recovery anymore if you are replacing a file but it would be now termed as the media recovery, a different thing altogether.
    As I think, it should be go like this:

    after mount, oracle find that that thread status is open (since database is crashed), will it immediate start crash recovery ? or it will check all datafiles header for checkpoint
    SCN and match with corresponding stop SCN in datafiles and then decide weather to perform crash recovery or media recovery ?
    What do you mean by "thread status is open" ?
    one more thing when we say start SCN in datafile (which incremented during instance progress) is checkpoint SCN that matched with the stop SCN in the datafile (which again checkpoint SCN)
    I am not sure that I followed you here. Can you explain your point more please?

    Aman....
  • 14. Re: checkpoint SCN and checkpoint counter
    sunilkumar Newbie
    Currently Being Moderated
    Hi Aman,

    Please let me know how instance recovery work, I read in "RMAN Recipies for oracle 11g" by Arup Nanada, that following formula used during instance recovery:


    select a.thread#,b.open_mode,a.status ,
    case
    when ((b.open_mode='MOUNTED' and (a.status='OPEN')) THEN 'Crash Recovery'
    when ((b.open_mode='MOUNTED' and (a.status='CLOSED')) THEN 'No crash Required'
    when ((b.open_mode='READ WRITE') and (a.status='OPEN')) THEN 'Inst. Already open'
    Elase 'huh ?'
    end status

    from v$thread# a,v$database b,v$instance c
    where a.thread#=c.thread#

    I was talking about redo thread, so when we start the database and this thread is open, it means crash recovery is required, am I right ?

    I was just discussing a scenario where a database was shut abort and by mistake a datafile has been replaced by old once,
    in that case how all will progress ?

    :(

    Thanks & Regards
    Sunil Kumar

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points