Oracle中的聯機日誌檔案發生不同程度損壞的恢
ORACLE通過redo來保證資料的重演，從而使得在資料庫故障的時候，可以恢復，在資料庫中redo檔案至關重要。?????????
1.對於聯機日誌損壞要根據日誌狀態進行分析，聯機日誌一般分為Current Active Inactive和unused四種狀態，unused日誌組還沒有使用。Inactive狀態的日誌不會造成資料的丟失，而Active和Current狀態的日誌一般會造成資料的丟失。
1）模擬錯誤，Inactive的日誌損壞
SYS@ENMOEDU > select GROUP#,ARCHIVED,STATUS from v$log;
GROUP# ARC STATUS
---------- --- ----------------
1 YES INACTIVE
2 NO CURRENT
3 YES INACTIVE
[oracle@ENMOEDU ENMOEDU]$ ls
control01.ctl he.dbf redo01.log redo02.log redo03.log redo04.log sample01.dbf system01.dbf undotbs01.dbf
control03.ct redo01b.log redo02b.log redo03b.log redo04b.log sysaux01.dbf temp01.dbf undotbs02.dbf users01.dbf
[oracle@ENMOEDU ENMOEDU]$ rm -rf redo01.log
[oracle@ENMOEDU ENMOEDU]$ rm -rf redo01b.log
SYS@ENMOEDU > shutdown immedaite;
SP2-0717: illegal SHUTDOWN option
SYS@ENMOEDU > shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ENMOEDU > startup
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1345380 bytes
Variable Size 343935132 bytes
Database Buffers 71303168 bytes
Redo Buffers 6086656 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 3777
Session ID: 125 Serial number: 5
2）檢視alter日誌
Tue Feb 25 22:42:59 2014
ARC3 started with pid=24, OS id=5077
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Errors in file /u01/app/oracle/diag/rdbms/enmoedu/ENMOEDU/trace/ENMOEDU_lgwr_5036.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/ENMOEDU/redo01b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/ENMOEDU/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/enmoedu/ENMOEDU/trace/ENMOEDU_lgwr_5036.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/ENMOEDU/redo01b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/ENMOEDU/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/enmoedu/ENMOEDU/trace/ENMOEDU_ora_5069.trc:
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/ENMOEDU/redo01.log'
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/ENMOEDU/redo01b.log'
USER (ospid: 5069): terminating the instance due to error 313
System state dump requested by (instance=1, osid=5069), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/enmoedu/ENMOEDU/trace/ENMOEDU_diag_5026.trc
Dumping diagnostic data in directory=[cdmp_20140225224300], requested by (instance=1, osid=5069), summary=[abnormal instance termination].
Instance terminated by USER, pid = 5069
3）由於日誌組1已經被歸檔，狀態是Inactive，這個日誌內容在instance racovery時不需要，這種日誌可以簡單的清空或者刪除都可以。
在mount狀態下，清空日誌組，在開啟資料庫就行。
[oracle@ENMOEDU ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Feb 25 22:45:26 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SYS@ENMOEDU > startup mount
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1345380 bytes
Variable Size 339740828 bytes
Database Buffers 75497472 bytes
Redo Buffers 6086656 bytes
Database mounted.
SYS@ENMOEDU > alter database clear logfile group 1;
Database altered.
SYS@ENMOEDU > alter database open;
Database altered.
也可以把損壞的日誌組，直接刪除，再重建（如果當前一共兩個日誌組，一定要先建一個日誌組，再刪除）。
SYS@ENMOEDU > alter database drop logfile group 1;
Database altered.
SYS@ENMOEDU > alter database add logfile group 3('/u01/app/oracle/oradata/ENMOEDU/redo03.log','/u01/app/oracle/oradata/ENMOEDU/redo03b.log') size 50M;
Database altered.
2.Active狀態說明這個日誌記錄的修改的資料還沒有記錄到資料檔案，Current是資料庫當前正在使用的日誌。但是如果資料庫中還有未決的事務，需要使用這些日誌進行恢復，這是就會造成資料的丟失，所進行的就是不完全恢復。
1）模擬錯誤，刪除CURRENT狀態下的日誌組
SYS@ENMOEDU > select GROUP#,ARCHIVED,STATUS from v$log;
GROUP# ARC STATUS
---------- --- ----------------
1 YES INACTIVE
2 NO CURRENT
4 YES INACTIVE
[oracle@ENMOEDU ENMOEDU]$ ls
control01.ctl he.dbf redo01.log redo02.log redo03.log redo04.log sample01.dbf system01.dbf undotbs01.dbf users011.dbf.bak
control03.ct redo01b.log redo02b.log redo03b.log redo04b.log rman01.dbf sysaux01.dbf temp01.dbf undotbs02.dbf users01.dbf
[oracle@ENMOEDU ENMOEDU]$ rm -rf redo02b.log
[oracle@ENMOEDU ENMOEDU]$ rm -rf redo02.log
SYS@ENMOEDU > shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ENMOEDU > startup
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1345380 bytes
Variable Size 339740828 bytes
Database Buffers 75497472 bytes
Redo Buffers 6086656 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 5714
Session ID: 125 Serial number: 5
2）檢視Alter日誌
Tue Feb 25 23:01:51 2014
ARC3 started with pid=24, OS id=5722
ARC1: Archival started
ARC2: Archival started
ARC2: Becoming the 'no FAL' ARCH
ARC2: Becoming the 'no SRL' ARCH
ARC1: Becoming the heartbeat ARCH
Errors in file /u01/app/oracle/diag/rdbms/enmoedu/ENMOEDU/trace/ENMOEDU_lgwr_5682.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ENMOEDU/redo02b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ENMOEDU/redo02.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/enmoedu/ENMOEDU/trace/ENMOEDU_lgwr_5682.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ENMOEDU/redo02b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ENMOEDU/redo02.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/enmoedu/ENMOEDU/trace/ENMOEDU_ora_5714.trc:
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ENMOEDU/redo02.log'
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/ENMOEDU/redo02b.log'
USER (ospid: 5714): terminating the instance due to error 313
System state dump requested by (instance=1, osid=5714), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/enmoedu/ENMOEDU/trace/ENMOEDU_diag_5672.trc
Dumping diagnostic data in directory=[cdmp_20140225230152], requested by (instance=1, osid=5714), summary=[abnormal instance termination].
Instance terminated by USER, pid = 5714
3）資料庫進行恢復
[oracle@ENMOEDU ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Feb 25 23:04:30 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SYS@ENMOEDU > startup mount
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1345380 bytes
Variable Size 339740828 bytes
Database Buffers 75497472 bytes
Redo Buffers 6086656 bytes
Database mounted.
4）執行alter database open resetlogs;會看到有報錯，需要進行不完全恢復
SYS@ENMOEDU > alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
5）對資料庫進行不完全恢復，以resetlogs的方式開啟資料庫
SYS@ENMOEDU > recover database until cancel;
Media recovery complete.
SYS@ENMOEDU > alter database open resetlogs;
Database altered.
總結：如果是current狀態的檔案丟失，例項在mount狀態下，執行‘alter database open resetlogs’命令；如果需要，進行一次完全恢復，啟動資料庫。如果是inacrtive狀態的日誌損壞，把該日誌組drop和clear都可以。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29435844/viewspace-1090316/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle控制檔案的損壞或完全丟失的恢復辦法Oracle
- Oracle資料庫不同損壞級別的恢復詳解Oracle資料庫
- Oracle 控制檔案損壞解決方案Oracle
- Oracle使用備份檔案集恢復歸檔日誌Oracle
- u盤檔案損壞怎麼恢復資料 u盤恢復損壞資料的有效方法
- 學習這篇Oracle資料庫檔案壞塊損壞的恢復方法,擴充你的知識面Oracle資料庫
- Oracle asm磁碟損壞異常恢復OracleASM
- 【RMAN】如果控制檔案損壞那麼如何恢復？恢復控制檔案的方式有哪幾種？
- oracle dg 歸檔日誌恢復情況Oracle
- 探究MySQL中的日誌檔案MySql
- 【Oracle】如何修改資料檔案和日誌檔案的路徑Oracle
- 2.7.10 恢復丟失或損壞的伺服器引數檔案(SPFILE)伺服器
- 在Oracle中，如何定時刪除歸檔日誌檔案？Oracle
- u盤檔案損壞怎麼恢復資料 u盤損壞無法讀取怎麼恢復資料
- 控制檔案損壞處理
- 當前日誌損壞的案例(轉)
- 【資料庫資料恢復】MongoDB資料庫檔案損壞的資料恢復案例資料庫資料恢復MongoDB
- Oracle 無備份情況下undo檔案損壞處理Oracle
- oracle歸檔日誌Oracle
- PostgreSQL DBA(30) - Backup&Recovery#3(資料檔案損壞恢復)SQL
- ibdata1檔案損壞時恢復InnoDB單表測試
- 【儲存資料恢復】IBM儲存檔案NTFS系統損壞的資料恢復案例資料恢復IBM
- Spring按業務模組輸出日誌到不同的檔案Spring
- InterBase資料庫檔案損壞的修復方法資料庫
- 【伺服器資料恢復】伺服器reiserfs檔案系統損壞的資料恢復案例伺服器資料恢復
- Linux中監視日誌檔案常用的命令！Linux
- 在Linux中，有一堆日誌檔案，如何刪除7天前的日誌檔案？Linux
- 教你自動恢復MySQL資料庫的日誌檔案(binlog)MySql資料庫
- 歸檔oracle alert日誌Oracle
- Oracle歸檔日誌清理Oracle
- 記一次Oracle RAC for aix 儲存雙控鎖盤導致ASM控制檔案損壞恢復OracleAIASM
- go開發屬於自己的日誌庫-檔案日誌庫實現Go
- 分析Oracle資料庫日誌檔案（三）EPOracle資料庫
- 分析Oracle資料庫日誌檔案（二）DOOracle資料庫
- 分析Oracle資料庫日誌檔案（一）HBOracle資料庫
- oracle 刪除過期的歸檔日誌Oracle
- Oracle單個資料檔案損壞，在Rman命令裡設定表空間、資料檔案offline方式來恢復最方便Oracle
- mysql的日誌檔案詳解MySql
- Vsan分散式檔案系統邏輯架構損壞恢復過程分散式架構