You have online redo log files in the wrong place and want to move them.
- Run the following SQL to locate the current redo log groups and their members:
- Find a location that can contain the new log files.
- Create the new log files using the following SQL. Note that there must be at least two logfile groups after dropping unnecessary log files.
- Run the SQL from Step 1 to determine which logfile is CURRENT.
- Run the following command to switch log files. After running the SWITCH command, run the SQL from Step 1 again. Repeat the process until one of the newly created logfile groups has a CURRENT status.
- After one of the newly created log files is CURRENT, run the following SQL. You should only drop logfile groups that have an INACTIVE status.
- Repeat Step 6 for each undesired logfile group.
select l.group# group_number
, l.status group_status
, f.member group_member
, f.status file_status
from v$log l
, v$logfile f
where l.group# = f.group#
order by l.group#
SQL> connect / as sysdba
SQL> alter database add logfile ('/path/to/new/redo_01_01.log','/path/to/new/redo_01_02.log') size 150M;
SQL> alter database add logfile ('/path/to/new/redo_02_01.log','/path/to/new/redo_02_02.log') size 150M;
SQL> alter system switch logfile;
SQL> alter database drop logfile group GROUP#;
The dropped log files should have automatically been deleted from the file system.
Oracle® Database Administrator’s Guide – Managing the Redo Log