SQL> ALTER DATABASE CLEAR LOGFILE GROUP 4;
ALTER DATABASE CLEAR LOGFILE GROUP 4
*
ERROR at line 1:
ORA-00350: log 4 of instance orcl (thread 1) needs to be archived
ORA-00312: online log 4 thread 1: '/u01/app/oracle/oradata/orcl/sredo01.log'
由于未归档所以直接CLEAR失败。
SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 4;
Database altered.
CLEAR UNARCHIVED成功。
对Standby Logfile的处理办法和对Online Redo Logfile的处理办法一致。
SQL> select group#,thread#,status from v$standby_log;
GROUP# THREAD# STATUS
---------- ---------- ----------
4 1 UNASSIGNED
5 1 UNASSIGNED
6 1 UNASSIGNED
7 1 UNASSIGNED
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/oradata/orcl/REDO03.LOG
/oradata/orcl/REDO02.LOG
/oradata/orcl/REDO01.LOG
/u01/app/oracle/oradata/orcl/sredo01.log
/oradata/orcl/sredo02.log
/oradata/orcl/sredo03.log
/oradata/orcl/sredo04.log
7 rows selected.
SQL> alter database drop logfile group 4;
Database altered.
成功DROP该日志组。
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/oradata/orcl/REDO03.LOG
/oradata/orcl/REDO02.LOG
/oradata/orcl/REDO01.LOG
/oradata/orcl/sredo02.log
/oradata/orcl/sredo03.log
/oradata/orcl/sredo04.log
6 rows selected.
SQL> select group#,thread#,bytes/1024/1024 mb from v$standby_log;
GROUP# THREAD# MB
---------- ---------- ----------
5 1 50
6 1 50
7 1 50
SQL> ALTER DATABASE ADD STANDBY LOGFILE group 4 ('/oradata/orcl/sredo01.log') SIZE 50M;
Database altered.
添加GROUP 4新的位置。
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/oradata/orcl/REDO03.LOG
/oradata/orcl/REDO02.LOG
/oradata/orcl/REDO01.LOG
/oradata/orcl/sredo01.log
/oradata/orcl/sredo02.log
/oradata/orcl/sredo03.log
/oradata/orcl/sredo04.log
7 rows selected.
完成Standby Logfile的迁移。
以下两篇文章可用于该知识点的巩固：
《ALTER DATABASE CLEAR UNARCHIVED LOGFILE的使用》：
《Redo丢失的4种情况及处理方法》：
注意：以上的操作可能在备库上无法完成，解决方法是，在主库完成Standby Logfile迁移之后，主库在MOUNT状态下创建新的for Standby Controlfile（alter database create standby controlfile as '/tmp/controlf.ctl'; ），将新的Standby Controlfile和Standby Logfile传递到相同的位置，恢复备库到一致状态，打开备库，开始应用日志即可。可以参考文章《Oracle Active Data Guard调整案例[2]》：
Data Guard备库一定要是一致的状态才能open read only打开，否者执行alter database recover managed standby database应用日志，恢复到一致性的状态，再open read only。