<rt id="bn8ez"></rt>
<label id="bn8ez"></label>

  • <span id="bn8ez"></span>

    <label id="bn8ez"><meter id="bn8ez"></meter></label>

    Decode360's Blog

    業精于勤而荒于嬉 QQ:150355677 MSN:decode360@hotmail.com

      BlogJava :: 首頁 :: 新隨筆 :: 聯系 ::  :: 管理 ::
      397 隨筆 :: 33 文章 :: 29 評論 :: 0 Trackbacks
    關于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
    ?
    ?
    posted on 2009-04-09 23:19 decode360 閱讀(409) 評論(0)  編輯  收藏 所屬分類: 07.Oracle
    主站蜘蛛池模板: 久久精品无码免费不卡| 男人扒开添女人下部免费视频| 午夜亚洲国产理论秋霞| 国产成人亚洲综合色影视| 99久久亚洲精品无码毛片| 亚洲一久久久久久久久| 日韩精品免费一线在线观看| 国产免费卡一卡三卡乱码| 亚洲AV无码久久精品蜜桃| 亚洲粉嫩美白在线| 99久久成人国产精品免费| 日韩在线免费视频| 免费一级毛片在线观看| 亚洲熟妇无码久久精品| 无遮挡a级毛片免费看| 亚洲精品乱码久久久久久不卡| 亚洲大片在线观看| 日韩精品无码专区免费播放| 国产精品视频免费一区二区三区| 亚洲av无码国产精品夜色午夜| 无码一区二区三区免费| 亚洲av区一区二区三| 亚洲精品国产成人| 在线观看AV片永久免费| 亚洲福利在线观看| 久久久久久久免费视频| 亚洲高清一区二区三区电影| 天天影院成人免费观看| 在线亚洲午夜理论AV大片| 亚洲一区二区三区写真| 全部免费毛片在线| 99re6在线精品免费观看| 久久久久久亚洲Av无码精品专口 | 中文字幕亚洲第一| 精品女同一区二区三区免费播放 | 婷婷久久久亚洲欧洲日产国码AV| 中文字幕在线免费| 亚洲一区二区三区高清| 久久精品免费一区二区喷潮| 亚洲第一区视频在线观看| 国产免费一区二区三区VR|