inactive狀態日誌組檔案損壞的恢復
1：丟失inactive狀態的日誌檔案
如果資料庫丟失的是inactive日誌組，由於inactive狀態的日誌組已經完成檢查點，
資料庫不會發生資料丟失，此時只需要透過clear重建該日誌組即可
資料庫不會發生資料丟失，此時只需要透過clear重建該日誌組即可
SQL> select group#,sequence#,status,first_change#,next_change# from v$log;
GROUP# SEQUENCE# STATUS FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ---------------- ------------- ------------
1 45 CURRENT 879651 2.8147E+14
2 44 ACTIVE 879534 879651
3 43 INACTIVE 879527 879534
SQL> col member for a50;
SQL> select * from v$logfile;
---------- ---------- ---------------- ------------- ------------
1 45 CURRENT 879651 2.8147E+14
2 44 ACTIVE 879534 879651
3 43 INACTIVE 879527 879534
SQL> col member for a50;
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
3 ONLINE /home/oracle/oradata/ora11g/redo03.log NO
2 ONLINE /home/oracle/oradata/ora11g/redo02.log NO
1 ONLINE /home/oracle/oradata/ora11g/redo01.log NO
1 ONLINE /home/oracle/oradata/ora11g/redo01a.log NO
2 ONLINE /home/oracle/oradata/ora11g/redo02a.log NO
3 ONLINE /home/oracle/oradata/ora11g/redo03a.log NO
---------- ------- ------- -------------------------------------------------- ---
3 ONLINE /home/oracle/oradata/ora11g/redo03.log NO
2 ONLINE /home/oracle/oradata/ora11g/redo02.log NO
1 ONLINE /home/oracle/oradata/ora11g/redo01.log NO
1 ONLINE /home/oracle/oradata/ora11g/redo01a.log NO
2 ONLINE /home/oracle/oradata/ora11g/redo02a.log NO
3 ONLINE /home/oracle/oradata/ora11g/redo03a.log NO
6 rows selected.
2：將原來的日誌組3的兩個檔案做備份，然後刪除
2：將原來的日誌組3的兩個檔案做備份，然後刪除
SQL> !cp /home/oracle/oradata/ora11g/redo03.log /home/oracle/oradata/ora11g/redo03.log.bak
SQL> !cp /home/oracle/oradata/ora11g/redo03a.log /home/oracle/oradata/ora11g/redo03a.log.bak
SQL> !rm /home/oracle/oradata/ora11g/redo03.log
SQL> !rm /home/oracle/oradata/ora11g/redo03a.log
SQL>
3：如果資料庫發生多次日誌切換，使用到該日誌組3時候，則資料庫可能馬上崩潰
SQL> select group#,sequence#,status,first_change#,next_change# from v$log;
GROUP# SEQUENCE# STATUS FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ---------------- ------------- ------------
1 45 CURRENT 879651 2.8147E+14
2 44 ACTIVE 879534 879651
3 43 INACTIVE 879527 879534
---------- ---------- ---------------- ------------- ------------
1 45 CURRENT 879651 2.8147E+14
2 44 ACTIVE 879534 879651
3 43 INACTIVE 879527 879534
SQL>
SQL> alter system switch logfile;
SQL> alter system switch logfile;
System altered.
SQL> select group#,sequence#,status,first_change#,next_change# from v$log;
GROUP# SEQUENCE# STATUS FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ---------------- ------------- ------------
1 45 ACTIVE 879651 879780
2 44 INACTIVE 879534 879651
3 46 CURRENT 879780 2.8147E+14
---------- ---------- ---------------- ------------- ------------
1 45 ACTIVE 879651 879780
2 44 INACTIVE 879534 879651
3 46 CURRENT 879780 2.8147E+14
SQL> alter system switch logfile;
System altered.
SQL> select group#,sequence#,status,first_change#,next_change# from v$log;
GROUP# SEQUENCE# STATUS FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ---------------- ------------- ------------
1 45 ACTIVE 879651 879780
2 47 CURRENT 879812 2.8147E+14
3 46 ACTIVE 879780 879812
---------- ---------- ---------------- ------------- ------------
1 45 ACTIVE 879651 879780
2 47 CURRENT 879812 2.8147E+14
3 46 ACTIVE 879780 879812
SQL> select group#,sequence#,status,first_change#,next_change# from v$log;
GROUP# SEQUENCE# STATUS FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ---------------- ------------- ------------
1 45 ACTIVE 879651 879780
2 47 CURRENT 879812 2.8147E+14
3 46 ACTIVE 879780 879812
---------- ---------- ---------------- ------------- ------------
1 45 ACTIVE 879651 879780
2 47 CURRENT 879812 2.8147E+14
3 46 ACTIVE 879780 879812
SQL> alter system switch logfile;
System altered.
SQL> select group#,sequence#,status,first_change#,next_change# from v$log;
GROUP# SEQUENCE# STATUS FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ---------------- ------------- ------------
1 48 CURRENT 879848 2.8147E+14
2 47 INACTIVE 879812 879848
3 46 INACTIVE 879780 879812
---------- ---------- ---------------- ------------- ------------
1 48 CURRENT 879848 2.8147E+14
2 47 INACTIVE 879812 879848
3 46 INACTIVE 879780 879812
SQL> alter system switch logfile;
（一直沒有反應，資料庫hang住了）
（一直沒有反應，資料庫hang住了）
在進行多次切換之後，資料庫hang住了
檢視alter.log檔案（v$diag_info檢視中/home/oracle/product/diag/rdbms/ora11g/ora11g/trace）
有如下詳細資訊
有如下詳細資訊
Sat Apr 27 15:52:58 2013
Errors in file /home/oracle/product/diag/rdbms/ora11g/ora11g/trace/ora11g_arc0_907.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/home/oracle/oradata/ora11g/redo03a.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: '/home/oracle/oradata/ora11g/redo03.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Errors in file /home/oracle/product/diag/rdbms/ora11g/ora11g/trace/ora11g_arc0_907.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/home/oracle/oradata/ora11g/redo03a.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: '/home/oracle/oradata/ora11g/redo03.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
4：另開一個會話，關閉db，然後啟動db，啟動的過程中會報錯
[oracle@sp ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sat Apr 27 11:08:07 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL>
SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.
SQL>
SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 1322467328 bytes
Fixed Size 1336316 bytes
Variable Size 452987908 bytes
Database Buffers 855638016 bytes
Redo Buffers 12505088 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 741
Session ID: 1 Serial number: 5
Fixed Size 1336316 bytes
Variable Size 452987908 bytes
Database Buffers 855638016 bytes
Redo Buffers 12505088 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 741
Session ID: 1 Serial number: 5
SQL>
5：首先啟動到mount狀態
SQL> startup mount;
ORACLE instance started.
ORACLE instance started.
Total System Global Area 1322467328 bytes
Fixed Size 1336316 bytes
Variable Size 452987908 bytes
Database Buffers 855638016 bytes
Redo Buffers 12505088 bytes
Database mounted.
Fixed Size 1336316 bytes
Variable Size 452987908 bytes
Database Buffers 855638016 bytes
Redo Buffers 12505088 bytes
Database mounted.
檢視log組的狀態
SQL> select group#,sequence#,status,first_change#,next_change# from v$log;
GROUP# SEQUENCE# STATUS FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ---------------- ------------- ------------
1 48 CURRENT 879848 2.8147E+14
3 46 INACTIVE 879780 879812
2 47 INACTIVE 879812 879848
---------- ---------- ---------------- ------------- ------------
1 48 CURRENT 879848 2.8147E+14
3 46 INACTIVE 879780 879812
2 47 INACTIVE 879812 879848
SQL> col member for a50;
SQL> set linesize 200;
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
3 ONLINE /home/oracle/oradata/ora11g/redo03.log NO
2 ONLINE /home/oracle/oradata/ora11g/redo02.log NO
1 ONLINE /home/oracle/oradata/ora11g/redo01.log NO
1 ONLINE /home/oracle/oradata/ora11g/redo01a.log NO
2 ONLINE /home/oracle/oradata/ora11g/redo02a.log NO
3 ONLINE /home/oracle/oradata/ora11g/redo03a.log NO
---------- ------- ------- -------------------------------------------------- ---
3 ONLINE /home/oracle/oradata/ora11g/redo03.log NO
2 ONLINE /home/oracle/oradata/ora11g/redo02.log NO
1 ONLINE /home/oracle/oradata/ora11g/redo01.log NO
1 ONLINE /home/oracle/oradata/ora11g/redo01a.log NO
2 ONLINE /home/oracle/oradata/ora11g/redo02a.log NO
3 ONLINE /home/oracle/oradata/ora11g/redo03a.log NO
6 rows selected.
SQL>
手動刪除損壞的日誌組3，報錯
SQL> alter database clear logfile group 3;
alter database clear logfile group 3
*
ERROR at line 1:
ORA-00350: log 3 of instance ora11g (thread 1) needs to be archived
ORA-00312: online log 3 thread 1: '/home/oracle/oradata/ora11g/redo03.log'
ORA-00312: online log 3 thread 1: '/home/oracle/oradata/ora11g/redo03a.log'
如果在歸檔模式下，並且損壞的日誌組還沒有完成歸檔，則需要使用clear unarchived 命令強制刪除
SQL> alter database clear unarchived logfile group 3;
Database altered.
（執行完上述命令之後，在/home/oracle/oradata/ora11g/目錄下
又重新自動建立了兩個日誌檔案redo03a.log、redo03.log）
又重新自動建立了兩個日誌檔案redo03a.log、redo03.log）
開啟db
SQL>
SQL> alter database open;
Database altered.
SQL> select group#,sequence#,status,first_change#,next_change# from v$log;
GROUP# SEQUENCE# STATUS FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ---------------- ------------- ------------
1 48 CURRENT 879848 2.8147E+14
2 47 INACTIVE 879812 879848
3 0 UNUSED 879780 879812
---------- ---------- ---------------- ------------- ------------
1 48 CURRENT 879848 2.8147E+14
2 47 INACTIVE 879812 879848
3 0 UNUSED 879780 879812
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
3 ONLINE /home/oracle/oradata/ora11g/redo03.log NO
2 ONLINE /home/oracle/oradata/ora11g/redo02.log NO
1 ONLINE /home/oracle/oradata/ora11g/redo01.log NO
1 ONLINE /home/oracle/oradata/ora11g/redo01a.log NO
2 ONLINE /home/oracle/oradata/ora11g/redo02a.log NO
3 ONLINE /home/oracle/oradata/ora11g/redo03a.log NO
---------- ------- ------- -------------------------------------------------- ---
3 ONLINE /home/oracle/oradata/ora11g/redo03.log NO
2 ONLINE /home/oracle/oradata/ora11g/redo02.log NO
1 ONLINE /home/oracle/oradata/ora11g/redo01.log NO
1 ONLINE /home/oracle/oradata/ora11g/redo01a.log NO
2 ONLINE /home/oracle/oradata/ora11g/redo02a.log NO
3 ONLINE /home/oracle/oradata/ora11g/redo03a.log NO
6 rows selected.
再次切換日誌組
SQL> alter system switch logfile;
System altered.
SQL> select group#,sequence#,status,first_change#,next_change# from v$log;
GROUP# SEQUENCE# STATUS FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ---------------- ------------- ------------
1 48 ACTIVE 879848 880139
2 47 INACTIVE 879812 879848
3 49 CURRENT 880139 2.8147E+14
---------- ---------- ---------------- ------------- ------------
1 48 ACTIVE 879848 880139
2 47 INACTIVE 879812 879848
3 49 CURRENT 880139 2.8147E+14
SQL> alter system switch logfile;
System altered.
SQL> select group#,sequence#,status,first_change#,next_change# from v$log;
GROUP# SEQUENCE# STATUS FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ---------------- ------------- ------------
1 48 ACTIVE 879848 880139
2 50 CURRENT 880145 2.8147E+14
3 49 ACTIVE 880139 880145
---------- ---------- ---------------- ------------- ------------
1 48 ACTIVE 879848 880139
2 50 CURRENT 880145 2.8147E+14
3 49 ACTIVE 880139 880145
SQL> alter system switch logfile;
System altered.
SQL> select group#,sequence#,status,first_change#,next_change# from v$log;
GROUP# SEQUENCE# STATUS FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ---------------- ------------- ------------
1 51 CURRENT 880150 2.8147E+14
2 50 ACTIVE 880145 880150
3 49 INACTIVE 880139 880145
---------- ---------- ---------------- ------------- ------------
1 51 CURRENT 880150 2.8147E+14
2 50 ACTIVE 880145 880150
3 49 INACTIVE 880139 880145
SQL> alter system switch logfile;
System altered.
SQL> select group#,sequence#,status,first_change#,next_change# from v$log;
GROUP# SEQUENCE# STATUS FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ---------------- ------------- ------------
1 51 ACTIVE 880150 880153
2 50 INACTIVE 880145 880150
3 52 CURRENT 880153 2.8147E+14
---------- ---------- ---------------- ------------- ------------
1 51 ACTIVE 880150 880153
2 50 INACTIVE 880145 880150
3 52 CURRENT 880153 2.8147E+14
馬上對資料庫做一次全備份
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28373936/viewspace-1722261/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- INACTIVE日誌組損壞的修復
- 【備份恢復】恢復inactive狀態的日誌檔案
- 一次日誌檔案損壞的恢復
- 線上日誌檔案損壞恢復方法
- REDO日誌損壞,非歸檔模式資料檔案恢復模式
- 損壞聯機日誌 恢復
- 聯機日誌檔案損壞後的恢復方法[轉帖]
- Oracle重做日誌檔案損壞或丟失後的恢復Oracle
- 當前日誌組全部損壞的恢復
- 損壞聯機日誌的恢復方法
- 聯機日誌損壞恢復實驗
- oracle丟失inactive日誌檔案的恢復操作過程Oracle
- 【原創】模擬狀態為active的日誌損壞的資料恢復實驗（不完全恢復）資料恢復
- 損壞控制檔案的恢復方法
- 16、重做日誌檔案的狀態及重做日誌組的狀態說明
- 單個控制檔案損壞的恢復
- Oracle資料庫恢復：歸檔日誌損壞案例一則Oracle資料庫
- SQL Server ldf 檔案損壞恢復SQLServer
- 重做日誌檔案損壞測試
- Sql server日誌損壞後的資料恢復(轉)SQLServer資料恢復
- 【BBED】 SYSTEM檔案頭損壞的恢復(4)
- 資料檔案丟失損壞的恢復--
- 某個控制檔案損壞的恢復案例
- REDO檔案丟失或者損壞的恢復
- UNDO 表空間檔案損壞的恢復
- 一次控制檔案損壞的恢復
- 恢復之單個控制檔案損壞
- 聯機日誌檔案損壞問題
- 當前聯機日誌檔案損壞
- Oracle恢復例項之二：Inactive聯機日誌檔案丟失Oracle
- Oracle 11g 資料庫恢復-場景5：部分檔案損壞恢復,開庫狀態,低可用恢復方式Oracle資料庫
- Oracle 11g 資料庫恢復-場景4：部分檔案損壞恢復,開庫狀態, 高可用恢復方式Oracle資料庫
- Oralce 11g資料庫恢復-場景3：部分檔案損壞恢復,關庫狀態,高可用恢復方式資料庫
- Oracle中的聯機日誌檔案發生不同程度損壞的恢Oracle
- 狀態為inactive的日誌一定歸檔了嗎？
- 備份與恢復--重建控制檔案後資料檔案損壞的恢復
- 【備份與恢復】恢復受損的複用聯機重做日誌檔案
- Redo Log File（inactive、active）損壞，處理恢復對策