<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
    主站蜘蛛池模板: 成年女人喷潮毛片免费播放| 免费无码黄十八禁网站在线观看| 日韩精品免费电影| 四虎1515hm免费国产| 亚洲国产三级在线观看| 亚洲国产精品第一区二区| 久久久WWW成人免费精品| 91精品免费久久久久久久久| 精品无码一区二区三区亚洲桃色| 亚洲av乱码一区二区三区按摩| 美女视频黄免费亚洲| 亚洲欧美日本韩国| 国产一级淫片视频免费看| 欧洲美女大片免费播放器视频| 午夜不卡久久精品无码免费| 麻豆国产精品入口免费观看| 亚洲狠狠婷婷综合久久蜜芽| 国产精品免费看香蕉| 三级片免费观看久久| 一二三四在线播放免费观看中文版视频 | 国产精品成人啪精品视频免费| 最近中文字幕免费完整| 丁香亚洲综合五月天婷婷| 亚洲韩国—中文字幕| 最刺激黄a大片免费网站| 亚洲最大福利视频| 日韩亚洲国产二区| 两个人看的www免费高清 | 国产亚洲?V无码?V男人的天堂| 亚洲熟女乱色一区二区三区| 四虎影视在线永久免费看黄| 精品久久久久久无码免费| 久久久无码精品亚洲日韩蜜臀浪潮 | 亚洲午夜精品久久久久久浪潮 | 亚洲va中文字幕无码久久| 一级女人18毛片免费| 美女视频黄频a免费| 亚洲AV无码AV男人的天堂| 久久不见久久见免费影院| 一级黄色免费网站| 亚洲日本乱码一区二区在线二产线|