OraFAQ Forum: Performance Tuning » Thread 1 cannot allocate new log, sequence 8823--Checkpoint not complete

Home » RDBMS Server » Performance Tuning » Thread 1 cannot allocate new log, sequence 8823--Checkpoint not complete (Oracle 10.2, Win 2003)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Thread 1 cannot allocate new log, sequence 8823--Checkpoint not complete [message #355125]

Wed, 22 October 2008 14:50

youngb912
Messages: 56
Registered: October 2007
Location: New York

Member

I posted on the wrong thrend...I am therefore reposting here.....

am seeing this error message in my alert log --Thread 1 cannot allocate new log------

Tue Oct 21 18:21:37 2008
LOGMINER: Begin mining logfile: I:\ORADATA\PROD\ARCHIVE\1_8816_644163510.ARC
Tue Oct 21 18:21:53 2008
Thread 1 advanced to log sequence 8822
Current log# 1 seq# 8822 mem# 0: I:\ORADATA\PROD\LOGS\LOG01_01.DBF
Tue Oct 21 18:22:12 2008
LOGMINER: End mining logfile: I:\ORADATA\PROD\ARCHIVE\1_8816_644163510.ARC
Tue Oct 21 18:22:12 2008
LOGMINER: Begin mining logfile: I:\ORADATA\PROD\ARCHIVE\1_8817_644163510.ARC
Tue Oct 21 18:22:30 2008
Thread 1 cannot allocate new log, sequence 8823
Checkpoint not complete
Current log# 1 seq# 8822 mem# 0: I:\ORADATA\PROD\LOGS\LOG01_01.DBF

Initially my redo log size was 100Meg and I increased it to 200Meg. I still see the error. I have the following related setting -

log_buffer integer 14274560
log_checkpoint_interval integer 140000
log_checkpoint_timeout integer 1800
log_checkpoints_to_alert boolean FALSE

Here is my log switch history ---

RECID START_TIME RECID END_TIME MINUTES
----- ------------------ ---------- ------------------ ----------
8839 22-oct-08 07:33:08 8840 22-oct-08 07:33:30 .38
8840 22-oct-08 07:33:30 8841 22-oct-08 07:33:48 .31
8841 22-oct-08 07:33:48 8842 22-oct-08 07:34:25 .64
8842 22-oct-08 07:34:25 8843 22-oct-08 07:35:06 .71
8843 22-oct-08 07:35:06 8844 22-oct-08 07:35:50 .76
8844 22-oct-08 07:35:50 8845 22-oct-08 07:37:11 1.41
8845 22-oct-08 07:37:11 8846 22-oct-08 08:00:22 24.15
8846 22-oct-08 08:00:22 8847 22-oct-08 09:50:04 114.27
8847 22-oct-08 09:50:04 8848 22-oct-08 11:44:59 119.7
8848 22-oct-08 11:44:59 8849 22-oct-08 12:32:40 49.67

Everytime there is that error you will see that the archivelog was size at 200Meg or the log switch was seconds/minute apart.

I will like to have the log switch occur every 30minutes. Anytime will be appreciated. I have done detail research and need help at this point.

I came across a post that said setting archive_lag_target to 1800seconds might resolve the issue. Most of this postings/documentations were relating to standby environment. my environment is streams.

Thanks in advance for your help. I really want to have the switch occur every 30 minutes instead of the 1 minute and sometime hours.

Report message to a moderator

Re: Thread 1 cannot allocate new log, sequence 8823--Checkpoint not complete [message #355143 is a reply to message #355125]

Wed, 22 October 2008 20:42

ebrian
Messages: 2794
Registered: April 2006

Senior Member

In your situation, if you do not have a standby involved, I wouldn't recommend setting archive_lag_target. In addition, I would suggest setting FAST_START_MTTR_TARGET instead of the deprecated log_checkpoint_interval and log_checkpoint_timeout parameters. With FAST_START_MTTR_TARGET set, the optimal_logfile_size column in V$INSTANCE_RECOVERY should help to set the size of the redo logs.

Several questions:

How many redo logs do you currently have ?
Are you doing bulk operations during the time when "checkpoint not complete" is occurring ?
What type of disk subsystem is being used ?

Report message to a moderator

Re: Thread 1 cannot allocate new log, sequence 8823--Checkpoint not complete [message #355329 is a reply to message #355143]

Thu, 23 October 2008 13:11

youngb912
Messages: 56
Registered: October 2007
Location: New York

Member

Thanks for the response -

How many redo logs do you currently have ?

I currently have 5 redo logs of 200meg each

Are you doing bulk operations during the time when "checkpoint not complete" is occurring ?

I'm not 100% sure that bulk operations are being done at the times that this issue occured. I sometime see this mesage "C001: long txn committed, xid: 0x001b.015.00000153" in the log at the same time as the "checkpoint not complete" message. There is also a little correlation to my apply process topping. Again, to answer your question I don't think there is bulk operation during the time.

What type of disk subsystem is being used ?

Each server has internal RAID1 15k rpm and three external RAID1 15k rpm in a Dell MD3000 connected via multipath SAS

Report message to a moderator

Re: Thread 1 cannot allocate new log, sequence 8823--Checkpoint not complete [message #355382 is a reply to message #355329]

Thu, 23 October 2008 20:09

ebrian
Messages: 2794
Registered: April 2006

Senior Member

From your log history table, there definitely seems to be a high resource process running around 7:30am. I would investigate what process is running during this time. In addition, you should incorporate 10g's parameter of FAST_START_MTTR_TARGET and unset log_checkpoint_interval and log_checkpoint_timeout values.

If the contention continues, you could try to add additional redo logs.

Report message to a moderator

Re: Thread 1 cannot allocate new log, sequence 8823--Checkpoint not complete [message #355385 is a reply to message #355382]

Thu, 23 October 2008 21:45

youngb912
Messages: 56
Registered: October 2007
Location: New York

Member

Thanks for the recommendation. I implemented it tonight and will submit a feedback within 24hrs/48hrs after monitoring the system for performance, etc.

Report message to a moderator

Re: Thread 1 cannot allocate new log, sequence 8823--Checkpoint not complete [message #356150 is a reply to message #355385]

Wed, 29 October 2008 14:59

youngb912
Messages: 56
Registered: October 2007
Location: New York

Member

I made the changes but still get inconsistency in my redo log generation frequency. I expected the log to be generated every 30mins but it still fluctuate b/w 1mins and hrs. See attached text for redo log generation frequency.

Parameters FAST_START_MTTR_TARGET is set to 1800 (30mins---shouldn't log be generated every 30mins) and I have unset log_checkpoint_interval and log_checkpoint_timeout values as shown below -

SQL> show parameter fast
NAME TYPE VALUE
------------------------------------ ----------- --------
fast_start_io_target integer 0
fast_start_mttr_target integer 1800

SQL> show parameter log
log_archive_max_processes integer 2
log_archive_min_succeed_dest integer 1
log_archive_start boolean FALSE
log_archive_trace integer 0
log_buffer integer 14274560
log_checkpoint_interval integer 0
log_checkpoint_timeout integer 0
log_checkpoints_to_alert boolean FALSE
log_file_name_convert string
logmnr_max_persistent_sessions integer 1

Thanks for your help

Attachment: REDO_LOG.txt
(Size: 18.84KB, Downloaded 1445 times)

Report message to a moderator

Re: Thread 1 cannot allocate new log, sequence 8823--Checkpoint not complete [message #356188 is a reply to message #356150]

Wed, 29 October 2008 20:56

ebrian
Messages: 2794
Registered: April 2006

Senior Member

I would recommend capturing a STATSPACK or AWR report on the instances to see what could be causing the workload.

Report message to a moderator

Previous Topic:	Do you know how to find out the underlying tables for a dynamic view?
Next Topic:	Performance issues between Databases

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jun 29 08:52:55 CDT 2024