Urgent - Archive logs genrating too frequent
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Urgent - Archive logs genrating too frequent

Hybrid View

  1. #1
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    Oracle 8.1.6
    SCO Unixware 7

    Hi Freinds,

    Since Dec 01 as of around 12:30am my archive logs have switched into overdrive. A log is being generated every 2/3 minutes when previously they were being generated every 10/20 minutes. This is causing a space issue problem.

    I need to find out why suddenly there is such an increase.
    The tablespaces are not in backup mode.
    There are no major deletes.
    Only 70 connected users currently.
    At 12:30 am there would have been hardly anyone connected.
    Database is about 3Gb in size and not growing rapidly.

    Archive logs are 10Mb
    Currently generating around 2 -300Mb of archive logs an hour!

    Checked al;ert log and got :
    Failure to extend rollback segment RBS15 because of 1650 condition. Gotr this message for RBS 15 and 14 about 4 times.

    The database has been bounced to no avail.

    Can anyone please suggest what the problem could be or give me a plan of attack to discover where all the redo log generation is coming from ?

    Thanks in advance

    Suresh


    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

  2. #2
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    Hi Friends,

    found that 1650 condition was due to insufficient space on RBS tablespace. I have increased tablespace size to resolve.

    Could this be the reason that more archive logs where being generated ? I thought that Oracle would return an error and the statment which aquired thr rollback segment would not complete. This did not happen.

    I looked at v$waitstat and got

    undo header 6
    undo block 22

    I guess this means I should created more rolback segments, although, I already have 25 for a max of 120 users...

    Any help on this would be appreciated.

    Thanks

    Suresh
    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

  3. #3
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    You might want to check the redolog size and the log switches.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  4. #4
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    Hi Sam,

    redo log size is 10Mb
    sometimes getting 5 - 7 log switches a minute (50 - 70MB)
    sometimes 1 a minute.

    Database is not growing or shrinking in size at an unusual rate.

    check v$waitstat and an getting
    undo header 48
    undo block 859

    could this have something to do with the amount of redo ?
    How?

    I believe I already have enough rollback segments (25 for 100 users)

    checking v$rollstat xacts is either 0 or 1
    transactions_per_rollback_segments set to 4
    optimal is set.





    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

  5. #5
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    The log switches seems to be too frequent. Oracle advises you to have the log switch every 30min so, you would require to increase your redo log size appropriately. This would inturn reduce the archive logs that gets generated.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  6. #6
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    Hi Sam,

    Thanks. . am going to increase redo log size.

    However this does not resolve the investigation into why the amount of redo log information generated has increased so dramatically overnight.

    I am going to use Logmnr to detect what type of redo log info is being stored.

    Thanks for your help.

    Suresh
    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

  7. #7
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by sambavan
    The log switches seems to be too frequent. Oracle advises you to have the log switch every 30min so
    The better rule is to have about 20-30 log switches a day.


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