手机版
你好,游客 登录 注册
背景:
阅读新闻

Oracle 11g调整redo日志大小、组数和每组成员数

[日期:2017-07-29] 来源:Linux社区  作者:neverinit [字体: ]

调整redo日志大小和组数

  1. [Oracle@scdb1 ~]$ export ORACLE_SID=cams
  2. [oracle@scdb1 ~]$ sqlplus / as sysdba
  3. SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 13 12:35:42 2017
  4. Copyright (c) 1982, 2013, Oracle. All rights reserved.
  5. Connected to:
  6. Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
  7. SQL> alter database add logfile group 4 '/data/cams/redo04.log' size 1g;
  8. Database altered.
  9. SQL> alter database add logfile group 5'/data/cams/redo05.log' size 1g;
  10. Database altered.
  11. SQL> alter database add logfile group 6'/data/cams/redo06.log' size 1g;
  12. Database altered.
  13. SQL> set linesize 300;
  14. SQL> col member for a30;
  15. SQL> select * from v$logfile;
  16.     GROUP# STATUS TYPE MEMBER            IS_
  17. ---------- ------- ------- ------------------------------ ---
  18.     3    ONLINE /data/cams/redo03.log    NO
  19.     2    ONLINE /data/cams/redo02.log    NO
  20.     1    ONLINE /data/cams/redo01.log    NO
  21.     4    ONLINE /data/cams/redo04.log    NO
  22.     5    ONLINE /data/cams/redo05.log    NO
  23.     6    ONLINE /data/cams/redo06.log    NO
  24. 6 rows selected.
  25. SQL> select GROUP#,BYTES,MEMBERS,STATUS from v$log;
  26.     GROUP#    BYTES    MEMBERS STATUS
  27. ---------- ---------- ---------- ----------------
  28.     1 52428800    1 INACTIVE
  29.     2 52428800    1 INACTIVE
  30.     3 52428800    1 CURRENT
  31.     4 1073741824    1 UNUSED
  32.     5 1073741824    1 UNUSED
  33.     6 1073741824    1 UNUSED
  34. 6 rows selected.
  35. SQL> alter system switch logfile;
  36. System altered.
  37. SQL> select GROUP#,BYTES,MEMBERS,STATUS from v$log;
  38.     GROUP#    BYTES    MEMBERS STATUS
  39. ---------- ---------- ---------- ----------------
  40.     1 52428800    1 INACTIVE
  41.     2 52428800    1 INACTIVE
  42.     3 52428800    1 ACTIVE
  43.     4 1073741824    1 CURRENT
  44.     5 1073741824    1 UNUSED
  45.     6 1073741824    1 UNUSED
  46. 6 rows selected.
  47. SQL> alter system checkpoint;
  48. System altered.
  49. SQL> select GROUP#,BYTES,MEMBERS,STATUS from v$log;
  50.     GROUP#    BYTES    MEMBERS STATUS
  51. ---------- ---------- ---------- ----------------
  52.     1 52428800    1 INACTIVE
  53.     2 52428800    1 INACTIVE
  54.     3 52428800    1 INACTIVE
  55.     4 1073741824    1 CURRENT
  56.     5 1073741824    1 UNUSED
  57.     6 1073741824    1 UNUSED
  58. 6 rows selected.
  59. SQL> alter database drop logfile GROUP 1;
  60. Database altered.
  61. SQL> alter database drop logfile GROUP 2;
  62. Database altered.
  63. SQL> alter database drop logfile GROUP 3;
  64. Database altered.
  65. SQL> select GROUP#,BYTES,MEMBERS,STATUS from v$log;
  66.     GROUP#    BYTES    MEMBERS STATUS
  67. ---------- ---------- ---------- ----------------
  68.     4 1073741824    1 CURRENT
  69.     5 1073741824    1 UNUSED
  70.     6 1073741824    1 UNUSED
  71. SQL> !mv /data/cams/redo01.log /data/cams/redo01.log_bak20160811
  72. SQL> !mv /data/cams/redo02.log /data/cams/redo02.log_bak20160811
  73. SQL> !mv /data/cams/redo03.log /data/cams/redo03.log_bak20160811
  74. SQL> alter database add logfile GROUP 1 ('/data/cams/redo01.log') size 1g;
  75. Database altered.
  76. SQL> alter database add logfile GROUP 2 ('/data/cams/redo02.log') size 1g;
  77. Database altered.
  78. SQL> alter database add logfile GROUP 3 ('/data/cams/redo03.log') size 1g;
  79. Database altered.
  80. SQL> select GROUP#,BYTES,MEMBERS,STATUS from v$log;
  81.     GROUP#    BYTES    MEMBERS STATUS
  82. ---------- ---------- ---------- ----------------
  83.     1 1073741824    1 UNUSED
  84.     2 1073741824    1 UNUSED
  85.     3 1073741824    1 UNUSED
  86.     4 1073741824    1 CURRENT
  87.     5 1073741824    1 UNUSED
  88.     6 1073741824    1 UNUSED
  89. 6 rows selected.
  90. SQL> alter system switch logfile;
  91. System altered.
  92. SQL> alter system switch logfile;
  93. System altered.
  94. SQL> alter system switch logfile;
  95. System altered.
  96. SQL> alter system switch logfile;
  97. System altered.
  98. SQL> alter system switch logfile;
  99. System altered.
  100. SQL> select GROUP#,BYTES,MEMBERS,STATUS from v$log;
  101.     GROUP#    BYTES    MEMBERS STATUS
  102. ---------- ---------- ---------- ----------------
  103.     1 1073741824    1 ACTIVE
  104.     2 1073741824    1 ACTIVE
  105.     3 1073741824    1 ACTIVE
  106.     4 1073741824    1 ACTIVE
  107.     5 1073741824    1 ACTIVE
  108.     6 1073741824    1 CURRENT
  109. 6 rows selected.
  110. SQL> alter system switch logfile;
  111. System altered.
  112. SQL> select GROUP#,BYTES,MEMBERS,STATUS from v$log;
  113.     GROUP#    BYTES    MEMBERS STATUS
  114. ---------- ---------- ---------- ----------------
  115.     1 1073741824    1 INACTIVE
  116.     2 1073741824    1 INACTIVE
  117.     3 1073741824    1 INACTIVE
  118.     4 1073741824    1 CURRENT
  119.     5 1073741824    1 INACTIVE
  120.     6 1073741824    1 INACTIVE
  121. SQL> exit
  122. 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> /
  3.     GROUP# STATUS TYPE MEMBER            IS_
  4. ---------- ------- ------- ------------------------------ ---
  5.     1    ONLINE /data/cams/redo01.log    NO
  6.     2    ONLINE /data/cams/redo02.log    NO
  7.     3    ONLINE /data/cams/redo03.log    NO
  8.     4    ONLINE /data/cams/redo04.log    NO
  9.     5    ONLINE /data/cams/redo05.log    NO
  10.     6    ONLINE /data/cams/redo06.log    NO
  11. 修改存储权限
  12. chown -R oracle:oinstall /file
  13. chown -R oracle:oinstall /backup
  14. 添加组成员
  15. SQL> !mkdir -p /file/cams/redo
  16. SQL> !mkdir -p /backup/cams/redo
  17. SQL> alter database add logfile member '/file/cams/redo/redo01.log' to group 1;
  18. SQL> alter database add logfile member '/backup/cams/redo/redo01.log' to group 1;
  19. SQL> alter database add logfile member '/file/cams/redo/redo02.log' to group 2;
  20. SQL> alter database add logfile member '/backup/cams/redo/redo02.log' to group 2;
  21. SQL> alter database add logfile member '/file/cams/redo/redo03.log' to group 3;
  22. SQL> alter database add logfile member '/backup/cams/redo/redo03.log' to group 3;
  23. SQL> alter database add logfile member '/file/cams/redo/redo04.log' to group 4;
  24. SQL> alter database add logfile member '/backup/cams/redo/redo04.log' to group 4;
  25. SQL> alter database add logfile member '/file/cams/redo/redo05.log' to group 5;
  26. SQL> alter database add logfile member '/backup/cams/redo/redo05.log' to group 5;
  27. SQL> alter database add logfile member '/file/cams/redo/redo06.log' to group 6;
  28. SQL> alter database add logfile member '/backup/cams/redo/redo06.log' to group 6;
  29. SQL> select * from v$logfile;
  30.     GROUP# STATUS TYPE MEMBER            IS_
  31. ---------- ------- ------- ------------------------------ ---
  32.     1    ONLINE /data/cams/redo01.log    NO
  33.     2    ONLINE /data/cams/redo02.log    NO
  34.     3    ONLINE /data/cams/redo03.log    NO
  35.     4    ONLINE /data/cams/redo04.log    NO
  36.     5    ONLINE /data/cams/redo05.log    NO
  37.     6    ONLINE /data/cams/redo06.log    NO
  38.     1 INVALID ONLINE /file/cams/redo/redo01.log    NO
  39.     1 INVALID ONLINE /backup/cams/redo/redo01.log NO
  40.     2 INVALID ONLINE /file/cams/redo/redo02.log    NO
  41.     2 INVALID ONLINE /backup/cams/redo/redo02.log NO
  42.     3 INVALID ONLINE /file/cams/redo/redo03.log    NO
  43.     GROUP# STATUS TYPE MEMBER            IS_
  44. ---------- ------- ------- ------------------------------ ---
  45.     3 INVALID ONLINE /backup/cams/redo/redo03.log NO
  46.     4 INVALID ONLINE /file/cams/redo/redo04.log    NO
  47.     4 INVALID ONLINE /backup/cams/redo/redo04.log NO
  48.     5 INVALID ONLINE /file/cams/redo/redo05.log    NO
  49.     5 INVALID ONLINE /backup/cams/redo/redo05.log NO
  50.     6 INVALID ONLINE /file/cams/redo/redo06.log    NO
  51.     6 INVALID ONLINE /backup/cams/redo/redo06.log NO
  52. 18 rows selected.
  53. 刚加入到redolog group的日志文件在被使用之前也是INVALID状态
  54. SQL> alter system switch logfile;
  55. System altered.
  56. SQL> select * from v$logfile;
  57.     GROUP# STATUS TYPE MEMBER            IS_
  58. ---------- ------- ------- ------------------------------ ---
  59.     1    ONLINE /data/cams/redo01.log    NO
  60.     2    ONLINE /data/cams/redo02.log    NO
  61.     3    ONLINE /data/cams/redo03.log    NO
  62.     4    ONLINE /data/cams/redo04.log    NO
  63.     5    ONLINE /data/cams/redo05.log    NO
  64.     6    ONLINE /data/cams/redo06.log    NO
  65.     1 INVALID ONLINE /file/cams/redo/redo01.log    NO
  66.     1 INVALID ONLINE /backup/cams/redo/redo01.log NO
  67.     2 INVALID ONLINE /file/cams/redo/redo02.log    NO
  68.     2 INVALID ONLINE /backup/cams/redo/redo02.log NO
  69.     3    ONLINE /file/cams/redo/redo03.log    NO
  70.     GROUP# STATUS TYPE MEMBER            IS_
  71. ---------- ------- ------- ------------------------------ ---
  72.     3    ONLINE /backup/cams/redo/redo03.log NO
  73.     4 INVALID ONLINE /file/cams/redo/redo04.log    NO
  74.     4 INVALID ONLINE /backup/cams/redo/redo04.log NO
  75.     5 INVALID ONLINE /file/cams/redo/redo05.log    NO
  76.     5 INVALID ONLINE /backup/cams/redo/redo05.log NO
  77.     6 INVALID ONLINE /file/cams/redo/redo06.log    NO
  78.     6 INVALID ONLINE /backup/cams/redo/redo06.log NO
  79. 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日志的其他成员存放在不同的磁盘中,以避免磁盘故障造成数据损失。

更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址http://www.linuxidc.com/Linux/2017-07/145981.htm

linux
相关资讯       redo  redo日志  调整redo日志大小 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款