Skip to Main Content
  • Questions
  • Relation between SCN and checkpoint SCN

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Ravi.

Asked: September 15, 2016 - 7:25 am UTC

Last updated: September 17, 2016 - 4:04 am UTC

Version: 11.2.0.3

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I have gone through many of your threads regarding SCN and I have a few questions regarding them.

1. Is SCN generated only for committed transctions and that is when the transaction is committed not when the transaction is begun, right ?
A) If so, if the redo of that transaction is written to online redo log before that transaction is committed, and now if the transaction is committed, the generated transaction SCN will be added to online redolog record of that transaction, right ?
B) if so, does that mean SCN will not be generated for uncommitted transactions at all, hence not at all stored in the redo record of that transaction ?
C) is there any possibility of multiple transaction committing at the same time and hence could share the same SCN ?
D) Is the current_scn column in the v$database view is nothing but SCN of the most recently committed transaction ?
E) whenevr a transaction is committed, the generated SCN will immediately replace the CURRENT_SCN number with the newly generated SCN of that reccent transaction ?
F) if the current_scn is only stored in control file, where does oracle find current_scn incase of recovery using backup control file ? Is that from current online redolog ?

2. Checkpoint SCN is only generated when the checkpoint happens (I mean when DBWR writes dirty buffers to database db files).
A) how come will the new checkpoint SCN be generated, Is it generated using the current_scn value, because I have observed that whenever checkpoint happens, the newly generated checkpoint SCN is either qual to or higher than current_scn ?
B) Whenever checkpoint happens, checkpoint SCN value is stored in 3 places i.e control file (System checkpoint SCN),Control file (Datafile checkpoint SCN for each datafile), datafile header (Datafile header SCN/start SCN), right ?
C) if the dirty buffers are written to only few datafiles, then the checkpoint SCN will be only updated in the headers of those perticular datafiles and unmodified datafiles will have checkpoint SCN remain unchanged ?
D) incase of media recovery (eg: datafile lost), database checks datafile_SCN in the current control file and applies all archives & redo until that checkpoint SCN on the restored old datafile ?
E) incase of instance crash recovery(eg: power failure), oracle takes system checkpoint SCN as a baseline and rollforward the redo entries from the system checkpoint SCN and commits those who have SCN and rollbacks those redo entries which doesn't have SCN, Am I right ?

Those are whole lot of questions, kindly take your time and answer them.

Thanks & regards,
Ravi Kishor K

and Connor said...

Try not to think of an SCN as being "generated". It is more of a reflection of the "current time" of the database.

So when we start a transaction, we "look at the clock" and know the SCN is (say) 123. And when we commit, we look at the clock again see that the SCN is (say) 135. So the commit scn is 135 for this transaction. So as we start logging information to the redo logs, we'll have scn information throughout, not *just* the commit scn. In fact, in this sense, a commit is less of a "commit" and more of set of instructions to the redo logs saying "Hey, this undo information we needed for rollback is no longer needed". Since we're are updating something (ie, the undo information) this must (like every other change) be written to the redo log.

Where the "time" metaphor differs to the scn is about your question on "sharing" scns. This does not happen because the scn is an atomic structure - the moment someone grabs one, it is immediately incremented - so every transaction will have its own scn.

For example, even asking for scn increments it:

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    7587690

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    7587691

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    7587692

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    7587693

<code>

Similarly, we dont *generate* a checkpoint scn...it simply reflects the scn at which point the datafiles contain changes up to.  Which is why when you do an "alter system checkpoint", the checkpoint scn will be very close to the current one.  And they can be different per datafile

<code>
SQL> select file#, CHECKPOINT_CHANGE# from v$datafile;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            7579267
         3            7579267
         5            7579267
         6            7579267
         7            7579267
         8            7579267
         9            7583686



I think that will do for now.... asking 11 questions and calling it 1 question generally isnt considered a polite usage of asktom.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.