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.
------------------------------------------------------------------------------------------------------------
Difference between the Checkpoint and SCN
Checkpoint
Checkpoint in database is used to reduce the amount of the time for recovery. It is a background process in oracle(CKPT), which cause DBWR to write all the data blocks that have been modified since the last checkpoint to the datafile.
Checkpoint is responsible for
->Signaling DBWn
->Updating datafile headers with checkpoint info. (i.e. SCN)
->Updating Control file with checkpoint info. (i.e. scn)
SCN
SCN is the ever increasing number to determine the age of database.
it’s System Change Number / System Commit Number…
It’s used to determine the consistency of database..
It’s stored at various places like control file, data files, redo log files.
If it doesn’t match, that means inconsistency..
So in short,
Checkpoint is the concept/background process while SCN is like serial number in our table..
------------------------------------------------------------------------------------------------------------
the system change number (SCN) is Oracle's clock - every time we commit, the clock
increments. The SCN just marks a consistent point in time in the database.
A checkpoint is the act of writing dirty (modified blocks from the buffer cache to disk.
The database ALWAYS has transactions going on, ALWAYS. SMON and many other background
processes are always doing work, the database (unless it is opened read only) is always
doing transactions.
------------------------------
About your question, I am not sure why you are terming that the checkpoint SCN and the checkpoint number would be same? Checkpoint SCN is the most latest SCN available and it's value would keep on changing. Whereas , the checkpoint change# would be changed when there would be a global checkpoint that would occur. The checkpoint SCN would be always pulled ahead, even with a query too(Query SCN) but not the checkpoint_change#. See below,
SQL> select RTCKP_SCN from x$kccrt;
RTCKP_SCN
----------------
23498044
22914221
0
SQL*Plus: Release 11.1.0.6.0 - Production on Wed Apr 27 22:21:53 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 326414336 bytes
Fixed Size 1299708 bytes
Variable Size 289409796 bytes
Database Buffers 29360128 bytes
Redo Buffers 6344704 bytes
Database mounted.
Database opened.
SQL> select current_scn from V$database;
CURRENT_SCN
-----------
23534630
SQL> select checkpoint_change# from V$datafile;
CHECKPOINT_CHANGE#
------------------
22914221
22914221
22914221
22914221
22914221
22914221
6 rows selected.
SQL>
SQL> select current_scn from V$database;
CURRENT_SCN
-----------
23877297
SQL> /
CURRENT_SCN
-----------
23884777
SQL> SQL> select checkpoint_change# from V$datafile;
SP2-0734: unknown command beginning "SQL> selec..." - rest of line ignored.
SQL> select checkpoint_change# from V$datafile;
CHECKPOINT_CHANGE#
------------------
22914221
22914221
22914221
22914221
22914221
22914221
6 rows selected.
SQL> select RTCKP_SCN from x$kccrt;
RTCKP_SCN
----------------
23498044
22914221
0
SQL> SQL> alter system checkpoint;
System altered.
SQL> select RTCKP_SCN from x$kccrt;
RTCKP_SCN
----------------
23905660
23905663
0
SQL> SQL>
SQL> SQL> alter system checkpoint;
System altered.
SQL> select RTCKP_SCN from x$kccrt;
RTCKP_SCN
----------------
23905660
23905663
0
SQL> select checkpoint_change# from V$datafile;
CHECKPOINT_CHANGE#
------------------
23905660
23905660
23905660
23905660
23905660
23905660
6 rows selected.
SQL> select checkpoint_change# from V$datafile;
CHECKPOINT_CHANGE#
------------------
23905660
23905660
23905660
23905660
23905660
23905660
6 rows selected.
Difference between SCN and checkpoint.
» Original article Author: Oracle blog
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.
No comments:
Post a Comment