增加redo日志大小操作演练,说明：此处为RAC环境且日志组成员为1个.
SQL> select group#,thread#,sequence#,members,status,archived from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 1 2075809 1 CURRENT NO
2 1 2075804 1 INACTIVE YES
3 2 1966613 1 ACTIVE YES
4 2 1966614 1 CURRENT NO
添加group 5和6，group5给实例1，group 6给实例2
SQL> alter database add logfile thread 1 group 5 '+DATADG' size 200M;
Database altered.
SQL> alter database add logfile thread 2 group 6 '+DATADG' size 200M;
Database altered.
SQL> select group#,thread#,sequence#,members,status,archived from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 1 2075858 1 INACTIVE YES
2 1 2075859 1 CURRENT NO
3 2 1966756 1 ACTIVE YES
4 2 1966757 1 CURRENT NO
5 1 2075857 1 INACTIVE YES
6 2 1966755 1 INACTIVE YES
6 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint;
System altered.
SQL> select group#,thread#,sequence#,members,status,archived from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 1 2075858 1 INACTIVE YES
2 1 2075859 1 INACTIVE YES
3 2 1966756 1 INACTIVE YES
4 2 1966757 1 INACTIVE YES
5 1 2075860 1 CURRENT NO
6 2 1966758 1 CURRENT NO
#确认group 5和6状态为current后，删除group 1和3
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> select group#,thread#,sequence#,members,status,archived from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
2 1 2075861 1 INACTIVE YES
4 2 1966763 1 INACTIVE YES
5 1 2075862 1 CURRENT NO
6 2 1966764 1 CURRENT NO
SQL> alter database add logfile thread 1 group 1 '+DATADG' size 200M;
Database altered.
SQL> alter database add logfile thread 2 group 3 '+DATADG' size 200M;
Database altered.
SQL> select group#,thread#,sequence#,members,status,archived from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 1 0 1 UNUSED YES
2 1 2075863 1 CURRENT NO
3 2 0 1 UNUSED YES
4 2 1966765 1 ACTIVE YES
5 1 2075862 1 ACTIVE YES
6 2 1966766 1 CURRENT NO
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint;
System altered.
SQL> select group#,thread#,sequence#,members,status,archived from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 1 2075864 1 CURRENT NO
2 1 2075863 1 INACTIVE YES
3 2 1966767 1 INACTIVE YES
4 2 1966768 1 INACTIVE YES
5 1 2075862 1 INACTIVE YES
6 2 1966769 1 CURRENT NO
6 rows selected.
#直到group2和4状态为inactive，后删除这两个组
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database drop logfile group 4;
Database altered.
SQL> select group#,thread#,sequence#,members,status,archived from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 1 2075864 1 CURRENT NO
3 2 1966770 1 CURRENT NO
5 1 2075862 1 INACTIVE YES
6 2 1966769 1 ACTIVE YES
SQL> alter database add logfile thread 1 group 2 '+DATADG' size 200M;
Database altered.
SQL> alter database add logfile thread 2 group 4 '+DATADG' size 200M;
Database altered.
SQL> select group#,thread#,sequence#,members,status,archived from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 1 2075864 1 ACTIVE YES
2 1 0 1 UNUSED YES
3 2 1966770 1 ACTIVE YES
4 2 0 1 UNUSED YES
5 1 2075865 1 CURRENT NO
6 2 1966771 1 CURRENT NO
6 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint;
System altered.
SQL> select group#,thread#,sequence#,members,status,archived from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 1 2075864 1 INACTIVE YES
2 1 2075866 1 CURRENT NO
3 2 1966770 1 INACTIVE YES
4 2 1966772 1 CURRENT NO
5 1 2075865 1 INACTIVE YES
6 2 1966771 1 INACTIVE YES
6 rows selected.
SQL> alter database drop logfile group 5;
Database altered.
SQL> alter database drop logfile group 6;
Database altered.
SQL> select group#,thread#,sequence#,members,status,archived from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 1 2075864 1 INACTIVE YES
2 1 2075866 1 CURRENT NO
3 2 1966773 1 CURRENT NO
4 2 1966772 1 ACTIVE YES
rac环境下，增加redo日志大小的演练过程结束。
