Recreate and Resize Oracle Redo Logs

1. First see the size of the current logs:             
               
   > sqlplus /nolog
   SQL> connect / as sysdba 

select group#, bytes, status from v$log;

2. Retrieve all the log member names for the groups: 

select group#, member from v$logfile;

3. Retrieve the status of log members of the groups: 

select group#, status from v$log;

4.  In older versions of the database you needed to shutdown and issue the following

   commands in restricted mode. You can still do this, but the database can be online
   to perform these changes.

   Let's create 3 new log groups and name them groups 4, 5, and 6, each 150MB in
   size:                           

alter database add logfile group 4 '/swapphome/oracle/app/oradata/fcproddb/redo04.log' size 150m;

alter database add logfile group 5 '/swapphome/oracle/app/oradata/fcproddb/redo05.log' size 150m;

alter database add logfile group 6 '/swapphome/oracle/app/oradata/fcproddb/redo06.log' size 150m;

5. Now run a query to view the v$log status:  

SQL> select group#, status from v$log;                                       

      GROUP# STATUS
   --------- ----------------
           1 INACTIVE
           2 CURRENT
           3 INACTIVE            
           4 UNUSED
           5 UNUSED
           6 UNUSED   

6. Switch until we are into log group 4, so we can drop log groups 1, 2, and 3:

alter system switch logfile;

** repeat as necessary until group 4 is CURRENT **

7. Run the query again to verify the current log group is group 4:                                                            

                                                                
   SQL> select group#, status from v$log;                                       
                                                                  
      GROUP# STATUS
   --------- ----------------
           1 ACTIVE
           2 INACTIVE
           3 INACTIVE            
           4 CURRENT
           5 UNUSED
           6 UNUSED    

Note: redo log Group 1 or 2 or 3 can be active after "alter system switch log file" which means could not be dropped, in this case, 
you need to do "alter system checkpoint" to make redo log groups 1,2 and 3 inactive. 

8. Run the below command to make redo log groups 1,2 and 3 inactive.

SQL> alter system checkpoint;

Run the query again to verify the current log group is group 4:                                                            
                                                                
   SQL> select group#, status from v$log;                                       
                                                                  
      GROUP# STATUS
   --------- ----------------
           1 INACTIVE
           2 INACTIVE
           3 INACTIVE            
           4 CURRENT
           5 UNUSED
           6 UNUSED    


7. Now drop redo log groups 1, 2, and 3

alter database drop logfile group 1;

alter database drop logfile group 2;

alter database drop logfile group 3;

8. Verify the groups were dropped, and the new groups' sizes are correct.

   SVRMGR> select group#, bytes, status from v$log;

      GROUP#     BYTES STATUS
   --------- --------- ----------------
           4  10485760 CURRENT
           5  10485760 UNUSED
           6  10485760 UNUSED       

8.  At this point, you consider taking a backup of the database.

9.  You can now go out to the operating system and delete the filesASSOCIATED
    with redo log groups 1, 2, and 3 in step 2 above as they are no longer
    needed:
    
    % rm /usr/oracle/dbs/log1PROD.dbf
    % rm /usr/oracle/dbs/log2PROD.dbf  
    % rm /usr/oracle/dbs/log3PROD.dbf                                                 
                                                  
   Monitor the alert.log for the times of redo log switches. Due to increased
   redo log size, the groups should not switch as frequently under the same 
   load conditions.