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

Thread: ARCHIVE LOGS GENERATED EXCESSIVELY

  1. #1
    Join Date
    Aug 2000
    Posts
    236
    DBA's,

    I have a test database on a NT Server. There is absolutely no activity on it; no clients connected.

    For the past 4 daya, I am seeing excessive generation of archive logs (almost 10MB per day)

    What is happening here?

    Thanks,
    Nizar

  2. #2
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    Someone had asked a similar question in the past and here is what I thought was the case; let me know if it solves your problem --

    Are you running Management Server that happens to have its Repository on that database (or any other such repository) ? If yes then that generates a lot of transactions depending upon what the refresh rate of the monitored jobs is.

    You may want to look into the v$session view at different times to see what sessions (unusual sessions) are there and what are they doing.

    In case of Management server, you may want to increase the refresh interval to reduce transaction information.


    - Rajeev


  3. #3
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Following could be the reson for the archive log generation. There might be a checkpoint option enabled and that would switch the log between spcified period of time. So when there is a logswitch, an archive log will be created. If you think that the archive log is not important, on a test database, then why not just disable it. One other reasons could be because of the OMS as Rajeev, had suggested on the previous thread.

    Good luck,
    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    I'm not aware of any such parameter that allows a checkpoint to perform a logswitch. Typically, checkpoints and log switches are two seperate but related processes. I've been preaching that "CHECKPOINTS DON'T FORCE A LOGSWITCH!" Could you enlighten us? Uday might have some enlightenment for us...



    [Edited by marist89 on 02-13-2001 at 05:32 PM]
    Jeff Hunter

  5. #5
    Join Date
    Aug 2000
    Posts
    236
    I agree with Marist in that Checkpoint do not force a log switch. And I am sure of this because, all the redo logs generated are the same 1MB size, meaning they are archived because the onlines become full.

    To awnser Rajeev, yes I have the OMS running which has its repository on the same databse, but I have no pending or submitted jobs. Even if they are, monitoring of jobs should not generate 15MB of redo for a 1Gb database.

    And please enlightner me on what the DBSNMP session does.

    Thanks for the participation guys.

    Nizar

  6. #6
    Join Date
    Oct 2000
    Posts
    80
    NABAIG:

    I have the same problem on my test setup on NT4.0 SP1. I'm convinced it's an Oracle bug. My DB generates 1M redo every three and a half hours whether I'm doing anything or not.
    Also, I played with the settings of the two checkpoint parameters and they don't seem to be working right either. I set timeout to various numbers, including very small ones, and had checkpoints to alert set to true, but nothing shows up in the trace file.
    Could you do the following test and report the results, please?
    alter system set log_checkpoints_to_alert=true
    alter system set log_checkpoint timeout=120

    let it run a while and see if the checkpoints show up in your trace file?

    Much appreciated.
    John Doyle

  7. #7
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    DBSNMP session is created when you run the Intelligent Agent to monitor the database with Management Server.

    That is exactly what I was talking about. This is what is causing the redo generation. If you want to be sure, stop the Management server on this server for a while and see that the rego generation will reduce.

    You may also notice that when there is absolutely no database activity, the logs are switching at a fixed interval (5 minutes or so). This is determined by a parameter in the OMS or it could be related to Frequency you have setup for each job. I am not sure what parameter that is.

    What is the name of the user that owns the OMS Repository ?
    You may see that user in the v$session as well (of-course if you monitoring any job)

    I have a similar setup on one of the database that is used mostly for OMS repository and there is not much activity in this otherwise; I did a Log Mining of one of the logs just to prove the point and here follows the contents of -- "select
    sql_redo from v$logmnr_contents".

    set transaction read write;
    insert into REPOSIT.SMP_VDP_NODES(NODE,TIMESTAMP) values ('dba',TO_DATE('14-FEB-2001 10:22:19', 'DD-
    MON-YYYY HH24:MI:SS'));
    delete from REPOSIT.SMP_VDP_NODES where ROWID = 'AAAAzKAAGAAAAADAAE'
    commit;
    set transaction read write;

    SQL_REDO
    ----------------------------------------------------------------------------------------------------
    update REPOSIT.SMP_VDG_NODE_LIST set PROCESSING = '208.223.219.160' where ROWID = 'AAAA0aAAGAAAADjAA
    D';

    commit;
    set transaction read write;
    insert into REPOSIT.SMP_VDP_NODES(NODE,TIMESTAMP) values ('dbname.companyname.com',TO_DATE('14-FE
    B-2001 10:22:19', 'DD-MON-YYYY HH24:MI:SS'));

    delete from REPOSIT.SMP_VDP_NODES where ROWID = 'AAAAzKAAGAAAAADAAE'
    commit;
    set transaction read write;
    update REPOSIT.SMP_VDG_NODE_LIST set PROCESSING = '208.223.219.160' where ROWID = 'AAAA0aAAGAAAADjAA
    B';

    commit;
    set transaction read write;
    insert into REPOSIT.SMP_VDP_NODES(NODE,TIMESTAMP) values ('dbname.companyname.com',TO_DATE('14-FE
    B-2001 10:22:19', 'DD-MON-YYYY HH24:MI:SS'));

    delete from REPOSIT.SMP_VDP_NODES where ROWID = 'AAAAzKAAGAAAAADAAE'
    commit;


    So, you can see there are a lot of updates happening in the repository. The owner of my repository is REPOSIT

    - Rajeev


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