これをふまえて、drop/add を繰り返せばよい。
使用するコマンドは以下
確認用
set markup csv on select * from v$log; select * from v$logfile;
削除/追加用
alter database drop logfile '/u01/app/oracle/oradata/TARGET1/redo01.log' ; alter database add logfile group 1 '/u01/app/oracle/oradata/TARGET1/redo01.log' size 10M reuse ; alter database drop logfile '/u01/app/oracle/oradata/TARGET1/redo02.log' ; alter database add logfile group 2 '/u01/app/oracle/oradata/TARGET1/redo02.log' size 10M reuse ; alter database drop logfile '/u01/app/oracle/oradata/TARGET1/redo03.log' ; alter database add logfile group 3 '/u01/app/oracle/oradata/TARGET1/redo03.log' size 10M reuse ;
エラーはどれもわかりやすい。
カレントログは削除だめ。switch logfile してあげる。
SQL> alter database drop logfile '/u01/app/oracle/oradata/TARGET1/redo03.log' ; alter database drop logfile '/u01/app/oracle/oradata/TARGET1/redo03.log' * ERROR at line 1: ORA-01623: log 3 is current log for instance target1 (thread 1) - cannot drop ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/TARGET1/redo03.log'
2つ以上のログファイルが必要。削除する前に add してあげる。作業は基本、1グループずつ。
SQL> alter database drop logfile '/u01/app/oracle/oradata/TARGET1/redo02.log' ; alter database drop logfile '/u01/app/oracle/oradata/TARGET1/redo02.log' * ERROR at line 1: ORA-01567: dropping log 2 would leave less than 2 log files for instance target1 (thread 1) ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/TARGET1/redo02.log'
REDOログがACTIVE な場合。checkpoint を発行すればよい。
SQL> alter database drop logfile '/u01/app/oracle/oradata/TARGET1/redo01.log' ; alter database drop logfile '/u01/app/oracle/oradata/TARGET1/redo01.log' * ERROR at line 1: ORA-01624: log 1 needed for crash recovery of instance target1 (thread 1) ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/TARGET1/redo01.log'