Re: Checkpoint not complete error
Date: Mon, 27 Apr 2009 14:13:55 -0400
Thanks Jared, Vamshi, Sarah, Mark, Jonathan and Ivan for your valuable input.
I've set the following parameters to get a control over abrupt logswitching and havent heard any cry from the users yet. The room for performance improvement is still there though:
LOG_CHECKPOINT_INTERVAL = 10000 LOG_CHECKPOINT_TIMEOUT = 1800 LOG_CHECKPOINTS_TO_ALERT= TRUE
ARCHIVE_LAG_TARGET= 1800 And here is the extract of alert logfile after these changes reflecting the logswitch occuring in exactly 30 min:
*Mon Apr 27 13:06:52 2009
Beginning log switch checkpoint up to RBA [0xc5a8.2.10], SCN: 30981657 Thread 1 advanced to log sequence 50600
Current log# 5 seq# 50600 mem# 0:
/dblogs/oracle/CLDQA1/redo/logCLDQA105.ora Mon Apr 27 13:11:32 2009
Completed checkpoint up to RBA [0xc5a8.2.10], SCN: 30981657 Mon Apr 27 13:14:25 2009
Incremental checkpoint up to RBA [0xc5a8.419.0], current log tail at RBA [0xc5a8.419.0]
Mon Apr 27 13:36:51 2009
Beginning log switch checkpoint up to RBA [0xc5a9.2.10], SCN: 30985876 Thread 1 advanced to log sequence 50601
Current log# 6 seq# 50601 mem# 0:
/dblogs/oracle/CLDQA1/redo/logCLDQA106.ora Mon Apr 27 13:41:54 2009
Completed checkpoint up to RBA [0xc5a9.2.10], SCN: 30985876 Mon Apr 27 13:44:28 2009
Incremental checkpoint up to RBA [0xc5a9.935.0], current log tail at RBA [0xc5a9.c9e.0]
Mon Apr 27 14:06:52 2009
Beginning log switch checkpoint up to RBA [0xc5aa.2.10], SCN: 30988484*
Any suggestions to further improve the performance are appreciated.
On Fri, Apr 24, 2009 at 5:19 PM, Jared Still <jkstill_at_gmail.com> wrote:
> On Fri, Apr 24, 2009 at 1:37 PM, Saad Khan <saad4u_at_gmail.com> wrote:
>> Ok gurus, I've resized the redologs to 500MB but the users are now
>> complaining that the app is dragging. In the alert logs I dont see any other
>> message neither I see logswitches occuring fastly.
> It might be interesting to see what the user sessions are waiting on.
> Assuming of course that the users app directly logs on to the database.
> If not, then this may be less helpful.
> No one here knows anything about your application or environment.
> Increasing the number and/or size of logs is what is normally needed
> for 'cannot archive log' errors.
> But then again, maybe your storage system is overwhelmed?
> Hard to say without more info.
> But, back to the users.
> The following query will show what sessions are currently waiting on .
> Keep in mind this bit from the docs:
> WAIT_TIME NUMBER A nonzero value is the session's last wait time. A zero
> value means the session is currently waiting. SECONDS_IN_WAIT NUMBER If
> WAIT_TIME = 0, then SECONDS_IN_WAIT is the seconds spent in the current
> wait condition. If WAIT_TIME > 0, then SECONDS_IN_WAIT is the seconds
> since the start of the last wait, and SECONDS_IN_WAIT - WAIT_TIME / 100 is
> the active seconds since the last wait ended. STATE VARCHAR2(19) Wait
> 0 - WAITING (the session is currently waiting)
> -2 - WAITED UNKNOWN TIME (duration of last wait is unknown)
> -1 - WAITED SHORT TIME (last wait <1/100th of a second)
> >0 - WAITED KNOWN TIME (WAIT_TIME = duration of last wait)
> s.username username,
> e.event event,
> e.seq# seq,
> from v$session s, v$session_wait e
> where s.username is not null
> and s.sid = e.sid
> and s.username like upper('&uusername')
> -- skip sqlnet idle session messages
> and e.event not like '%message%client'
> order by s.username, upper(e.event)
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist