项目需求：
搭建完成数据库发现数据库只有3组，每组一个成员，每个数据文件为20M。计划把数据库组增加到5组，每组2个成员，每个数据文件扩大到200M。
1、查看数据库重做日志的现有配置：
vSQL> select group#,sequence#,members,bytes,status,archived from v$log;
GROUP# SEQUENCE# MEMBERS BYTES STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 199 1 52428800 CURRENT NO
2 197 1 52428800 INACTIVE NO
3 198 1 52428800 INACTIVE NO
备注：发现有三组重做日志组，每组1个成员，数据文件大小为50M。
2、查看数据库每个重做日志文件存放的位置：
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------ ---
3 ONLINE /ora_data/orcl/redo03.log NO
2 ONLINE /ora_data/orcl/redo02.log NO
1 ONLINE /ora_data/orcl/redo01.log NO
3、在数据库中添加两个新的重做日志组，第四组和第五组，每个组2个成员，日志文件大小为200M：
SQL> alter database add LOGFILE
2 ('/ora_data/orcl/redo04a.log',
3 '/ora_data/orcl/redo04b.log')
4 size 200M;
Database altered.
SQL> alter database add LOGFILE
2 ('/ora_data/orcl/redo05a.log',
3 '/ora_data/orcl/redo05b.log')
4 size 200M;
Database altered.
4、添加完以后查看日志信息：
SQL> select group#,sequence#,members,bytes,status,archived from v$log;
GROUP# SEQUENCE# MEMBERS BYTES STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 199 1 52428800 CURRENT NO
2 197 1 52428800 INACTIVE NO
3 198 1 52428800 INACTIVE NO
4 0 2 209715200 UNUSED YES
5 0 2 209715200 UNUSED YES
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------ ---
3 ONLINE /ora_data/orcl/redo03.log NO
2 ONLINE /ora_data/orcl/redo02.log NO
1 ONLINE /ora_data/orcl/redo01.log NO
4 ONLINE /ora_data/orcl/redo04a.log NO
4 ONLINE /ora_data/orcl/redo04b.log NO
5 ONLINE /ora_data/orcl/redo05a.log NO
5 ONLINE /ora_data/orcl/redo05b.log NO
7 rows selected.
5、因为当前日志组是第一组，所以决定先删除第3组重做日志，进行更改(成员加到2个，文件大小变为200M)：
SQL> alter database drop logfile group 3;
Database altered.
SQL> select group#,sequence#,members,bytes,status,archived from v$log;
GROUP# SEQUENCE# MEMBERS BYTES STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 199 1 52428800 CURRENT NO
2 197 1 52428800 INACTIVE NO
4 0 2 209715200 UNUSED YES
5 0 2 209715200 UNUSED YES
SQL> alter database add logfile group 3
2 ('/ora_data/orcl/redo03a.log',
3 '/ora_data/orcl/redo03b.log')
4 size 200M;
Database altered.
SQL> select group#,sequence#,members,bytes,status,archived from v$log;
GROUP# SEQUENCE# MEMBERS BYTES STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 199 1 52428800 CURRENT NO
2 197 1 52428800 INACTIVE NO
3 0 2 209715200 UNUSED YES
4 0 2 209715200 UNUSED YES
5 0 2 209715200 UNUSED YES
6、因为当前日志组是第一组，所以决定先删除第2组重做日志，进行更改(成员加到2个，文件大小变为200M)：
SQL> alter database drop logfile group 2;
Database altered.
SQL> select group#,sequence#,members,bytes,status,archived from v$log;
GROUP# SEQUENCE# MEMBERS BYTES STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 199 1 52428800 CURRENT NO
3 0 2 209715200 UNUSED YES
4 0 2 209715200 UNUSED YES
5 0 2 209715200 UNUSED YES
SQL> alter database add logfile group 2
2 ('/ora_data/orcl/redo02a.log',
3 '/ora_data/orcl/redo02b.log')
4 size 200M;
Database altered.
SQL> select group#,sequence#,members,bytes,status,archived from v$log;
GROUP# SEQUENCE# MEMBERS BYTES STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 199 1 52428800 CURRENT NO
2 0 2 209715200 UNUSED YES
3 0 2 209715200 UNUSED YES
4 0 2 209715200 UNUSED YES
5 0 2 209715200 UNUSED YES
7、因为当前日志组是第一组，所以不能直接删除，我们需要进行手动切换后，让他变为INACTIVE状态，然后再删除并添加(成员加到2个，文件大小变为200M)：
SQL> alter system switch logfile; /手动切换重做日志组
System altered.
SQL> select group#,sequence#,members,bytes,status,archived from v$log;
GROUP# SEQUENCE# MEMBERS BYTES STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 199 1 52428800 ACTIVE NO
2 200 2 209715200 CURRENT NO
3 0 2 209715200 UNUSED YES
4 0 2 209715200 UNUSED YES
5 0 2 209715200 UNUSED YES
SQL> select group#,sequence#,members,bytes,status,archived from v$log;
GROUP# SEQUENCE# MEMBERS BYTES STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 199 1 52428800 INACTIVE NO
2 200 2 209715200 CURRENT NO
3 0 2 209715200 UNUSED YES
4 0 2 209715200 UNUSED YES
5 0 2 209715200 UNUSED YES
删除group 1重做日志组，并重建：
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database add logfile group 1
2 ('/ora_data/orcl/redo01a.log',
3 '/ora_data/orcl/redo01b.log')
4 size 200M;
Database altered.
SQL> select group#,sequence#,members,bytes,status,archived from v$log;
GROUP# SEQUENCE# MEMBERS BYTES STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 0 2 209715200 UNUSED YES
2 200 2 209715200 CURRENT NO
3 0 2 209715200 UNUSED YES
4 0 2 209715200 UNUSED YES
5 0 2 209715200 UNUSED YES
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------- ---
3 ONLINE /ora_data/orcl/redo03a.log NO
2 ONLINE /ora_data/orcl/redo02a.log NO
1 ONLINE /ora_data/orcl/redo01a.log NO
4 ONLINE /ora_data/orcl/redo04a.log NO
4 ONLINE /ora_data/orcl/redo04b.log NO
5 ONLINE /ora_data/orcl/redo05a.log NO
5 ONLINE /ora_data/orcl/redo05b.log NO
3 ONLINE /ora_data/orcl/redo03b.log NO
2 ONLINE /ora_data/orcl/redo02b.log NO
1 ONLINE /ora_data/orcl/redo01b.log NO
8、上述步骤做完以后，多切几次日志组：
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> select group#,sequence#,members,bytes,status,archived from v$log;
GROUP# SEQUENCE# MEMBERS BYTES STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 206 2 209715200 CURRENT NO
2 205 2 209715200 INACTIVE NO
3 202 2 209715200 INACTIVE NO
4 203 2 209715200 INACTIVE NO
5 204 2 209715200 INACTIVE NO
总结：上述步骤就完成了redo log group的相关改造工作。重做日志组由3组增加到5组，每组由1个成员增加到2个成员，每个日志文件大小由20M增加为200M。
9、查看重做日志的切换次数：
column h0 format 999
column h1 format 999
column h2 format 999
column h3 format 999
column h4 format 999
column h5 format 999
column h6 format 999
column h7 format 999
column h8 format 999
column h9 format 999
column h10 format 999
column h11 format 999
column h12 format 999
column h13 format 999
column h14 format 999
column h15 format 999
column h16 format 999
column h17 format 999
column h18 format 999
column h19 format 999
column h20 format 999
column h21 format 999
column h22 format 999
column h23 format 999
column avg format 999.99
column day format a6
SELECT TRUNC (first_time) "Date", TO_CHAR (first_time, 'Dy') "Day", COUNT (1) "Total",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '00', 1, 0)) h0,
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '01', 1, 0)) "h1",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '02', 1, 0)) "h2",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '03', 1, 0)) "h3",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '04', 1, 0)) "h4",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '05', 1, 0)) "h5",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '06', 1, 0)) "h6",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '07', 1, 0)) "h7",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '08', 1, 0)) "h8",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '09', 1, 0)) "h9",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '10', 1, 0)) "h10",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '11', 1, 0)) "h11",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '12', 1, 0)) "h12",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '13', 1, 0)) "h13",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '14', 1, 0)) "h14",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '15', 1, 0)) "h15",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '16', 1, 0)) "h16",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '17', 1, 0)) "h17",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '18', 1, 0)) "h18",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '19', 1, 0)) "h19",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '20', 1, 0)) "h20",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '21', 1, 0)) "h21",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '22', 1, 0)) "h22",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '23', 1, 0)) "h23", to_char(ROUND (COUNT (1) / 24, 2),'fm99999999990.00') "Avg"
FROM gv$log_history
WHERE first_time >= trunc(SYSDATE) - 30
and thread# = inst_id
GROUP BY TRUNC (first_time), TO_CHAR (first_time, 'Dy')
ORDER BY 1 DESC;
Date Day Total H0 h1 h2 h3 h4 h5 h6 h7 h8 h9 h10 h11 h12 h13 h14 h15 h16 h17 h18 h19 h20 h21 h22 h23 Avg
------------------- ------ ---------- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---------------------------------------------
2017-09-14 00:00:00 Thu 6 6 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0.25
2017-09-13 00:00:00 Wed 4 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 4 0 0 0 0.17
网友评论