Resizing redo log files!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Resizing redo log files!

  1. #1
    Join Date
    Feb 2001
    Posts
    286
    Hi all!

    Working on Oracle v8i with OS as Win2k/NT.

    Is it possible to Resize redo log files!What is the syntax to do it.

    Regards,

    Amit.
    Oracle DBA (OCP) v8i,v9i

  2. #2
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    Got this off the net, there is a world of info there if you look.



    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.

  3. #3
    Join Date
    Feb 2001
    Posts
    286

    Thanks Sureshy!

    Many thanks Sureshy!

    I was under the impression that Oracle supported the command: ALTER DATABASE DATAFILE ...... RESIZE.

    Won't ALTER DATABASE CLEAR UNARCHIVED LOG FILE work so that it would automatically drop the old ones and recreate new ones.

    By issuing the above commands,how would Oracle behave during
    recovery.My database is at present in NOArchivelog mode.What happens in case it's in Archivelog mode!

    Regards,

    Amit.



    Oracle DBA (OCP) v8i,v9i

  4. #4
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    redo log files are NOT datafiles. They are redo log files.

    ALTER DATABASE DATAFILE ...... RESIZE - resizes datafiles inside tablespaces.


    ALTER DATABASE CLEAR UNARCHIVED LOG FILE - will NOT work this is equivelant of dropping the redo log file and adding it again exactly the same size. Usually used when a log file is corrupt.

    Regarding recovery - It doesn't matter either way.

    Archive Log Mode :

    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

    No archive log mode

    Since you can only go back to a cold, offline backup (out of date) you will have to resize your redo log files again after restore.

    I suggest you back your database ASAP after a redo log resize.








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

  5. #5
    Join Date
    Oct 2002
    Posts
    391
    is there a need to alter the size of the redo log files? i read somewhere that 20M is actually the standard of one redo log file. and redo log works in a circular way so it is being reused all the time.. with the copy written to the arch directory..

    please clarify..

  6. #6
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  7. #7
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    Originally posted by yls177
    is there a need to alter the size of the redo log files? i read somewhere that 20M is actually the standard of one redo log file. and redo log works in a circular way so it is being reused all the time.. with the copy written to the arch directory..

    please clarify..
    In our production environment one database has redo log file size of 30M and other one 100M.

    The key word is 'it depends'. It depends on your application. Look for 'log file switch' wait event and 'check point not completed" msg in your alert log file to tune your redo log files.
    -nagarjuna

  8. #8
    Join Date
    Oct 2002
    Posts
    391
    wow.. thats great.. thanks for providing the key words to watch out for tuning...

  9. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Unless you have lots of massive updates, you might want to consider MORE rather than BIGGER redologs.

    We discussed this recently:
    http://www.dbasupport.com/forums/sho...threadid=34596
    Last edited by DaPi; 03-21-2003 at 11:51 AM.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

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