网站开发协议书 英文版seo技术服务外包公司
环境：
- 有一个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 02 1 338 209715200 512 2 NO CURRENT 2838640 14-DEC-23 9295429630892703743 03 2 1 209715200 512 2 NO INACTIVE 2195630 13-DEC-23 2713793 14-DEC-23 04 2 2 209715200 512 2 NO CURRENT 2713793 14-DEC-23 9295429630892703743 0SQL> 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 01 ONLINE +DATAC1/ORCL/ONLINELOG/group_1.693.1155482175 NO 02 ONLINE +RECOC1/ORCL/ONLINELOG/group_2.625.1155482177 YES 02 ONLINE +DATAC1/ORCL/ONLINELOG/group_2.694.1155482175 NO 03 ONLINE +DATAC1/ORCL/ONLINELOG/group_3.697.1155482693 NO 03 ONLINE +RECOC1/ORCL/ONLINELOG/group_3.626.1155482693 YES 04 ONLINE +DATAC1/ORCL/ONLINELOG/group_4.698.1155482693 NO 04 ONLINE +RECOC1/ORCL/ONLINELOG/group_4.627.1155482693 YES 08 rows selected.
有几个实例，就有几个thread：
SQL> select thread#,status from v$thread;THREAD# STATUS
__________ _________1 OPEN2 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 requiredtwo log files per enabled thread.
*Action: Either drop fewer logs or disable the thread before deleting thelogs. 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;
好了，修改成功。
