Oracle11g調整redo日誌大小、組數和每組成員數

煙花丶易冷發表於2017-07-26
Oracle

調整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日誌的其他成員存放在不同的磁碟中，以避免磁碟故障造成資料損失。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29618264/viewspace-2142612/，如需轉載，請註明出處，否則將追究法律責任。

相關文章