Hi Tom

I dont understand how instance recovery is reduced by checkpoint

For example:

database no archive log

1. I have 3 redo log groups
2. I have 10 uncomitted transactions, 3 in redo 1 , 3 in redo 2 and 4 in redo 4
3. the uncomitted data are written to datafiles
4. redo 1 is overwritten by new redo
5. instance failre
6. we start database

instance recovery starts:
rolling forward to recover data that has not been recorded in the datafiles, yet has been recorded in the online redo log, including the contents of rollback segments.

Marking all transactions system-wide that were active at the time of failure as DEAD. I dont understand how Oracle can know what transactions were active in the time of failure? I mean for example my uncomitted transactions in redo 1 has been overwritten then from where does Oracle obtain information about these 4 uncomitted transactions?

thank you!

Hi

Does Oracle get dead transaction information from RBS segment headers? From that small transaction table in RBS header I mean...

...
instance recovery starts:
rolling forward to recover data that has not been recorded in the datafiles, yet

has been recorded in the online redo log, including the contents of rollback
segments.
...

Since at every log switch there is an implicit checkpoint which consequently triggers CKPT process to update the file headers and CKPT then triggers DBWR to flush dirty blocks to disk does this mean that Oracle always read the last current and active redo only therefore speeding up instance recovery? And that's why it does not matter if older redo logs have been overwritten since uncomitted transactions informations can be obtained from rollback segments transaction table and can be rollback by reading that? (no redo is needed to rollback these uncommitted transaction)

What is Checkpoint Queue? What is the underlying mechanism for Oracle checkpointing. How internally and in how many steps it does it.?

Please give valuable advise.

Sir,

At checkpoint dirty blocks from database buffer are written to the datafiles.Now for example an uncommitted transaction
like the update statement in my example is present in the buffer ,
it will be subsequently written to the data file at checkpoint.
So now datafile contains some uncommitted data.Isn't it ??

Thanks.
atul

Sir,

# When a DML is issued , there are two blocks in the database buffer

a) A modified data block that contains new value & a block of rollback segment that contain old image.
b) Also Redo ( buffer ) is generated that contains old & new image (values).

CASE 1: THERE IS NO COMMIT.
# Users are reading data from rollback blocks ( old image )
Case2: Commit happens ?? then they should read data from buffer that contains new values & rollback blocks should disappear.

How does it happen ?


Thanks.
atul

Hi Tom,

Just a clarification on the answers u gave...

Quote your answer -
1. "When you commit -- the block is not
written (but the REDO LOG is -- that makes it so we can "replay" your transaction in the event of a failure)."

Question : Ok, when a user commits the data, if the data is not immediately written on to the block, then how is concurrency handled ? On commit from User A, if user B wants to query the data just saved, then how is he able to get the updated data ??


2.
"...just because the user committed does not imply at all that the rollback is no longer needed.... that undo data might be needed for a long time after the user commits."

Question : It was assumed that once a user commits the data, the rollback is cleared. But, as per ur answer u r saying that it "might be needed for a long time after the user" ? Could u explain it's purpose ?

Rgds,

Santhosh

you said

"No, because the checkpoint need not have completed. Say we have 10 log files. When we switch from 1 to 2 then 2 to 3 then .... then 9 to 10 -- now we want to switch from 10 to 1. The blocks protected by 1 MIGHT NOT YET BE CHECKPOINTED. You might get a message "checkpoint not complete cannot allocate new log" in the alert, the system will pause whilst the checkpoint completes.
We might have to read every redo log file upon crash recovery.
We will not reuse a redo log until the blocks it "protects" are flushed to disk and once they are we no longer need that redo (that is why it is OK for older redo logs to be reused)"

I dont understand this fully, for example I have 4 redo log groups and an uncommitted transaction recorded in the first group if the corresponding dirty blocks are flushed to disk and the first group has been overwritten by new redo vectors what happens if instance fails? I assume the redo logs are needed to rebuild the RBS of my uncommitted transaction but is that information to rebuild the RBS available anywhere? Or Oracle simply reads ths RBS from the datafiles in disk? (No need to rebuild the RBS for this specific transaction?)

So in resume, I guess Oracle does roll forward using the redo logs but it also reads RBS (NOT rebuilt using the redo) to rollback my uncommitted transaction. Am I correct? Or all RBS are rebuilt using redo logs?

Hi again

Thank you very much for prompt reply.

you said
"a) roll forward from all changes in the redo logs. this INCLUDES rolling "rollback" forward -- we generate redo for undo"

however what I meant initially is what happen if the redo to build the rollback is overwritten? I mean if we always redo to rebuld RBS then what happens if I have a uncommitted transaction if it does not commit for long timeand the redo of this transaction and the redo of RBS related to this transaction are overwritten?

Tom, I was thinking of a scenario that sometimes scares me...

**From a database perspective** -- theoretically -- when data is commited it inevitably goes to the redo log files on disk.

However, there are other layers between the database and the hardware. I mean, the commited data doesn't go "directly" to disk, because you have "intermediate" structures like i/o buffers, filesystem buffers, etc.

1) What if you have commited and the redo data has not yet "made it" to the redo log. In the middle of the way -- while this data is still in the OS cache -- the OS crashes. So, I think, Oracle is believing the commited data got to the redo logs -- but is hasn't in fact **from an OS perspective**. It just "disapeared" while in the OS cache. So redo would be unsusable. Is it a possible scenario ?

3) I imagine the so-called journaled file systems can recover from this kind of crash, but how about non-journaled file system and systems that use raw devices ?

4) Could you point me to some links or threads in this regard ?

Thanks

I first heard about JFS at this link
</code> http://www.linuxgazette.com/issue55/florido.html <code>

A quick search on google.com returned a stack of
sites

Regards
Jim

In your book you mention that LGWR (now, may be, CKPT) writes the headers of the datafiles; whereas the data in the datafiles are written by the DBWR / DBWn.

Does not this increase to the need of locking / latching ?
(One moe point of contention ?!)

Tom,

ver: 8.1.7.4

Is if the log file size is 512M. Log_check_point_interval is
25000. log_check_point_timeout = 0.

db_block_buffers= x = fast_start_io_target.

I am once checkpointing around 12M (along with regular checkpointing) . That is i am checkpointing around 50 times per log file with this one only. Don't you think its really too high to get "check point not complete " error message....

Further i am switching the log file once every 4-5 minutes during peak hr. so with overiding checkpoint priority might be i am not completing some of the checkpoints with higher priority ....

with dbwr = 2 and with db_block_buffers= x = fast_start_io_target are my DBWR not that active to flush the dirty blocks as quickly as possible.

I know i have to add more redo and might be the size also...
but....

q2) with checkpointing not complete will the database freeze for hours with out any trace or error messages to alert?

Thanks,
Kasturi

Tom,


Sorry if i was not clear enough. I was just trying to understand the below:


I was refering to Doc Id 147468.1

where

"The LOG_CHECKPOINT_INTERVAL init.ora parameter controls how often a checkpoint
operation will be performed based upon the number of operating system blocks
that have been written to the redo log. If this value is larger than the size
of the redo log, then the checkpoint will only occur when Oracle performs a
log switch from one group to another, which is preferred.

NOTE: Starting with Oracle 8.1, LOG_CHECKPOINT_INTERVAL will be interpreted
to mean that the incremental checkpoint should not lag the tail of the
log by more than log_checkpoint_interval number of redo blocks.

On most Unix systems the operating system block size is 512 bytes. This means
that setting LOG_CHECKPOINT_INTERVAL to a value of 10,000 (the default
setting), causes a checkpoint to occur after 5,120,000 (5M) bytes are written
to the redo log. If the size of your redo log is 20M, you are taking 4
checkpoints for each log.

............."



and "Starting from Oracle8i, Oracle Corporation recommends that Enterprise
Edition users who were using incremental checkpoints in an earlier release
to use fast-start checkpointing in Oracle8i. In fast-start checkpointing, the
FAST_START_IO_TARGET parameter replaces DB_FILE_MAX_DIRTY_TARGET.
FAST_START_IO_TARGET specifies the number of I/Os that should be needed during
crash or instance recovery.When you set this parameter, DBWR writes dirty buffers out
more aggressively, so that the number of blocks that must be processed during recovery
stays below the value specified in the parameter.

So in Oracle8i The incremental checkpoint position should not lag the tail of the
log by more than LOG_CHECKPOINT_INTERVAL operating system blocks.The
LOG_CHECKPOINT_INTERVAL and FAST_START_IO_TARGET is taken into account
to determine how far behind the end of the redo stream the checkpoint position can really be.

In Oracle9i FAST_START_MTTR_TARGET is the preferred method of specifying
how far the checkpoint position should be behind the tail of the redo stream. However,
LOG_CHECKPOINT_INTERVAL is still supported if needed. It functions as per the Oracle8i
behaviour above.

"
Thanks,

Kasturi

While refering to checkpoint priority:

I am refereeing to Global check points/local check points /File check points with Fast/Slow/Over ride options of check pointing.

Thanks,
Kasturi.

Tom,

My question is if i am checkpointing as frequently as 50 times per log file along with other checkpoints(as my log files are switched once for every 4-5 minutes during peak hr), that "i am getting checkpoint not complete error"? or is it purely becuase of my redo log files which are tiny?

Thanks,

Tom,
Checkpoint process updates the file headers of the data files.
What does it update the file headers of the data files with?

Thank you

Tom, Do the start and end scn in redolog files correspond to checkpoint scn on the file headers? Is this how oracle knows which log file needs to be applied in case we need to restore a datafile from backup and perform recovery? Thanks.

Tom, what is the use of the command ALTER SYSTEM CHECKPOINT as we have incremental checkpoints these days? I was wondering when this command would be useful? Thanks.

Tom, in what circumstances this command is useful for DBAs, say in 8i/9i version of oracle? Thanks.

As you say, if the checkpoint for every log file switch cannot complete on time, all transactions will halt until the earlies checkpoint complete. If this situation really occur, the following problem might exist:
- the I/O is slow.
- the redo log file size is small.

To fix the problem, we may:
1. increase the no. of log group.
2. increase the size of 1 (or every) log group.

So, is there simple guildline to determine weather I should use method 1 or 2?
Or there is method 3,4,5...etc?

My understanding is that CKPT process updates the controlfile every three seconds the current checkpoint position. Does it mean that by default incremental checkpoint occurs every 3 seconds by asking DBWR process write some number of blocks from buffer cache to data files? Thanks.

What are the different activities taking place in instance Recovery ? The order of execution.. Which processes are involved ? What buffers they are using ?

I wanted to know which process is responsible for reading from the logfiles, which buffer it is using (DB buffer or Log Buffer) for applying to data files.

(1) Assume I have created a new 9i database with default values for checkpoint related parameters like log_checkpoint_interval and the like. Then, users do concurrent transactions. My question is, does oracle do incremental checkpoint with the default settings? If so, how often does the incremental checkpoint happen? How do I find it is happening? Thanks.

(2) Does DBWR do checkpoint every three seconds by default? If so, is this called incremental checkpoint? Thanks.

You are the best.

I was reading your answers above. Then, what is meant by incremental checkpoint and how often DBWR does it? You say DBWR wakes up every 3 seconds and at that time does it do checkpoint? Thanks.

Dear Tom,

Suppose I update a record, update emp set sal=2000 where empno=123 and sal=1000;


it will have the redo and undo of the transaction as well as the redo
of the changes made to the rollback segments block.

Now you say,
"If there is UNDO that is protected by REDO -- the redo will not be reused until those dirty UNDO blocks are likewise flushed to disk -- just like data."

Suppose, I have 3 redo logs and all these redo/undo are in log file 1. During log switches these are written to disk but the transaction is not commited. Since, the transaction is not commited and both the undo and redo are to be overwritten then

1. what should be the value of sal in this emp table (on disk). I think, as per the explanation given by you, it should be 1000 since undo blocks are also flushed to disk.

2. Suppose, the user commits now, what happens ? The redo was flushed to log file which is overwritten, the dirty blocks have been flushed to disk and overwritten. We only have the undo image in the rbs which was not overwritten since the transaction was not commited.

I need some clarification on your statement and hence would request you to explain in some detail.

Thanks and Regards
Vivek Sharma

sir i am reading this thread and the documentation besides.I am sorry if my question is going to sound stupid but still i have this doubt.my doubt from reading the documentation is that what happens when do not commit and just rollback?then is the DBWR going to write the whole information back from RBS or from DBC or it will not do anything as the datablocks are not affected since all the transaction happened logically uptil now?
thnx
you are the best among the bests !!

sir you are right!but then what happens when a rollback is issued?the changed info is there in the REDO as well as in the DBC too!the old info is there in the RBS!if rollback is there then what will happen to that data tht is there in the DBC and REDO?i know that DBWR wont take part in commit and rollback but hw blocks are written when there is a rollback?is there an overwrite of info which is already there in the datafiles as nothing is changed in them?
thanx

Tom,

I have the following very basic question :

I have 2 log files and database and NOARCHIVELOG.
1.Suppose the process completes writing to log file #1 and switches to log file #2.
2.Now a log switch takes place which triggers a checkpoint process.
3. Since one of the occasion on which DBWR writes modified buffers to disk is during checkpoint and since DBWR signals LGWR to flush the redo to disk, this process seems to be catch22 situation. Can you please clarify me how these processes really work. I have read the pages you had mentioned in your book, but still not clear

could you tell me what all are things happening in oracle architechure while inserting a record to the table
ie
1)The table having 10 records &i want to insert two more records
2)I want update a particular value

Very Good.
But sometimes , the author does not seem to have understood
what the questioner is really asking, despite their being clear. But that's only sometimes.
BUt the responses are amazingly good in general.

Tom,
Sorry if this sounds incredibly dumb - but I'll ask anyway!

Why doesn't Oracle allow a "reverse" database recovery? Is it just very difficult to implement - or does that make no sense at all? Either way, it'll be great if you could help me understand.

Wouldn't database recoveries, be that much faster with a "reverse recovery"? For example in a point in time recovery: All that RMAN would have to do is restore a level 0, apply a level 1, "figure out" that it's SCN are ahead of what is required..and that it ought to "reverse" recover 1 archivelog for consistency and voila we're done.

AS AGAINST - restoring a level 0 and applying 500 archivelogs as part of "forward" recovery.

HI Tom,

I understand that the redologfile groups cannot be reused unless the checkpoint that flushes the corresponding dirty data is completed. Along with this condition, is there any other reason why the redologfile group will not be overwritten? To explain my query, i would take this scenario:

1. i have 2 redologfile groups rg1, rg2. both are small-sized
2. i have a very long running transaction.
3. the transaction starts, and the corresponding redo records are written to rg1.
4. the rg1 group gets filled up, logswitch takes place. The corresponding dirty buffers are written to the respective datafiles.
5. The transaction starts writing redo records to the group rg2.
6. The group rg2 is filled up. log switch takes place. The checkpoint responsible for flushing out dirty data corresponding to rg1 is already completed.

My question is that:
The transaction is not yet over. Will it be allowed to overwrite rg1 with the new redo records? If yes, then what would happen if there is an instance failure now? if no, then what kind of error/problem would the database encounter?

Thanks and regards,
Puja..

Instance recovery works well even if the database is in noarchivelog mode. That means the rules that are applicable to the db in archivelog mode are the same for that in noarchivelog mode as well. The instance recovery phase never looks for the archive log files, only the online redologfiles. The only difference would be that the ckpt process now needs to take care of archiving as well. My question remains the same:
Will it overwrite the first redolog group, despite the transaction still being in-doubt one.

It was helpful..

Thanks and Regards,

Puja

Hi..
Thanks for all the replies to my query regarding Reusing online redologfiles..

I am now convinced with the fact that the first loggroup would be reused once the corresponding checkpoint is finished. Now the only doubt that remains is that in case now the instance fails, then how would the recovery of the transaction take place whose redo has been overwritten? Will it go to to Rollback segment and if yes, then from where is getting the transaction id?
Thanks and Regards,

Puja

Undo is protected by redo just like any other data-- table, index etc.You have mentioned that several times in this thread as well as in your book. That's clear. My question is when the rollback segment is buffered, has the undo already been added to the segment and only the redo of the undo generated in the buffer cache or only the rollback segment is bufferd and it has undo and 'redo of the undo'--so to say appended to it in the database buffer cache?
After reading your book Expert one-on-one, my understanding is that undo information is appended to the rollback segment and then buffered. But which process does this and where in the SGA is this done? Thanks in advance.

Sorry Tom, I didn't fully get it. In your book expert one-on-one page 151, para 1, line 9 you have written:
"Undo data is added to the rollback segment, and is cached in the buffer cache just like any other piece of data would be. Like the undo generated redo may consist of many pieces of information as well."
The import of the above seems to be:
Undo information is generated and added to the rollback segment BEFORE it is buffered and naturally redo for such undo has to be generated AFTER the undo has been generated.
My question concerns specifically:
1) When exactly is the undo information generated?
2) Where(in SGA) is the undo information added to the rollback segment--I can't believe the undo information is appended when the rollback segment blocks are still on the disk!
Thanks Tom for your time and infinite patience

Tom,

I have 9 redo log groups x 100 Mb in a DSS environment, oracle 9iv2 on Sun with SAN storage raid 10. So pretty fast. Database parameters as follows. My understanding was that checkpoints should occure based on fast_start_mttr_target = 30 however they are not. The problems begis at 4 AM when Mview begin COMPLETE refresh and the hell breaks loose and all checkpoints are not complete. Is there anything else I can do? BTW mviews built with NOLOGGING, PARALLEL. Database is in NOARCHIVELOG. Any input greatly appreciated.

log_checkpoint_interval = 0
log_checkpoint_timeout = 0
fast_start_io_target = 0
fast_start_mttr_target = 30

Fri Oct 28 03:40:58 2005
Thread 1 advanced to log sequence 22965
Current log# 9 seq# 22965 mem# 0: /d01/redo09A.log
Fri Oct 28 03:42:42 2005
Thread 1 advanced to log sequence 22966
Current log# 2 seq# 22966 mem# 0: /d01/redo02A.log
Fri Oct 28 03:44:16 2005
Thread 1 advanced to log sequence 22967
Current log# 3 seq# 22967 mem# 0: /d01/redo03A.log

It starts at 4 AM when Mviews begin to refresh. I don't think making larger logs will help making DBWR lag even further. Mviews refresh lots of data therefore if any particular view has 100 Gb and it is all will be done in one transaction then no matter how many logs I have the check point will never complete. I am right? Is there way around it?

Fri Oct 28 04:00:26 2005
Thread 1 advanced to log sequence 22968
Current log# 4 seq# 22968 mem# 0: /d01/redo04A.log
Thread 1 advanced to log sequence 22969
Current log# 1 seq# 22969 mem# 0: /d01/redo01A.log
Thread 1 advanced to log sequence 22970
Current log# 5 seq# 22970 mem# 0: /d01/redo05A.log
Thread 1 advanced to log sequence 22971
Current log# 6 seq# 22971 mem# 0: /d01/redo06A.log
Thread 1 advanced to log sequence 22972
Current log# 7 seq# 22972 mem# 0: /d01/redo07A.log
Thread 1 advanced to log sequence 22973
Current log# 8 seq# 22973 mem# 0: /d01/redo08A.log
Thread 1 advanced to log sequence 22974
Current log# 9 seq# 22974 mem# 0: /d01/redo09A.log
Thread 1 advanced to log sequence 22975
Current log# 2 seq# 22975 mem# 0: /d01/redo02A.log
Thread 1 advanced to log sequence 22976
Current log# 3 seq# 22976 mem# 0: /d01/redo03A.log
Thread 1 advanced to log sequence 22977
Current log# 4 seq# 22977 mem# 0: /d01/redo04A.log
Thread 1 cannot allocate new log, sequence 22978
Checkpoint not complete
Current log# 4 seq# 22977 mem# 0: /d01/redo04A.log
Thread 1 advanced to log sequence 22978
Current log# 1 seq# 22978 mem# 0: /d01/redo01A.log
Thread 1 cannot allocate new log, sequence 22979
Checkpoint not complete
....
And so on untill 6 AM when it finishes.

So is there a way to find optimal redo log size and/or number of redo logs for recurring problem. I am kind of unsatisfied with method of "scientific poking" therefore hoping for our input.
I looked at v$instance_recovery but don’t think it’s that useful I wish in 9iv2 there was v$redo_size_advice or v$logstats.

Tom - What does it mean when stas pack report shows redo size as a negative number?

Per Second Per Transaction
--------------- --------------
Redo size: -456,871.17 -74,802,270.36

Thank you.

1-Does checkpoint occurs when we shutdown the database regardless of the status of redo log files.?

2- DBWR always reads from the redo log file what needs to be changed and then change (write) it in the datafile, it does never read from RAM(buffer cache), correct?


Thanks

SQL> select checkpoint_change#,controlfile_change# from v$database;

CHECKPOINT_CHANGE# CONTROLFILE_CHANGE#
------------------ -------------------
            203454              204955

Qs.) Why the number is different ? 

            
SQL> select controlfile_sequence# from v$database;

CONTROLFILE_SEQUENCE#
---------------------
                  293

Qs.) what is controlfile_sequence# ?
 

these below two statements are of the book "begining oracle programming"

1)A checkpoint is an event that takes place in the database THAT CAUSES database writer to flush blocks from buffer pool to disk.

2)The database writer will flush its buffer to disk periodically WHICH TRIGGERS CHECKPOINT.

Are these statements not contradicting that checkpoint causes database writer to flush blocks to disk and altoghether it says database writer flushes block that causes checkpoint......

Hi Tom

I was reading about checkpoint recently and have some doubts:

When Oracle starts its recovery process, it needs to read the log files first and determine from which point it needs to recover the data blocks. Oracle also needs to open the redo log files and

1)what is "log files" he is referring to. is it the same as redo log file....

2)why it needs to read log file ,like what it gets from there...shouldn't it read the control file.
=================================================
Each checkpoint record consists of a list of all active transactions and the address of the most recent log record for those transactions. Checkpoints are taken at specified intervals by Oracle and involve the following steps:

Flush the contents of the redo log buffers to the redo log files.

Write a checkpoint record to the redo log file.

Flush the contents of the database buffers cache to disk.

Update the data file headers and the control files after the checkpoint completes.

3)He says that checkpoint record consists of two things what is this 2nd thing "most recent log record for those transactions"...it is somewhat blurred,like what does it contains
================================================
At the same time, the control file records the checkpoint location by using a checkpoint redo byte address (RBA).


4)what is this checkpoint location?
===================================================

thanks

Hi Tom,

Thanks for quicker reply.

could you please make it a bit more clear for a novice like me that what checkpoint information is there in redo log file..i know checkpoint scn is there in datafile and control file....

thanks

I've heard of "checkpoint progress records" and those are stored in the control file. But I have never heard of a "checkpoint record" that maintains a list of active transactions. But I don't know what material is being quoted here.

For a novice, hopefully it's more than sufficient to understand that checkpointing (the verb) is how a Database Management System balances the need for high performance with the need to recover quickly from an outage.
Checkpoints (the noun) are the structures that support that activity. One can get lost trying to understand those structures and implementation in too much detail. I'm not sure when a DBA would need to know much about those structures. Just follow a good backup / recovery strategy and test it.
From a performance point of view, maybe, at most, you need to be aware that redo log size, number of log groups, and fast_start_mttr_target can influence how aggressively DBWR flushes dirty blocks to disk (as part of checkpointing - the verb). But instance tuning can be achieved without understanding how checkpoints work in much detail these days.

Hi

A transactions modifies 100 blocks, then commits. Are thse 100 blocks still considered dirty?

Hi

May be I should have stated mreo cleary so the answer would not be depends :-P

What I mean is if I modify 100 blocks, commit, since these blocks are modified they should be consider as dirty by DBWR isnt it?

I wonder if a modified block not commited and commited are considered both dirty in cache?

Can we have dirty blocks in datafiles?

Tom, 

I have a small table:
SQL> select blocks from dba_tables where table_name  = 'CWPACTIVITY';

  BLOCKS
--------
      26

That has an index (called CWPACTIVITY1) on a column called PROCESS_ID:
SQL> select blocks from dba_segments where segment_name = 'CWPACTIVITY1';

  BLOCKS
--------
      64

It appears that the DML against the table is high(lots of inserts and deletes).

When I look into the buffer pool I see:
SQL> exec ismdba.print_table('select * from whats_in_buffer where OBJECT=''CW.CWPACTIVITY1''');

BF_POOL                       : DEFAULT
OB_TYPE                       : INDEX
OBJECT                        : CW.CWPACTIVITY1
BLOCKS                        : 7543
DATA_BLOCKS                   : 64
PCT_IN                        : 11785.9
DRTY                          : #####% -    7542
CACHE_USAGE                   :  05.1% -    59MB
-----------------

PL/SQL procedure successfully completed.


 
There are 2 interesting issues:
#1 - 7542 dirty blocks, but the index is only 64 blocks
#2 - the index never gets physically bigger on disk even though PROCESS_ID is increasing and its constantly inserting and deleting in that column (this alludes to some other posts you've written about the necessity to rebuild indexes)
#3 - This is the most confusing, a checkpoint doesn't appear to write out the dirty buffers to disk, or rather, they never appear to become clean.  The dirty buffers just stay at around 7000 dirty blocks.

Reference: View - whats_in_buffer

select a.*,
to_char(round(ratio_to_report(blocks) over(partition by bf_pool)*100,1),'00.0')||'% - ' || 
to_char(round(a.blocks*(select value from v$parameter where name = 'db_block_size')/1024/1024,0),'9900') ||'MB' cache_usage
from
(
select
        /*+ ordered */
        bp.name                        bf_pool,
        o.object_type  ob_type, 
        o.owner||'.'||o.object_name                 object,
        sum(ct)                        blocks,
        seg.blocks               data_blocks,
        round(sum(ct)/ seg.blocks *100,1) pct_in,      to_char(round(sum(bh.BUF_DIRTY)/nullif(sum(ct),0)*100,1),'00.0') ||'% - '||to_char(sum(bh.BUF_DIRTY),'999900') drty    
from
        (
        select
               set_ds,
               obj,
               count(*) ct,
               SUM(DECODE(BITAND(flag,1),0,0,1)) BUF_DIRTY, 
               SUM(DECODE(BITAND(flag,1),0,1,0)) BUF_CLEAN
        from
               x$bh
        group by
               set_ds, 
               obj
        having count(*)/5 > (
                       select max(set_count) 
                       from v$buffer_pool
                       )
        )                      bh,
        dba_objects            o,
        obj$                   ob, 
        dba_segments           seg,
        x$kcbwds               ws,
        v$buffer_pool          bp
where
        o.data_object_id = bh.obj and o.object_name = seg.segment_name and o.owner = seg.owner
and     ob.dataobj# = bh.obj
and     ob.owner# > 0
and     bh.set_ds = ws.addr
and     ws.set_id between bp.lo_setid and bp.hi_setid
and     bp.buffers != 0        --  Eliminate any pools not in use
group by
        bp.name,
        object_type,
        o.owner,o.object_name,
        seg.blocks
order by blocks desc,
        bp.name,
        o.object_name) a;
 

I apologize.  I've removed all X$ view entries(is obj$ fine?).  The same situation still exists:

SQL> set serveroutput on size 999999
SQL> create view whats_in_buffer as
  2  select a.*,
  3  to_char(round(ratio_to_report(blocks) over()*100,1),'00.0')||'% - ' ||
  4  to_char(round(a.blocks*(select value from v$parameter where name = 'db_block_size')/1024/1024,0),'9900') ||'MB' cache_usage
  5  from
  6  (
  7  select
  8          /*+ ordered */
  9          o.object_type  ob_type,
 10          o.owner||'.'||o.object_name   object,
 11          sum(ct)                       blocks,
 12          seg.blocks                    data_blocks,
 13          round(sum(ct)/ seg.blocks *100,1) pct_in,
 14  to_char(round(sum(bh.BUF_DIRTY)/nullif(sum(ct),0)*100,1),'00.0') ||'% - '||to_char(sum(bh.BUF_DIRTY),'999900') drty
 15  from
 16          (
 17          select
 18                 objd obj,
 19                 count(*) ct,
 20                 SUM(case when dirty = 'Y' then 1 else 0 end) BUF_DIRTY,
 21                 SUM(case when dirty = 'N' then 1 else 0 end) BUF_CLEAN
 22          from
 23                 v$bh
 24          group by
 25                 objd
 26          having count(*)/5 > (
 27                         select max(set_count)
 28                         from v$buffer_pool
 29                         )
 30          )                      bh,
 31          dba_objects            o,
 32          obj$                   ob,
 33          dba_segments           seg
 34  where
 35          o.data_object_id = bh.obj and o.object_name = seg.segment_name and
 36  o.owner = seg.owner
 37  and     ob.dataobj# = bh.obj
 38  and     ob.owner# > 0
 39  group by
 40          object_type,
 41          o.owner,o.object_name,
 42          seg.blocks
 43  order by blocks desc,
 44          o.object_name) a;

View created.


SQL> exec print_table('select * from whats_in_buffer where OBJECT=''CW.CWPACTIVITY1''');
OB_TYPE                       : INDEX
OBJECT                        : CW.CWPACTIVITY1
BLOCKS                        : 7786
DATA_BLOCKS                   : 64
PCT_IN                        : 12165.6
DRTY                          : #####% -    7785
CACHE_USAGE                   :  05.1% -    61MB
-----------------

PL/SQL procedure successfully completed.

Your starting to make me feel like I'm back in high school with your response. Regardless, after speaking to some other oracle people they pointed me to some solutions and other issues (although, they were suprised by the scale of the problem).

The sys statistic of 'switch current to new buffer' was increasing rapidly (150 per second).  He also advised me to check the # of copies of the blocks in the buffer using another query:
SQL> select file#, block#, count(*) from v$bh where objd = 25313 group by file#, block# having count(*)>5;

     FILE#     BLOCK#   COUNT(*)
---------- ---------- ----------
         8       3990       7737

(It was querying the x$ view and I hopefully have corrected so not to make the same mistake twice in this post).

I just found this very odd and interesting behaviour.  It looked to me like a design problem that I'd never seen before and was hoping you had. 

I respect your comments about x$.  Here is some additional info (including version, which may play a part as well):

The application inserts and deletes a lot into this small table and this index is modified a lot (yet, it never gets phyiscally larger).

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production

SQL> set lin 140
SQL> select file#, block#, status, dirty, count(*) from v$bh where objd = 25313 group by file#, block#, status, dirty;

     FILE#     BLOCK# STATU D   COUNT(*)
---------- ---------- ----- - ----------
         8       3983 xcur  N          1
         8       3990 cr    N          1
         8       3990 cr    Y       7783
         8       3990 xcur  Y          1

SQL> alter system checkpoint;

System altered.

Immediately:
     FILE#     BLOCK# STATU D   COUNT(*)
---------- ---------- ----- - ----------
         8       3983 xcur  N          1
         8       3990 cr    N          1
         8       3990 cr    Y       7021
         8       3990 xcur  Y          1

After a few seconds:


     FILE#     BLOCK# STATU D   COUNT(*)
---------- ---------- ----- - ----------
         8       3983 xcur  N          1
         8       3990 cr    N          1
         8       3990 cr    Y       5737
         8       3990 xcur  Y          1

After a approx a minute:

     FILE#     BLOCK# STATU D   COUNT(*)
---------- ---------- ----- - ----------
         8       3983 xcur  N          1
         8       3990 cr    Y       2849
         8       3990 xcur  Y          1

But then...
     FILE#     BLOCK# STATU D   COUNT(*)
---------- ---------- ----- - ----------
         8       3983 xcur  N          1
         8       3990 cr    Y       3195
         8       3990 xcur  Y          1

and eventually:
     FILE#     BLOCK# STATU D   COUNT(*)
---------- ---------- ----- - ----------
         8       3983 xcur  N          1
         8       3990 cr    Y       7311
         8       3990 xcur  Y          1

It appears that the dirty blocks are mostly flushed to disk, but by the time they've all been flushed many more dirty blocks show up again, and eventually an equilibrium occurs around 7000 dirty blocks.

Again, thanks. 

The bit about the small table that undergoes a lot of DML resonates. I have worked with an application that uses a single number generator table instead of Oracle sequences. Instead of a bunch of hits to seq$, you get a bunch of hits on this number generator table. Gets/execution for a single row access ( a single row is analagous to a single sequence ) should have been very low ( < 10 I suppose ) but we saw 6,000 gpe during relatively slow times, up to 250k gpe when the demand for blocks was high. The additional LIO seems to have been due to read consistency activity - the "data blocks .. undo applied" statistic had very large numbers.

In this application the problem was fixed in by forcing one row per block in the number generator table - we'll see what happens if they ever start up multiple thread per row ( per sequence, if you will). I didn't know about v$bh at the time I was looking at this problem, I wonder if I v$bh would have shown very high CR numbers during peak load times before the fix was applied. And perhaps the buffer cache would have contained an unusually large number of undo blocks being used for read consistency(?).

Just speculating...

Yes, the last post sounds very similar. I didn't build the application, the design sounds similar, but I think they are using a sequence, however they are inserting and deleting into this temporary table (there are only ever a few thousand rows in the table) and that the sequence or id can be repeated many times.

I was only brought on because of performance issues and I noticed that there was a lot of IO problems and was investigating what was consuming so much of the buffer cache (even pushing it out). This wasn't "necessarily" the problem, but was an oddity I noticed during the investigation.

I still find the fact that this index is not growing incredible, even though it is constantly changing(rather inserting/deleting) and indexing a column that is a sequence (even if it is used multiple times).

Tom,

A question on how checkpointing is done when the datafile is taken offline.

This is the scenario

1. Insert/Update data into a table which uses datafile01
2. take datafile01 offline
3. commit
4. alter system --- log switch
5. log switches and old log group status is "active"
6. alter system checkpoint
7. old log group status is "inactive"


My question is, in this scenario how can checkpoint complete when the datafile to which data needs to be written into is offline?

Thanks,

Now the question is what happens if you roll back in step 3 instead of commit? :)

Michel

Tom,

Apologies for following up so late on the question. This is a followup to my review question on how checkpointing is done in the given scenario of taking datafile offline (October 25, 2006).

Oracle documentation says that taking a datafile offline does not perform a checkpoint.

</code> http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/statements_1004.htm#i2150231 <code>

"OFFLINE

Specify OFFLINE to take the datafile offline. If the database is open, you must perform media recovery on the datafile before bringing it back online, because a checkpoint is not performed on the datafile before it is taken offline."

Iam confused. Please, could you clear this one for me?

Thanks,

Yes. and so that redo log information is absolutely essential, right?

So, unless the datafile is brought back online and media recovery happens, how can 'alter system checkpoint' succeed and how can the redo log group be made 'INACTIVE'. By making it 'INACTIVE', the redo group becomes a candidate for reuse. It cannot be reused as it still protects information needed to bring back the datafile online.

Wouldnt there be a data loss if the redo group gets reused and the instance goes down. Next time when the datafile is brought back online, will it rely on the archive logs to bring it back online?

Thanks,

Tom, this is probably a silly question, but could you please explain why writing to the redo log files is faster than writing to the datafiles? I never really got that... Why dont we just write to the datafiles when we commit our work?

Thanks a lot.

Hi Tom,
Is there any system view from where can i know whether any nologging operation was performed after last checkpoint.


Thanks And Regards,
Anupam Pandey

Hi sir;

If I take the datafile offline, I will need recovery until last_change# of the datafile.
The reason behind this, checkpoint is not issued.
I know the task of checkpoint (write dirty blocks to disk,etc) but what is the relation between
checkpoint and recovery?
Since dirty blocks remain in buffer cache, they can be written when I bring up the datafile.
or
Suppose there is no dirty blocks of that datafile and I take it offline.
Again why do I need recovery?

Hi sir,
every committed transaction has system change number and it is recorded into control file.and record byte address is also recorded into control file.then what is difference between record byte address and system changed number?

hi sir,
i know what is redo byte address and system change number but please give me difference between RBA and SCN.and what is use of RBA and SCN................

hi sir,
Thank you very much for good description.it's solved my difficulty.

sir , i have doubt that is RBA is stored in control file?

i am waiting for your reply.
bye sir. have a nice day.

hi sir,
sorry sir,for asking same question but this question makes me more eager to know .

as explain,the RBA is a pointer into the redo stream, it is like a pointer, an arrow, a postition in a file.
but how to oracle know which pointer is useful to points to redo log.
so my question is that, is RBA information recorded into control file?

and at last thank you sir again for giving me reply

bye sir

hi sir,
Thank you sir, i understood very well about R.B.A.
but now my question is that whenever user fire statement at that time what is done first.

1)weather memory is allocated for cursor or
2)oracle checks statement in shared pool.

bye sir.

hi ,tom
how can i recover my instance if i drop my main instance(orcl) with oradim -delete
if i recreate it ever thing will work good
thanks

hi sir,
please give me answer of the following question.

For a tablespace created with automatic segment-space management, where is free space managed?



A. in the extent

B. in the control file

C. in the data dictionary

D. in the undo tablespace

bye sir.

hi,tom
how can i register my asm that i have created with oradim
in my enterprise manger(em)
(shortly when i open my em i don't found my asm instance that i have created with oradim)
although i have my mounted diskgroup

thanks

hi,tom
how can i register my asm that i have created with oradim
in my enterprise manger(em)
(shortly when i open my em i don't found my asm instance that i have created with oradim)
although i have my mounted diskgroup

thanks

hi sir,
According to me answer should be 1) in the extent. but please tell me that am i right or not? bye sir

Hi sir;

What is the difference between issuing:

alter system checkpoint;
and
flush buffer cache;

Suppose, my intention is to flush entire buffer cache.
Is there any difference between issuing above two statements?

If I am taking benchmark and want to clear buffer_cache, which one of them is preferred?

Many thanks

....one command does a checkpoint which writes out dirty blocks, this does not empty the cache

If the dirty blocks are written, there shouldnt anything else inside the buffer cache?
Am I wrong? little bit confused tho.


Suppose I am benchmarking two diffrent sql queries.In this case, I may need to flush buffer cache once I
benchmark first query.
Am I wrong?

Hi sir;

so, checkpoint writes out the dirty blocks to disk but dirty blocks are not cleared in the cache, they still exist.
am I right?
If that is the case, how does oracle understand wheter the dirty block is already written to disk or not?


BTW, i never considered os file system cache, thanks sir

Thanks sir.

I just want to confirm that;

1-)Suppose I performed a "select statament" and block doesnt exist in buffer cache, oracle will retrieve this block
from disk to buffer cache. But the block will be still clean? Am I right?

2-)The block will only be dirty, if we perform: insert,update,delete on this block am I right?


3-)so, does it mean that after checkpoint dirty blocks will not become clean automatically?

What would happen if my database goes down[say due to electricity failure or some other issue] while a huge database insertion/updation [say 100M] is going on.
My autocommit is off and half of the data is inserted/updated before database goes down. What would be the state of my table after my database is restarted.

Thanks

I know that, once the checkpoint is issued,oracle will write dirty blocks to disk.

Once the drity blocks are written to disk, do they become clean? or are they still dirty? I mean is there a background process which cleans them once they are written

Hi Tom,

I was going through the threads and found some interesting queries in my mind.

1) Checkpointing is done once log switch happens, after every 3secs or if DB cache fills (DBWR will ask LGWR to flush the info to log file and then in turn checkpoints).
Now, (i) If it happens every 3 secs, can it be in sync with the timings of LGWR (since LGWR also does it after every 3 secs, will then checkpoint)? (ii) I read that checkpoint keeps the record of last SCN occurred in the database, how does it find it? (iii)Why CKPT process updates datafile header and control file with last SCN and RBA, why cannot LGWR do it?

2) You said Undo blocks are flushed to undo segment (tablespace) in disk like data whenever log switch happens. Then (i) Is undo buffer inside DB buffer cache? (ii) Is the same LRU list maintained for undo like data? (iii) If concerned log contents of a uncommitted transaction is overwritten during log switch then does the user process finds the undo data in disk? If yes then how can it succeeds?

Hi Tom,

So you are saying that Checkpointing is happening all the time due to several causes, so the datafile header is updated with latest SCN and control files are updated with latest SCN and RBA all the time. And the recovery will happen on the basis of the SCN and RBA.

1) Is it correct?
2) Now why checkpoint number which is supposed to grow by 1 with a small update, increases by huge numbers? I came to know many other processes increases checkpoint number (or I can say SCN). So are the processes those which you had mentioned?
3) Can I calculate how much my SCN or checkpoint number will increase?

Hi Tom,

Thanks. You said that "The SCN is incremented every time a commit or rollback occurs, every time a transaction completes."
Now my queries are:
1) I thought that whenever rollback happens the SCN stays as it is. Then how can it be changed during rollback?
2) If at SCN 12000 suppose I update a row and in the mean time someone else perform another update and commits it. Then when I commit or rollback, what will be the value of the SCN in the Database at both the cases?
3) If my database is not distributed and is my personal database then can I calculate the SCN? What factors should be counted then?
4) The column checkpoint_change# in v$database will show the latest SCN. Isn't right?

Thanks,

ops$tkyte%ORA11GR2> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 3748052

ops$tkyte%ORA11GR2> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 3748053

Hi Tom,

That is quite an interesting thing.Thanks for the response. So whenever we do a commit or rollback SCN is generated. Now 1) Do the LGWR writes the corresponding change vectors in online log files for both commit and rollback as I thought SCN will be generated whenever LGWR writes in online log files?
2) We would not get an SCN if we complete a transaction but still haven't committed or rolled back. Isn't it correct?
3) Checkpoint will be always happening which will try to keep the datafiles and controlfiles updated with the SCN at that point in time.Isn't it correct? Now I can say that during database shutdown the CKPT will update datafile header and control file with latest SCN.

Hi Tom,

Thanks. I tried to get some idea by testing it in my personal database. I found that SCN is gradually increasing every time and checkpointing only makes the entry of SCN recorded that particular time. Now
1) what is making this SCN to be readily incremented? is it with time mainly?
2) I thought it is only incremented by transaction complete events(commit/rollback). But if it is only increases by time then how can I it be differentiated by whether the transaction is completed or not?
3) Also I have seen in some docs that CKPT process updates the controlfile and datafile header at every 3 secs. Is it true? (as you told earlier that checkpointing happens by various means and CKPT process to me, updates controlfile and datafile header all the time about checkpoint_change# made last time)

Hi Tom,

This is pretty much clear now. But one last question.
If CKPT updates the controlfile at every 3 secs then it shouldn't mean that checkpointing also being done at every 3 secs. I mean CKPT only updates the controlfile about the last checkpoint_change# recorded at every 3 secs. Isn't it right?

Hi Tom,

My checkpoint_change# at any point of time is suppose 12000. We know that if log buffer fills up then it may flush out some uncommitted change to online log group. So that time we have uncommitted log in the online log. Now, if we switch the log group manually or if it switches automatically then we would expect a checkpoint trigger which asks DBWn to write all dirty blocks (protected by that online log) which also includes some uncommitted data to disk.
So if that time SCN is 12030, would the CKPT process write that checkpoint_change#:12030 on datafile header since it contains uncommitted changes as well?

As per your comment:
"a checkpoint of a block will take place at some time and when it does, we no longer need the online redo log that protects that block for instance recovery (we only need it for media recovery)".
So we would not need any instance recovery till 12030. Thus can we expect the uncommited change is permanent now?

I hope I have made you aware correctly about the situation.

Thanks Tom, Nice answer.

so I would expect if instance doesn't fail/crash but if I rollback that uncommitted change, then the checkpoint_change# 12030 will not decrease.

Hi Tom,

Suppose I have checkpoint_change# at any point of time T1 is 12000.So that means all the datafile header is updated with SCN 12000 at T1. Now, I am making an update in the DB buffer cache for a block which is a part of Datafile D1 and commits it at T2. Should the block hold the then SCN (say 12030) at T2? If yes then why?

Now another transaction does some other work for block of datafile D2 at T3 (>T2), commits it. If the above scenario is true then it should contain SCN say 12050.

Now if the DB cache requires some space at T4 while SCN is 13000, then it will ask the LGWR to flush out some logs which in turn makes the DBWn to flush out concerned blocks protected by those logs. If those blocks which are flushed also include block of D1(with SCN 12030) and block of D2(with SCN 12050) datafiles, then when checkpoint will happen what will be the ultimate checkpoint_change# for all the datafiles? Will all the block header of all the datafile be incremented to SCN 13000 at T4?

Hi Tom,

1) Does "alter system checkpoint" signal DBWn to flush data block images for only committed transaction?

2) Why do we need "object checkpointing"?

3) LOG_CHECKPOINT_TIMEOUT: oracle will check what is written that time (say 60 sec) ago in log file and advance checkpoint automatically so that the time gap between checkpoint position and content of log file progress always becomes that time.
LOG_CHECKPOINT_INTERVAL: oracle will check what is written that much records ago (say 1000) ago in log file and advance checkpoint automatically so that the gap between checkpoint position and content of log file progress always becomes that much record.

Am I correct?

Hi Tom,

Can you please explain how parallel processing can bypass the cache? what type of command invokes object checkpoint or is it invoked internally?
Also why truncate or drop command uses object checkpoint?

<quote>
..everything we need must be on disk so we can "undo the drop/truncate bits we've done so far".
</quote>

You mean data dictionary changes right?


Also does truncate and drop generate redo?


Thanks

<drop and truncate generate redo - FOR THE DICTIONARY changes... >

and also redo for undo in case of failure to revert back the changes related to dictionary

so what I mean from your statement below

<everything we need must be on disk so we can "undo the drop/truncate bits we've done so far". >

is we are undo the change that made so far related to data dictionary if truncate/drop fail


Thanks

Hi Tom,

I have couple of questions regarding checkpoint:

1) Does instance recovery is performed to fill up the gap between the checkpoint and SCN in redo log at any point of time?
2) Does redo log and control file holds almost the same SCN or different?
3) For committed transactions, the dirty blocks in db buffer are written in Datafiles one after the other whenever there is any space requirement in DB buffer cache. Do every writing by DBWn during this case causes CKPT to write the SCN in datafiles and controlfiles?

Hi Tom,

Can you please elaborate in #2 above how datafile can tell which stream of redo log we need to read? (I can understand the controlfile part though that it stores RBA and redo log sequence).
Also for #3 above will checkpoint happen when uncommitted data blocks (dirty) needing space which are flushed to disk by DBWR? Is SCN at that time is also written in datafile header and control file?

Hi Tom,

This is quoting from your book "Expert Oracle Database Architecture":

"The database buffer cache is where database blocks are stored temporarily. This is a structure in Oracle’s SGA. As blocks are read, they are stored in this cache, hopefully so we won’t have to physically reread them later. The buffer cache is first and foremost a performance-tuning device. It exists solely to make the very slow process of physical I/O appear to be much faster than it is. When we modify a block by updating a row on it, these modifications are done in memory to the blocks in the buffer cache. Enough information to redo this modification is stored in the redo log buffer, another SGA data structure. When we COMMIT our modifications, making them permanent, Oracle does not go to all of the blocks we modified in the SGA and write them to disk. Rather, it just writes the contents of the redo log buffer out to the online redo logs. As long as that modified block is in the buffer cache and not on disk, we need the contents of that online redo log in case the database fails. If, at the instant after we committed, the power was turned off, the database buffer cache would be wiped out. If this happens, the only record of our change is in that redo log file. Upon restart of the database, Oracle will actually replay our transaction, modifying the block again in the same way we did and committing it for us. So, as long as that modified block is cached and not written to disk, we can’t reuse that redo log file."

All seems perfectly logical to me except for the last sentence. Why would Oracle wait for DBWn to complete its task? All that is necessary for recovery exists already in redo log. Isn't that enough? (Or is releasing cache blocks so holy?..)

Hi Tom,

In your response you wrote:

"So, as long as that modified block is cached and not
written to disk, we can’t reuse that redo log file."

My question is: why?

Isn't the mere existence of a redo log entry ensures a data block change (whether or not this block been written to disk)? Because if an instance terminates abnormally before Oracle writes block to disk, recovery will still be possible due to a redo log entry, wouldn't it?

Tom,

Thanks for making our livelihoods a lot more easier. You wouldn't even start to imagine the huge help you've been giving to us all.

You wrote:

"If we reuse the redo log - we would not have the redo log entry anymore.

So, the existence of the redo would be "not existent anymore".

That is the entire point - we have to keep that redo UNTIL the block it protects is on disk.

If we were to overwrite it - to reuse that redo log file - before we didn't need it anymore - we WOULD NOT be able to recover it with redo (because the redo doesn't EXIST)"

Question:

When you say "redo log reused" would that mean that it was first archived? (as it should be..)

Hi Tom,

Thanks for your swift and detailed response.

I'm ashamed to admit it, but the cyclical manner of redo logging has totally slipped my mind..

Your help is precious.

Many thanks,

Zvi

Hello Tom need to know one thng like if we have 3 redo log group rg1 , rg2, rg3. If after 3 seconds when lgwr moves the change vector into rg1, when it full then it will switch to rg2 after that it will switch to rg3. when it switch from rg1 to rg2, check point is occured. when checkpoint occurs data will move from buffer cache to datafiles.
Now my question is if the changes data is uncommitted, when instance fails then data that moved to datafiles already during checkpoint will be comitted automatically or will be lost when instance fails.

Hi tom,
You said,
When you commit -- the block is not
written (but the REDO LOG)

If i have 10 REDO LOG file and all REDO LOG file are in active.
My Question is,
1) How it will update the changes to disk. 10 Redo Log will have different SCN. (Since checkpoint is ACTIVE in 10 Redo log file)

2) Is a single checkpoint process that reads all the REDO content or Multiple process (ckpt)

Hi tom,

You said,
a checkpoint doesn't really read redo, a checkpoint flushes buffers from the SGA to the datafiles.

My Question,
1) When we give alter system checkpoint.
Redolog file status change from Active to Inactive.

What it mean.

2)Then how checkpoint knows the latest SCN .

Does a commit ALWAYS trigger a Checkpoint ?
If no, then for the same data, won't we have a different SCN# in the redolog and the datafile headers ?

If yes, then there is no need for the un-committed checkpoints to the datafile as it is going to be rolledback anyways. Is it for the performance sake that the periodic checkpoint of the un-committed data happens ?

Can you please clarify this doubt if you don't mind ?

Thanks

V V

hi,teacher tom:
when execute drop user statesment,whether it fires checkpoint?
how can i prove it?

Hi tom,

First i thank you for your effort to oracle community.I found your website is most helpful and i learn a lot from here.

My question is 'if a user update some rows of a table ,checkpoint initiates before user commits.what happen to the modified blocks when checkpoint started.'

Thanks