關于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
?
?