Procedure to drop and recreate log files

Perform following DDL/DCL commands after loging to database with OS command sqlplus "/ as sysdba".

ALTER DATABASE ADD LOGFILE GROUP 6 ('/orafiles/vis/redo/redo06a.dbf') SIZE 150m;
ALTER DATABASE ADD LOGFILE GROUP 7 ('/orafiles/vis/redo/redo07a.dbf') SIZE 150m;
ALTER DATABASE ADD LOGFILE GROUP 8 ('/orafiles/vis/redo/redo08a.dbf') SIZE 150m;
ALTER DATABASE ADD LOGFILE GROUP 9 ('/orafiles/vis/redo/redo09a.dbf') SIZE 150m;
ALTER DATABASE ADD LOGFILE GROUP 10 ('/orafiles/vis/redo/redo10a.dbf') SIZE 150m;

reapeat "alter system switch logfile;" command till CURRENT log switch reaches group 6 or 7 or 8
or 9 or 10 and old redo log group members 1-5 reaches in INACTIVE status (not in CURRENT or ACTIVE status)

alter system switch logfile;

set lines 200
select * from v$log;

The status should be something like this

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 ..... 15728640 1 YES INACTIVE 1.9618E+12 04-AUG-08
2 1 ..... 15728640 1 YES INACTIVE 1.9618E+12 04-AUG-08
3 1 ..... 10485760 1 YES INACTIVE 1.9618E+12 04-AUG-08
4 1 ..... 15728640 1 YES INACTIVE 1.9618E+12 04-AUG-08
5 1 ..... 15728640 1 YES INACTIVE 1.9618E+12 04-AUG-08
6 1 ..... 157286400 1 YES INACTIVE 1.9618E+12 04-AUG-08
7 1 ..... 157286400 1 YEs INACTIVE 1.9618E+12 04-AUG-08
8 1 ..... 157286400 1 YES INACTIVE 1.9618E+12 04-AUG-08
9 1 ..... 157286400 1 YES INACTIVE 1.9618E+12 04-AUG-08
10 1 ..... 157286400 1 YES CURRENT 1.9618E+12 04-AUG-08


if redo log group 1 to 5 are INACTIVE drop them, make surw ecorresponding OS file is removed from relevant
directory too and then recreate dropped groups back (A GROUP HAS TO BE DROPPED WHEN IT INACTIVE)

...check group 1 is INACTIVE the drop it
ALTER DATABASE DROP LOGFILE GROUP 1;

...check group 2 is INACTIVE the drop it
ALTER DATABASE DROP LOGFILE GROUP 2;

...check group 3 is INACTIVE the drop it
ALTER DATABASE DROP LOGFILE GROUP 3;

...check group 4 is INACTIVE the drop it
ALTER DATABASE DROP LOGFILE GROUP 4;

...check group 5 is INACTIVE the drop it
ALTER DATABASE DROP LOGFILE GROUP 5;

After dropping an online redo log group, make sure that the drop completed successfully, and then use
the appropriate operating system command to delete the dropped online redo log files.

rm -f /orafiles/vis/redo/redo01a.dbf
rm -f /orafiles/vis/redo/redo02a.dbf
rm -f /orafiles/vis/redo/redo03a.dbf
rm -f /orafiles/vis/redo/redo04a.dbf
rm -f /orafiles/vis/redo/redo05a.dbf

The add them again with redo log memeber size 150M

ALTER DATABASE ADD LOGFILE GROUP 1 ('/orafiles/vis/redo/redo01a.dbf') SIZE 150m;
ALTER DATABASE ADD LOGFILE GROUP 2 ('/orafiles/vis/redo/redo02a.dbf') SIZE 150m;
ALTER DATABASE ADD LOGFILE GROUP 3 ('/orafiles/vis/redo/redo03a.dbf') SIZE 150m;
ALTER DATABASE ADD LOGFILE GROUP 4 ('/orafiles/vis/redo/redo04a.dbf') SIZE 150m;
ALTER DATABASE ADD LOGFILE GROUP 5 ('/orafiles/vis/redo/redo05a.dbf') SIZE 150m;

The final status should be somthing like this...
set lines 200
select * from v$log;

The status should be something like this
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 ..... 157286400 1 NO UNUSED 1.9618E+12 04-AUG-08
2 1 ..... 157286400 1 NO UNUSED 1.9618E+12 04-AUG-08
3 1 ..... 157286400 1 NO UNUSED 1.9618E+12 04-AUG-08
4 1 ..... 157286400 1 NO UNUSED 1.9618E+12 04-AUG-08
5 1 ..... 157286400 1 NO UNUSED 1.9618E+12 04-AUG-08
6 1 ..... 157286400 1 YES INACTIVE 1.9618E+12 04-AUG-08
7 1 ..... 157286400 1 YEs INACTIVE 1.9618E+12 04-AUG-08
8 1 ..... 157286400 1 YES INACTIVE 1.9618E+12 04-AUG-08
9 1 ..... 157286400 1 YES INACTIVE 1.9618E+12 04-AUG-08
10 1 ..... 157286400 1 YES CURRENT 1.9618E+12 04-AUG-08

The perform multilple switch logs till all files come out of UNUSED status...something like the following..

alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;

The status should be something like this
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 ..... 157286400 1 YES INACTIVE 1.9618E+12 04-AUG-08
2 1 ..... 157286400 1 YES INACTIVE 1.9618E+12 04-AUG-08
3 1 ..... 157286400 1 YES INACTIVE 1.9618E+12 04-AUG-08
4 1 ..... 157286400 1 YES INACTIVE 1.9618E+12 04-AUG-08
5 1 ..... 157286400 1 YES INACTIVE 1.9618E+12 04-AUG-08
6 1 ..... 157286400 1 YES INACTIVE 1.9618E+12 04-AUG-08
7 1 ..... 157286400 1 YEs INACTIVE 1.9618E+12 04-AUG-08
8 1 ..... 157286400 1 YES CURRENT 1.9618E+12 04-AUG-08
9 1 ..... 157286400 1 YES INACTIVE 1.9618E+12 04-AUG-08
10 1 ..... 157286400 1 YES INACTIVE 1.9618E+12 04-AUG-08