RAC修改redo log 日志大小

修改日志文件大小，要确保每个节点至少有两组日志文件，步骤如下：
1.查看现有日志组信息
SELECT v$logfile.group#, v$log.status,v$log.ARCHIVED, v$log.bytes/1024/1024 ,v$log.thread# FROM v$log, v$logfile WHERE v$log.group# = v$logfile.group# group by v$logfile.group#, v$log.status,v$log.ARCHIVED, v$log.bytes/1024 /1024 ,v$log.thread# order by group#;  


GROUP# STATUS     ARC V$LOG.BYTES/1024/1024    THREAD#
---------- ---------- --- --------------------- ----------
         7 CURRENT    NO                    100          1
         8 INACTIVE   YES                   100          1
         9 INACTIVE   YES                   100          1
        10 INACTIVE   YES                   100          2
        11 CURRENT    NO                    100          2
        12 INACTIVE   YES                   100          2


6 rows selected.


SQL> SELECT group#,member FROM v$logfile;


    GROUP# MEMBER
---------- --------------------------------------------------
         7 +DATA/hyw/onlinelog/group_7.270.904131423
         7 +DATA/hyw/onlinelog/group_7.271.904131447
         8 +DATA/hyw/onlinelog/group_8.272.904131463
         8 +DATA/hyw/onlinelog/group_8.273.904131479
         9 +DATA/hyw/onlinelog/group_9.274.904131505
         9 +DATA/hyw/onlinelog/group_9.275.904131519
        10 +DATA/hyw/onlinelog/group_10.276.904131535
        10 +DATA/hyw/onlinelog/group_10.277.904131549
        11 +DATA/hyw/onlinelog/group_11.278.904141003
        11 +DATA/hyw/onlinelog/group_11.279.904141013
        12 +DATA/hyw/onlinelog/group_12.280.904141025


    GROUP# MEMBER
---------- --------------------------------------------------
        12 +DATA/hyw/onlinelog/group_12.281.904141035


12 rows selected.


首先执行一次完全检查点，确保日志已经提交  
SQL> alter system checkpoint;  
  
System altered. 


其次每个实例增加3个200m日志组，每组3个日志文件
SQL> select l.GROUP#,l.THREAD#,l.ARCHIVED,l.STATUS from v$log l;


    GROUP#    THREAD# ARC STATUS
---------- ---------- --- ----------
         7          1 NO  CURRENT
         8          1 YES INACTIVE
         9          1 YES INACTIVE
        10          2 YES INACTIVE
        11          2 NO  CURRENT
        12          2 YES INACTIVE


6 rows selected.


--增加，ASM自动管理
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 1 ('+DATA', '+DATA') SIZE 200M;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 2 ('+DATA', '+DATA') SIZE 200M;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 3 ('+DATA', '+DATA') SIZE 200M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 4 ('+DATA', '+DATA') SIZE 200M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 5 ('+DATA', '+DATA') SIZE 200M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 6 ('+DATA', '+DATA') SIZE 200M;


多切换几次，验证每组日志是否可以正常使用和切换
SQL> alter system switch logfile;


--删除  
ALTER DATABASE DROP LOGFILE GROUP 7;
ALTER DATABASE DROP LOGFILE GROUP 8;
ALTER DATABASE DROP LOGFILE GROUP 9;
ALTER DATABASE DROP LOGFILE GROUP 10;
ALTER DATABASE DROP LOGFILE GROUP 11;
ALTER DATABASE DROP LOGFILE GROUP 12;




删除旧日志组时碰到的问题及解决方法
SQL> ALTER DATABASE DROP LOGFILE GROUP 7;
ALTER DATABASE DROP LOGFILE GROUP 7
*
ERROR at line 1:
ORA-00350: log 7 of instance hyw1 (thread 1) needs to be archived
ORA-00312: online log 7 thread 1: '+DATA/hyw/onlinelog/group_7.270.904131423'
ORA-00312: online log 7 thread 1: '+DATA/hyw/onlinelog/group_7.271.904131447






SQL> alter database clear unarchived logfile group 7;


Database altered.


SQL> ALTER DATABASE DROP LOGFILE GROUP 7;


Database altered.