共计 7740 个字符,预计需要花费 20 分钟才能阅读完成。
调整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 日志的其他成员存放在不同的磁盘中,以避免磁盘故障造成数据损失。
更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12
本文永久更新链接地址:http://www.linuxidc.com/Linux/2017-07/145981.htm
正文完
星哥玩云-微信公众号