DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

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

  1. 12-11-2003, 03:04 AM #1
    rajabalachandra
    rajabalachandra is offline Member
    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
    Reply With Quote Reply With Quote
  2. 12-11-2003, 04:42 AM #2
    charlton
    charlton is offline Junior Member
    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.
    Reply With Quote Reply With Quote
  3. 12-11-2003, 05:03 AM #3
    rajabalachandra
    rajabalachandra is offline Member
    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.
    Reply With Quote Reply With Quote
  4. 12-11-2003, 05:12 AM #4
    Thomasps
    Thomasps is offline Advisor
    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/
    Reply With Quote Reply With Quote
  5. 12-11-2003, 06:14 AM #5
    rajabalachandra
    rajabalachandra is offline Member
    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
    Reply With Quote Reply With Quote
  6. 12-11-2003, 06:18 AM #6
    Thomasps
    Thomasps is offline Advisor
    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/
    Reply With Quote Reply With Quote
  7. 12-11-2003, 06:31 AM #7
    rajabalachandra
    rajabalachandra is offline Member
    Join Date
    Jan 2003
    Location
    india
    Posts
    175
    i don't run in archivelog mode.

    i think i can't use logminer
    Reply With Quote Reply With Quote
  8. 12-11-2003, 06:48 AM #8
    Thomasps
    Thomasps is offline Advisor
    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/
    Reply With Quote Reply With Quote
  9. 12-11-2003, 06:56 AM #9
    rajabalachandra
    rajabalachandra is offline Member
    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?
    Reply With Quote Reply With Quote
  10. 12-11-2003, 07:03 AM #10
    Thomasps
    Thomasps is offline Advisor
    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/
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

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