Re: Re-sizing Redo Log Files in 8.0.5

From: Cong TANG <lai-tang_at_mycity.at>
Date: Thu, 09 Dec 1999 15:27:12 +0100
Message-ID: <384FBC40.50A82275@mycity.at>

You already followed the standard procedure to drop and create redo log files. Unfortunately, the command "Alter
database logfile group 1 resize 200M;" is not available. There are few things to be kept  in mind.
a) At least  two redo log groups are required
b) You cannot drop a redo log group when it is marked as CURRENT
c) When you drop a log member, it is not removed from the operating system.
d) Use the same size for all redo log groups.
e) Use three redo log groups instead of two. Each should have two members
f) Size the logfile so that a log switch occur every 30-45 minutes (Check the v$log_history)

I would do the following to drop and create redo log files.

1) Check the locations, sizes and status of the current log files.
SQL> select member from v$logfile;
SQL> select * from v$log;

2) Add the third log group
SQL> alter database add logfile
          group 3 ('/location1/redo3a.log', '/location2/redo3b.log') size 200M.
Note that 200MB is somewhat too much in your case. You can monitor v$log_history to determine an appropriate logfile size.

3) Recreate group 1
SQL> select * from v$log
 If group 1 is CURRENT, switch logfile
SQL> alter system switch logfile;
If group 1 is not CURRENT, you can drop it
SQL> alter database drop logfile group 1;
Then remove it from the operating system and recreate a new group 1
SQL> alter database add logfile
            group 1 ('/location1/redo1a.log', '/location2/redo1b.log') size 200M.
The REUSE option may be used instead of removing files from the OS.

4) Recreate group 2
Follow the same procedure in step 3)

Cong Tang
------------------
CTBTO
Vienna, Austria

"s. hunter" wrote:

Hi,

I wanted to resize my redo logs in an active database.  I actually
managed it but I cannot believe that there isn't an easier way to do
this.

I have 2 redo log groups containing two members each, size 10M.  I
wanted the same redo logs, but re-sized to 200M.  Here is how I did it:

(SVRMGRL)
>shutdown
>startup restrict; (etc.etc)

See which group is current (e.g. group 1 is current)
>select * from v$log;

Now, if I tried to do alter database drop group 2, I got oracle errors
ORA-01567 and ORA-00312, stating that there couldn't be fewer than 2
redo logs in thread 1. (?)

So instead, I had to create a third group:
>alter database add logfile group 3 'log3a.rdo' size 1M;

Then I could drop Group 2
>Alter database drop logfile group 2;

Remove the original redo log files at the OS level
>!rm log1a.rdo
>!rm log1b.rdo

And then recreate those log files with their new size
>Alter database add logfile group 2 ('log2a.rdo','log2b.rdo') size 200M;

Then do the same with group 1, after switching logs:
>Alter system switch logfile;

etc.

Finally, remove the "temporary" 3rd group:
>Alter database drop logfile group 3;

So is this the only way to do this?  Is the need to create another
redolog group peculiar to my database?  Why isn't there an "Alter
database logfile group 1 resize 200M;"

I'd appreciate peoples ideas and apologise for the length of this post!

Sarah

Received on Thu Dec 09 1999 - 08:27:12 CST