Home » RDBMS Server » Server Administration » Checkpoint
Checkpoint [message #173444] Mon, 22 May 2006 08:41 Go to next message
gajini
Messages: 262
Registered: January 2006
Senior Member
Checkpoint occurs mainly for two reasons,

1) Checkpoint occurs by means of Log Switch,Data buffer full,etc.. to move data from memory to disk & from log buffer to logfiles

2) Checkpoint occurs at commit to move data from memory to disk & from log buffer to logfiles

How Oracle identifies difference between those checkpoint(as mentioned in 1) and checkpoint which occurs at commit so that in case of recovery it'll recover upto the commit checkpoint,eventhough some checkpoints(other than commit) occurred very recent after the commit checkpoint?

Report message to a moderator

Re: Checkpoint [message #173450 is a reply to message #173444] Mon, 22 May 2006 08:59 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Actually, a checkpoint is realized on five types of events:

- At each switch of the redo log files.
- When the delay for LOG_CHECKPOINT_TIMEOUT is reached.
- When the size in bytes corresponding to :
(LOG_CHECKPOINT_INTERVAL* size of IO OS blocks) is written on the current redo log file.
- Directly by the ALTER SYSTEM SWITCH LOGFILE command.
- Directly with the ALTER SYSTEM CHECKPOINT command.

A COMMIT does not necessarily move data to the datafiles. The data is written to the actual datafiles when one of the following conditions occurs:

- A shadow process must scan more than one-quarter of the db_block_buffer parameter.
- Every three seconds.
- When a checkpoint is produced.

Report message to a moderator

Re: Checkpoint [message #173527 is a reply to message #173444] Mon, 22 May 2006 23:34 Go to previous messageGo to next message
gajini
Messages: 262
Registered: January 2006
Senior Member
But you've not cleared by one more doubt,on what basis Oracle recover only upto the last commit given whether is there any flag setting internally i.e, how oracle identifies difference between ordinary SCN value & a SCN value occurs at commit?

Report message to a moderator

Re: Checkpoint [message #173594 is a reply to message #173527] Tue, 23 May 2006 06:04 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
gajini wrote on Mon, 22 May 2006 23:34

But you've not cleared by one more doubt,on what basis Oracle recover only upto the last commit given whether is there any flag setting internally i.e, how oracle identifies difference between ordinary SCN value & a SCN value occurs at commit?



Not really sure what you mean by "ordinary SCN"??

The SCN is a stamp that defines a committed version of a database at a point in time. It functions as an internal timestamp for Oracle. The Oracle database server assigns every committed transaction a unique SCN.

The checkpoint process updates all datafiles and control files with the SCN at the time of the checkpoint and signals database writer to write out the blocks. A successful checkpoint guarantees that all database changes up to the checkpoint SCN have been recorded in the control files and the datafiles. As a result, only those changes made after the checkpoint need to be applied during recovery. Oracle stores the SCN associated with the checkpoint in the control files and in the datafile header for each datafile. Following a checkpoint, while the database is up and open for use, the system checkpoint in the control files and the SCN in each datafile header all match.

Report message to a moderator

Re: Checkpoint [message #173923 is a reply to message #173594] Thu, 25 May 2006 04:46 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Just to clarify what you are asking,
If there is a commit -- Chkpnt1
some more transactions are performed but not committed (I use the term transaction loosley here)
then, let's say a log switch -- chkpnt2
but there is a failure.
You envision that Oracle will somehow work out that it only needs to recover to chkpnt1.
Oracle will actually recover to chkpnt2, it's just that those uncommitted transactions will then be rolled back by smon once the db is open.

Jim

Report message to a moderator

Re: Checkpoint [message #173929 is a reply to message #173444] Thu, 25 May 2006 04:58 Go to previous messageGo to next message
yash_v
Messages: 24
Registered: April 2006
Junior Member
use this query to find out when was the last checkpoint took place

select name, checkpoint_change#, to_char(checkpoint_time, 'DD.MM.YYYY HH24:MI:SS') from v$datafile_header

for more info visit here

http://www.adp-gmbh.ch/ora/concepts/checkpoint.html

Regards,
Yash

Report message to a moderator

Re: Checkpoint [message #174006 is a reply to message #173444] Thu, 25 May 2006 08:56 Go to previous message
gajini
Messages: 262
Registered: January 2006
Senior Member
Thanks JSI2001

Now i got clear idea about rollback & recovery process.But i've still more
doubts based on the example given by you,please clear it.


Both Chkpnt1 & Chkpnt2 will update the datafile header & control file

header,but datafile header & control file header contains the entry made by

Chkpnt2(since it's the latest one).Then,how oracle identifies that it'll

rollback to till Chkpnt1(which occurred due to commit)?where it'll get info

that it should rollback to Chkpnt1?Where the commit SCN value is recorded &

how it identifies that SCN value is assigned due to commit?

Please give answer for those questions,so that i'll have clear picture
about datafile,control file & redo log and recovery & rollback process.

Report message to a moderator

Previous Topic: Package body Not visible
Next Topic: Privileges required to gather stats on tables in another schema
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Sep 20 11:52:16 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.