Oracle 联机重做日志文件(ONLINE LOG FILE)
--=========================================
-- Oracle?聯(lián)機(jī)重做日志文件(ONLINE LOG FILE)
--=========================================
?
一、Oracle中的幾類日志文件
????Redo?log?files??????-->聯(lián)機(jī)重做日志
????Archive?log?files???-->歸檔日志
????Alert?log?files?????-->告警日志
????Trace files?????????-->跟蹤日志
????????user_dump_dest??????????-->用戶跟蹤日志
????????backupground_dump_dest??-->進(jìn)程跟蹤日志
???
????--查看后臺(tái)進(jìn)程相關(guān)目錄
????????SQL>?show parameter?dump
?
????????NAME?????????????????????????????????TYPE????????VALUE
????????------------------------------------ ----------- ------------------------------
????????background_core_dump?????????????????string??????partial
????????background_dump_dest?????????????????string??????/u01/app/oracle/admin/orcl/bdump
????????core_dump_dest???????????????????????string??????/u01/app/oracle/admin/orcl/cdump
????????max_dump_file_size???????????????????string??????UNLIMITED
????????shadow_core_dump?????????????????????string??????partial
????????user_dump_dest???????????????????????string??????/u01/app/oracle/admin/orcl/udump??
???????
????關(guān)于Oracle?常用目錄及路徑請(qǐng)參考：Oracle?常用目錄結(jié)構(gòu)(10g)
????關(guān)于Oracle?體系結(jié)構(gòu)請(qǐng)參考：Oracle實(shí)例和Oracle數(shù)據(jù)庫(Oracle體系結(jié)構(gòu))
?
二、聯(lián)機(jī)重做日志的規(guī)劃管理
????1.聯(lián)機(jī)重做日志?????
????????記錄了數(shù)據(jù)的所有變化(DML,DDL或管理員對(duì)數(shù)據(jù)所作的結(jié)構(gòu)性更改等)
????????提供恢復(fù)機(jī)制(對(duì)于意外刪除或宕機(jī)利用日志文件實(shí)現(xiàn)數(shù)據(jù)恢復(fù))
????????可以被分組管理
???????
????2.聯(lián)機(jī)重做日志組
????????由一個(gè)或多個(gè)相同的聯(lián)機(jī)日志文件組成一個(gè)聯(lián)機(jī)重做日志組
????????至少兩個(gè)日志組，每組一個(gè)成員(建議每組兩個(gè)成員，分散放開到不同的磁盤)
????????由LGWR后臺(tái)進(jìn)程同時(shí)將日志內(nèi)容寫入到一個(gè)組的所有成員
????????????LGWR的觸發(fā)條件
????????????????在事務(wù)提交的時(shí)候(COMMIT)
????????????????Redo?Log?Buffer?三分之一滿
????????????????Redo?Log?Buffer?多于一兆的變化記錄
????????????????在DBWn寫入數(shù)據(jù)文件之前
???????????
????3.聯(lián)機(jī)重做日志成員
????????重做日志組內(nèi)的每一個(gè)聯(lián)機(jī)日志文件稱為一個(gè)成員
????????一個(gè)組內(nèi)的每一個(gè)成員具有相同的日志序列號(hào)(log?sequence number),且成員的大小相同
????????每次日志切換時(shí)，Oracle服務(wù)器分配一個(gè)新的LSN號(hào)給即將寫入日志的日志文件組
????????LSN號(hào)用于唯一區(qū)分每一個(gè)聯(lián)機(jī)日志組和歸檔日志
????????處于歸檔模式的聯(lián)機(jī)日志，LSN號(hào)在歸檔時(shí)也被寫入到歸檔日志之中
???????
????4.日志文件的工作方式
????????日志文件采用按順序循環(huán)寫的方式
????????當(dāng)一組聯(lián)機(jī)日志組寫滿，LGWR則將日志寫入到下一組，當(dāng)最后一組寫滿則從第一組開始寫入
????????寫入下一組的過程稱為日志切換
????????切換時(shí)發(fā)生檢查點(diǎn)過程
????????檢查點(diǎn)的信息同時(shí)寫入到控制文件
???
????5.聯(lián)機(jī)日志文件的規(guī)劃
????????總原則
????????????分散放開，多路復(fù)用
????????????日志所在的磁盤應(yīng)當(dāng)具有較高的I/O
????????????一般日志組大小應(yīng)滿足自動(dòng)切換間隔至少15-20分鐘左右業(yè)務(wù)需求
????????????建議使用rdo結(jié)尾的日志文件名，避免誤刪日志文件。如redo1.rdo,redo2.rdo
????????規(guī)劃樣例
????????????Redo?Log?Group1?????Redo?Log?Group2?????Redo?Log?Group3
???????????
????????????Member1?????????????Member1??????????????Member1????????????-->Physical Disk 1
???????????
????????????Member2?????????????Member2??????????????Member2????????????-->Physical Disk 2
???????????
????????????Member3?????????????Member3??????????????Member3????????????-->Physical Disk 3
???????????
????6.日志切換和檢查點(diǎn)切換
????????ALTER?SYSTEM SWITCH LOGFILE;???--強(qiáng)制手動(dòng)切換
???????
????????ALTER?SYSTEM CHECKPOINT;
???????
????????強(qiáng)制設(shè)置檢查點(diǎn)間隔
????????ALTER?SYSTEM?SET?FAST_START_MTTR_TARGET?=?n
???????
????7.添加日志文件組
????????ALTER?DATABASE?ADD?LOGFILE [GROUP?n]
????????????('$ORACLE_BASE/oradata/u01/logn1.rdo',
?????????????'$ORACLE_BASE/oradata/u01/logn2.rdo')
????????????SIZE?mM;
???????????
????8.添加日志成員
????????ALTER?DATABASE?ADD?LOGFILE MEMBER
????????'$ORACLE_BASE/oradata/u01/logn1.rdo'?TO?GROUP?1,
????????'$ORACLE_BASE/oradata/u01/logn2.rdo'?TO?GROUP?2;
???????
????9.刪除日志成員
????????不能刪除組內(nèi)的唯一一個(gè)成員
????????不能刪除處于active?和current?狀態(tài)組內(nèi)的成員
????????刪除處于active?和current?狀態(tài)組內(nèi)的成員，應(yīng)使用日志切換使其處于INACTIVE狀態(tài)后再刪除
????????對(duì)于組內(nèi)如果一個(gè)成員為NULL?值，一個(gè)為INVALID，且組處入INACTIVE，僅能刪除INVALID狀態(tài)成員
????????刪除日志成員，物理文件并沒有真正刪除，需要手動(dòng)刪除
????????刪除日志文件后，控制文件被更新
????????對(duì)于處于歸檔模式下的數(shù)據(jù)庫，刪除成員時(shí)確保日志已被歸檔，查看v$log視圖獲得歸檔信息
????????ALTER?DATABASE?DROP?LOGFILE MEMBER?'$ORACLE_BASE/oradata/u01/logn1.rdo'
???????
????10.刪除日志組
????????一個(gè)實(shí)例至少需要兩個(gè)聯(lián)機(jī)日志文件組
????????活動(dòng)或當(dāng)前的日志組不能被刪除
????????組內(nèi)成員狀態(tài)有NULL?值或INVALID狀態(tài)并存，組不可刪除
????????日志組被刪除后，物理文件需要手動(dòng)刪除(對(duì)于非OMF)
????????ALTER?DATABASE?DROP?LOGFILE?GROUP?n
???????
????11.日志的重定位及重命名
????????所需權(quán)限
????????????ALTER?DATABASE?系統(tǒng)權(quán)限
????????????復(fù)制文件到目的位置操作系統(tǒng)權(quán)限(寫權(quán)限)
????????CURRENT狀態(tài)組內(nèi)的成員不能被重命名
????????建議該行為之前備份數(shù)據(jù)庫
????????重命名或重定位之后建議立即備份控制文件
????????重定位及重命名的兩種方法
????????????添加一個(gè)新成員到日志組，然后刪除一個(gè)舊的成員
????????????使用ALTER?DATABASE?RENAME?FILE?命令(不區(qū)分歸檔與非歸檔模式)
????????????????復(fù)制聯(lián)機(jī)日志文件到新路徑:ho cp <oldfile> <newfile>
????????????????執(zhí)行ALTER?DATABASE?RENAME?FILE?'<oldfile>'?TO?'<newfile>'
????????????對(duì)于處于CURRENT狀態(tài)的需要改名且不切換的情況下
????????????????辦法是切換到MOUNT狀態(tài)下再執(zhí)行上述操作
???????????????????????????????
????12.清空日志文件組
????????ALTER?DATABASE?CLEAR LOGIFLE?GROUP?n
????????ALTER?DATABASE?CLEAR UNARCHIVED LOGFILE?GROUP?n?--使用unarchived?避免歸檔
???
????13.日志周期循環(huán)及切換分析
???????
????????Group?1??????Group?2??????Group?3
???????
????????Current??????Inactive?????Inactive
????????---------- Log Switch -------------
????????Active???????Current??????Inactive
????????---------- Log Switch -------------
????????Active???????Active???????Current
????????---------- Log Switch -------------
????????Current??????Inactive?????Inactive
???????
????????--Active?和Current?稱之為在一個(gè)循環(huán)周期之內(nèi)(按順序?qū)懭罩?
????????--Inactive?稱為一個(gè)周期之外(一個(gè)新的循環(huán))
????????--新一輪循環(huán)開始如在歸檔狀態(tài)則先歸檔再清空，否則直接清空日志
????????--數(shù)據(jù)庫啟動(dòng)時(shí)Active?和Current?狀態(tài)的日志不能丟失，否則出錯(cuò)
???????
????14.日志的監(jiān)視
????????查看日志視圖中的物理日志文件是否存在、位置、大小等
????????????SELECT?'ho cp '||member?FROM?v$logfile;
????????查看日志文件所處的磁盤空間是否足夠
????????????SQL>?ho df?-h
????????查看組內(nèi)是否存在多個(gè)成員，如為單一成員應(yīng)考慮增加日志成員
????????日志切換的間隔時(shí)間，應(yīng)滿足15-20分鐘業(yè)務(wù)需求，如果切換間隔很短，應(yīng)當(dāng)增加日志文件的大小
????????????增加方法，先刪除日志組，再重建該組(對(duì)于current和active的需要切換再做處理)
?
????????--查看切換時(shí)間間隔(下面的示例中為手工切換的時(shí)間，不作考慮)
????????SQL>?SELECT?TO_CHAR(first_time,'yyyy-mm-dd hh24:mi:ss'),group#?FROM?v$log;
???
????????TO_CHAR(FIRST_TIME,?????GROUP#
????????------------------- ----------
????????2010-07-20 09:43:18??????????1
????????2010-07-19 22:44:30??????????2
????????2010-07-19 22:44:32??????????3?????????
???????
????15.日志的異常處理(參照演示中9小節(jié))
????????不一致的情況(啟動(dòng)時(shí))
????????????ALTER?DATABASE?CLEAR LOGFILE?GROUP?n
????????????ALTER?DATABASE?CLEAR UNARCHIVED LOGFILE?GROUP?n
???????????
三、與日志有關(guān)的動(dòng)態(tài)性能視圖
????????V$LOG
????????V$LOGFILE
???????
????????V$LOG中STATUS的狀態(tài)值
????????????UNUSED:?從未對(duì)該聯(lián)機(jī)日志寫入任何內(nèi)容，一般為新增加聯(lián)機(jī)日志文件或是使用resetlog后的狀態(tài)
????????????CURRENT：當(dāng)前重做日志文件，表示該重做日志文件為活動(dòng)狀態(tài)，能夠被打開和關(guān)閉
????????????ACTIVE：處于活動(dòng)狀態(tài)，不屬于當(dāng)前日志，崩潰恢復(fù)需要該狀態(tài)，可用于塊恢復(fù)，可能歸檔，也可能未歸檔
????????????CLEARING：表示在執(zhí)行alter?database?clear logfile命令后正將該日志重建為一個(gè)空日志，重建后狀態(tài)變?yōu)閡nused
????????????CLEARING_CURRENT:當(dāng)前日志處于關(guān)閉線程的清除狀態(tài)。如日志某些故障或?qū)懭胄氯罩緲?biāo)頭時(shí)發(fā)生I/O錯(cuò)誤
????????????INACTIVE:實(shí)例恢復(fù)不在需要聯(lián)機(jī)重做文件日志組，可能歸檔也可能未歸檔
???????????
????????V$LOGFILE中STATUS的狀態(tài)值
????????????INVALID?:表明該文件不可訪問
????????????STALE?:表明文件內(nèi)容不完全
????????????DELETED?:?表明該文件不再使用
????????????NULL?：表明文件正在使用
?
四、演示
????--1.查看當(dāng)前數(shù)據(jù)庫的日志
????????SQL>?SELECT?*?FROM?v$log;
?
????????????GROUP#????THREAD#??SEQUENCE#??????BYTES????MEMBERS ARC STATUS???????????FIRST_CHANGE# FIRST_TIM
????????---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
?????????????????1??????????1??????????5???52428800??????????1?NO??CURRENT????????????????2758062 19-JUL-10
?????????????????2??????????1??????????3???52428800??????????2 YES INACTIVE???????????????2695010 16-JUL-10
?????????????????3??????????1??????????4??104857600??????????2 YES INACTIVE???????????????2716552 18-JUL-10
?????????????????
?
????????SQL>?SELECT?*?FROM?v$logfile?ORDER?BY?group#;??
?
????????????GROUP# STATUS??TYPE????MEMBER??????????????????????????????????????????????????IS_
????????---------- ------- ------- ------------------------------------------------------- ---
?????????????????1?????????ONLINE??/u01/app/oracle/oradata/orcl/redo01.log?????????????????NO
?????????????????2 STALE???ONLINE??/u01/app/oracle/oradata/orcl/redo02.log?????????????????NO
?????????????????2 STALE???ONLINE??/u01/app/oracle/oradata/orcl/redo2.log??????????????????NO
?????????????????3 STALE???ONLINE??/u01/app/oracle/oradata/orcl/redo03.log?????????????????NO
?????????????????3 STALE???ONLINE??/u01/app/oracle/oradata/orcl/redo3.log??????????????????NO??
?????????????????
????--2.添加日志組
????????SQL>?SELECT?*?FROM?v$logfile;
?
????????????GROUP# STATUS??TYPE????MEMBER??????????????????????????????????????????????????IS_
????????---------- ------- ------- ------------------------------------------------------- ---
?????????????????2 STALE???ONLINE??/u01/app/oracle/oradata/orcl/redo2.log??????????????????NO
?????????????????2 STALE???ONLINE??/u01/app/oracle/oradata/orcl/redo02.log?????????????????NO
?????????????????1?????????ONLINE??/u01/app/oracle/oradata/orcl/redo01.log?????????????????NO
?????????????????3 STALE???ONLINE??/u01/app/oracle/oradata/orcl/redo3.log??????????????????NO
?????????????????3 STALE???ONLINE??/u01/app/oracle/oradata/orcl/redo03.log?????????????????NO
?????????????????4?????????ONLINE??/u01/app/oracle/oradata/orcl/redo4.log??????????????????NO
?????????????????4?????????ONLINE??/u01/app/oracle/oradata/orcl/redo04.log?????????????????NO
?????????????????
????--3.添加日志成員
????????SQL>?ALTER?DATABASE?ADD?LOGFILE MEMBER?'/u01/app/oracle/oradata/orcl/redo1.log'?TO?GROUP?1;
?
????????Database?altered.
?
????????SQL>?SELECT?*?FROM?v$logfile?WHERE?group#?=?1?;
?
????????????GROUP# STATUS??TYPE????MEMBER??????????????????????????????????????????????????IS_
????????---------- ------- ------- ------------------------------------------------------- ---
?????????????????1?????????ONLINE??/u01/app/oracle/oradata/orcl/redo01.log?????????????????NO
?????????????????1 INVALID ONLINE??/u01/app/oracle/oradata/orcl/redo1.log??????????????????NO
?????????????????
????--4.刪除日志成員
????????SQL>?ALTER?DATABASE?DROP?LOGFILE MEMBER?'/u01/app/oracle/oradata/orcl/redo01.log';
????????ALTER?DATABASE?DROP?LOGFILE MEMBER?'/u01/app/oracle/oradata/orcl/redo01.log'
????????*
????????ERROR at line 1:??--redo01.log處于NULL狀態(tài)且該日志組為current狀態(tài)不能刪除
????????ORA-00362:?member?is?required?to?form a valid logfile?in?group?1
????????ORA-01517:?log?member:?'/u01/app/oracle/oradata/orcl/redo01.log'
?
?
????????SQL>?ALTER?DATABASE?DROP?LOGFILE MEMBER?'/u01/app/oracle/oradata/orcl/redo04.log';
?
????????Database?altered.
?
????????SQL>??ALTER?DATABASE?DROP?LOGFILE MEMBER?'/u01/app/oracle/oradata/orcl/redo4.log';
?????????ALTER?DATABASE?DROP?LOGFILE MEMBER?'/u01/app/oracle/oradata/orcl/redo4.log'
????????*
????????ERROR at line 1:???--最后一個(gè)日志成員不能被刪除
????????ORA-00361:?cannot remove last?log?member?/u01/app/oracle/oradata/orcl/redo4.log?for?group?4
?
????--5.日志切換
????????SQL>?SELECT?*?FROM?v$log;??--當(dāng)前的日志組處于CURRENT狀態(tài)
?
????????????GROUP#????THREAD#??SEQUENCE#??????BYTES????MEMBERS ARC STATUS???????????FIRST_CHANGE# FIRST_TIM
????????---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
?????????????????1??????????1??????????5???52428800??????????2?NO??CURRENT????????????????2758062 19-JUL-10
?????????????????2??????????1??????????3???52428800??????????2 YES INACTIVE???????????????2695010 16-JUL-10
?????????????????3??????????1??????????4??104857600??????????2 YES INACTIVE???????????????2716552 18-JUL-10
?????????????????4??????????1??????????0???31457280??????????1 YES UNUSED???????????????????????0
?
????????SQL>?ALTER?SYSTEM SWITCH LOGFILE;??--進(jìn)行日志切換
?
????????System altered.
?
????????SQL>?SELECT?*?FROM?v$log;????--原來的日志組4的unused狀態(tài)變?yōu)閏urrent狀態(tài)
?
????????????GROUP#????THREAD#??SEQUENCE#??????BYTES????MEMBERS ARC STATUS???????????FIRST_CHANGE# FIRST_TIM
????????---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
?????????????????1??????????1??????????5???52428800??????????2 YES ACTIVE?????????????????2758062 19-JUL-10
?????????????????2??????????1??????????3???52428800??????????2 YES INACTIVE???????????????2695010 16-JUL-10
?????????????????3??????????1??????????4??104857600??????????2 YES INACTIVE???????????????2716552 18-JUL-10
?????????????????4??????????1??????????6???31457280??????????1?NO??CURRENT????????????????2759277 19-JUL-10
?
????????SQL>?ALTER?SYSTEM SWITCH LOGFILE;???--再次進(jìn)行日志切換
?
????????System altered.
?
????????SQL>?SELECT?*?FROM?v$log;??????????--日志組1變?yōu)閏urrent且組4變?yōu)閍ctive?狀態(tài)
?
????????????GROUP#????THREAD#??SEQUENCE#??????BYTES????MEMBERS ARC STATUS???????????FIRST_CHANGE# FIRST_TIM
????????---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
?????????????????1??????????1??????????5???52428800??????????2 YES ACTIVE?????????????????2758062 19-JUL-10
?????????????????2??????????1??????????7???52428800??????????2?NO??CURRENT????????????????2759293 19-JUL-10
?????????????????3??????????1??????????4??104857600??????????2 YES INACTIVE???????????????2716552 18-JUL-10
?????????????????4??????????1??????????6???31457280??????????1 YES ACTIVE?????????????????2759277 19-JUL-10???????
?
????由上可得知，在日志切換時(shí)對(duì)于unused組將優(yōu)先作為下一組切換對(duì)象
????????--再次刪除redo01.log還是收到錯(cuò)誤提示
????????SQL>?ALTER?DATABASE?DROP?LOGFILE MEMBER?'/u01/app/oracle/oradata/orcl/redo01.log';
????????ALTER?DATABASE?DROP?LOGFILE MEMBER?'/u01/app/oracle/oradata/orcl/redo01.log'
????????*
????????ERROR at line 1:
????????ORA-00362:?member?is?required?to?form a valid logfile?in?group?1
????????ORA-01517:?log?member:?'/u01/app/oracle/oradata/orcl/redo01.log'
?
????????SQL>?ALTER?SYSTEM SWITCH LOGFILE;??--再次進(jìn)行日志切換
?
????????System altered.
?
????????SQL>?SELECT?*?FROM?v$log;??????????--group1變?yōu)閕nactive
?
????????????GROUP#????THREAD#??SEQUENCE#??????BYTES????MEMBERS ARC STATUS???????????FIRST_CHANGE# FIRST_TIM
????????---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
?????????????????1??????????1??????????5???52428800??????????2 YES INACTIVE???????????????2758062 19-JUL-10
?????????????????2??????????1??????????7???52428800??????????2 YES ACTIVE?????????????????2759293 19-JUL-10
?????????????????3??????????1??????????8??104857600??????????2?NO??CURRENT????????????????2759420 19-JUL-10
?????????????????4??????????1??????????6???31457280??????????1 YES INACTIVE???????????????2759277 19-JUL-10
?
????????--反復(fù)多切幾次日志之后redo01.log被成功刪除?????
????????SQL>??ALTER?DATABASE?DROP?LOGFILE MEMBER?'/u01/app/oracle/oradata/orcl/redo01.log';
?
????Database?altered.
?????????
????--6.刪除日志組(CURRENT和ACTIVE狀態(tài)的不能被刪除)
????????SQL>?SELECT?*?FROM?v$log;
?
????????????GROUP#????THREAD#??SEQUENCE#??????BYTES????MEMBERS ARC STATUS???????????FIRST_CHANGE# FIRST_TIM
????????---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
?????????????????1??????????1??????????9???52428800??????????1 YES ACTIVE?????????????????2759487 19-JUL-10
?????????????????2??????????1?????????11???52428800??????????2?NO??CURRENT????????????????2759502 19-JUL-10
?????????????????3??????????1??????????8??104857600??????????2 YES ACTIVE?????????????????2759420 19-JUL-10
?????????????????4??????????1?????????10???31457280??????????1 YES ACTIVE?????????????????2759499 19-JUL-10
?????????????????
????????SQL>?ALTER?DATABASE?DROP?LOGFILE?GROUP?4;
????????ALTER?DATABASE?DROP?LOGFILE?GROUP?4
????????*
????????ERROR at line 1:???--處于活動(dòng)狀態(tài)的group4?用于災(zāi)難恢復(fù)，不能被刪除
????????ORA-01624:?log?4 needed?for?crash recovery?of?instance orcl?(thread 1)
????????ORA-00312:?online?log?4 thread 1:?'/u01/app/oracle/oradata/orcl/redo4.log'
?
????????SQL>?ALTER?SYSTEM SWITCH LOGFILE;???--進(jìn)行日志切換
?
????????System altered.
?
????????SQL>?/
?
????????System altered.
?
????????SQL>?SELECT?*?FROM?v$log;????--group 4的狀態(tài)變?yōu)閕nactvie
?
????????????GROUP#????THREAD#??SEQUENCE#??????BYTES????MEMBERS ARC STATUS???????????FIRST_CHANGE# FIRST_TIM
????????---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
?????????????????1??????????1?????????13???52428800??????????1?NO??CURRENT????????????????2759720 19-JUL-10
?????????????????2??????????1?????????11???52428800??????????2 YES ACTIVE?????????????????2759502 19-JUL-10
?????????????????3??????????1?????????12??104857600??????????2 YES ACTIVE?????????????????2759718 19-JUL-10
?????????????????4??????????1?????????10???31457280??????????1 YES INACTIVE???????????????2759499 19-JUL-10
?
????????SQL>?ALTER?DATABASE?DROP?LOGFILE?GROUP?4;???--成功刪除group 4
?
????????Database?altered.
?
????????SQL>?ho ls?/u01/app/oracle/oradata/orcl/redo*
????????/u01/app/oracle/oradata/orcl/redo01.log??/u01/app/oracle/oradata/orcl/redo1.log
????????/u01/app/oracle/oradata/orcl/redo02.log??/u01/app/oracle/oradata/orcl/redo2.log
????????/u01/app/oracle/oradata/orcl/redo03.log??/u01/app/oracle/oradata/orcl/redo3.log
????????/u01/app/oracle/oradata/orcl/redo04.log??/u01/app/oracle/oradata/orcl/redo4.log
?
????????SQL>?ho rm?/u01/app/oracle/oradata/orcl/redo04.log???--刪除物理文件
?
????????SQL>?ho rm?/u01/app/oracle/oradata/orcl/redo4.log????--刪除物理文件
?
????--7.日志的重定位及重命名(僅演示ALTER DATABASE RENAME FILE?命令)
????????SQL>?SELECT?name,log_mode?FROM?v$database;
?
????????NAME??????LOG_MODE
????????--------- ------------
????????ORCL??????ARCHIVELOG
?
????????SQL>?SELECT?*?FROM?v$logfile?ORDER?BY?group#;
?
????????????GROUP# STATUS??TYPE????MEMBER??????????????????????????????????????????????????IS_
????????---------- ------- ------- ------------------------------------------------------- ---
?????????????????1?????????ONLINE??/u01/app/oracle/oradata/orcl/redo01.log?????????????????NO
?????????????????2?????????ONLINE??/u01/app/oracle/oradata/orcl/redo02.log?????????????????NO
?????????????????2?????????ONLINE??/u01/app/oracle/oradata/orcl/redo2.log??????????????????NO
?????????????????3 STALE???ONLINE??/u01/app/oracle/oradata/orcl/redo03.log?????????????????NO
?????????????????3 STALE???ONLINE??/u01/app/oracle/oradata/orcl/redo3.log??????????????????NO
?
????????SQL>?ho cp?/u01/app/oracle/oradata/orcl/redo01.log?/u01/app/oracle/oradata/redo01.rdo??????
?
????????SQL>?ALTER?DATABASE?RENAME?FILE?'/u01/app/oracle/oradata/orcl/redo01.log'
??????????2????TO?'/u01/app/oracle/oradata/redo01.rdo';
?
????????Database?altered.
?
????????SQL>?SELECT?*?FROM?v$logfile?WHERE?group#?=?1;
?
????????????GROUP# STATUS??TYPE????MEMBER??????????????????????????????????????????????????IS_
????????---------- ------- ------- ------------------------------------------------------- ---
?????????????????1?????????ONLINE??/u01/app/oracle/oradata/redo01.rdo??????????????????????NO
???????
?
????--8.清空日志文件組(只有非active?和非current狀態(tài)的組才能被清空)
????????SQL>?SELECT?*?FROM?v$log;
?
????????????GROUP#????THREAD#??SEQUENCE#??????BYTES????MEMBERS ARC STATUS???????????FIRST_CHANGE# FIRST_TIM
????????---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
?????????????????1??????????1?????????13???52428800??????????1 YES ACTIVE?????????????????2759720 19-JUL-10
?????????????????2??????????1?????????14???52428800??????????2?NO??CURRENT????????????????2761383 19-JUL-10
?????????????????3??????????1?????????12??104857600??????????2 YES INACTIVE???????????????2759718 19-JUL-10
?????????????????
????????SQL>?ALTER?DATABASE?CLEAR LOGFILE?GROUP?1;
????????ALTER?DATABASE?CLEAR LOGFILE?GROUP?1
????????*
????????ERROR at line 1:?????--active?狀態(tài)不能被清空
????????ORA-01624:?log?1 needed?for?crash recovery?of?instance orcl?(thread 1)
????????ORA-00312:?online?log?1 thread 1:?'/u01/app/oracle/oradata/redo1.rdo'
?
????????SQL>??ALTER?DATABASE?CLEAR LOGFILE?GROUP?2;
?????????ALTER?DATABASE?CLEAR LOGFILE?GROUP?2
????????*
????????ERROR at line 1:????--current?狀態(tài)不能被清空
????????ORA-01624:?log?2 needed?for?crash recovery?of?instance orcl?(thread 1)
????????ORA-00312:?online?log?2 thread 1:?'/u01/app/oracle/oradata/orcl/redo2.log'
????????ORA-00312:?online?log?2 thread 1:?'/u01/app/oracle/oradata/orcl/redo02.log'
?
?
????????SQL>??ALTER?DATABASE?CLEAR LOGFILE?GROUP?3;
?
????????Database?altered.
?
?
????????SQL>?SELECT?*?FROM?v$log;???--group 3被清空后狀態(tài)變?yōu)閡nused
?
????????????GROUP#????THREAD#??SEQUENCE#??????BYTES????MEMBERS ARC STATUS???????????FIRST_CHANGE# FIRST_TIM
????????---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
?????????????????1??????????1?????????13???52428800??????????1 YES INACTIVE???????????????2759720 19-JUL-10
?????????????????2??????????1?????????14???52428800??????????2?NO??CURRENT????????????????2761383 19-JUL-10
?????????????????3??????????1??????????0??104857600??????????2 YES UNUSED?????????????????2759718 19-JUL-10
?????????????
????--9.日志異常處理
????????--啟動(dòng)時(shí)提示日志不一致
????????SQL>?startup
????????ORACLE instance started.
?
????????Total System Global Area??251658240 bytes
????????Fixed?Size??????????????????1218796 bytes
????????Variable?Size??????????????83887892 bytes
????????Database?Buffers??????????163577856 bytes
????????Redo Buffers????????????????2973696 bytes
????????Database?mounted.
????????ORA-00341:log?1?of?thread 1,wrong?log?#?in?header
????????ORA-00312:online?log?1 thread 1:'/u01/app/oracle/oradata/orcl/redo1a.rdo'??
????????ORA-00312:online?log?1 thread 1:'/u01/app/oracle/oradata/orcl/redo1b.rdo'
?
????????SQL>?ALTER?DATABASE?CLEAR LOGFILE?GROUP?1;
?
????????Database?altered.
?
????????SQL>?ALTER?DATABASE?OPEN;
?
????????Database?opened.
?
????????--日志文件丟失(非current狀態(tài)日志組)
????????SQL>?startup
????????ORACLE instance started.
?
????????Total System Global Area??251658240 bytes
????????Fixed?Size??????????????????1218796 bytes
????????Variable?Size??????????????88082196 bytes
????????Database?Buffers??????????159383552 bytes
????????Redo Buffers????????????????2973696 bytes
????????Database?mounted.
????????ORA-00313:?open?failed?for?members?of?log?group?1?of?thread 1
????????ORA-00312:?online?log?1 thread 1:?'/u01/app/oracle/oradata/orcl/redo1a.rdo'
????????ORA-00312:?online?log?1 thread 1:?'/u01/app/oracle/oradata/orcl/redo1b.rdo'
?
????????SQL>?ALTER?DATABASE?CLEAR LOGFILE?GROUP?1;
?
????????Database?altered.
?
????????SQL>?ALTER?DATABASE?OPEN;
?
????????Database?altered.
?
????????--日志文件丟失(current狀態(tài)日志組)
????????SQL>?startup
????????ORACLE instance started.
?
????????Total System Global Area??251658240 bytes
????????Fixed?Size??????????????????1218796 bytes
????????Variable?Size??????????????83887892 bytes
????????Database?Buffers??????????163577856 bytes
????????Redo Buffers????????????????2973696 bytes
????????Database?mounted.
????????ORA-00313:?open?failed?for?members?of?log?group?3?of?thread 1
????????ORA-00312:?online?log?3 thread 1:?'/u01/app/oracle/oradata/orcl/redo3a.rdo'
????????ORA-00312:?online?log?3 thread 1:?'/u01/app/oracle/oradata/orcl/redo3b.rdo'
?
????????--查看告警日志
????????SQL>?ho tail?-n 30?/u01/app/oracle/admin/orcl/bdump/alert_orcl.log
????????ORA-27037:?unable?to?obtain?file?status
????????Linux Error:?2:?No?such?file?or?directory
????????Additional information:?3
????????ORA-00312:?online?log?3 thread 1:?'/u01/app/oracle/oradata/orcl/redo3a.rdo'
????????ORA-27037:?unable?to?obtain?file?status
????????Linux Error:?2:?No?such?file?or?directory
????????Additional information:?3
????????Tue Jul 20 10:45:58 2010
????????Errors?in?file?/u01/app/oracle/admin/orcl/bdump/orcl_lgwr_4112.trc:
????????ORA-00313:?open?failed?for?members?of?log?group?3?of?thread 1
????????ORA-00312:?online?log?3 thread 1:?'/u01/app/oracle/oradata/orcl/redo3b.rdo'
????????ORA-27037:?unable?to?obtain?file?status
????????Linux Error:?2:?No?such?file?or?directory
????????Additional information:?3
????????ORA-00312:?online?log?3 thread 1:?'/u01/app/oracle/oradata/orcl/redo3a.rdo'
????????ORA-27037:?unable?to?obtain?file?status
????????Linux Error:?2:?No?such?file?or?directory
????????Additional information:?3
????????Tue Jul 20 10:45:58 2010
????????ARC0:?STARTING ARCH PROCESSES
????????Tue Jul 20 10:45:58 2010
????????ARC1:?Becoming the?'no FAL'?ARCH
????????ARC1:?Becoming the?'no SRL'?ARCH
????????Tue Jul 20 10:45:58 2010
????????ARC2:?Archival started
????????ARC0:?STARTING ARCH PROCESSES COMPLETE
????????ARC0:?Becoming the heartbeat ARCH
????????ARC2 started?with?pid=18,?OS id=4137
????????Tue Jul 20 10:45:58 2010
????????ORA-313 signalled during:?ALTER?DATABASE?OPEN...
???????
????????--查看物理日志文件是否存在
????????SQL>?ho ls?/u01/app/oracle/oradata/orcl/redo3a.rdo
????????ls:?/u01/app/oracle/oradata/orcl/redo3a.rdo:?No?such?file?or?directory
?
????????SQL>?ho ls?/u01/app/oracle/oradata/orcl/redo3b.rdo
????????ls:?/u01/app/oracle/oradata/orcl/redo3b.rdo:?No?such?file?or?directory
???????
--嘗試使用清空日志組命令
????????SQL>?ALTER?DATABASE?CLEAR LOGFILE?GROUP?3;
????????ALTER?DATABASE?CLEAR LOGFILE?GROUP?3
????????*
????????ERROR at line 1:???--系統(tǒng)處于非歸檔模式，且group 3狀態(tài)為CURRENT
????????ORA-00350:?log?3?of?instance orcl?(thread 1)?needs?to?be archived
????????ORA-00312:?online?log?3 thread 1:?'/u01/app/oracle/oradata/orcl/redo3a.rdo'
????????ORA-00312:?online?log?3 thread 1:?'/u01/app/oracle/oradata/orcl/redo3b.rdo'
?
????????--嘗試使用不歸檔清空日志
????????SQL>?ALTER?DATABASE?CLEAR UNARCHIVED LOGFILE?GROUP?3;
????????ALTER?DATABASE?CLEAR UNARCHIVED LOGFILE?GROUP?3
????????*
????????ERROR at line 1:??
????????ORA-00313:?open?failed?for?members?of?log?group?3?of?thread 1
????????ORA-00312:?online?log?3 thread 1:?'/u01/app/oracle/oradata/orcl/redo3b.rdo'
????????ORA-27037:?unable?to?obtain?file?status
????????Linux Error:?2:?No?such?file?or?directory
????????Additional information:?3
????????ORA-00312:?online?log?3 thread 1:?'/u01/app/oracle/oradata/orcl/redo3a.rdo'
????????ORA-27037:?unable?to?obtain?file?status
????????Linux Error:?2:?No?such?file?or?directory
????????Additional information:?3
?
????????--使用帶控制文件的介質(zhì)恢復(fù)
????????SQL>?RECOVER?DATABASE?USING?BACKUP?CONTROLFILE;
????????ORA-00279:?change 2835232 generated at 07/20/2010 10:40:23 needed?for?thread 1
????????ORA-00289:?suggestion?:?/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_07_20/o1_mf_1_39_%u_.arc
????????ORA-00280:?change 2835232?for?thread 1?is?in?sequence #39
?
?
????????Specify?log:?{<RET>=suggested?|?filename?|?AUTO?|?CANCEL}
?
????????ORA-00308:?cannot?open?archived?log?'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_07_20/o1_mf_1_39_%u_.arc'
????????ORA-27037:?unable?to?obtain?file?status
????????Linux Error:?2:?No?such?file?or?directory
????????Additional information:?3
?
????????--使用resetlogs選項(xiàng)打開數(shù)據(jù)庫
????????SQL>?ALTER?DATABASE?OPEN?RESETLOGS;
?
????????Database?altered.??????
?
????????SQL>?SELECT?*?FROM?v$log;???--系統(tǒng)重建group 3
?
????????????GROUP#????THREAD#??SEQUENCE#??????BYTES????MEMBERS ARC STATUS???????????FIRST_CHANGE# FIRST_TIM
????????---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
?????????????????1??????????1??????????2???31457280??????????2?NO??CURRENT????????????????2835234 20-JUL-10
?????????????????2??????????1??????????1???31457280??????????2 YES INACTIVE???????????????2835233 20-JUL-10
?????????????????3??????????1??????????0???31457280??????????2 YES UNUSED???????????????????????0??????????
?????????????????
????????SQL>?SELECT?*?FROM?v$logfile;???--為group 3增加了兩個(gè)成員redo3a.rdo?，redo3b.rdo??
?
????????????GROUP# STATUS??TYPE????MEMBER??????????????????????????????????????????????????IS_
????????---------- ------- ------- ------------------------------------------------------- ---
?????????????????2?????????ONLINE??/u01/app/oracle/oradata/orcl/redo2a.rdo?????????????????NO
?????????????????2?????????ONLINE??/u01/app/oracle/oradata/orcl/redo2b.rdo?????????????????NO
?????????????????1?????????ONLINE??/u01/app/oracle/oradata/orcl/redo1a.rdo?????????????????NO
?????????????????3?????????ONLINE??/u01/app/oracle/oradata/orcl/redo3a.rdo?????????????????NO
?????????????????3?????????ONLINE??/u01/app/oracle/oradata/orcl/redo3b.rdo?????????????????NO
?????????????????1?????????ONLINE??/u01/app/oracle/oradata/orcl/redo1b.rdo?????????????????NO??????
?
????????對(duì)于CURRENT組的也可以使用隱藏參數(shù)來解決
????????步驟：
????????????alter?system?set?"_allow_resetlogs_corruption"?=?true scope?=?spfile;
???????????
????????????recover?database?using bakcup controlfile;
???????????
????????????alter?database?open?resetlogs;
???????????
????????????shutdown?immediate;
???????????
????????????startup mount;
???????????
????????????alter?database?open?resetlogs;
???????????
????????????alter?system reset "_allow_resetlogs_corruption" scope?=?spfile sid?=?'*'
???
????????對(duì)于歸檔模式下的日志文件丟失，同樣可以按上述步驟處理
轉(zhuǎn)：http://blog.csdn.net/leshami/article/details/5749556
轉(zhuǎn)載于:https://www.cnblogs.com/andy6/p/5728715.html
總結(jié)
以上是生活随笔為你收集整理的Oracle 联机重做日志文件(ONLINE LOG FILE)的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： HTTP消息结构
- 下一篇： 网络编程学习笔记一：Socket编程