调整redo日志大小和组数
[Oracle@scdb1 ~]$ export ORACLE_SID=cams
[oracle@scdb1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 13 12:35:42 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
SQL> alter database add logfile group 4 '/data/cams/redo04.log' size 1g;
Database altered.
SQL> alter database add logfile group 5'/data/cams/redo05.log' size 1g;
Database altered.
SQL> alter database add logfile group 6'/data/cams/redo06.log' size 1g;
Database altered.
SQL> set linesize 300;
SQL> col member for a30;
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------ ---
3 ONLINE /data/cams/redo03.log NO
2 ONLINE /data/cams/redo02.log NO
1 ONLINE /data/cams/redo01.log NO
4 ONLINE /data/cams/redo04.log NO
5 ONLINE /data/cams/redo05.log NO
6 ONLINE /data/cams/redo06.log NO
6 rows selected.
SQL> select GROUP#,BYTES,MEMBERS,STATUS from v$log;
GROUP# BYTES MEMBERS STATUS
---------- ---------- ---------- ----------------
1 52428800 1 INACTIVE
2 52428800 1 INACTIVE
3 52428800 1 CURRENT
4 1073741824 1 UNUSED
5 1073741824 1 UNUSED
6 1073741824 1 UNUSED
6 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> select GROUP#,BYTES,MEMBERS,STATUS from v$log;
GROUP# BYTES MEMBERS STATUS
---------- ---------- ---------- ----------------
1 52428800 1 INACTIVE
2 52428800 1 INACTIVE
3 52428800 1 ACTIVE
4 1073741824 1 CURRENT
5 1073741824 1 UNUSED
6 1073741824 1 UNUSED
6 rows selected.
SQL> alter system checkpoint;
System altered.
SQL> select GROUP#,BYTES,MEMBERS,STATUS from v$log;
GROUP# BYTES MEMBERS STATUS
---------- ---------- ---------- ----------------
1 52428800 1 INACTIVE
2 52428800 1 INACTIVE
3 52428800 1 INACTIVE
4 1073741824 1 CURRENT
5 1073741824 1 UNUSED
6 1073741824 1 UNUSED
6 rows selected.
SQL> alter database drop logfile GROUP 1;
Database altered.
SQL> alter database drop logfile GROUP 2;
Database altered.
SQL> alter database drop logfile GROUP 3;
Database altered.
SQL> select GROUP#,BYTES,MEMBERS,STATUS from v$log;
GROUP# BYTES MEMBERS STATUS
---------- ---------- ---------- ----------------
4 1073741824 1 CURRENT
5 1073741824 1 UNUSED
6 1073741824 1 UNUSED
SQL> !mv /data/cams/redo01.log /data/cams/redo01.log_bak20160811
SQL> !mv /data/cams/redo02.log /data/cams/redo02.log_bak20160811
SQL> !mv /data/cams/redo03.log /data/cams/redo03.log_bak20160811
SQL> alter database add logfile GROUP 1 ('/data/cams/redo01.log') size 1g;
Database altered.
SQL> alter database add logfile GROUP 2 ('/data/cams/redo02.log') size 1g;
Database altered.
SQL> alter database add logfile GROUP 3 ('/data/cams/redo03.log') size 1g;
Database altered.
SQL> select GROUP#,BYTES,MEMBERS,STATUS from v$log;
GROUP# BYTES MEMBERS STATUS
---------- ---------- ---------- ----------------
1 1073741824 1 UNUSED
2 1073741824 1 UNUSED
3 1073741824 1 UNUSED
4 1073741824 1 CURRENT
5 1073741824 1 UNUSED
6 1073741824 1 UNUSED
6 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select GROUP#,BYTES,MEMBERS,STATUS from v$log;
GROUP# BYTES MEMBERS STATUS
---------- ---------- ---------- ----------------
1 1073741824 1 ACTIVE
2 1073741824 1 ACTIVE
3 1073741824 1 ACTIVE
4 1073741824 1 ACTIVE
5 1073741824 1 ACTIVE
6 1073741824 1 CURRENT
6 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> select GROUP#,BYTES,MEMBERS,STATUS from v$log;
GROUP# BYTES MEMBERS STATUS
---------- ---------- ---------- ----------------
1 1073741824 1 INACTIVE
2 1073741824 1 INACTIVE
3 1073741824 1 INACTIVE
4 1073741824 1 CURRENT
5 1073741824 1 INACTIVE
6 1073741824 1 INACTIVE
SQL> exit
Disconnected from Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
完成调整redo日志大小、组数的操作,并将redo日志从默认的3组,每个日志大小为50M调整为6组,每个redo日志大小为1g。
增加redo日志组成员数
SQL> col member for a30;
SQL> /
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------ ---
1 ONLINE /data/cams/redo01.log NO
2 ONLINE /data/cams/redo02.log NO
3 ONLINE /data/cams/redo03.log NO
4 ONLINE /data/cams/redo04.log NO
5 ONLINE /data/cams/redo05.log NO
6 ONLINE /data/cams/redo06.log NO
修改存储权限
chown -R oracle:oinstall /file
chown -R oracle:oinstall /backup
添加组成员
SQL> !mkdir -p /file/cams/redo
SQL> !mkdir -p /backup/cams/redo
SQL> alter database add logfile member '/file/cams/redo/redo01.log' to group 1;
SQL> alter database add logfile member '/backup/cams/redo/redo01.log' to group 1;
SQL> alter database add logfile member '/file/cams/redo/redo02.log' to group 2;
SQL> alter database add logfile member '/backup/cams/redo/redo02.log' to group 2;
SQL> alter database add logfile member '/file/cams/redo/redo03.log' to group 3;
SQL> alter database add logfile member '/backup/cams/redo/redo03.log' to group 3;
SQL> alter database add logfile member '/file/cams/redo/redo04.log' to group 4;
SQL> alter database add logfile member '/backup/cams/redo/redo04.log' to group 4;
SQL> alter database add logfile member '/file/cams/redo/redo05.log' to group 5;
SQL> alter database add logfile member '/backup/cams/redo/redo05.log' to group 5;
SQL> alter database add logfile member '/file/cams/redo/redo06.log' to group 6;
SQL> alter database add logfile member '/backup/cams/redo/redo06.log' to group 6;
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------ ---
1 ONLINE /data/cams/redo01.log NO
2 ONLINE /data/cams/redo02.log NO
3 ONLINE /data/cams/redo03.log NO
4 ONLINE /data/cams/redo04.log NO
5 ONLINE /data/cams/redo05.log NO
6 ONLINE /data/cams/redo06.log NO
1 INVALID ONLINE /file/cams/redo/redo01.log NO
1 INVALID ONLINE /backup/cams/redo/redo01.log NO
2 INVALID ONLINE /file/cams/redo/redo02.log NO
2 INVALID ONLINE /backup/cams/redo/redo02.log NO
3 INVALID ONLINE /file/cams/redo/redo03.log NO
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------ ---
3 INVALID ONLINE /backup/cams/redo/redo03.log NO
4 INVALID ONLINE /file/cams/redo/redo04.log NO
4 INVALID ONLINE /backup/cams/redo/redo04.log NO
5 INVALID ONLINE /file/cams/redo/redo05.log NO
5 INVALID ONLINE /backup/cams/redo/redo05.log NO
6 INVALID ONLINE /file/cams/redo/redo06.log NO
6 INVALID ONLINE /backup/cams/redo/redo06.log NO
18 rows selected.
刚加入到redolog group的日志文件在被使用之前也是INVALID状态
SQL> alter system switch logfile;
System altered.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------ ---
1 ONLINE /data/cams/redo01.log NO
2 ONLINE /data/cams/redo02.log NO
3 ONLINE /data/cams/redo03.log NO
4 ONLINE /data/cams/redo04.log NO
5 ONLINE /data/cams/redo05.log NO
6 ONLINE /data/cams/redo06.log NO
1 INVALID ONLINE /file/cams/redo/redo01.log NO
1 INVALID ONLINE /backup/cams/redo/redo01.log NO
2 INVALID ONLINE /file/cams/redo/redo02.log NO
2 INVALID ONLINE /backup/cams/redo/redo02.log NO
3 ONLINE /file/cams/redo/redo03.log NO
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------ ---
3 ONLINE /backup/cams/redo/redo03.log NO
4 INVALID ONLINE /file/cams/redo/redo04.log NO
4 INVALID ONLINE /backup/cams/redo/redo04.log NO
5 INVALID ONLINE /file/cams/redo/redo05.log NO
5 INVALID ONLINE /backup/cams/redo/redo05.log NO
6 INVALID ONLINE /file/cams/redo/redo06.log NO
6 INVALID ONLINE /backup/cams/redo/redo06.log NO
18 rows selected.
完成redo日志每组成员数调整,由默认的每组一个成员调整为每组3个成员
如果觉得每组成员太多,可能会影响效率,可以删除一组冗余,提高效率:
SQL> alter database drop logfile member '/backup/cams/redo/redo01.log';
SQL> alter database drop logfile member '/backup/cams/redo/redo02.log';
SQL> alter database drop logfile member '/backup/cams/redo/redo03.log';
SQL> alter database drop logfile member '/backup/cams/redo/redo04.log';
SQL> alter database drop logfile member '/backup/cams/redo/redo05.log';
SQL> alter database drop logfile member '/backup/cams/redo/redo06.log';
执行删除之后,调整为每组2个成员。
这里建议将redo日志的其他成员存放在不同的磁盘中,以避免磁盘故障造成数据损失。