某电子政务系统，10g的OCFS2架构的RAC系统 redolog 设置太小、没有镜像，redo log 设置太小则容易导致日志切换过于频繁，对系统IO有影响
现在较原始的方法增加新的redo log
注:在业务的低谷期间进行redo log的删改
1.查询当前redolog 情况
SQL> select group#,thread#,SEQUENCE# ,BYTES/1024/1024 "MB", ARCHIVED,STATUS,FIRST_CHANGE# ,FIRST_TIME FROM V$LOG;
GROUP# THREAD# SEQUENCE# MB ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 436448 50 YES ACTIVE 1.3795E+13 06-MAY-14
2 1 436450 50 NO CURRENT 1.3795E+13 06-MAY-14
3 1 436449 50 YES ACTIVE 1.3795E+13 06-MAY-14
4 2 175630 50 NO CURRENT 1.3795E+13 06-MAY-14
5 2 175628 50 YES INACTIVE 1.3795E+13 06-MAY-14
6 2 175629 50 YES ACTIVE 1.3795E+13 06-MAY-14
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------- ---
1 ONLINE /oradata/orcl/redo01.log NO
2 ONLINE /oradata/orcl/redo02.log NO
3 ONLINE /oradata/orcl/redo03.log NO
4 ONLINE /oradata/orcl/redo04.log NO
5 ONLINE /oradata/orcl/redo05.log NO
6 ONLINE /oradata/orcl/redo06.log NO
2. 增加新的redo log
分别先后在节点1,2逐个操作
节点1 上操作
ALTER DATABASE
ADD LOGFILE GROUP 7 ('/oradata/orcl/redo07a.log','/oradata/orcl/redo07b.log') SIZE 400M;
节点 2 上操作
ALTER DATABASE
ADD LOGFILE GROUP 8 ('/oradata/orcl/redo08a.log','/oradata/orcl/redo08b.log') SIZE 400M;
节点1 上操作
ALTER DATABASE
ADD LOGFILE GROUP 9 ('/oradata/orcl/redo09a.log','/oradata/orcl/redo09b.log') SIZE 400M;
节点 2 上操作
ALTER DATABASE
ADD LOGFILE GROUP 10 ('/oradata/orcl/redo10a.log','/oradata/orcl/redo10b.log') SIZE 400M;
节点1 上操作
ALTER DATABASE
ADD LOGFILE GROUP 11 ('/oradata/orcl/redo11a.log','/oradata/orcl/redo11b.log') SIZE 400M;
节点 2 上操作
ALTER DATABASE
ADD LOGFILE GROUP 12 ('/oradata/orcl/redo12a.log','/oradata/orcl/redo12b.log') SIZE 400M;
2.所有新redo log 增加完成之后 ,查看目前所有redolog
SQL> select group#,thread#,SEQUENCE# ,BYTES/1024/1024 "MB", ARCHIVED,STATUS,FIRST_CHANGE# ,FIRST_TIME FROM V$LOG;
GROUP# THREAD# SEQUENCE# MB ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 436472 50 YES INACTIVE 1.3795E+13 06-MAY-14
2 1 436471 50 YES INACTIVE 1.3795E+13 06-MAY-14
3 1 436473 50 YES ACTIVE 1.3795E+13 06-MAY-14
4 2 175636 50 YES INACTIVE 1.3795E+13 06-MAY-14
5 2 175637 50 YES INACTIVE 1.3795E+13 06-MAY-14
6 2 175638 50 YES ACTIVE 1.3795E+13 06-MAY-14
7 1 436474 400 NO CURRENT 1.3795E+13 06-MAY-14
8 2 175639 400 NO CURRENT 1.3795E+13 06-MAY-14
9 1 0 400 YES UNUSED 0
10 2 0 400 YES UNUSED 0
11 1 0 400 YES UNUSED 0
12 2 0 400 YES UNUSED 0
3.设置全局检查点，使要被delete的redo log变成INACTIVE、多次切换日志、delete过小的redolog
SQL> alter system checkpoint global;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select group#,thread#,SEQUENCE# ,BYTES/1024/1024 "MB", ARCHIVED,STATUS,FIRST_CHANGE# ,FIRST_TIME FROM V$LOG;
GROUP# THREAD# SEQUENCE# MB ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 436472 50 YES INACTIVE 1.3795E+13 06-MAY-14
2 1 436471 50 YES INACTIVE 1.3795E+13 06-MAY-14
3 1 436473 50 YES INACTIVE 1.3795E+13 06-MAY-14
4 2 175636 50 YES INACTIVE 1.3795E+13 06-MAY-14
5 2 175637 50 YES INACTIVE 1.3795E+13 06-MAY-14
6 2 175638 50 YES INACTIVE 1.3795E+13 06-MAY-14
7 1 436474 400 YES INACTIVE 1.3795E+13 06-MAY-14
8 2 175639 400 NO CURRENT 1.3795E+13 06-MAY-14
9 1 436475 400 NO CURRENT 1.3795E+13 06-MAY-14
10 2 0 400 YES UNUSED 0
11 1 0 400 YES UNUSED 0
12 2 0 400 YES UNUSED 0
4.delete原来50M 状态为INACTIVE的redo log 若原来的redo log状态为非INACTIVE，则多做几次全局检查点和切换redo log 即可
alter database drop logfile group 1；
alter database drop logfile group 2；
alter database drop logfile group 3；
alter database drop logfile group 4；
alter database drop logfile group 5；
alter database drop logfile group 6；
5.查看当前最新redo log
SQL> select group#,thread#,SEQUENCE# ,BYTES/1024/1024 "MB", ARCHIVED,STATUS,FIRST_CHANGE# ,FIRST_TIME FROM V$LOG;
GROUP# THREAD# SEQUENCE# MB ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
7 1 436474 400 YES INACTIVE 1.3795E+13 06-MAY-14
8 2 175639 400 NO CURRENT 1.3795E+13 06-MAY-14
9 1 436475 400 NO CURRENT 1.3795E+13 06-MAY-14
10 2 0 400 YES UNUSED 0
11 1 0 400 YES UNUSED 0
12 2 0 400 YES UNUSED 0
SQL> select * from v$logfile
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- ------------------------------- ------------------------
7 ONLINE /oradata/orcl/redo07a.log NO
7 ONLINE /oradata/orcl/redo07b.log NO
8 ONLINE /oradata/orcl/redo08a.log NO
8 ONLINE /oradata/orcl/redo08b.log NO
9 ONLINE /oradata/orcl/redo09a.log NO
9 ONLINE /oradata/orcl/redo09b.log NO
10 ONLINE /oradata/orcl/redo10a.log NO
10 ONLINE /oradata/orcl/redo10b.log NO
11 ONLINE /oradata/orcl/redo11a.log NO
11 ONLINE /oradata/orcl/redo11b.log NO
12 ONLINE /oradata/orcl/redo12a.log NO
12 ONLINE /oradata/orcl/redo12b.log NO
到此 RAC的新redolog 就增加完了
--------------------------------------分割线 --------------------------------------