环境：
- 有一个2节点RAC
- 每一个节点2个logfile group
- 每一个group含2个member
- 每一个member的大小为200M
目标：将每一个member的大小有200M扩充到1G。
先来看下redo log的配置：
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID
_________ __________ ____________ ____________ ____________ __________ ___________ ___________ ________________ _____________ ______________________ ____________ _________
1 1 337 209715200 512 2 NO INACTIVE 2820063 14-DEC-23 2838640 14-DEC-23 0
2 1 338 209715200 512 2 NO CURRENT 2838640 14-DEC-23 9295429630892703743 0
3 2 1 209715200 512 2 NO INACTIVE 2195630 13-DEC-23 2713793 14-DEC-23 0
4 2 2 209715200 512 2 NO CURRENT 2713793 14-DEC-23 9295429630892703743 0
SQL> select * from v$logfile order by group#;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE CON_ID
_________ _________ _________ ________________________________________________ ________________________ _________
1 ONLINE +RECOC1/ORCL/ONLINELOG/group_1.624.1155482177 YES 0
1 ONLINE +DATAC1/ORCL/ONLINELOG/group_1.693.1155482175 NO 0
2 ONLINE +RECOC1/ORCL/ONLINELOG/group_2.625.1155482177 YES 0
2 ONLINE +DATAC1/ORCL/ONLINELOG/group_2.694.1155482175 NO 0
3 ONLINE +DATAC1/ORCL/ONLINELOG/group_3.697.1155482693 NO 0
3 ONLINE +RECOC1/ORCL/ONLINELOG/group_3.626.1155482693 YES 0
4 ONLINE +DATAC1/ORCL/ONLINELOG/group_4.698.1155482693 NO 0
4 ONLINE +RECOC1/ORCL/ONLINELOG/group_4.627.1155482693 YES 0
8 rows selected.
有几个实例，就有几个thread：
SQL> select thread#,status from v$thread;
THREAD# STATUS
__________ _________
1 OPEN
2 OPEN
从SQL Developer中看到的配置：
直接删掉group重建是不行的，因为每一个实例必须保证最少2个group。
SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
Error starting at line : 1 in command -
ALTER DATABASE DROP LOGFILE GROUP 1
Error report -
ORA-01567: dropping log 1 would leave less than 2 log files for instance orcl1 (thread 1)
ORA-00312: online log 1 thread 1: '+DATAC1/ORCL/ONLINELOG/group_1.693.1155482175'
ORA-00312: online log 1 thread 1: '+RECOC1/ORCL/ONLINELOG/group_1.624.1155482177'
01567. 00000 - "dropping log %s would leave less than 2 log files for instance %s (thread %s)"
*Cause: Dropping all the logs specified would leave fewer than the required
two log files per enabled thread.
*Action: Either drop fewer logs or disable the thread before deleting the
logs. It may be possible to clear the log rather than drop it.
方法其实简单，就是先加一个临时的redo log file group，然后就可以删除重建了，最终再把这个临时的删除就好。
先处理2个状态为Inactive的log file group，即#1和#3，因为他们可以直接删。
先对实例1上的#1进行操作：
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 ('+DATAC1') SIZE 200M;
ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 1 ('+DATAC1', '+RECOC1') SIZE 1G;
ALTER DATABASE DROP LOGFILE GROUP 5;
再对实例2上的#3进行操作：
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 5 ('+DATAC1') SIZE 200M;
ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 3 ('+DATAC1', '+RECOC1') SIZE 1G;
ALTER DATABASE DROP LOGFILE GROUP 5;
现在logfile group 1和3都改好了，还剩#2和#4。
先对实例1上的#2进行操作：
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 ('+DATAC1') SIZE 200M;
此时无法删除#2，因为他的状态是current：
SQL> ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE DROP LOGFILE GROUP 2
*
ERROR at line 1:
ORA-01623: log 2 is current log for instance orcl1 (thread 1) - cannot drop
ORA-00312: online log 2 thread 1:
'+DATAC1/ORCL/ONLINELOG/group_2.694.1155482175'
ORA-00312: online log 2 thread 1:
'+RECOC1/ORCL/ONLINELOG/group_2.625.1155482177'
先做一次log switch：
SQL> alter system switch logfile;
System altered.
此时#2的状态变为Active，但仍无法删除，因为其要用于实例恢复：
SQL> ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE DROP LOGFILE GROUP 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance orcl1 (thread 1)
ORA-00312: online log 2 thread 1:
'+DATAC1/ORCL/ONLINELOG/group_2.694.1155482175'
ORA-00312: online log 2 thread 1:
'+RECOC1/ORCL/ONLINELOG/group_2.625.1155482177'
过一会，其状态变为Inactive，就可以删除了。如果实在等不急，也可以运行命令ALTER SYSTEM CHECKPOINT：
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 2 ('+DATAC1', '+RECOC1') SIZE 1G;
ALTER DATABASE DROP LOGFILE GROUP 5;
实例2也可以照此操作（连接到实例2运行）：
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 5 ('+DATAC1') SIZE 200M;
alter system switch logfile;
ALTER SYSTEM CHECKPOINT;
ALTER DATABASE DROP LOGFILE GROUP 4;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 4 ('+DATAC1', '+RECOC1') SIZE 1G;
ALTER DATABASE DROP LOGFILE GROUP 5;
好了，修改成功。
