Oracle RAC环境下redo log 文件的扩容

环境：

• 有一个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;

好了，修改成功。