Thursday, March 30, 2006

Difference between SCN and checkpoint.

SCN and checkpoint:

System change number(SCN) is represented with SCN_WRAP and SCN_BASE. Whenever SCN_BASE reaches 4294967290 (2 power 32), SCN_WRAP goes up by one and SCN_BASE will be reset to 0. This way you can have a maximum SCN at 1.8E+19.

SCN = (SCN_WRAP * 4294967290) + SCN_BASE

Checkpoint number is the SCN number at which all the dirty buffers are written to the disk, there can be a checkpoint at object/tablespace/datafile/database level.

Checkpoint number is never updated for the datafiles of readonly tablespaces.

Oracle doesn't use any sequence to generate SCN_BASE numbers, it make calls to "kcmgas" function (per Steve Adams), which is a permanent memory structure for the instance. It is reported in v$sysstat under "calls to kcmgas" name.

Does Oracle do either crash recovery or transaction recovery after shutdown abort if the checkpoint was taken right before the instance crash?

Yes, Oracle perform rollforward first if there are any changes beyond last checkpoint and rollback any uncommitted transactions.

SCN numbers are being reported at frequent intervals by SMON in "SMON_SCN_TIME" table.

You can also query v$transaction to arrive at the SCN for that transaction.

Controlfile records information about last checkpoint and archived sequence along with other information.

25 Comments:

At July 20, 2007 at 10:27:00 PM PDT, Blogger Aman.... said...

Hi there,
Very nice post!
Ok 2 stupid questions
(1)When this no of highest scn will be over, than what will happen?Will oracle restart from the first number?
(2)we see every second(almost) scn getting increased.So does it mean background processes every second are increasing SCN_WRAP?
thanks and regards,
Aman....

 
At July 24, 2007 at 4:02:00 AM PDT, Blogger Ruderesh said...

Hi,
nice article .
My question is:
1)Does all redo entries has scn attached to them or does only the commit entry has.
2)Checkpoint updates scn only in controlfile or online datafiles header also?? alter switch controlfile command show checkpoint_change updated for both, but switch logfile command updates controlefile_change only.I had read somewahere that only at time of logswitch ckpt updates datafile headers.

 
At July 24, 2007 at 11:48:00 PM PDT, Blogger Saibabu Devabhaktuni said...

Hi Aman Sharma,

Here is what I think:

1) If SCN really reached to it's maximum allowed value (after exhausting all the wraps), database has to be opened in RESETLOGS mode and SCN will start from the beginning all over again.

2) Just like foreground (shadow) processes making calls to increase SCN_BASE; background processes also do the same (when they make changes to some of the data they work on).

Thanks.

 
At July 25, 2007 at 12:32:00 AM PDT, Blogger Saibabu Devabhaktuni said...

Hi Ruderesh,

1) All changes recorded in the redo stream (including commits and rollbacks) will have SCN associated with them.

2) Whenever DBWR advances checkpoint by writing dirty buffers to the datafiles on disk, it will post CKPT process to update the checkpoint_change# and checkpoint_time in the online datafiles (only read/write enabled datafiles) headers and controlfile. Controlfile_change# is just like any other change happening on the database; it is not related to the checkpoint_change# (or SCN) recorded in the controlfile. I think CKPT process is the only process that updates online datafile headers whenever checkpoint is advanced (I need to test this).

Thanks.

 
At May 21, 2008 at 1:34:00 AM PDT, Blogger nick said...

hi

when a checkpoint occurs

does ckpt process writes the datafile headers with current scn before dbwr writes data to that datafile or after dbwr has written data to the datafile

 
At November 7, 2008 at 2:50:00 AM PST, Anonymous Anonymous said...

very very good post i ever seen

 
At April 27, 2009 at 7:30:00 PM PDT, Blogger Saibabu Devabhaktuni said...

Hi Nick,

when a checkpoint occurs, ckpt process writes to the datafile headers (and controlfile is also updated) with the SCN up to which dbwr has already written all changes to the datafiles.

Thanks.

 
At October 8, 2009 at 6:58:00 AM PDT, Blogger Guru Shankar Aaasaamy said...

Hi Sai,

I have a question, What are the types of SCN.

If SCN is generated on a commit, how is the database synchronized on a Shutdown.

Thanks.
Guru

 
At December 28, 2009 at 5:43:00 AM PST, Anonymous Anonymous said...

Easily I to but I contemplate the brief should prepare more info then it has.

 
At December 30, 2009 at 9:53:00 PM PST, Anonymous Anonymous said...

Again a gentle post. Thank your also pen-friend

 
At January 3, 2010 at 1:31:00 AM PST, Blogger Saibabu Devabhaktuni said...

hi Guru,

There is only one SCN type and it is synchronized across the database instances for recoverability, read consistency, and transaction management. Upon database startup, controlfile, online redo logs, and datafile headers are read for making sure that database is consistent to the most recent SCN.

Thanks,
Sai.

 
At January 8, 2010 at 3:45:00 PM PST, Anonymous Anonymous said...

I wish not approve on it. I over nice post. Especially the title attracted me to be familiar with the whole story.

 
At January 13, 2010 at 8:13:00 AM PST, Anonymous Anonymous said...

I will not agree on it. I regard as warm-hearted post. Expressly the title attracted me to review the unscathed story.

 
At January 17, 2010 at 5:54:00 AM PST, Anonymous Anonymous said...

Genial post and this enter helped me alot in my college assignement. Thank you as your information.

 
At February 17, 2010 at 12:37:00 AM PST, Anonymous Anonymous said...

Do you have copy writer for so good articles? If so please give me contacts, because this really rocks! :)

 
At February 19, 2010 at 11:14:00 AM PST, Anonymous Anonymous said...

It is useful to try everything in practise anyway and I like that here it's always possible to find something new. :)

 
At March 12, 2010 at 3:10:00 PM PST, Anonymous Anonymous said...

Not bad article, but I really miss that you didn't express your opinion, but ok you just have different approach

 
At March 20, 2010 at 10:04:00 AM PDT, Anonymous Anonymous said...

i truthfully enjoy your writing type, very unique,
don't quit as well as keep posting because it just that is worth to look through it.
looking forward to look into a whole lot more of your own content, have a great day :)

 
At February 16, 2011 at 4:41:00 AM PST, Blogger Unknown said...

hi sai,
i live in lucknow india.
and working as Oracle DBA (beginner). Can u suggest some good books , as u have very good
knowledge.And What's ur email id??

THanks Saurabh
engr.saurabh@gmail.com

 
At July 9, 2011 at 4:48:00 AM PDT, Anonymous Investigation Services said...

The information that you have shared are truly useful.I am glad that I have visited your blog. I'll keep visiting for more of your updates.

 
At September 15, 2011 at 11:04:00 PM PDT, Anonymous Anonymous said...

hi..
great information.. thanks for posting..

 
At December 8, 2011 at 9:31:00 PM PST, Anonymous Anonymous said...

hi... i have a question..
when is the checkpoint occures?

 
At February 13, 2012 at 12:28:00 PM PST, Blogger Oracle DBA said...

Very nice post sai

Thanks,
Chandrashekar
http://chandu208.blogspot.com/

 
At July 14, 2012 at 12:06:00 AM PDT, Anonymous Samsung said...

I still thought it had been practical. Excellent post!

 
At February 12, 2014 at 11:20:00 PM PST, Anonymous Anonymous said...

Very nice post. but there is a question WHEN COMMIT COMMAND APPLY then what would be the Scenario.

 

Post a Comment

<< Home