Archive Log Interval
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Archive Log Interval

  1. #1
    Join Date
    Jan 2000
    Posts
    387

    Exclamation

    Hi,

    There is a archival log being generated every 3 mins, is it too fast? How to slow down the generation of the archive logs? Thanks!

  2. #2
    Join Date
    Jun 2000
    Posts
    179
    enlarge your redo log files in size
    Hisham Nagia
    IT Manager For Development
    Oracle Consultant - OCP

  3. #3
    Join Date
    Jan 2000
    Posts
    387
    HI,

    What is the bext interval time for the logs to be generated? How do I increase the size of the redo logs without shutting down the database? Thanks!

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by mooks
    HI,

    What is the bext interval time for the logs to be generated? How do I increase the size of the redo logs without shutting down the database? Thanks!
    1. There is no best interval but for busy systems every half an hours seems normal to me.

    2. Create new log groups (size 4M-10M for a .log file is best possible choice), then "ALTER SYSTEM SWITCH LOGFILE" as many times as necessary in order to "bite" the news log groups. Then drop the old ones.



  5. #5
    Join Date
    Jan 2000
    Posts
    387

    Arrow

    Can I dop all the old redo log group while the db is running? As long as the status is not ACTIVE then I can drop them, am i right? Then those archive logs earlier on before the log groups are dropped will be useless already, am I right?

    How do i view the members in the group? When I remove the logfile group, I will need to remove the members physically from OS level, am i right?

    [Edited by mooks on 09-12-2001 at 04:06 AM]

  6. #6
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    You must have at least 3 redo groups in order to recreate logs. Note that some DBAs use only 2, i.e., the minimum. If that is the case create a 3rd group:

    ALTER DATABASE ADD LOGFILE GROUP 'file' SIZE 5M;

    Then:

    SHUTDOWN and STARTUP RESTRICT MOUNT

    Then if the database is in archivelog mode ARCHIVE LOG ALL;

    loop for inactive:

    Drop an INACTIVE group ALTER DATABASE DROP LOGFILE GROUP ;

    Then recreate it ALTER DATABASE ADD LOGFILE GROUP 'file' SIZE 5M REUSE;

    end loop; :-)

    Then ALTER DATABASE OPEN;

    Then ALTER SYSTEM SWITCH LOGFILE;

    Then SHUTDOWN, possible BACKUP, startup.

    Isn't there in your office somebody who could assist you with all that? R U the only DBA?





  7. #7
    Join Date
    Jan 2000
    Posts
    387
    Is there any ways that I can do it without shutting down the database? Can I just :

    1. create 3 new logfile groups
    2. switch the logfile a few times
    3. drop the old inactive logfile groups
    4. remove members from OS level


  8. #8
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    Hi...

    I think this answers your question...

    Assuming you wish to add 2 new groups of 2 members each and each logfile is
    going to be 10 meg is size, try this :


    code:
    select max(group) from v$logfile;

    This gets the current highest group number - assume 3 for the following.

    code:
    alter database add logfile group 4
    'new_logfile_4a.log' size 10m,
    'new_logfile_4b.log' size 10m;

    alter database add logfile group 5
    'new_logfile_5a.log' size 10m
    'new_logfile_5b.log' size 10m;

    If you want to add an extra logfile member to each group :

    code:
    alter database add logfile member
    'new_logfile_4c' to group 4;

    alter database add logfile member
    'new_logfile_5c' to group 5;

    Next you can get rid of the old ones :

    First find out which is current :

    code:
    Select group# from v$log where status = 'CURRENT';

    This tells you the curent active group number - you can get rid of the old ones which are not current as follows :

    code:
    alter database drop logfile group N;

    Repeat until all old ones are dropped - except for the current one of course, which you can't drop !

    Then, if a new logfile group is not current :

    code:
    alter system switch logfile;

    And check the current group number again - it should be one of the new ones and you can drop the old group again.

    There you have it - an instance with new bigger logfiles. BUT, if the database is not in archivelog mode, all the redo in the old logs is gone ( so take a backup. Also, because you have changed the database structure,
    you will need :

    code:
    alter database backup controlfile to trace;

    I presume that if a recovery is required and archivelog mode is on, and one of the old redo logs (as archived) is required, Oracle will handle it no problems at all and simply askfor/use the smaller copy of the old logfiles.

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

  9. #9
    Join Date
    Jan 2000
    Posts
    387
    Thanks! That was what I have intended to do! I hope that it is not wrong! After dropping the logfile group, I can remove the members manually from the OS level right??

  10. #10
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    After dropping the logfile group, I can remove the members manually from the OS level right??
    Right. In case they are cooked files of course :-)






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