Monday, August 18, 2008

Difference Between 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.

1 comment:

  1. Refreshing Material....

    Good One...