Cannot change size of REDO Log files

i am half way through a process to increase the size of my redo files and have run into a snag.  I'm on Oracle 9.2.0.8  running in noarchivelog mode on Windows 2003 server.  I started this  after reading an article about proper sizing.  I checked and my log switches on my data warehouse box switch every 5 seconds during data loads and the author stated that they should change every 5 minutes or so.  My logs are only 50M in size and I want to increase them to 100M .  

I am testing this on my staging server.  I found a previous PAC that gave the solution as:

SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;
GROUP#     ARC STATUS          
---------- --- ----------------
         1 YES INACTIVE        
         2 YES INACTIVE        
         3 NO  CURRENT        

Whichever is not current and has been archived, drop that group and recreate it with appropriate size.

ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE ADD LOGFILE GROUP 1 ('F:\Oracle\Oradata\NEDARR\REDO01A') SIZE 1M;
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE ADD LOGFILE GROUP 2 ('F:\Oracle\Oradata\NEDARR\REDO02A') SIZE 1M;

Then switch the logfile
ALTER SYSTEM ARCHIVE LOG CURRENT;
Wait for the next group to be archived.

Then recreate the remaining group.

ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE ADD LOGFILE GROUP 3 ('F:\Oracle\Oradata\NEDARR\REDO03A') SIZE 1M;


I did this and it failed when I tried to recreate the last group as shown below.

              GROUP# ARC STATUS
-------------------- --- ----------------
                   1 YES UNUSED
                   2 YES UNUSED
                   3 NO  CURRENT

3 rows selected.

NEDARS-DFREEMAN> ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE DROP LOGFILE GROUP 3
*
ERROR at line 1:
ORA-01624: log 3 needed for crash recovery of thread 1
ORA-00312: online log 3 thread 1: 'F:\ORACLE\ORADATA\NEDARS\REDO03A.LOG'

Now I'm stuck.  I would bounce the database but I suspect it is not going to want to come up.  I managed to drop groups one and 2 again and create a groups 4 and 5.  I tried to create a group 6 but it fails with:

NEDARS-DFREEMAN> ALTER DATABASE ADD LOGFILE GROUP 6 ('F:\Oracle\Oradata\NEDARR\REDO06A.LOG') SIZE 100M;
ALTER DATABASE ADD LOGFILE GROUP 6 ('F:\Oracle\Oradata\NEDARR\REDO06A.LOG') SIZE 100M
*
ERROR at line 1:
ORA-01185: logfile group number 6 is invalid


Where do I go from here?


Start Free Trial