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

[rajabalachandra]

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

[charlton]

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.

[rajabalachandra]

my log files are 1 mb size.

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

[Thomasps]

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

[rajabalachandra]

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

[Thomasps]

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 !!

[rajabalachandra]

i don't run in archivelog mode.

i think i can't use logminer

[Thomasps]

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.

[rajabalachandra]

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?

[Thomasps]

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.