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
- Oracle不同檔案丟失/損壞的恢復方法Oracle
- 一次日誌檔案損壞的恢復
- inactive狀態日誌組檔案損壞的恢復
- 聯機重做日誌檔案的恢復
- 備份＆恢復之十一：損壞當前聯機日誌
- 【備份與恢復】恢復受損的複用聯機重做日誌檔案
- REDO日誌損壞,非歸檔模式資料檔案恢復模式
- 備份＆恢復之十：損壞非當前聯機日誌
- 重做日誌檔案損壞測試
- 【備份與恢復】重建受損的聯機重做日誌檔案成員
- 線上日誌檔案損壞恢復方法
- 丟失聯機重做日誌檔案的恢復
- oracle 聯機重做日誌檔案Oracle
- Oracle資料庫恢復：歸檔日誌損壞案例一則Oracle資料庫
- 損壞控制檔案的恢復方法
- 單個控制檔案損壞的恢復
- Sql server日誌損壞後的資料恢復(轉)SQLServer資料恢復
- 【故障恢復】【驚魂】ORACLE聯機日誌檔案無故全部消失Oracle
- redo日誌損壞
- SQL Server 2005日誌檔案損壞的處理方法SQLServer
- Oracle恢復例項之一：資料檔案、控制檔案、聯機日誌丟失Oracle
- Oracle恢復例項之二：Inactive聯機日誌檔案丟失Oracle
- 【BBED】 SYSTEM檔案頭損壞的恢復(4)
- 資料檔案丟失損壞的恢復--
- 某個控制檔案損壞的恢復案例
- REDO檔案丟失或者損壞的恢復
- UNDO 表空間檔案損壞的恢復
- 一次控制檔案損壞的恢復