Oracle redo log 大小及组数的设置（在线修改日志组数目及组成员个数）

Oracle redo log 大小及组数的设置（在线修改日志组数目及组成员个数）1.查看当前系统现有的redo log状况（组数/大小/名称/状态）
SYS@ORCL>desc v$log
Name Null？ Type
----------------------------------------- -------- ----------------------------
GROUP# NUMBER
THREAD# NUMBER
SEQUENCE# NUMBER
BYTES NUMBER
BLOCKSIZE NUMBER
MEMBERS NUMBER
ARCHIVED VARCHAR2（3）
STATUS VARCHAR2（16）
FIRST_CHANGE# NUMBER
FIRST_TIME DATE
NEXT_CHANGE# NUMBER
NEXT_TIME DATESYS@ORCL>select GROUP# ,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log; GROUP# SIZE_M STATUS ARC
---------- ---------- ---------------- ---
1 50 INACTIVE YES
2 50 INACTIVE YES
3 50 CURRENT NO SYS@ORCL>desc v$logfile;
Name Null？ Type
----------------------------------------- -------- ----------------------------
GROUP# NUMBER
STATUS VARCHAR2（7）
TYPE VARCHAR2（7）
MEMBER VARCHAR2（513）
IS_RECOVERY_DEST_FILE VARCHAR2（3）SYS@ORCL>select MEMBER from v$logfile;MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_b0fh9w87_.log
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_3_b0fh9wqw_.log
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_b0fh9nk4_.log
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_2_b0fh9o8x_.log
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_b0fh9drr_.log
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_1_b0fh9dxx_.log 6 rows selected. --查看日志文件的路径2.添加3组大小为100M的日志组；
SYS@ORCL>alter database add logfile group 4 "/u01/app/oracle/oradata/ORCL/onlinelog/redo04.log"size 100m;Database altered.SYS@ORCL>alter database add logfile group 5"/u01/app/oracle/oradata/ORCL/onlinelog/redo05.log"size 100m;Database altered.SYS@ORCL>alter database add logfile group 6"/u01/app/oracle/oradata/ORCL/onlinelog/redo06.log"size 100m;Database altered.SYS@ORCL>select GROUP# ,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log; GROUP# SIZE_M STATUS ARC
---------- ---------- ---------------- ---
1 50 INACTIVE YES
2 50 INACTIVE YES
3 50 CURRENT NO
4 100 UNUSED YES
5 100 UNUSED YES
6 100 UNUSED YES 6 rows selected.
3.删除原有的日志组；（只操作状态为inactive的日志组）
若要操作日志组为current时，需先进行日志切换：alter system switch logfile;
若为active时，则可强制进行检查点：alter system checkpoint;SYS@ORCL>alter system switch logfile;System altered.SYS@ORCL>select GROUP# ,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log; GROUP# SIZE_M STATUS ARC
---------- ---------- ---------------- ---
1 50 INACTIVE YES
2 50 INACTIVE YES
3 50 ACTIVE YES
4 100 CURRENT NO
5 100 UNUSED YES
6 100 UNUSED YES 6 rows selected.SYS@ORCL>alter database drop logfile group 1;Database altered.SYS@ORCL>alter database drop logfile group 2;Database altered.SYS@ORCL>select GROUP# ,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log; GROUP# SIZE_M STATUS ARC
---------- ---------- ---------------- ---
3 50 ACTIVE YES
4 100 CURRENT NO
5 100 UNUSED YES
6 100 UNUSED YES
SYS@ORCL>alter system switch logfile;System altered.SYS@ORCL>select GROUP# ,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log; GROUP# SIZE_M STATUS ARC
---------- ---------- ---------------- ---
3 50 ACTIVE YES
4 100 ACTIVE YES
5 100 CURRENT NO
6 100 UNUSED YES
SYS@ORCL>select GROUP# ,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log; GROUP# SIZE_M STATUS ARC
---------- ---------- ---------------- ---
3 50 ACTIVE YES
4 100 ACTIVE YES
5 100 CURRENT NO
6 100 UNUSED YES SYS@ORCL>alter system checkpoint; --强制执行检查点System altered.SYS@ORCL>select GROUP# ,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log; GROUP# SIZE_M STATUS ARC
---------- ---------- ---------------- ---
3 50 INACTIVE YES
4 100 ACTIVE YES
5 100 CURRENT NO
6 100 UNUSED YES SYS@ORCL>alter database drop logfile group 3;Database altered.SYS@ORCL>select GROUP# ,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log; GROUP# SIZE_M STATUS ARC
---------- ---------- ---------------- ---
4 100 ACTIVE YES
5 100 CURRENT NO
6 100 UNUSED YES 4.向新创建的日志组添加成员
注意：若在生产库中，则将新创建的成员放到不同的磁盘上
SYS@ORCL>alter system switch logfile;System altered.SYS@ORCL>select GROUP# ,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log; GROUP# SIZE_M STATUS ARC
---------- ---------- ---------------- ---
4 100 ACTIVE YES
5 100 ACTIVE YES
6 100 CURRENT NO
SYS@ORCL>alter system switch logfile;System altered.SYS@ORCL>select GROUP# ,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log; GROUP# SIZE_M STATUS ARC
---------- ---------- ---------------- ---
4 100 CURRENT NO
5 100 ACTIVE YES
6 100 ACTIVE YES
SYS@ORCL>alter database add logfile member"/u01/app/oracle/oradata/ORCL/onlinelog/redo06_b.log" to group 6;Database altered.SYS@ORCL>select GROUP#,members,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log; GROUP# MEMBERS SIZE_M STATUS ARC
---------- ---------- ---------- ---------------- ---
4 1 100 CURRENT NO
5 1 100 INACTIVE YES
6 2 100 ACTIVE YES SYS@ORCL>alter database add logfile member "/u01/app/oracle/oradata/ORCL/onlinelog/redo05_b.log" to group 5;Database altered.SYS@ORCL>select GROUP#,members,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log; GROUP# MEMBERS SIZE_M STATUS ARC
---------- ---------- ---------- ---------------- ---
4 1 100 CURRENT NO
5 2 100 INACTIVE YES
6 2 100 ACTIVE YES SYS@ORCL>alter system switch logfile;System altered.SYS@ORCL>select GROUP#,members,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log; GROUP# MEMBERS SIZE_M STATUS ARC
---------- ---------- ---------- ---------------- ---
4 1 100 ACTIVE YES
5 2 100 CURRENT NO
6 2 100 INACTIVE YES SYS@ORCL>alter database add logfile member "/u01/app/oracle/oradata/ORCL/onlinelog/redo04_b.log" to group 4;Database altered.SYS@ORCL>select GROUP#,members,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log; GROUP# MEMBERS SIZE_M STATUS ARC
---------- ---------- ---------- ---------------- ---
4 2 100 ACTIVE YES
5 2 100 CURRENT NO
6 2 100 INACTIVE YES SYS@ORCL>alter system checkpoint;System altered.SYS@ORCL>select GROUP#,members,BYTES/1024/1024 size_M,STATUS,ARCHIVED from v$log; GROUP# MEMBERS SIZE_M STATUS ARC
---------- ---------- ---------- ---------------- ---
4 2 100 INACTIVE YES
5 2 100 CURRENT NO
6 2 100 INACTIVE YES
SYS@ORCL>select MEMBER from v$logfile;MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/onlinelog/redo06_b.log
/u01/app/oracle/oradata/ORCL/onlinelog/redo05_b.log
/u01/app/oracle/oradata/ORCL/onlinelog/redo04_b.log
/u01/app/oracle/oradata/ORCL/onlinelog/redo04.log
/u01/app/oracle/oradata/ORCL/onlinelog/redo05.log
/u01/app/oracle/oradata/ORCL/onlinelog/redo06.log 6 rows selected.
SYS@ORCL>spool offOracle 修改日志大小及增加日志成员 http://www.linuxidc.com/Linux/2011-06/37850.htm更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址