cannot allocate new log, ....(from alert log)

hi,

every midnight while the date is changing i get alert
message like.

"Thread 1 cannot allocate new log, sequence 129671
Checkpoint not complete"

it goes like this for few (approx. 5) seconds and
it becomes normal after few (approx. 20) log switches.

i don't have any scheduled jobs running at this time.

can anybody help me to understand this..


thanks in advance


the following is a cutting from my alert log file.
(yea, really big piece)

Wed Dec 10 23:57:36 2003
Thread 1 advanced to log sequence 129665
Current log# 4 seq# 129665 mem# 0: D:\ORACLE\ORADATA\PHARMA\LOG4_01.ORA
Current log# 4 seq# 129665 mem# 1: E:\ORACLE\ORADATA\PHARMA\LOG4_02.ORA
Thu Dec 11 00:00:33 2003
Thread 1 advanced to log sequence 129666
Current log# 5 seq# 129666 mem# 0: D:\ORACLE\ORADATA\PHARMA\LOG5_01.ORA
Current log# 5 seq# 129666 mem# 1: E:\ORACLE\ORADATA\PHARMA\LOG5_02.ORA
Thread 1 advanced to log sequence 129667
Current log# 6 seq# 129667 mem# 0: D:\ORACLE\ORADATA\PHARMA\LOG6_01.ORA
Current log# 6 seq# 129667 mem# 1: E:\ORACLE\ORADATA\PHARMA\LOG6_02.ORA
Thread 1 cannot allocate new log, sequence 129668
Checkpoint not complete
Current log# 6 seq# 129667 mem# 0: D:\ORACLE\ORADATA\PHARMA\LOG6_01.ORA
Current log# 6 seq# 129667 mem# 1: E:\ORACLE\ORADATA\PHARMA\LOG6_02.ORA
Thread 1 advanced to log sequence 129668
Current log# 7 seq# 129668 mem# 0: D:\ORACLE\ORADATA\PHARMA\LOG7_01.ORA
Current log# 7 seq# 129668 mem# 1: E:\ORACLE\ORADATA\PHARMA\LOG7_02.ORA
Thread 1 advanced to log sequence 129669
Current log# 4 seq# 129669 mem# 0: D:\ORACLE\ORADATA\PHARMA\LOG4_01.ORA
Current log# 4 seq# 129669 mem# 1: E:\ORACLE\ORADATA\PHARMA\LOG4_02.ORA
Thread 1 advanced to log sequence 129670
Current log# 5 seq# 129670 mem# 0: D:\ORACLE\ORADATA\PHARMA\LOG5_01.ORA
Current log# 5 seq# 129670 mem# 1: E:\ORACLE\ORADATA\PHARMA\LOG5_02.ORA
Thread 1 cannot allocate new log, sequence 129671
Checkpoint not complete
Current log# 5 seq# 129670 mem# 0: D:\ORACLE\ORADATA\PHARMA\LOG5_01.ORA
Current log# 5 seq# 129670 mem# 1: E:\ORACLE\ORADATA\PHARMA\LOG5_02.ORA
Thread 1 advanced to log sequence 129671
Current log# 6 seq# 129671 mem# 0: D:\ORACLE\ORADATA\PHARMA\LOG6_01.ORA
Current log# 6 seq# 129671 mem# 1: E:\ORACLE\ORADATA\PHARMA\LOG6_02.ORA
Thread 1 advanced to log sequence 129672
Current log# 7 seq# 129672 mem# 0: D:\ORACLE\ORADATA\PHARMA\LOG7_01.ORA
Current log# 7 seq# 129672 mem# 1: E:\ORACLE\ORADATA\PHARMA\LOG7_02.ORA
Thread 1 advanced to log sequence 129673
Current log# 4 seq# 129673 mem# 0: D:\ORACLE\ORADATA\PHARMA\LOG4_01.ORA
Current log# 4 seq# 129673 mem# 1: E:\ORACLE\ORADATA\PHARMA\LOG4_02.ORA
Thread 1 cannot allocate new log, sequence 129674
Checkpoint not complete
..................................
.................................

then normal

Check the size of you redo logs.
It may be too small and you may have to recreate them with a larger size.
That's the only advice I have for you so far.

my log files are 1 mb size.

why only during the midnight - day changing time
this happens.

Check the crontab or DBMS jobs for scheduled batch jobs. I don't think this database is using for NEW DAY CREATION!

hi,

i don't have any scheduled jobs running at that time.

one of my applications deletes nearly 15,000 rows
from a table, when the date is changed.
that too, when the application is run next time
after the system date has changed.

does oracle do any house keeping for itself....?

-Raja

Using logminer investigate in the archive log files. identify the SQL statements executed. I suspect this is your delete generating the redo logs. Oracle will not do any work as its on unless you asked for it! Very lazey !!

i don't run in archivelog mode.

i think i can't use logminer

Identify the time of redo log generation. (From your posts i assume every day its happening in the same time) Write a shell script to gather the active session and SQL associated with active session. Run the shell script using crontab, get the information.

hi,

sorry not to have mentioned the full details.

i am running oracle 8i on window nt 4.0


how do i script?

you mean unix script or sqlplus script?

I meant Unix (SQL in Shell script) scripting... Some NT guys will help you. I think there is a command AT to schedule jobs in NT. Don't have much info.