阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

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

187次阅读
没有评论

共计 7740 个字符,预计需要花费 20 分钟才能阅读完成。

调整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.     3    ONLINE /data/cams/redo03.log    NO
  18.     2    ONLINE /data/cams/redo02.log    NO
  19.     1    ONLINE /data/cams/redo01.log    NO
  20.     4    ONLINE /data/cams/redo04.log    NO
  21.     5    ONLINE /data/cams/redo05.log    NO
  22.     6    ONLINE /data/cams/redo06.log    NO
  23. 6 rows selected.
  24. SQL> select GROUP#,BYTES,MEMBERS,STATUS from v$log;
  25.     GROUP#    BYTES    MEMBERS STATUS
  26.     1 52428800    1 INACTIVE
  27.     2 52428800    1 INACTIVE
  28.     3 52428800    1 CURRENT
  29.     4 1073741824    1 UNUSED
  30.     5 1073741824    1 UNUSED
  31.     6 1073741824    1 UNUSED
  32. 6 rows selected.
  33. SQL> alter system switch logfile;
  34. System altered.
  35. SQL> select GROUP#,BYTES,MEMBERS,STATUS from v$log;
  36.     GROUP#    BYTES    MEMBERS STATUS
  37.     1 52428800    1 INACTIVE
  38.     2 52428800    1 INACTIVE
  39.     3 52428800    1 ACTIVE
  40.     4 1073741824    1 CURRENT
  41.     5 1073741824    1 UNUSED
  42.     6 1073741824    1 UNUSED
  43. 6 rows selected.
  44. SQL> alter system checkpoint;
  45. System altered.
  46. SQL> select GROUP#,BYTES,MEMBERS,STATUS from v$log;
  47.     GROUP#    BYTES    MEMBERS STATUS
  48.     1 52428800    1 INACTIVE
  49.     2 52428800    1 INACTIVE
  50.     3 52428800    1 INACTIVE
  51.     4 1073741824    1 CURRENT
  52.     5 1073741824    1 UNUSED
  53.     6 1073741824    1 UNUSED
  54. 6 rows selected.
  55. SQL> alter database drop logfile GROUP 1;
  56. Database altered.
  57. SQL> alter database drop logfile GROUP 2;
  58. Database altered.
  59. SQL> alter database drop logfile GROUP 3;
  60. Database altered.
  61. SQL> select GROUP#,BYTES,MEMBERS,STATUS from v$log;
  62.     GROUP#    BYTES    MEMBERS STATUS
  63.     4 1073741824    1 CURRENT
  64.     5 1073741824    1 UNUSED
  65.     6 1073741824    1 UNUSED
  66. SQL> !mv /data/cams/redo01.log /data/cams/redo01.log_bak20160811
  67. SQL> !mv /data/cams/redo02.log /data/cams/redo02.log_bak20160811
  68. SQL> !mv /data/cams/redo03.log /data/cams/redo03.log_bak20160811
  69. SQL> alter database add logfile GROUP 1 (‘/data/cams/redo01.log’) size 1g;
  70. Database altered.
  71. SQL> alter database add logfile GROUP 2 (‘/data/cams/redo02.log’) size 1g;
  72. Database altered.
  73. SQL> alter database add logfile GROUP 3 (‘/data/cams/redo03.log’) size 1g;
  74. Database altered.
  75. SQL> select GROUP#,BYTES,MEMBERS,STATUS from v$log;
  76.     GROUP#    BYTES    MEMBERS STATUS
  77.     1 1073741824    1 UNUSED
  78.     2 1073741824    1 UNUSED
  79.     3 1073741824    1 UNUSED
  80.     4 1073741824    1 CURRENT
  81.     5 1073741824    1 UNUSED
  82.     6 1073741824    1 UNUSED
  83. 6 rows selected.
  84. SQL> alter system switch logfile;
  85. System altered.
  86. SQL> alter system switch logfile;
  87. System altered.
  88. SQL> alter system switch logfile;
  89. System altered.
  90. SQL> alter system switch logfile;
  91. System altered.
  92. SQL> alter system switch logfile;
  93. System altered.
  94. SQL> select GROUP#,BYTES,MEMBERS,STATUS from v$log;
  95.     GROUP#    BYTES    MEMBERS STATUS
  96.     1 1073741824    1 ACTIVE
  97.     2 1073741824    1 ACTIVE
  98.     3 1073741824    1 ACTIVE
  99.     4 1073741824    1 ACTIVE
  100.     5 1073741824    1 ACTIVE
  101.     6 1073741824    1 CURRENT
  102. 6 rows selected.
  103. SQL> alter system switch logfile;
  104. System altered.
  105. SQL> select GROUP#,BYTES,MEMBERS,STATUS from v$log;
  106.     GROUP#    BYTES    MEMBERS STATUS
  107.     1 1073741824    1 INACTIVE
  108.     2 1073741824    1 INACTIVE
  109.     3 1073741824    1 INACTIVE
  110.     4 1073741824    1 CURRENT
  111.     5 1073741824    1 INACTIVE
  112.     6 1073741824    1 INACTIVE
  113. SQL> exit
  114. 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.     1    ONLINE /data/cams/redo01.log    NO
  5.     2    ONLINE /data/cams/redo02.log    NO
  6.     3    ONLINE /data/cams/redo03.log    NO
  7.     4    ONLINE /data/cams/redo04.log    NO
  8.     5    ONLINE /data/cams/redo05.log    NO
  9.     6    ONLINE /data/cams/redo06.log    NO
  10. 修改存储权限
  11. chown R oracle:oinstall /file
  12. chown R oracle:oinstall /backup
  13. 添加组成员
  14. SQL> !mkdir p /file/cams/redo
  15. SQL> !mkdir p /backup/cams/redo
  16. SQL> alter database add logfile member ‘/file/cams/redo/redo01.log’ to group 1;
  17. SQL> alter database add logfile member ‘/backup/cams/redo/redo01.log’ to group 1;
  18. SQL> alter database add logfile member ‘/file/cams/redo/redo02.log’ to group 2;
  19. SQL> alter database add logfile member ‘/backup/cams/redo/redo02.log’ to group 2;
  20. SQL> alter database add logfile member ‘/file/cams/redo/redo03.log’ to group 3;
  21. SQL> alter database add logfile member ‘/backup/cams/redo/redo03.log’ to group 3;
  22. SQL> alter database add logfile member ‘/file/cams/redo/redo04.log’ to group 4;
  23. SQL> alter database add logfile member ‘/backup/cams/redo/redo04.log’ to group 4;
  24. SQL> alter database add logfile member ‘/file/cams/redo/redo05.log’ to group 5;
  25. SQL> alter database add logfile member ‘/backup/cams/redo/redo05.log’ to group 5;
  26. SQL> alter database add logfile member ‘/file/cams/redo/redo06.log’ to group 6;
  27. SQL> alter database add logfile member ‘/backup/cams/redo/redo06.log’ to group 6;
  28. SQL> select * from v$logfile;
  29.     GROUP# STATUS TYPE MEMBER            IS_
  30.     1    ONLINE /data/cams/redo01.log    NO
  31.     2    ONLINE /data/cams/redo02.log    NO
  32.     3    ONLINE /data/cams/redo03.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
  36.     1 INVALID ONLINE /file/cams/redo/redo01.log    NO
  37.     1 INVALID ONLINE /backup/cams/redo/redo01.log NO
  38.     2 INVALID ONLINE /file/cams/redo/redo02.log    NO
  39.     2 INVALID ONLINE /backup/cams/redo/redo02.log NO
  40.     3 INVALID ONLINE /file/cams/redo/redo03.log    NO
  41.     GROUP# STATUS TYPE MEMBER            IS_
  42.     3 INVALID ONLINE /backup/cams/redo/redo03.log NO
  43.     4 INVALID ONLINE /file/cams/redo/redo04.log    NO
  44.     4 INVALID ONLINE /backup/cams/redo/redo04.log NO
  45.     5 INVALID ONLINE /file/cams/redo/redo05.log    NO
  46.     5 INVALID ONLINE /backup/cams/redo/redo05.log NO
  47.     6 INVALID ONLINE /file/cams/redo/redo06.log    NO
  48.     6 INVALID ONLINE /backup/cams/redo/redo06.log NO
  49. 18 rows selected.
  50. 刚加入到 redolog group 的日志文件在被使用之前也是 INVALID 状态
  51. SQL> alter system switch logfile;
  52. System altered.
  53. SQL> select * from v$logfile;
  54.     GROUP# STATUS TYPE MEMBER            IS_
  55.     1    ONLINE /data/cams/redo01.log    NO
  56.     2    ONLINE /data/cams/redo02.log    NO
  57.     3    ONLINE /data/cams/redo03.log    NO
  58.     4    ONLINE /data/cams/redo04.log    NO
  59.     5    ONLINE /data/cams/redo05.log    NO
  60.     6    ONLINE /data/cams/redo06.log    NO
  61.     1 INVALID ONLINE /file/cams/redo/redo01.log    NO
  62.     1 INVALID ONLINE /backup/cams/redo/redo01.log NO
  63.     2 INVALID ONLINE /file/cams/redo/redo02.log    NO
  64.     2 INVALID ONLINE /backup/cams/redo/redo02.log NO
  65.     3    ONLINE /file/cams/redo/redo03.log    NO
  66.     GROUP# STATUS TYPE MEMBER            IS_
  67.     3    ONLINE /backup/cams/redo/redo03.log NO
  68.     4 INVALID ONLINE /file/cams/redo/redo04.log    NO
  69.     4 INVALID ONLINE /backup/cams/redo/redo04.log NO
  70.     5 INVALID ONLINE /file/cams/redo/redo05.log    NO
  71.     5 INVALID ONLINE /backup/cams/redo/redo05.log NO
  72.     6 INVALID ONLINE /file/cams/redo/redo06.log    NO
  73.     6 INVALID ONLINE /backup/cams/redo/redo06.log NO
  74. 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

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计7740字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中