DB Wiki

This is an old revision of the document!

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;

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;

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#;