Home » RDBMS Server » Server Administration » checkpoint/SCN
checkpoint/SCN [message #59771] Sun, 21 December 2003 23:23 Go to next message
ora
Messages: 47
Registered: June 2002
Member
Hello,

Please explain me is there any difference between SCN and checkpoint number/information? If there is no diff then how oracle identify the checkpoint information?
Re: checkpoint/SCN [message #59781 is a reply to message #59771] Mon, 22 December 2003 04:43 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
SCN = system change number that is assigned(& incremented) everytime someone commits(ie transaction completes) and acts as the internal timestamp for Oracle.

Whenever there is a checkpoint, the CHECKPOINT SCN is incremented and acts as the starting point during instance recovery.

For eg),
-- shows the CHECKPOINT SCN recorded in the datafiles
SQL> select max(CHECKPOINT_CHANGE#) from v$datafile;

MAX(CHECKPOINT_CHANGE#)
-----------------------
789051

-- gets the current system SCN
SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
789124

-Thiru
Re: checkpoint/SCN [message #59789 is a reply to message #59781] Mon, 22 December 2003 19:15 Go to previous messageGo to next message
Daljit Singh
Messages: 290
Registered: October 2003
Location: Texas
Senior Member
Hi,

Its ok but i think oracle assign SCN to each block whenever it get updated/dirty weather it is committed or not. and on the basis of this SCN oracle identifies from where to read the block for another transaction means from Rollback segments or from Data blocks.

If i am wrong so plz clarify me Thiru, I am explaining u little more wht i want to say. Suppose user A give an update statement which will update all the records in table and the transanction of user A is not yet committed. All the records have been modified in buffers. And suppose at the time of issuing this update statement the SCN was 50. After that according to u the transaction is not committed so these blocks would still have the SCN of 50. After this a select statement has been fired by user B at this point the SCN is 60 and oracle show him the old data from Undo segments now the question is how oracle identifies that for this transaction rollback should be used. Bcz oracle doc says that transaction will read the data with low or equal SCN of the transaction.

Plz put some light on it. I am little confused on this topic.
Re: checkpoint/SCN [message #59796 is a reply to message #59789] Tue, 23 December 2003 04:37 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Daljit,
I should have made it more clear. There are checkpoint SCNs(end of checkpoint),Commit SCNs(end of transaction),Snapshot SCNs(beginning of the query) ,system SCN etc..They are basically sequence of integers Oracle uses internally to keep track of various events and block versions to ensure statement level read consistency,multiversioning,transaction rollback,instance recovery etc etc. They(system SCNs) DO GET incremented as and when you make changes to data blocks irrespective of the state of the transaction. Oracle differentiates between these SCNs depending on its purpose(for example you are concerned about Snapshot SCN when you begin your statement. You are concerned about COMMIT SCN when you commit or rollback.You are concerned about CHECKPOINT SCN when performing recovery and so on )

See the thread mentioned below for a discussion on SCNs with respect to statment level read consistency and the answer to your question. Note that behaviour changes with different isolation level. Default is READ COMMITTED. The SCN is fixed for isolation level of READ ONLY .The snapshot SCN is fixed for isolation level of Serializable ( and the internal sequence numbers are incremented to keep track of its own changes).

When user B issues the Select statement,he will see the data blocks reflected by the Snapshot SCN(60 here). As he proceeds through the blocks, lets say he encounters a block with SCN 70, which means that this has been modified by some other transaction and hence to preserve the statement level read consistency, the query goes to to the rollback segments searching for blocks whose SCN is 60 . The other transactions NEVER see the changes until you commit(ie No Dirty Reads).

-Thiru

[Updated on: Fri, 18 February 2005 23:32]

Report message to a moderator

Re: checkpoint/SCN [message #59804 is a reply to message #59796] Tue, 23 December 2003 22:52 Go to previous messageGo to next message
Daljit Singh
Messages: 290
Registered: October 2003
Location: Texas
Senior Member
Hi,

Thanx a lot Thiru and Merry Christmas.
Re: checkpoint/SCN [message #342788 is a reply to message #59771] Mon, 25 August 2008 02:02 Go to previous messageGo to next message
gajini
Messages: 259
Registered: January 2006
Senior Member
Hi Thiru,

May I know the difference between Checkpoint SCN and System SCN and can you please explain during which situation these SCN values will be used?

Thanks...
Re: checkpoint/SCN [message #342790 is a reply to message #342788] Mon, 25 August 2008 02:15 Go to previous message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
system change number (SCN)

Checkpoint scn is the scn of the last checkpoint. Checkpoint is the process to flush dirty buffer from cache to data files.
Have a look at Overview of Oracle Processes

Regards
Michel
Previous Topic: How to change the character set in oracle
Next Topic: ORA-00600
Goto Forum:
  


Current Time: Wed Dec 07 06:52:45 CST 2016

Total time taken to generate the page: 0.17239 seconds