System

Redo Logs

Add
Clear
Display
Drop
Move
Switch

Add

ALTER DATABASE ADD LOGFILE
GROUP <group_number> (<'Path_FileName of 1st Member File',
      'path_filename of 2nd member file' ...>)
SIZE <nnnnM|nnnnG>;

ALTER DATABASE ADD LOGFILE
GROUP 5 ('C:\oradata\rdo\redo5a.rdo','X:\oradata\rdo\redo5b.rdo')
SIZE 100m;

Group number to add must not already be used.

Clear

If an online redo log file becomes corrupt while the database is open, it may stop the database because archiving cannot continue. In this situation the ALTER DATABASE CLEAR LOGFILE statement can be used reinitialize the file without shutting down the database.

This overcomes situations where dropping redo logs is not possible. Some examples of this include:

When only two log groups exist.
When the corrupt redo log file belongs to the current group.

ALTER DATABASE CLEAR LOGFILE GROUP 3;

If the corrupt redo log file has not been archived, use the UNARCHIVED keyword in the statement. This statement clears the corrupted redo logs and avoids archiving them. Cleared redo logs can be used even though they were not archived.

ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;

Display

 COL member FORMAT a50
 SELECT group#,member,status,type FROM v$logfile ORDER BY group#,member;

 SELECT * FROM v$log;

 -- Show Redo Logs Info
 column REDOLOG_FILE_NAME format a50
 SELECT
    a.GROUP#,
    a.THREAD#,
    a.SEQUENCE#,
    a.ARCHIVED,
    a.STATUS,
    b.MEMBER    AS REDOLOG_FILE_NAME,
    (a.BYTES/1024/1024) AS SIZE_MB
 FROM v$log a
 JOIN v$logfile b ON a.Group#=b.Group# 
 ORDER BY a.GROUP# ASC;

Drop

1. Ensure status of group to drop is not CURRENT.
   SELECT * FROM v$log;
   You can issue a few switch commands if needed: ALTER SYSTEM SWITCH LOGFILE;
2. ALTER DATABASE DROP LOGFILE GROUP group_number;
   If you know it is not CURRENT and you get the message: ORA-01624:
      1. ALTER SYSTEM CHECKPOINT;
      2. ALTER DATABASE DROP LOGFILE GROUP group_number;
3. Check status: select * from v$logfile;

SELECT * FROM v$log;
ALTER SYSTEM SWITCH LOGFILE;
ALTER DATABASE DROP LOGFILE GROUP 5;
SELECT * FROM v$log;

Delete the dropped redo log member files at OS level after appropriate confirmation that all is working OK for your environment.

Move

Option 1 (online)
   1. Add new group in new location.
   2. Drop group from old location.

Option 2
   1. shutdown immediate
   2. Copy redo log file member(s) to new location.
   3. startup mount
   4. ALTER DATABASE RENAME FILE 'existing_path_and_file_name' TO 'new_path_and_file_name';
   5. ALTER DATABASE OPEN

Delete the dropped redo log member files at OS level after appropriate confirmation that all is working OK for your environment.

Switch

ALTER SYSTEM CHECKPOINT;
ALTER SYSTEM SWITCH LOGFILE;

<- System