from v$logfile a,v$log b
where a.group#=b.group#;
GROUP# MEMBER M STATUS ARC
---------- -------------------------------------------------- ---------- ---------------- ---
3 /home/oracle/app/oracle/oradata/orcl/redo03.log 50 INACTIVE YES
2 /home/oracle/app/oracle/oradata/orcl/redo02.log 50 CURRENT NO
一个实例必须要有两组重做日组，如果删除其中组就会报would leave less than 2 log files for instance orcl (thread 1)
SQL> alter database drop logfile group 3 ;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01567: dropping log 3 would leave less than 2 log files for instance orcl (thread 1)
ORA-00312: online log 3 thread 1: '/home/oracle/app/oracle/oradata/orcl/redo03.log'
6.清空重做日志组
清空重做日志：对重做日志文件内容全部初始化，相当于删除重做日志文件，并重新创建重做日志文件一个实例必须要两组重做日志，如果重做日志组只有两组，不能被被清空
重做日志组内有重做日志文件处于current或者active状态，不能清空重做日志组
SQL> select a.group#,a.member,b.bytes/1024/1024,b.status,b.archived
from v$logfile a,v$log b
where a.group#=b.group#;
GROUP# MEMBER B.BYTES/1024/1024 STATUS ARC
---------- -------------------------------------------------- ----------------- ---------------- ---
3 /home/oracle/app/oracle/oradata/orcl/redo03.log 50 INACTIVE YES
2 /home/oracle/app/oracle/oradata/orcl/redo02.log 50 CURRENT NO
5 /home/oracle/app/oracle/oradata/orcl/redo05_01.log 100 UNUSED YES
清空重做日志组：
SQL> alter database clear logfile group 3;
清空重做日志文件
SQL> alter database clear logfile '/home/oracle/app/oracle/oradata/orcl/redo05_01.log';
如果重做日志处于未归档状态，必须使用unarchived 进行清空
SQL> alter database clear unarchived logfile group 6;
Database altered.
6.与重做日志有关的动态视图
v$log
v$logfile
v$log_history
v$log视图中重要的字段
Status of the log member:UNUSED - Online redo log has never been written to. This is the state of a redo log that was just added, or just after a RESETLOGS, when it is not the current redo log.
CURRENT - Current redo log. This implies that the redo log is active. The redo log could be open or closed.
ACTIVE - Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.
CLEARING - Log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, the status changes to UNUSED.
CLEARING_CURRENT - Current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header.
INACTIVE - Log is no longer needed for instance recovery. It may be in use for media recovery. It may or may not be archived.
v$logfile视图中重要的字段：Status of the log member:
-
INVALID - File is inaccessible
STALE - File's contents are incomplete
DELETED - File is no longer used
null - File is in use