Oracle 10g OCFS2系统RAC节点更改redolog

某电子政务系统,10g的OCFS2架构的RAC系统 redolog 设置太

某电子政务系统,,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 就增加完了

--------------------------------------分割线 --------------------------------------

在CentOS 6.4下安装Oracle 11gR2(x64)

Oracle 11gR2 在VMWare虚拟机中安装步骤

Debian 下 安装 Oracle 11g XE R2

--------------------------------------分割线 --------------------------------------

本文永久更新链接地址:

linux