关于Log的一些操作
最近一直受困于一些Log的操作问题，很多问题都被集中遇到了，于是收集了各种资料，进行一下统一的学习。从自己遇到问题的几个方面做了解答。很多资料来自ASKTOM。这是很有用也很重要的内容。
一、Clear LogFile命令
说明：Clear LogFile命令基本上等同于删掉之后重建LogFile。要注意的是后面的UNARCHIVED和UNRECOVERABLE DATAFILE子句的应用规则。一般来说Clear LogFile主要用于以下几种情况：
① 降低Database版本时，需要改变redo log的格式
② online redo log被破坏，但又不希望重启database时
③ logfile在OS层面被意外破坏时
但是需要注意的是CLEAR LOGFILE无法单独重建LOG，即必须针对实际存在的LOGFILE。
CLEAR LOGFILE
reinitializes an online redo log, optionally without archiving the redo log. CLEAR
LOGFILE is similar to adding and dropping a redo log, except that the statement may be
issued even if there are only two logs for the thread and also may be issued for the
current redo log of a closed thread.
UNARCHIVED You must specify UNARCHIVED if you want to reuse a redo log that was not
archived.
WARNING: Specifying UNARCHIVED makes backups unusable if
the redo log is needed for recovery.
Do not use CLEAR LOGFILE to clear a log needed for media recovery. If it is necessary to
clear a log containing redo after the database checkpoint, you must first perform
incomplete media recovery. The current redo log of an open thread can be cleared. The
current log of a closed thread can be cleared by switching logs in the closed thread.
If the CLEAR LOGFILE statement is interrupted by a system or instance failure, then the
database may hang. If this occurs, reissue the statement after the database is restarted.
If
the failure occurred because of I/O errors accessing one member of a log group, then that
member can be dropped and other members added.
UNRECOVERABLE DATAFILE
You must specify UNRECOVERABLE DATAFILE if you have taken
the datafile offline with the database in ARCHIVELOG mode (that is, you specified ALTER
DATABSE ... DATAFILE OFFLINE without the
DROP keyword), and if the unarchived log to be cleared is needed to recover the datafile
before bringing it back online. In this case, you must drop the datafile and the entire
tablespace once the CLEAR LOGFILE statement completes.
二、V$LOG中的STATUS说明
说明：可以通过V$LOG来查看系统redo log的当前使用状态。需要注意的是INACTIVE状态是指longer needed for instance recovery而已，并不代表无效。当CHECKPOINT后即将redo log中的信息全部写入datafile，所有ACTIVE状态均会切换至INACTIVE。
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 might or might 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 might or might not be archived.
结合上面clear logfile的说明，举个例子：
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 333 104857600 1 NO CURRENT 8.2047E+12 07-JUN-04
3 1 332 104857600 1 NO INACTIVE 8.2047E+12 07-JUN-04
SQL> alter database clear logfile group 1;
alter database clear logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of thread 1
ORA-00312: online log 1 thread 1: '/home/ora9ir2/oradata/ora9ir2/redo01.log'
there we cannot clear it because it is current, so we make it not be current:
SQL> alter system switch logfile;
System altered.
SQL> alter database clear logfile group 1;
alter database clear logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of thread 1
ORA-00312: online log 1 thread 1: '/home/ora9ir2/oradata/ora9ir2/redo01.log'
now we cannot clear it because of dirty blocks in the cache, so we get rid of them
SQL> alter system checkpoint
2 /
System altered.
SQL> alter database clear logfile group 1;
Database altered.
SQL>
and we can clear it.
三、V$LOG中异常STATUS的模拟
在数据库正常的执行过程中，V$LOG表中的STATUS字段一般只会有current/active/inactive这三种状态，其余的所有状态均为非正常状态，肯定是出了某些问题或进行了预料之外的操作引起的。下面的这段就模拟了某一类情况。
it'll be normal for them to be either active, current or inactive.
a log switch is not at all anything like or related to a resetlogs -- that is what you do after an incomplete recovery.
forget about "transactions" for a moment, they are events that just are always happening (even if your database is "idle", it is never idle backgrounds are always doing things)
it would be normal for a redo log file to be
active -- has stuff in it that is needed for instance recovery if we crashed
right now.
current -- where stuff is being written to right now
inactive -- was used at some point, but isn't needed for instance recoveryanymore (all blocks it was protecting have been flushed to disk by acheckpoint) and isn't the current redo log file...
All other status would be "not normal" and after some period of activitywould become "normal"
I can only reproduce your findings with someone running an
alter system archive log current;
in another session -- if I don't, i get current, inactive...
My test was:
SQL> create table t ( dt timestamp, group# int, status varchar2(40) );
Table created.
SQL> create table stop(x int);
Table created.
SQL>
SQL> declare
2 l_n number;
3 begin
4 loop
5 insert into t select systimestamp, group#, status from v$log;
6 commit;
7 select count(*) into l_n from stop;
8 exit when l_n > 0;
9 end loop;
10 end;
11 /
PL/SQL procedure successfully completed.
SQL>
SQL> select status, count(*) from t group by status;
STATUS COUNT(*)
---------------------------------------- ----------
ACTIVE 7680
CURRENT 20381
INACTIVE 12701
INVALIDATED 2
UNUSED 2
If I just let that run for a while, nothing but active/current/inactive. If I do this however:
SQL> alter system archive log current;
System altered.
SQL> /
System altered.
SQL> insert into stop values ( 1 );
1 row created.
SQL> commit;
Commit complete.
So, maybe you were doing that in another session -- and then the definition of "invalidated" says it all...
四、重建被意外删除的LOGFILE
由于CLEAR LOGFILE命令只能针对存在的LOGFILE，所以若LOGFILE被意外删除时，不能用这个命令来重建日志文件，只能通过RESETLOGS子句来创建，具体过程如下所示：
SQL> SELECT GROUP#, MEMBERS, STATUS, ARCHIVED FROM V$LOG;
GROUP# MEMBERS STATUS ARC
---------- ---------- ---------------- ---
1 1 INACTIVE NO
2 1 CURRENT NO
3 1 INACTIVE NO
SQL>
SQL> SELECT GROUP#, STATUS, MEMBER FROM V$LOGFILE;
GROUP# STATUS MEMBER
---------- ------- -------------------------------------------------------------------------------
3 STALE /home/ora10gr2/oradata/ora10gr2/redo03.log
2 /home/ora10gr2/oradata/ora10gr2/redo02.log
1 /home/ora10gr2/oradata/ora10gr2/redo01.log
SQL> !rm /home/ora10gr2/oradata/ora10gr2/redo03.log
SQL> !rm /home/ora10gr2/oradata/ora10gr2/redo01.log
SQL> !ls /home/ora10gr2/oradata/ora10gr2/redo0*.log
/home/ora10gr2/oradata/ora10gr2/redo02.log
SQL> connect / as sysdba
Connected.
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 603979776 bytes
Fixed Size 1262248 bytes
Variable Size 448793944 bytes
Database Buffers 150994944 bytes
Redo Buffers 2928640 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/home/ora10gr2/oradata/ora10gr2/redo01.log'
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
SQL> recover until cancel;
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL> !ls /home/ora10gr2/oradata/ora10gr2/redo0*.log
/home/ora10gr2/oradata/ora10gr2/redo01.log /home/ora10gr2/oradata/ora10gr2/redo02.log
/home/ora10gr2/oradata/ora10gr2/redo03.log