<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
    主站蜘蛛池模板: 亚洲V无码一区二区三区四区观看 亚洲αv久久久噜噜噜噜噜 | 青苹果乐园免费高清在线| 久久久亚洲欧洲日产国码农村| 国产乱妇高清无乱码免费| 亚洲欧洲精品成人久久奇米网 | 最近免费中文字幕MV在线视频3| 亚洲午夜成人精品电影在线观看| 羞羞视频在线观看免费| 亚洲爽爽一区二区三区| 成年女人A毛片免费视频| 亚洲精品无码久久久久sm| 久久99热精品免费观看动漫| 亚洲精品日韩专区silk| 蜜桃视频在线观看免费网址入口| 亚洲中文字幕一区精品自拍| 国产精品成人免费综合| 一级特黄a免费大片| 亚洲大成色www永久网站| 亚洲一级毛片免费看| 亚洲中文字幕一区精品自拍| 亚洲AV中文无码乱人伦在线视色| 日韩精品免费一线在线观看| 亚洲乱色熟女一区二区三区丝袜| 久久成人无码国产免费播放| 亚洲av无码国产综合专区| 国产乱子伦精品免费女| a级毛片免费在线观看| 亚洲日产2021三区在线| 日韩高清免费在线观看| 久久高潮一级毛片免费| 亚洲无线一二三四区| 国产精品免费看香蕉| 拍拍拍无挡免费视频网站| 亚洲一卡2卡4卡5卡6卡在线99| 国产在线播放免费| 97人妻精品全国免费视频| 最新国产精品亚洲| 亚洲色婷婷综合久久| 四虎永久在线精品免费观看视频| 免费人成视频在线观看免费| 精品亚洲国产成AV人片传媒|