Abnormal Log Switching - URGENT
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Abnormal Log Switching - URGENT

  1. #1
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    Hi there,

    Could anyone shed any light on why the logs in my Oracle7.3.3 database are switching far more than normal.

    We are running Peoplesoft against this database so all transactions are controlled through this, I have checked the processes scheduler and no abnormal jobs are running.

    The logs are 7 groups 2 members each 40 M, they are switching roughly every 10 to 30 seconds (this is very unusual).

    Is there something I should be looking at to discover what's going on.

    We are running in archive log mode and this switching is eating up all the space on the server (so much so that the archiver process is stopping when we run out of space).

    Any help would be greatly appreciated.

    Alison

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Are the logs full when they are being switched? If so, you have activity on your server. Investigate through v$session and v$sqlarea to find the culprit...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    Hi there marist89,

    The logs are full on each switch.

    Could I check, select's won't be written to the logs will they, V$SQLAREA is only showing selects.

    As I said my users can only access the database through Peoplesoft therefor any job that they are running should show up in the process schedular.

    Could anything else be causeing this abnormal switching.

    Cheers

    Alison

  4. #4
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    Alison,

    1. When exactly did it start happening ?
    2. Was a new schema added to the database ?
    3. Was any new repository created on this database (like the one for OEM) ?


    In 8i you can use Log mining to see what exactly is in the Logs but not in 7.3.3 !

    - Rajeev
    Rajeev Suri

  5. #5
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    Hi Rajeev

    1. On a Saturday morning the archive logs are purged from the server, ever since then I have seen this abnormal switching.

    2. No new schema's have been adder, this is a very business critical environment so all changes are approved through change control processes.

    3. No new repository either, the system has been stable and unchanged for a while now.

    I have Oracle8i databases, could I use there log mining tool to look into these logs??

    Thanks for you help

    Alison

  6. #6

    the selects

    Am I right in thinking that selects will get written to logs - after all, selects cause writes to rollback segments, which get written to logs?

    Also, how can you tell if the log is full when it was written away?

    cheers,
    Bob

  7. #7
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    Alison,
    I have never tried this but I guess there is no harm in trying to Log a 7.3.3 log by using 8i Log Miner. 40M logfile may take a while a create the output file though.

    - Rajeev
    Rajeev Suri

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    Re: the selects

    Originally posted by bmycroft
    Am I right in thinking that selects will get written to logs - after all, selects cause writes to rollback segments, which get written to logs?

    Also, how can you tell if the log is full when it was written away?

    cheers,
    Bob
    Sure, selects cause changes to the rollback segments which causes redo to be generated.

    You can tell if the log was full when the switch occurred because of it's size. If your redo logs are defined as 40M and your archived redo log is 40M, it was full.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  9. #9
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by alison
    Hi there marist89,

    As I said my users can only access the database through Peoplesoft therefor any job that they are running should show up in the process schedular.

    Could anything else be causeing this abnormal switching.

    Cheers

    Alison
    Maybe its not a user process that is hosing your db. What does the OS say is your top process in terms of CPU utilization? The fact that the logs are full when they switch indicates that Oracle is doing his job correctly. The only other possibility is a bug of some kind, but that is unlikely.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  10. #10
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407

    Wink

    bmycroft,
    If the Archived Log size is equal to the size of the Redo log then the Log was full otherwise not.

    The log may not be full and you can do a manual Log Switch and that will result in a smaller size of Archived Log.

    For the first part (SELECTs cause Rollbacks and so Redo information).. it is an interesting aspect...I never saw it from that angle... I will let others reply to that and be an observer :-)

    - Rajeev
    Rajeev Suri

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