A reader, March 22, 2001 - 2:54 pm UTC
checkpoint frequency
Raja, August 24, 2003 - 11:34 am UTC
In Reducing Checkpoint Frequency to Optimize Runtime Performance section of the online doc,
</code>
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/instreco.htm#428332 <code>
setting the following parameters to ZERO, does checkpoint occur only at the log switch as fast-start checkpointing is disabled? It it like I take the 9i R2 database back to version 8 with regard to checkpointing as it does occur only at log switch in older version of oracle? Thanks.
LOG_CHECKPOINT_INTERVAL, LOG_CHECKPOINT_TIMEOUT, FAST_START_MTTR_TARGET and FAST_START_IO_TARGET
After log switch why I still need manually checkpoint?
frank, October 21, 2003 - 4:52 pm UTC
Tom,
Here is a testing I did:
SQL> select group#, sequence#, archived, status from v$log;
GROUP# SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
1 53 NO CURRENT
2 51 NO INACTIVE
3 52 NO INACTIVE
SQL> alter system switch logfile;
System altered.
SQL> select group#, sequence#, archived, status from v$log;
GROUP# SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
1 53 NO ACTIVE
2 54 NO CURRENT
3 52 NO INACTIVE
SQL> alter system checkpoint;
System altered.
SQL> select group#, sequence#, archived, status from v$log;
GROUP# SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
1 53 NO INACTIVE
2 54 NO CURRENT
3 52 NO INACTIVE
SQL>
=================================
From this test, I didn't see that the logswitch started the archieve process but the manual checkpoint did. If a checkpoint triggers the archieving process, and a logswtch triggers a checkpoint, then a logswitch should result in a log archive. What did I miss here?
Thanks.
October 21, 2003 - 5:52 pm UTC
? how do you think you saw that?
are you in archivelog mode
is automatic archiving enabled
a checkpoint has nothing but nothing to do with archiving redo. not sure what you are thinking you are seeing here?
all i see is that after you switched, you made group 1 active (needed for instance recovery -- it is still protecting blocks in the cache) and group 2 current. If you would have waited, eventually group 1 would become inactive all by itself (log switch will initiate a checkpoint, a really lazy, slow in the background one)
by demanding "system checkpoint RIGHT NOW" you made the redo group inactive -- no longer needed for instance recovery because all of the blocks it was protecting are on disk now and so it's not needed.
Thanks.
A reader, October 24, 2003 - 6:58 pm UTC
Didn't know there was a slow checkpoint and faster checkpoint. Thanks.
Chekpoints at logswitches only
Hatem, March 16, 2004 - 4:19 am UTC
Hi Tom
i want to achive checkpoints only at logswitch and eliminate the time and interval checkpoint so i've set log_checkpoint_interval to 999999 and log_checkpoint_timeout to zero but from my trace file it shows that the chekpoint takes about 3 hours to complete and once it completes it starts a new logswitch
i've 3 log groubs with 2 members each, each log size = 82
Tue Mar 16 00:38:40 2004
Beginning log switch checkpoint up to RBA [0x4915.2.10], SCN: 0x0000.0e23b831
Thread 1 advanced to log sequence 18709
Current log# 1 seq# 18709 mem# 0: E:\ORACLE\PRODDATA\LOG01A.DBF
Current log# 1 seq# 18709 mem# 1: E:\ORACLE\PRODDATA\LOG01B.DBF
Tue Mar 16 05:22:38 2004
Completed checkpoint up to RBA [0x4915.2.10], SCN: 0x0000.0e23b831
Tue Mar 16 06:15:02 2004
Beginning log switch checkpoint up to RBA [0x4916.2.10], SCN: 0x0000.0e2504bd
Thread 1 advanced to log sequence 18710
Current log# 2 seq# 18710 mem# 0: E:\ORACLE\PRODDATA\LOG02A.DBF
Current log# 2 seq# 18710 mem# 1: E:\ORACLE\PRODDATA\LOG02B.DBF
Tue Mar 16 09:55:42 2004
Completed checkpoint up to RBA [0x4916.2.10], SCN: 0x0000.0e2504bd
Tue Mar 16 10:08:14 2004
Beginning log switch checkpoint up to RBA [0x4917.2.10], SCN: 0x0000.0e2604ec
Thread 1 advanced to log sequence 18711
Current log# 3 seq# 18711 mem# 0: E:\ORACLE\PRODDATA\LOG03A.DBF
Current log# 3 seq# 18711 mem# 1: E:\ORACLE\PRODDATA\LOG03B.DBF
am i on the right way?
March 16, 2004 - 7:50 am UTC
why do you *think* you want to do this?
and where do you see it starting a logswitch as soon as the checkpoint completed? I see a logswitch starting sometime AFTER, but not triggered by the checkpoint completion?
Hatem, March 17, 2004 - 3:59 am UTC
Hi Tom
in peak time my db chekpoint every 2 min so i'm trying to minimize chekpoints,my prod db is about 4o gb size i think chekpointing every 2 min point cause degrade to the db
so why chekpoint every 1800 sec or every 1000 block?
anyway db checkpoint every logswitch.so i'm trying to disable timing and interval chekpoint.
i've resized my log file from 60 to 80 mb ,what i've realized is that chekpoint takes 3 hours to complete and 10 min to begin another logswitch after the last one complete
what i cant understand why it takes this time,are there any other factor cause this behaviour?
waiting your valuable comments
best regards
Hatem
March 17, 2004 - 7:41 am UTC
"i think" = "red flag"
how do you know it is checkpointing every 2 minutes.
how did you identify it as being a performance issue (what metrics did you use)
checkpoints happen lazily in the background, it is not like "oh - hey, quick, hurry up, flood the IO system -- get those blocks to disk NOW". It is more like -- 'hey, we just flipped logs, dbwr -- start getting those blocks to disk please. try to get it finished before we need to reuse this logfile'.
but, I'm not quite sure you've measured things correctly here.
Alexander, December 21, 2006 - 12:58 pm UTC
Tom, to followup on your last reply, does this mean if I want to backup my archive logs, and I issue I log switch, then backups my logs, it's likely the dirty blocks in the buffer cache were not written to disk to archive logs and thus my backup will be incomplete? Can you suggest how to do this properly is so? Thanks.
December 22, 2006 - 6:06 am UTC
dirty blocks are not written to archives - archives are full of redo, not dirty block buffers.
Beginning log switch checkpoint up to RBA - entries in alert log
Prasanth P, January 06, 2007 - 6:29 pm UTC
Tom,
I see the following entries in my alert log frequently:
Beginning log switch checkpoint up to RBA [0x5710.2.10], SCN: 9109481646570
Thread 1 advanced to log sequence 22288
Current log# 3 seq# 22288 mem# 0: /u003/oracle/oradata/cpscnprd/redo03.log
Sat Jan 6 16:18:28 2007
Completed checkpoint up to RBA [0x570e.2.10], SCN: 9109481646395
Completed checkpoint up to RBA [0x570d.2.10], SCN: 9109481646312
Completed checkpoint up to RBA [0x5710.2.10], SCN: 9109481646570
Completed checkpoint up to RBA [0x570f.2.10], SCN: 9109481646486
Sat Jan 6 16:19:00 2007
Beginning log switch checkpoint up to RBA [0x5711.2.10], SCN: 9109481647320
Thread 1 advanced to log sequence 22289
Current log# 1 seq# 22289 mem# 0: /u003/oracle/oradata/cpscnprd/redo01.log
Sat Jan 6 16:19:14 2007
Beginning log switch checkpoint up to RBA [0x5712.2.10], SCN: 9109481647417
Thread 1 advanced to log sequence 22290
Current log# 2 seq# 22290 mem# 0: /u003/oracle/oradata/cpscnprd/redo02.log
Sat Jan 6 16:20:51 2007
Beginning log switch checkpoint up to RBA [0x5713.2.10], SCN: 9109481647861
Thread 1 advanced to log sequence 22291
Current log# 4 seq# 22291 mem# 0: /u003/oracle/oradata/cpscnprd/redo04.log
Sat Jan 6 16:21:09 2007
Beginning log switch checkpoint up to RBA [0x5714.2.10], SCN: 9109481648025
Thread 1 advanced to log sequence 22292
Observations:
3 to 4 ACTIVE log groups(Status = ACTIVE in v$log)
Redo log size is 100M - 5 Groups
Archives generated are of the size ~88M
Archive generation :
High in the range 30~50 Gigs per day
Log switches are very frequent
select SEQUENCE#, to_char(COMPLETION_TIME,'DD-MM-YYYY HH24:MI:SS'),(BLOCKS*BLOCK_SIZE)/1048576 MB from v$archived_log order by 1;
22283 06-01-2007 16:17:16 88.9072266
22284 06-01-2007 16:17:33 88.902832
22285 06-01-2007 16:17:50 88.9042969
22286 06-01-2007 16:18:08 88.9082031
Init parameters:
fast_start_io_target integer 0
fast_start_mttr_target integer 60
fast_start_parallel_rollback string LOW
log_buffer integer 14274560
log_checkpoint_interval integer 1000000000
log_checkpoint_timeout integer 1800
Can you please advice on what needs to be done ?
Thanks,
Prasanth
January 07, 2007 - 8:09 pm UTC
nothing, your database is up and running and doing ok.
what problem have you identified?
Some doubts on this please
Prasanth, January 08, 2007 - 11:39 am UTC
Hi Tom,
Can you please let me know why we see 3 to 4 ACTIVE log groups(Status = ACTIVE in v$log). Also, the archive log generated is having the size 88MB , expected is 100MB as per the size of the log, Can you please let me know why this happens?
Thanks a lot!, Your response would definitely help me understand the concepts better.
Regards,
Prasanth
A reader, August 09, 2007 - 5:32 pm UTC
I purchased the 'Oracle Database 10g OCP Certification All-In-One Exam Guide' (Oracle Press - Damir Bersinic and John Watson) to prepare for the OCA/OCP exam.
http://www.amazon.com/Oracle-Database-Certification-Guide-Handbook/dp/0072257903 In Chapter 34, there is literature saying:
EXAM TIP: Many people think that a log switch triggers a checkpoint. It doesn't. It used to, but that behavior changed with release 8i, when incremental checkpointing was introduced. Now the checkpoint position continues to advance smoothly according to the algorithms described in the preceding section, with no activity at log swith time.
This seems to contradict that a log switch initiates a checkpoint. Tom, can you please comment on this discrepency. Do you think this is just an error in the book.
August 14, 2007 - 10:52 am UTC
a matter of semantics. It is true that incremental check pointing can be on - but it can be off as well. I think this is minutiae way beyond what anyone needs to know.
In general, today, checkpointing is a continual process. The act of switching logfiles does cause a "please make sure the blocks protected by this log file are on disk before I reuse this file" event to occur.
A reader, April 01, 2009 - 5:18 am UTC
Hey Tom
When I issue alter system switch logfile a checkpoint must be issued and therefore checkpoint change # of the datafiles should be incremented.
However sometimes checkpoint change # of the datafiles doesnt change after the log switch....
Why?
ie:
SQL> select substr(name,1,50) fname,checkpoint_change#,last_change#,status
from v$datafile;
FNAME
--------------------------------------------------------------------------------
CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
--------------------------------------------------------------------------------
------------
--------------------------------------------------------------------------------
C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF
219513 SYSTEM
C:\ORACLEXE\ORADATA\XE\UNDO.DBF
219513 ONLINE
C:\ORACLEXE\ORADATA\XE\SYSAUX.DBF
219513 ONLINE
FNAME
--------------------------------------------------------------------------------
CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
--------------------------------------------------------------------------------
------------
--------------------------------------------------------------------------------
C:\ORACLEXE\ORADATA\XE\USERS.DBF
219399 219399 OFFLINE
SQL> alter system switch logfile;
System altered.
select substr(name,1,50) fname,checkpoint_change#,last_change#,status
from v$datafile;
FNAME
--------------------------------------------------------------------------------
CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
--------------------------------------------------------------------------------
------------
--------------------------------------------------------------------------------
C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF
219513 SYSTEM
C:\ORACLEXE\ORADATA\XE\UNDO.DBF
219513 ONLINE
C:\ORACLEXE\ORADATA\XE\SYSAUX.DBF
219513 ONLINE
FNAME
--------------------------------------------------------------------------------
CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
--------------------------------------------------------------------------------
------------
--------------------------------------------------------------------------------
C:\ORACLEXE\ORADATA\XE\USERS.DBF
219399 219399 OFFLINE
April 01, 2009 - 9:43 am UTC
as I said, checkpoints are almost always going on.
and a log switch can initiate a checkpoint, it doesn't wait for anything to finish.
ops$tkyte%ORA10GR2> select substr(name,instr(name,'/',-1)) fname,checkpoint_change#,last_change#,status from v$datafile;
FNAME CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
------------------------------ ------------------ ------------ -------
/system01.dbf 82372096 SYSTEM
/o1_mf_demo_4rmkjx79_.dbf 82372096 ONLINE
/sysaux01.dbf 82372096 ONLINE
/users01.dbf 82372096 ONLINE
/o1_mf_undotbs_4rjnw1w7_.dbf 82372096 ONLINE
/o1_mf_big_tabl_4rs1tx1v_.dbf 82372096 ONLINE
/test.dbf 82372096 ONLINE
7 rows selected.
ops$tkyte%ORA10GR2> alter system checkpoint;
System altered.
ops$tkyte%ORA10GR2> select substr(name,instr(name,'/',-1)) fname,checkpoint_change#,last_change#,status from v$datafile;
FNAME CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
------------------------------ ------------------ ------------ -------
/system01.dbf 82372098 SYSTEM
/o1_mf_demo_4rmkjx79_.dbf 82372098 ONLINE
/sysaux01.dbf 82372098 ONLINE
/users01.dbf 82372098 ONLINE
/o1_mf_undotbs_4rjnw1w7_.dbf 82372098 ONLINE
/o1_mf_big_tabl_4rs1tx1v_.dbf 82372098 ONLINE
/test.dbf 82372098 ONLINE
7 rows selected.
<b>so after checkpoint is done - increment...</b>
ops$tkyte%ORA10GR2> select count(*) from v$log;
COUNT(*)
----------
3
<b>I have three logs</b>
ops$tkyte%ORA10GR2> alter system switch logfile;
System altered.
<b>yes, that did that checkpoint initiation - but - it doesn't have to FINISH until we go to switch back into that log again... It is lazy, background</b>
ops$tkyte%ORA10GR2> select substr(name,instr(name,'/',-1)) fname,checkpoint_change#,last_change#,status from v$datafile;
FNAME CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
------------------------------ ------------------ ------------ -------
/system01.dbf 82372098 SYSTEM
/o1_mf_demo_4rmkjx79_.dbf 82372098 ONLINE
/sysaux01.dbf 82372098 ONLINE
/users01.dbf 82372098 ONLINE
/o1_mf_undotbs_4rjnw1w7_.dbf 82372098 ONLINE
/o1_mf_big_tabl_4rs1tx1v_.dbf 82372098 ONLINE
/test.dbf 82372098 ONLINE
7 rows selected.
ops$tkyte%ORA10GR2> alter system switch logfile;
System altered.
ops$tkyte%ORA10GR2> select substr(name,instr(name,'/',-1)) fname,checkpoint_change#,last_change#,status from v$datafile;
FNAME CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
------------------------------ ------------------ ------------ -------
/system01.dbf 82372098 SYSTEM
/o1_mf_demo_4rmkjx79_.dbf 82372098 ONLINE
/sysaux01.dbf 82372098 ONLINE
/users01.dbf 82372098 ONLINE
/o1_mf_undotbs_4rjnw1w7_.dbf 82372098 ONLINE
/o1_mf_big_tabl_4rs1tx1v_.dbf 82372098 ONLINE
/test.dbf 82372098 ONLINE
7 rows selected.
<b>second log switch - the same, we switched from loga to logb and now logb to logc - but the checkpoint initiated from loga to logb - it doesn't have to be done yet..</b>
ops$tkyte%ORA10GR2> alter system switch logfile;
System altered.
<b>Now we are going from logc to loga - before that can happen, every change protected by the redo in loga MUST be on disk, must be checkpointed - this log switch will wait if it needs to for that to happen</b>
ops$tkyte%ORA10GR2> select substr(name,instr(name,'/',-1)) fname,checkpoint_change#,last_change#,status from v$datafile;
FNAME CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
------------------------------ ------------------ ------------ -------
/system01.dbf 82372100 SYSTEM
/o1_mf_demo_4rmkjx79_.dbf 82372100 ONLINE
/sysaux01.dbf 82372100 ONLINE
/users01.dbf 82372100 ONLINE
/o1_mf_undotbs_4rjnw1w7_.dbf 82372100 ONLINE
/o1_mf_big_tabl_4rs1tx1v_.dbf 82372100 ONLINE
/test.dbf 82372100 ONLINE
7 rows selected.
<b>and there you go...</b>
A reader, April 02, 2009 - 5:19 am UTC
as far as I know after every checkpoint,datafile headers(CHECKPOINT_CHANGE#) must be updated.
Since checkpoint is issued after every logswitch
why doesnt this number change? and increments only switch back into first log
April 02, 2009 - 9:59 am UTC
the checkpoint is INITIATED, STARTED, BEGUN.
I tried really hard above to demonstrate to you that concept. (please re-read the above, I said more than once "it started", "we don't have to wait", "it was begun")
If you have three logs, a, b, c...
and you switch from a to b, that INITIATES this checkpoint thing. We don't have to finish it fast - it is done lazily, in the background, slow.
when you switch from b to c, we still don't have to finish the previous work we INITIATED.
But when we switch from c to a - well, we do have to finish that INITIATED process. We cannot reuse "a" until the blocks in the cache that were protected by "a" are written out. So, you see, in my example I did three log switches to force the issue.
You could also just do the log switch and WAIT - how long? Could be a while - we don't HAVE to finish that checkpoint.
A reader, April 03, 2009 - 2:32 am UTC
Thanks a lot Tom
A reader, April 03, 2009 - 5:20 am UTC
The data is written into logbuffers untill it switches
back into first log.
After that, it should write the data from log buffer to disk by lgwr process and checkpoint finishes..
April 03, 2009 - 7:55 am UTC
no, you have the entire process wrong.
as you perform transactions, you generate redo. this redo is generated into the redo log buffer. this redo log buffer is CONSTANTLY being flushed to disk - when it is 10mb full, 1/3 full, every three seconds or every commit - whichever comes first. The log buffer is ALWAYS being flushed.
Now, say you have three logs A, B, C. As you fill log A and advance into B. That initiates (starts, begins) a checkpoint. The goal of that checkpoint? To put safely onto disk all of the blocks in the cache that are protected by the redo that is ALREADY on disk in the redo log buffer A. Before we can reuse A and overwrite the redo that is in there, we must make sure that EVERY BLOCK in the cache that is protected by the redo in A is on disk.
Vis, April 03, 2009 - 8:33 am UTC
Well, my ques is that when log switching cuases online log file 'a' to start archiving (assuming DB in archivelog) and once archiving is finished, that 'a' logfile will be available again. It shouldn't matter whether prev checkpoint process is completed or not. Am I write, Tom?
April 03, 2009 - 9:12 am UTC
no, you are absolutely wrong.
re-read what I wrote right before you posted this
you cannot re-use the A logfile UNTIL the blocks in the cache that are protected by the redo in A are safely on disk. That is what a checkpoint does.
forget archiving, that has nothing to do with checkpointing. We cannot, will not reuse A unless and until every single block protected by A is safely on disk. We only use online redo logs for instance crash recovery - we never consider the archives. If we have to consider archives - we are doing MEDIA recovery - you have restored from a backup.
Yogesh Tiwari, October 05, 2009 - 12:51 pm UTC
Thanks for clearing the doubt, that lingered for a longtime..Excellent explanation.
Incremental checkpoint
rizwan, August 17, 2011 - 2:35 am UTC
I have read this entire post but i am a bit confused about incremental checkpoint . My question is
1) what is incremental checkpoint ? when does that cocur ?
2) a log switch initiates a checkpoint . Is it full or incremental ?
3) alter system checkpoint .. This should be full checkpoint .. Am i right ?
4) Copying blocks that are protected by only one redo log file .. is it an incremental checkpoint ?
Checkpointing at the physical standby in a active dataguard environment
Rittick, January 06, 2012 - 10:54 pm UTC
How does a checkpoint at a standby in a active dataguard environment different from a checkpoint at aactive. Is it a lazy checkpoint or does the apply process stop till the checkpoint is complete? The "checkpoint complete" event (in our environment)appears as the top wait event with a very high %"timeouts" count. What can the potential cause be? How can this be addressed ?
January 10, 2012 - 9:44 pm UTC
you cannot advance over a redo log file until the data it protects is on disk - lazy or otherwise would not matter.
what is the magnitude of the 'wait' in this case?
how many logs do you have?
what are their sizes?
Checkpointing at the physical standby in a active dataguard environment - followup
Rittick Gupta, January 11, 2012 - 6:02 am UTC
The log size is 1 GByte. The standby falls behind by 20 seconds with each log switch. The primary and standby configuration are identical. Th DBWR thread seems to be taking almost 100% cpu (one processor) and it does not seem to make a difference if we configure with mutiple DBWR or have multiple io-slaves.
log file switch (checkpoint incomplete)
A reader, May 16, 2012 - 1:54 pm UTC
hi tom,
i am seeing a recent spike in my database on the alert below
"log file switch (checkpoint incomplete)"
I understand that during a logswitch, it will trigger a checkpoint, so that data protected by the redolog we are going to switch to, will be flushed to disk.
coming to that, i have several questions popping in my head and i hope it make sense
q1) what causes a log switch
-> because a log is filled up , thus it need to go to the 2nd log and so forth..
q2) why do we force a checkpoint instead of checking if the redolog is already archived ?
my thinking is that, if all we want is to protect the data changes that is recorded in the redolog before we reuse it, and since the redolog contain the changes made..
why dont oracle make sure that if the redolog is already archived, then it can be reuse ?
is it because checkpoint is faster , then archiving a redolog ?
q3) what is the above error about ?
log file switch (checkpoint incomplete) ?
r1 (current), -- i am going to switch to r2
r2,
r3
does it means the dbwr has not complete flushing the data into the disk ?
q4) how can we improve the above wait ?
Regards,
Noob
May 17, 2012 - 2:53 am UTC
q1) you answered that one
q2) huh? what does archiving have to do with checkpointing? they are two orthogonal concepts. they are not related.
We do not ever use archives for instance recovery. We only use archives for media recovery. Once the archive is cut - it is out of our control. We cannot rely on it being anywhere - you could have copied it off to tape and erased it. the only thing we KNOW will be around are the online redo logs.
archives = media recovery - only.
online redo logs = crash recovery
q3) if you have three online redo logs r1,r2,r3 - as you switch from r1 to r2 we will trigger checkpoint C-1. C-1 needs to write all of the blocks protected by the redo in r1 to disk. As you advance from r2 to r3 - that will trigger another checkpoint C-2. As you advance from r3 to r1 - we need to make sure that checkpoint C-1 is complete - that all of the blocks protected by r1 are on disk.
In your case, they are not - so we pause everything else and tell dbwr "get to work - go go go - get the data to disk". When that is done - we advance into r1.
Solutions:
a) configure more redo log files to get you through your peak times without encountering this message
b) use fast_start_mttr_target to make dbwr more aggressive about keeping the buffer cache clean - it will be more or less continuously checkpointing instead of waiting too long and building a backlog of work to do.
log file switch (checkpoint incomplete)
A reader, May 17, 2012 - 4:04 am UTC
hi tom,
marvellous. how can i actually forget online redologs are for instance recovery ~ omg
should not depend on archive for doing that.
thanks for pointing it out!
Regards,
Noob
checkpoint flush blocks onto disk= write in data files
Katy, May 20, 2012 - 8:13 am UTC
Hi Tom,
Just want to confirm my understanding from above discussion:
"When you say that at logswitch, checkpoint tries to flush the blocks in the redolog buffer cache (that are protected by online redo log files) on to the disk.
This implies that - blocks in the redo buffer cache are written on to the data files(.dbf).
Confirmation#2 in 11gR1 isqlplus is deprecated.
Regards
Katy
May 21, 2012 - 8:28 am UTC
correct.
isqlplus has been dead for a long time, yes.
When happens log switch
Mariami Kupatadze, February 18, 2015 - 1:30 pm UTC
Hello Tom,
You said that redo log switch happens when the redo log is full..ONLY?
If this is the only condition(I do not mention manual log switch by "alter system switch logfile"), why archivelog is smaller than redo?
What else causes redo log switch?
In my database:
log_buffer = 1073704960
redo log size=2147483648
archivelog size varies:
SELECT blocks*block_size
FROM v$archived_log
WHERE creator='ARCH'
ORDER BY completion_time DESC
-----------
91634176
93744128
101949952
108360192
Why? I really do not understand, sorry.
Truncate Statement
Rajeshwaran, Jeyabal, July 31, 2017 - 1:21 pm UTC
Team:
Very recently in our database, we identified that few "Truncate Table" statement was running from more than a hour.
upon monitoring the session, we identified that all the session doing "Truncate Table" operation were blocking by this wait event "log file switch (checkpoint incomplete)", the blocking session was from the Oracle background process related to "log writer" process.
after few mins, all the session doing "Truncate Table" operation were blocking by this wait event “local write wait", the blocking session was from the Oracle background process related to "DB writer" process (DBW0 and DBW2).
Could you help us to understand, why this Truncate Table statement got blocked? we are on 11.2.0.2.
August 01, 2017 - 2:37 am UTC
sounds a like a new question to me