Redo Logs

Resizing Online

Add New Log Groups

If we are using OMF then all that is needed is the below 3 commands. 

ALTER DATABASE ADD LOGFILE GROUP 4 SIZE 500M;
ALTER DATABASE ADD LOGFILE GROUP 5 SIZE 500M;
ALTER DATABASE ADD LOGFILE GROUP 6 SIZE 500M;

Without OMF then the following commands will be needed. 

ALTER DATABASE ADD LOGFILE GROUP 4 (
  2  '/u02/app/oradata/redo04a.log',  
  3  '/u03/app/oradata/redo04b.log') SIZE 500M;
ALTER DATABASE ADD LOGFILE GROUP 5 (
  2  '/u02/app/oradata/redo05a.log',  
  3  '/u03/app/oradata/redo05b.log') SIZE 500M;
ALTER DATABASE ADD LOGFILE GROUP 6 (
  2  '/u02/app/oradata/redo06a.log',  
  3  '/u03/app/oradata/redo06b.log') SIZE 500M;

To add a log file to an existing redo log group … 

ALTER DATABASE ADD LOGFILE MEMBER TO GROUP 1;

Note that the status of the logfile will be invalid before it is used.

Check Status of Current Logs

SELECT GROUP#, STATUS FROM V$LOG;

Drop Old Logs

You cannot drop log files if their status is ACTIVE or CURRENT.

If the log file you want to drop is CURRENT then 

ALTER SYSTEM SWITCH LOGFILE;

If the log file you want to drop is ACTIVE then 

ALTER SYSTEM CHECKPOINT GLOBAL;
ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE DROP LOGFILE GROUP 3;

To drop individual log files from within a group … 

ALTER DATABASE DROP LOGFILE MEMBER '<Full Filename>';

View Current Log Files

set linesize 120
col MEMBER format a60
SELECT A.GROUP#, A.MEMBER, B.BYTES
FROM V$LOGFILE A, V$LOG B WHERE A.GROUP# = B.GROUP#;