cannot allocate new log, ....(from alert log)
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

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

  1. #1
    Join Date
    Jan 2003
    Location
    india
    Posts
    175

    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

  2. #2
    Join Date
    Jan 2002
    Posts
    96
    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.

  3. #3
    Join Date
    Jan 2003
    Location
    india
    Posts
    175
    my log files are 1 mb size.

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

  4. #4
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Check the crontab or DBMS jobs for scheduled batch jobs. I don't think this database is using for NEW DAY CREATION!
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  5. #5
    Join Date
    Jan 2003
    Location
    india
    Posts
    175
    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

  6. #6
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    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 !!
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  7. #7
    Join Date
    Jan 2003
    Location
    india
    Posts
    175
    i don't run in archivelog mode.

    i think i can't use logminer

  8. #8
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    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.
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  9. #9
    Join Date
    Jan 2003
    Location
    india
    Posts
    175
    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?

  10. #10
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    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.
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width