任务：增加日志组数至4组，且每组日志成员大小为50M，每组2个成员。
--查看日志成员路径及日志组大小、状态
SQL> desc v$log
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP# NUMBER
THREAD# NUMBER
SEQUENCE# NUMBER
BYTES NUMBER
BLOCKSIZE NUMBER
MEMBERS NUMBER
ARCHIVED VARCHAR2(3)
STATUS VARCHAR2(16)
FIRST_CHANGE# NUMBER
FIRST_TIME DATE
NEXT_CHANGE# NUMBER
NEXT_TIME DATE
SQL> select GROUP#,BYTES/1024/1024 m,MEMBERS from v$log;
GROUP# M MEMBERS
---------- ---------- -------------------------------------------
1 50 1
2 50 1
3 50 1
SQL> desc v$logfile
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP# NUMBER
STATUS VARCHAR2(7)
TYPE VARCHAR2(7)
MEMBER VARCHAR2(513)
IS_RECOVERY_DEST_FILE VARCHAR2(3)
SQL> col MEMBER for a50
SQL> select GROUP#,MEMBER from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
3 /u01/app/oracle/oradata/ORA11GR2/redo03.log
2 /u01/app/oracle/oradata/ORA11GR2/redo02.log
1 /u01/app/oracle/oradata/ORA11GR2/redo01.log
--增加group 4,group 5,group 6,group 7四组日志
SQL> alter database add logfile group 4 ('/u01/app/oracle/oradata/ORA11GR2/redo4_a.log','/u01/app/oracle/oradata/ORA11GR2/redo4_b.log') size 50M;
Database altered.
SQL> alter database add logfile group 5('/u01/app/oracle/oradata/ORA11GR2/redo5_a.log','/u01/app/oracle/oradata/ORA11GR2/redo5_b.log') size 50M;
Database altered.
SQL> alter database add logfile group 6('/u01/app/oracle/oradata/ORA11GR2/redo6_a.log','/u01/app/oracle/oradata/ORA11GR2/redo6_b.log') size 50M;
Database altered.
SQL> alter database add logfile group 7('/u01/app/oracle/oradata/ORA11GR2/redo7_a.log','/u01/app/oracle/oradata/ORA11GR2/redo7_b.log') size 50M;
Database altered.
--查看日志组状态
SQL> select group#,member from v$logfile order by 1;
GROUP# MEMBER
---------- --------------------------------------------------
1 /u01/app/oracle/oradata/ORA11GR2/redo01.log
2 /u01/app/oracle/oradata/ORA11GR2/redo02.log
3 /u01/app/oracle/oradata/ORA11GR2/redo03.log
4 /u01/app/oracle/oradata/ORA11GR2/redo4_a.log
4 /u01/app/oracle/oradata/ORA11GR2/redo4_b.log
5 /u01/app/oracle/oradata/ORA11GR2/redo5_a.log
5 /u01/app/oracle/oradata/ORA11GR2/redo5_b.log
6 /u01/app/oracle/oradata/ORA11GR2/redo6_a.log
6 /u01/app/oracle/oradata/ORA11GR2/redo6_b.log
7 /u01/app/oracle/oradata/ORA11GR2/redo7_a.log
7 /u01/app/oracle/oradata/ORA11GR2/redo7_b.log
11 rows selected.
SQL> select GROUP#,BYTES/1024/1024 m,MEMBERS,STATUS from v$log;
GROUP# M MEMBERS STATUS
---------- ---------- ---------- --------------------------------------------
1 50 1 INACTIVE
2 50 1 CURRENT
3 50 1 INACTIVE
4 50 2 UNUSED
5 50 2 UNUSED
6 50 2 UNUSED
7 50 2 UNUSED
7 rows selected.
--切换日志，查看日志组状态
SQL> alter system switch logfile;
System altered.
SQL> select GROUP#,BYTES/1024/1024 m,MEMBERS,STATUS from v$log;
GROUP# M MEMBERS STATUS
---------- ---------- ---------- ----------------
1 50 1 INACTIVE
2 50 1 ACTIVE
3 50 1 INACTIVE
4 50 2 CURRENT
5 50 2 UNUSED
6 50 2 UNUSED
7 50 2 UNUSED
7 rows selected.
（switch logfile，当启动归档时则将前面的文件归档，应用下一个日志组写数据；当为非归档时，则也应用下一个日志组写数据，或者循环覆写）
--手工执行检查点事件
SQL> alter system checkpoint;
System altered.
SQL> select GROUP#,BYTES/1024/1024 m,MEMBERS,STATUS from v$log;
GROUP# M MEMBERS STATUS
---------- ---------- ---------- ----------------
1 50 1 INACTIVE
2 50 1 INACTIVE
3 50 1 INACTIVE
4 50 2 CURRENT
5 50 2 UNUSED
6 50 2 UNUSED
7 50 2 UNUSED
7 rows selected.
手工触发检查点就是将先前的使用的日志active（可能在实例恢复，报告inactive）转到inactive，然后将更新数据文件、控制文件，使数据文件、控制文件及redo日志三者一致性（即更新SCN一致）
--删除group 1,group 2,group 3
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> select group#,bytes/1024/1024 m,members,status from v$log;
GROUP# M MEMBERS STATUS
---------- ---------- ---------- ----------------
4 50 2 CURRENT
5 50 2 UNUSED
6 50 2 UNUSED
7 50 2 UNUSED
说明：
日志组的四种状态：
unused:表明该日志组从来没有被使用过，我们可以看到重建的日志组就是这个状态。
current:是oracle当前正在使用的日志组，即后台进程LGWR正在写的日志组。
active:当发生日志切换的时候，状态为current的日志组就会变成active状态，表明记录在这个日志组的事务提交所造成的数据块改变，还没有完全从database buffer cache 写入到data file中。当数据库处于archivelog mode时，ARCn就会归档这个日志组。所以处在这个状态的日志组是不允许被覆盖的。崩溃恢复需要该状态，可用于块恢复，可能归档也可能未归档。
inactive:处于这个状态的日志组，表明记录在这个日志组的事务提交所造成的数据块的改变，已经完全从database buffer cache写入到data file中了，实例恢复不再需要这个状态的日志组了，所以这个日志组是可以被覆盖的。