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

调整redo日志大小和组数

1. [oracle@scdb1 ~]$ export ORACLE_SID=cams
   
2. [oracle@scdb1 ~]$ sqlplus / as sysdba
   
3. 
   
4. SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 13 12:35:42 2017
   
5. 
   
6. Copyright (c) 1982, 2013, Oracle. All rights reserved.
   
7. 
   
8. 
   
9. Connected to:
   
10. Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
    
11. 
    
12. SQL> alter database add logfile group 4 '/data/cams/redo04.log' size 1g;
    
13. 
    
14. Database altered.
    
15. 
    
16. SQL> alter database add logfile group 5'/data/cams/redo05.log' size 1g;
    
17. 
    
18. Database altered.
    
19. 
    
20. SQL> alter database add logfile group 6'/data/cams/redo06.log' size 1g;
    
21. 
    
22. Database altered.
    
23. 
    
24. SQL> set linesize 300;
    
25. SQL> col member for a30;
    
26. SQL> select * from v$logfile;
    
27. 
    
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
    
36. 
    
37. 6 rows selected.
    
38. SQL> select GROUP#,BYTES,MEMBERS,STATUS from v$log;
    
39. 
    
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
    
48. 
    
49. 6 rows selected.
    
50. 
    
51. SQL> alter system switch logfile;
    
52. 
    
53. System altered.
    
54. 
    
55. SQL> select GROUP#,BYTES,MEMBERS,STATUS from v$log;
    
56. 
    
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
    
65. 
    
66. 6 rows selected.
    
67. 
    
68. SQL> alter system checkpoint;
    
69. 
    
70. System altered.
    
71. 
    
72. SQL> select GROUP#,BYTES,MEMBERS,STATUS from v$log;
    
73. 
    
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
    
82. 
    
83. 6 rows selected.
    
84. 
    
85. SQL> alter database drop logfile GROUP 1;
    
86. 
    
87. Database altered.
    
88. 
    
89. SQL> alter database drop logfile GROUP 2;
    
90. 
    
91. Database altered.
    
92. 
    
93. SQL> alter database drop logfile GROUP 3;
    
94. 
    
95. Database altered.
    
96. 
    
97. SQL> select GROUP#,BYTES,MEMBERS,STATUS from v$log;
    
98. 
    
99.     GROUP#    BYTES     MEMBERS STATUS
    
100. ---------- ---------- ---------- ----------------
     
101.      4 1073741824     1 CURRENT
     
102.      5 1073741824     1 UNUSED
     
103.      6 1073741824     1 UNUSED
     
104. 
     
105. SQL> !mv /data/cams/redo01.log /data/cams/redo01.log_bak20160811
     
106. 
     
107. SQL> !mv /data/cams/redo02.log /data/cams/redo02.log_bak20160811
     
108. 
     
109. SQL> !mv /data/cams/redo03.log /data/cams/redo03.log_bak20160811
     
110. 
     
111. SQL> alter database add logfile GROUP 1 ('/data/cams/redo01.log') size 1g;
     
112. 
     
113. Database altered.
     
114. 
     
115. SQL> alter database add logfile GROUP 2 ('/data/cams/redo02.log') size 1g;
     
116. 
     
117. Database altered.
     
118. 
     
119. SQL> alter database add logfile GROUP 3 ('/data/cams/redo03.log') size 1g;
     
120. 
     
121. Database altered.
     
122. 
     
123. SQL> select GROUP#,BYTES,MEMBERS,STATUS from v$log;
     
124. 
     
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
     
133. 
     
134. 6 rows selected.
     
135. SQL> alter system switch logfile;
     
136. 
     
137. System altered.
     
138. 
     
139. SQL> alter system switch logfile;
     
140. 
     
141. System altered.
     
142. 
     
143. SQL> alter system switch logfile;
     
144. 
     
145. System altered.
     
146. 
     
147. SQL> alter system switch logfile;
     
148. 
     
149. System altered.
     
150. 
     
151. SQL> alter system switch logfile;
     
152. 
     
153. System altered.
     
154. 
     
155. SQL> select GROUP#,BYTES,MEMBERS,STATUS from v$log;
     
156. 
     
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
     
165. 
     
166. 6 rows selected.
     
167. 
     
168. SQL> alter system switch logfile;
     
169. 
     
170. System altered.
     
171. 
     
172. SQL> select GROUP#,BYTES,MEMBERS,STATUS from v$log;
     
173. 
     
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
     
182. 
     
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> /
   
3. 
   
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
    
15. 
    
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;
    
31. 
    
32. SQL> select * from v$logfile;
    
33. 
    
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
    
47. 
    
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
    
57. 
    
58. 18 rows selected.
    
59. 刚加入到redolog group的日志文件在被使用之前也是INVALID状态
    
60. SQL> alter system switch logfile;
    
61. 
    
62. System altered.
    
63. 
    
64. SQL> select * from v$logfile;
    
65. 
    
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
    
79. 
    
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
    
89. 
    
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/31394774/viewspace-2142446/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31394774/viewspace-2142446/