Files with a status of:
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 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;