调整redo日志大小，说明：单实例环境，日志组成员2个.
SQL> select group#,bytes/1024/1024 from v$log;
GROUP# BYTES/1024/1024
---------- ---------------
1 50
2 50
3 50
SQL> select group#,thread#,sequence#,members,status,archived from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 1 722530 2 ACTIVE YES
2 1 722531 2 ACTIVE YES
3 1 722532 2 CURRENT NO
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG
D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02B.LOG
D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG
D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01B.LOG
D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG
D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03B.LOG
SQL> alter database add logfile group 4 ('D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO04.LOG','D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO04B.LOG') size 200M;
数据库已更改。
SQL> alter system switch logfile;
系统已更改
SQL> alter system checkpoint;
系统已更改。
SQL> select group#,thread#,sequence#,members,status,archived from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 1 722557 2 INACTIVE YES
2 1 722555 2 INACTIVE YES
3 1 722556 2 INACTIVE YES
4 1 722558 2 CURRENT NO
SQL> alter database drop logfile group 1; #删除需确认group1状态为inactive，下同
数据库已更改。
SQL> select group#,thread#,sequence#,members,status,archived from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
2 1 722555 2 INACTIVE YES
3 1 722556 2 INACTIVE YES
4 1 722558 2 CURRENT NO
SQL> alter database add logfile group 1 ('D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG','D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01B.LOG') size 200M; #添加前将此前REDO01.LOG、REDO01B.LOG文件删除
数据库已更改。
SQL> select group#,thread#,sequence#,members,status,archived from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 1 0 2 UNUSED YES
2 1 722555 2 INACTIVE YES
3 1 722556 2 INACTIVE YES
4 1 722558 2 CURRENT NO
SQL> alter database drop logfile group 2;
数据库已更改。
SQL> alter database drop logfile group 3;
数据库已更改。
SQL> select group#,thread#,sequence#,members,status,archived from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 1 0 2 UNUSED YES
4 1 722558 2 CURRENT NO
SQL> alter database add logfile group 2 ('D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG','D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02B.LOG') size 200M;
数据库已更改。
SQL> alter database add logfile group 3 ('D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG','D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03B.LOG') size 200M;
数据库已更改。
SQL> select group#,thread#,sequence#,members,status,archived from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 1 0 2 UNUSED YES
2 1 0 2 UNUSED YES
3 1 0 2 UNUSED YES
4 1 722558 2 CURRENT NO
SQL> alter system switch logfile;
系统已更改。
SQL> alter system checkpoint;
系统已更改。
SQL> select group#,thread#,sequence#,members,status,archived from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 1 722559 2 CURRENT NO
2 1 0 2 UNUSED YES
3 1 0 2 UNUSED YES
4 1 722558 2 INACTIVE YES
SQL> alter database drop logfile group 4;
数据库已更改。
SQL> select group#,thread#,sequence#,members,status,archived from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 1 722559 2 CURRENT NO
2 1 0 2 UNUSED YES
3 1 0 2 UNUSED YES
SQL> alter system switch logfile;
系统已更改。
SQL> alter system checkpoint;
系统已更改。
SQL> select group#,thread#,sequence#,members,status,archived from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 1 722559 2 INACTIVE YES
2 1 722560 2 CURRENT NO
3 1 0 2 UNUSED YES
SQL> alter system switch logfile;
系统已更改。
SQL> alter system checkpoint;
系统已更改。
SQL> select group#,thread#,sequence#,members,status,archived from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 1 722559 2 INACTIVE YES
2 1 722560 2 INACTIVE YES
3 1 722561 2 CURRENT NO
增加单实例每组日志两个成员的redo日志大小结束。
