Resize Online Redo Logs (10g) - Rough Notes

To resize redo log files, we have to drop and recreate them. The status of these files is critical.

Files with a status of:

• CURRENT - cannot/must not be dropped. To change the status of a current file, we have to initiate a log switch (ALTER SYSTEM SWITCH LOGFILE;).

• ACTIVE - Is likely to return an error; ORA-01624: log 1 needed for crash recovery of instance. This can be overcome by generating a checkpoint (ALTER SYSTEM CHECKPOINT GLOBAL;).

• INACTIVE - May be dropped.

• UNUSED - May be dropped.

Once again, the status of these files can be changed by initiating a log switch (ALTER SYSTEM SWITCH LOGFILE;).

The following SQLs assist with the task of:

Checking pertinent redo log file details

SELECT a.group#
     , a.member
     , b.bytes / 1024 / 1024 MB
     , b.status
  FROM v$logfile a
     , v$log b 
 WHERE a.group# = b.group#
ORDER BY a.group#;

Dropping redo log file groups

ALTER DATABASE DROP LOGFILE GROUP n;

Resizing redo log file

 
ALTER DATABASE ADD LOGFILE GROUP n 
('/member_path/redo_logfile_name1',  
 '/member_path/redo_logfile_name2') SIZE nM REUSE;

For a standby database the following additional steps are needed before dropping a online redo log group ...

Stop Standby Recovery

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Set Standby File Management to Manual

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

Clear Logfile Group

If the STATUS of the logfile group is CLEARING or CLEARING_CURRENT, you can set it to unused by running command

ALTER DATABASE CLEAR LOGFILE GROUP n;