-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|