altering redolog group size

[sanctus]

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

[Raminder]

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.

[reydp]

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.

[sanctus]

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.

[reydp]

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

[reydp]

I mean 5 groups all in all.

[julian]

3-4 groups is normal and should be OK. There is really no sense of ever making more than 5 groups.

[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.

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. :-))

[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 :-))

[reydp]

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 :-))