转载

Oracle11g调整redo日志大小、组数和每组成员数

调整redo日志大小和组数


  1. [oracle@scdb1 ~]$ export ORACLE_SID=cams
  2. [oracle@scdb1 ~]$ sqlplus / as sysdba

  4. SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 13 12:35:42 2017

  6. Copyright (c) 1982, 2013, Oracle. All rights reserved.


  9. Connected to:
  10. Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

  12. SQL> alter database add logfile group 4 '/data/cams/redo04.log' size 1g;

  14. Database altered.

  16. SQL> alter database add logfile group 5'/data/cams/redo05.log' size 1g;

  18. Database altered.

  20. SQL> alter database add logfile group 6'/data/cams/redo06.log' size 1g;

  22. Database altered.

  24. SQL> set linesize 300;
  25. SQL> col member for a30;
  26. SQL> select * from v$logfile;

  28.     GROUP# STATUS TYPE MEMBER             IS_
  29. ---------- ------- ------- ------------------------------ ---
  30.      3     ONLINE /data/cams/redo03.log     NO
  31.      2     ONLINE /data/cams/redo02.log     NO
  32.      1     ONLINE /data/cams/redo01.log     NO
  33.      4     ONLINE /data/cams/redo04.log     NO
  34.      5     ONLINE /data/cams/redo05.log     NO
  35.      6     ONLINE /data/cams/redo06.log     NO

  37. 6 rows selected.
  38. SQL> select GROUP#,BYTES,MEMBERS,STATUS from v$log;

  40.     GROUP#    BYTES     MEMBERS STATUS
  41. ---------- ---------- ---------- ----------------
  42.      1 52428800     1 INACTIVE
  43.      2 52428800     1 INACTIVE
  44.      3 52428800     1 CURRENT
  45.      4 1073741824     1 UNUSED
  46.      5 1073741824     1 UNUSED
  47.      6 1073741824     1 UNUSED

  49. 6 rows selected.

  51. SQL> alter system switch logfile;

  53. System altered.

  55. SQL> select GROUP#,BYTES,MEMBERS,STATUS from v$log;

  57.     GROUP#    BYTES     MEMBERS STATUS
  58. ---------- ---------- ---------- ----------------
  59.      1 52428800     1 INACTIVE
  60.      2 52428800     1 INACTIVE
  61.      3 52428800     1 ACTIVE
  62.      4 1073741824     1 CURRENT
  63.      5 1073741824     1 UNUSED
  64.      6 1073741824     1 UNUSED

  66. 6 rows selected.

  68. SQL> alter system checkpoint;

  70. System altered.

  72. SQL> select GROUP#,BYTES,MEMBERS,STATUS from v$log;

  74.     GROUP#    BYTES     MEMBERS STATUS
  75. ---------- ---------- ---------- ----------------
  76.      1 52428800     1 INACTIVE
  77.      2 52428800     1 INACTIVE
  78.      3 52428800     1 INACTIVE
  79.      4 1073741824     1 CURRENT
  80.      5 1073741824     1 UNUSED
  81.      6 1073741824     1 UNUSED

  83. 6 rows selected.

  85. SQL> alter database drop logfile GROUP 1;

  87. Database altered.

  89. SQL> alter database drop logfile GROUP 2;

  91. Database altered.

  93. SQL> alter database drop logfile GROUP 3;

  95. Database altered.

  97. SQL> select GROUP#,BYTES,MEMBERS,STATUS from v$log;

  99.     GROUP#    BYTES     MEMBERS STATUS
  100. ---------- ---------- ---------- ----------------
  101.      4 1073741824     1 CURRENT
  102.      5 1073741824     1 UNUSED
  103.      6 1073741824     1 UNUSED

  105. SQL> !mv /data/cams/redo01.log /data/cams/redo01.log_bak20160811

  107. SQL> !mv /data/cams/redo02.log /data/cams/redo02.log_bak20160811

  109. SQL> !mv /data/cams/redo03.log /data/cams/redo03.log_bak20160811

  111. SQL> alter database add logfile GROUP 1 ('/data/cams/redo01.log') size 1g;

  113. Database altered.

  115. SQL> alter database add logfile GROUP 2 ('/data/cams/redo02.log') size 1g;

  117. Database altered.

  119. SQL> alter database add logfile GROUP 3 ('/data/cams/redo03.log') size 1g;

  121. Database altered.

  123. SQL> select GROUP#,BYTES,MEMBERS,STATUS from v$log;

  125.     GROUP#    BYTES     MEMBERS STATUS
  126. ---------- ---------- ---------- ----------------
  127.      1 1073741824     1 UNUSED
  128.      2 1073741824     1 UNUSED
  129.      3 1073741824     1 UNUSED
  130.      4 1073741824     1 CURRENT
  131.      5 1073741824     1 UNUSED
  132.      6 1073741824     1 UNUSED

  134. 6 rows selected.
  135. SQL> alter system switch logfile;

  137. System altered.

  139. SQL> alter system switch logfile;

  141. System altered.

  143. SQL> alter system switch logfile;

  145. System altered.

  147. SQL> alter system switch logfile;

  149. System altered.

  151. SQL> alter system switch logfile;

  153. System altered.

  155. SQL> select GROUP#,BYTES,MEMBERS,STATUS from v$log;

  157.     GROUP#    BYTES     MEMBERS STATUS
  158. ---------- ---------- ---------- ----------------
  159.      1 1073741824     1 ACTIVE
  160.      2 1073741824     1 ACTIVE
  161.      3 1073741824     1 ACTIVE
  162.      4 1073741824     1 ACTIVE
  163.      5 1073741824     1 ACTIVE
  164.      6 1073741824     1 CURRENT

  166. 6 rows selected.

  168. SQL> alter system switch logfile;

  170. System altered.

  172. SQL> select GROUP#,BYTES,MEMBERS,STATUS from v$log;

  174.     GROUP#    BYTES     MEMBERS STATUS
  175. ---------- ---------- ---------- ----------------
  176.      1 1073741824     1 INACTIVE
  177.      2 1073741824     1 INACTIVE
  178.      3 1073741824     1 INACTIVE
  179.      4 1073741824     1 CURRENT
  180.      5 1073741824     1 INACTIVE
  181.      6 1073741824     1 INACTIVE

  183. SQL> exit
  184. Disconnected from Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
完成调整redo日志大小、组数的操作，并将redo日志从默认的3组，每个日志大小为50M调整为6组，每个redo日志大小为1g。

增加redo日志组成员数


  1. SQL> col member for a30;
  2. SQL> /

  4.     GROUP# STATUS TYPE MEMBER             IS_
  5. ---------- ------- ------- ------------------------------ ---
  6.      1     ONLINE /data/cams/redo01.log     NO
  7.      2     ONLINE /data/cams/redo02.log     NO
  8.      3     ONLINE /data/cams/redo03.log     NO
  9.      4     ONLINE /data/cams/redo04.log     NO
  10.      5     ONLINE /data/cams/redo05.log     NO
  11.      6     ONLINE /data/cams/redo06.log     NO
  12. 修改存储权限
  13. chown -R oracle:oinstall /file
  14. chown -R oracle:oinstall /backup

  16. 添加组成员
  17. SQL> !mkdir -p /file/cams/redo
  18. SQL> !mkdir -p /backup/cams/redo
  19. SQL> alter database add logfile member '/file/cams/redo/redo01.log' to group 1;
  20. SQL> alter database add logfile member '/backup/cams/redo/redo01.log' to group 1;
  21. SQL> alter database add logfile member '/file/cams/redo/redo02.log' to group 2;
  22. SQL> alter database add logfile member '/backup/cams/redo/redo02.log' to group 2;
  23. SQL> alter database add logfile member '/file/cams/redo/redo03.log' to group 3;
  24. SQL> alter database add logfile member '/backup/cams/redo/redo03.log' to group 3;
  25. SQL> alter database add logfile member '/file/cams/redo/redo04.log' to group 4;
  26. SQL> alter database add logfile member '/backup/cams/redo/redo04.log' to group 4;
  27. SQL> alter database add logfile member '/file/cams/redo/redo05.log' to group 5;
  28. SQL> alter database add logfile member '/backup/cams/redo/redo05.log' to group 5;
  29. SQL> alter database add logfile member '/file/cams/redo/redo06.log' to group 6;
  30. SQL> alter database add logfile member '/backup/cams/redo/redo06.log' to group 6;

  32. SQL> select * from v$logfile;

  34.     GROUP# STATUS TYPE MEMBER             IS_
  35. ---------- ------- ------- ------------------------------ ---
  36.      1     ONLINE /data/cams/redo01.log     NO
  37.      2     ONLINE /data/cams/redo02.log     NO
  38.      3     ONLINE /data/cams/redo03.log     NO
  39.      4     ONLINE /data/cams/redo04.log     NO
  40.      5     ONLINE /data/cams/redo05.log     NO
  41.      6     ONLINE /data/cams/redo06.log     NO
  42.      1 INVALID ONLINE /file/cams/redo/redo01.log     NO
  43.      1 INVALID ONLINE /backup/cams/redo/redo01.log NO
  44.      2 INVALID ONLINE /file/cams/redo/redo02.log     NO
  45.      2 INVALID ONLINE /backup/cams/redo/redo02.log NO
  46.      3 INVALID ONLINE /file/cams/redo/redo03.log     NO

  48.     GROUP# STATUS TYPE MEMBER             IS_
  49. ---------- ------- ------- ------------------------------ ---
  50.      3 INVALID ONLINE /backup/cams/redo/redo03.log NO
  51.      4 INVALID ONLINE /file/cams/redo/redo04.log     NO
  52.      4 INVALID ONLINE /backup/cams/redo/redo04.log NO
  53.      5 INVALID ONLINE /file/cams/redo/redo05.log     NO
  54.      5 INVALID ONLINE /backup/cams/redo/redo05.log NO
  55.      6 INVALID ONLINE /file/cams/redo/redo06.log     NO
  56.      6 INVALID ONLINE /backup/cams/redo/redo06.log NO

  58. 18 rows selected.
  59. 刚加入到redolog group的日志文件在被使用之前也是INVALID状态
  60. SQL> alter system switch logfile;

  62. System altered.

  64. SQL> select * from v$logfile;

  66.     GROUP# STATUS TYPE MEMBER             IS_
  67. ---------- ------- ------- ------------------------------ ---
  68.      1     ONLINE /data/cams/redo01.log     NO
  69.      2     ONLINE /data/cams/redo02.log     NO
  70.      3     ONLINE /data/cams/redo03.log     NO
  71.      4     ONLINE /data/cams/redo04.log     NO
  72.      5     ONLINE /data/cams/redo05.log     NO
  73.      6     ONLINE /data/cams/redo06.log     NO
  74.      1 INVALID ONLINE /file/cams/redo/redo01.log     NO
  75.      1 INVALID ONLINE /backup/cams/redo/redo01.log NO
  76.      2 INVALID ONLINE /file/cams/redo/redo02.log     NO
  77.      2 INVALID ONLINE /backup/cams/redo/redo02.log NO
  78.      3     ONLINE /file/cams/redo/redo03.log     NO

  80.     GROUP# STATUS TYPE MEMBER             IS_
  81. ---------- ------- ------- ------------------------------ ---
  82.      3     ONLINE /backup/cams/redo/redo03.log NO
  83.      4 INVALID ONLINE /file/cams/redo/redo04.log     NO
  84.      4 INVALID ONLINE /backup/cams/redo/redo04.log NO
  85.      5 INVALID ONLINE /file/cams/redo/redo05.log     NO
  86.      5 INVALID ONLINE /backup/cams/redo/redo05.log NO
  87.      6 INVALID ONLINE /file/cams/redo/redo06.log     NO
  88.      6 INVALID ONLINE /backup/cams/redo/redo06.log NO

  90. 18 rows selected.
完成redo日志每组成员数调整，由默认的每组一个成员调整为每组3个成员
如果觉得每组成员太多，可能会影响效率，可以删除一组冗余，提高效率：

  1. SQL> alter database drop logfile member '/backup/cams/redo/redo01.log';
  2. SQL> alter database drop logfile member '/backup/cams/redo/redo02.log';
  3. SQL> alter database drop logfile member '/backup/cams/redo/redo03.log';
  4. SQL> alter database drop logfile member '/backup/cams/redo/redo04.log';
  5. SQL> alter database drop logfile member '/backup/cams/redo/redo05.log';
  6. SQL> alter database drop logfile member '/backup/cams/redo/redo06.log';
执行删除之后，调整为每组2个成员。

这里建议将redo日志的其他成员存放在不同的磁盘中，以避免磁盘故障造成数据损失。

正文到此结束
所属分类： 编程技术

热门推荐

相关文章

阿里云首购8折
说给你听

本文目录
随机标签
书籍教程
springboot-demo Java入门教程 bootstrap3 CSS Apache基础教程 php ionic 教程 Python mysql教程 eclipse Ubuntu VPS系统配置 AngularJS 教程 MongoDB教程 Struts2教程 Redis教程 Spring教程 Git教程 Jenkins进阶系列 openfire参考指南 Java设计模式 HBase教程 Maven教程 hibernate教程 Docker 教程 memcached教程 Quartz指南 Hive教程 Ant教程 ANTLR教程 SpringCloud java实例教程 springcloud-demo XStream教程 Hazelcast教程 Elastic-Job-Lite 深入浅出MyBatis SVN教程 ibaties教程 rabittmq教程 solr教程 Hadoop教程 WebService CXF学习 JPA教程 ActiveMQ中文指南 java-demo Java内存模型 dubbo教程 python3-demo Linux入门视频教程
近期评论
  1. 1 Spring Cloud Consul实现选举机制
  2. 2 Spring Boot集成ShedLock实现分布式定时任务
  3. 3 利用oss进行数据库和网站图片备份
  4. 4 Spring Cloud Stream实现数据流处理
  5. 5 Python3访问MySQL数据库快速入门Demo
  6. 6 Github开源项目作者可以免费申请 JetBrains 全家桶
  7. 7 Spring Cloud Vault快速入门Demo
  8. 8 Spring Cloud Gateway快速入门Demo
  9. 9 Spring Cloud Contract快速入门Demo
  10. 10 Spring Cloud Consul快速入门Demo
网站信息
  • 文章总数：82,714 篇
  • 文件总数：284,287 个
  • 标签总数：2,393 个
  • 分类总数：85 个
  • 留言数量：2,560 条
  • 在线人数：674 人
  • 运行天数：4,409天
  • 最后更新：2024年11月22日05点
Loading...

其他链接

关于本站

本站定位：个人技术类博客

本站作用：写博客、记日志、闲聊扯淡鼓捣技术。

问题交流

[HBLOG]公众号 HBLOG HBLOG