altering redolog group size
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: altering redolog group size

  1. #1
    Join Date
    May 2001
    Posts
    39

    Question

    sir,
    Can u tell me the steps i should carry out to change the size of the three redolog groups i have.
    This i feel give me problems when i am importing large
    tables (500 mb each) via IMP command.
    Thanks in advance.

    Sanctus
    sanctus

  2. #2
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    You can create additional redolog groups of the correct size and delete the existing ones. Two log groups are absolutely essential to start the database, so make sure you create at least two before deleting the existing ones.

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  3. #3
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    1. query v$log and see group which has INACTIVE status.
    the group which has a status of CURRENT should be the last to be recreated(group 2 for example).
    select * from v$log;
    2. delete the logfile group which has an INACTIVE status.
    Assumed that group 3 has INACTIVE status.
    alter database drop logfile group 3;
    3. recreate again the logfile group 3 with bigger size.
    This is for mirrored redologs(common)
    alter database add logfile group 3 ('/u06/oradata/PRD/log03a.dbf','/u04/oradata/PRD/log03b.dbf') size 50M reuse;
    4. repeat step 2 and 3 for group 1.
    5. force the database to switch to new online redo logs. To force group 2 to become INACTIVE.
    alter system switch logfile.
    6. repeat step 2 and 3.

    You can add more group if you wanted to.
    Do this when no user is using your db. Or open your db in restrict mode.

    Hope this help.

  4. #4
    Join Date
    May 2001
    Posts
    39

    Question Thanx and more

    Thanx Mr.Reydp but i have another query,
    We have 3 redolog groups of 1.2mb each, but when i am
    importing large tables ,oracle warns me of redolog file size,
    and also it takes 30 to 40 minutes to import one table,
    so is it necessary to increase the existing redolog group size
    or increase the number of groups(1.2mb each).
    I hope i am clear.
    Do clear me on this oracle basics.

    Sanctus.
    sanctus

  5. #5
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    Hi Sanctus,
    You will be better if you add another group at least 5 groups and make 10mb or more from previous 1.2mb.

    Good luck.
    rey

  6. #6
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    I mean 5 groups all in all.

  7. #7
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    3-4 groups is normal and should be OK. There is really no sense of ever making more than 5 groups.


  8. #8
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    with 3 you might be in trouble if 1 of those redo logs gets STALE status, you left with 2 groups and then I/O contentions comes between LGWR and ARCH process.

    During a batch process which will give multiple transaction against the server and somehow will exhaust your redo logs, I will add 1 group for the normal 3 groups settings, and being proactive to prevent system hault when redo logs got STALE status, I add another one.

    So that's why I arrive to have it 5 groups. But that's me. :-))

  9. #9
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by reydp
    with 3 you might be in trouble if 1 of those redo logs gets STALE status, you left with 2 groups and then I/O contentions comes between LGWR and ARCH process.
    That's why I said 4 at most.

    So that's why I arrive to have it 5 groups. But that's me. :-))
    You will be fine with 4 :-))


  10. #10
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    Originally posted by julian
    Originally posted by reydp
    with 3 you might be in trouble if 1 of those redo logs gets STALE status, you left with 2 groups and then I/O contentions comes between LGWR and ARCH process.
    That's why I said 4 at most.

    So that's why I arrive to have it 5 groups. But that's me. :-))
    You will be fine with 4 :-))

    sure Julian, 4 will be fine, I usually make it 5 when there are other processes running in the server aside from Oracle process which sometimes cause redo logs to get STALE status if that process also has an I/O operation.

    so we're cool now :-))

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