转载

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教程 java-demo Maven教程 springcloud-demo hibernate教程 Docker 教程 memcached教程 Quartz指南 Hive教程 ANTLR教程 SpringCloud Ant教程 java实例教程 Hazelcast教程 XStream教程 Elastic-Job-Lite 深入浅出MyBatis ibaties教程 SVN教程 rabittmq教程 Hadoop教程 solr教程 WebService CXF学习 JPA教程 ActiveMQ中文指南 Java内存模型 dubbo教程 python3-demo Linux入门视频教程
近期评论
  1. 1 使用 Docker 打包和运行 Vue 应用
  2. 2 Dependency Check命令行方式扫描jar包的安全漏洞
  3. 3 maven使用Dependency-Check来扫描安全漏洞
  4. 4 使用sonarqube分析代码质量
  5. 5 使用screw来对比数据库表和字段差异
  6. 6 Spring Boot 实现防盗链功能
  7. 7 在Spring Boot中的实现国际化（i18n）
  8. 8 一个用于 SMB/CIFS 网络的 Java 库-JCIFS
  9. 9 Spring Boot 3.x嵌入MongoDB 进行测试
  10. 10 java如何解析和生成yaml文件？
网站信息
  • 文章总数：82,737 篇
  • 文件总数：284,312 个
  • 标签总数：2,416 个
  • 分类总数：85 个
  • 留言数量：2,559 条
  • 在线人数：662 人
  • 运行天数：4,439天
  • 最后更新：2024年12月22日15点
Loading...

其他链接

关于本站

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

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

问题交流

[HBLOG]公众号 HBLOG HBLOG