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.
posted by Saibabu Devabhaktuni @ 8:27 PM
25 Comments:
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....
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.
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.
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.
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
very very good post i ever seen
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.
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
Easily I to but I contemplate the brief should prepare more info then it has.
Again a gentle post. Thank your also pen-friend
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.
I wish not approve on it. I over nice post. Especially the title attracted me to be familiar with the whole story.
I will not agree on it. I regard as warm-hearted post. Expressly the title attracted me to review the unscathed story.
Genial post and this enter helped me alot in my college assignement. Thank you as your information.
Do you have copy writer for so good articles? If so please give me contacts, because this really rocks! :)
It is useful to try everything in practise anyway and I like that here it's always possible to find something new. :)
Not bad article, but I really miss that you didn't express your opinion, but ok you just have different approach
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 :)
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
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.
hi..
great information.. thanks for posting..
hi... i have a question..
when is the checkpoint occures?
Very nice post sai
Thanks,
Chandrashekar
http://chandu208.blogspot.com/
I still thought it had been practical. Excellent post!
Very nice post. but there is a question WHEN COMMIT COMMAND APPLY then what would be the Scenario.
Post a Comment
<< Home