【備份恢復】恢復inactive狀態的日誌檔案

恢復重做日誌

重做日誌有以下幾種狀態（主要看前三種），如下：

CURRENT：此狀態表示正在被LGWR使用的日誌組；

ACTIVE：重做日誌組中仍含有例項恢復所需的重做資料；

INACTIVE：表示例項恢復不再需要此日誌組，可以覆蓋；

UNUSED：未使用；

CLEARING：對已存在的日誌組執行clear操作的後的狀態；

CLEARING_CURRENT：日誌正在清空。當清空出錯時，該日誌組被置於這種狀態。

一般情況下，我們只能查詢到如下三種狀態：

SYS@ORA11GR2>select group#,members,status from v$log;

 

    GROUP#    MEMBERS STATUS

---------- ---------- ----------------

         1          2 CURRENT

         2          2 INACTIVE

         3          2 ACTIVE

 

1.2.1丟失INACTIVE狀態日誌檔案

一、資料庫聯機的情況下，刪除INACTIVE日誌組進行恢復

1.檢視當前日誌組狀態

SYS@ORA11GR2>select group#,members,status from v$log;

 

    GROUP#    MEMBERS STATUS

---------- ---------- ----------------

         1          2 INACTIVE

         2          2 INACTIVE

         3          2 CURRENT

 

SYS@ORA11GR2>select group#,member from v$logfile order by 1;

 

    GROUP# MEMBER

---------- ---------------------------------------------

         1 /u01/app/oracle/oradata/ORA11GR2/redo01.log

         1 /u01/app/oracle/oradata/ORA11GR2/redo01_a.log

         2 /u01/app/oracle/oradata/ORA11GR2/redo02_a.log

         2 /u01/app/oracle/oradata/ORA11GR2/redo02.log

         3 /u01/app/oracle/oradata/ORA11GR2/redo03_a.log

         3 /u01/app/oracle/oradata/ORA11GR2/redo03.log

 

6 rows selected.

 

2.作業系統層刪除第一組日誌組（第一組為inactive）

——先檢視：

SYS@ORA11GR2>!ls /u01/app/oracle/oradata/ORA11GR2/*.log

/u01/app/oracle/oradata/ORA11GR2/redo01_a.log

/u01/app/oracle/oradata/ORA11GR2/redo01.log

/u01/app/oracle/oradata/ORA11GR2/redo02_a.log

/u01/app/oracle/oradata/ORA11GR2/redo02.log

/u01/app/oracle/oradata/ORA11GR2/redo03_a.log

/u01/app/oracle/oradata/ORA11GR2/redo03.log

 

——執行刪除：

SYS@ORA11GR2>!rm /u01/app/oracle/oradata/ORA11GR2/redo01*

 

——驗證：

SYS@ORA11GR2>!ls /u01/app/oracle/oradata/ORA11GR2/*.log

/u01/app/oracle/oradata/ORA11GR2/redo02_a.log

/u01/app/oracle/oradata/ORA11GR2/redo02.log

/u01/app/oracle/oradata/ORA11GR2/redo03_a.log

/u01/app/oracle/oradata/ORA11GR2/redo03.log

 

3. 發現丟失後，沒等資料庫反應過來呢，就立即恢復

SYS@ORA11GR2>alter database drop logfile group 1;

 

Database altered.

（作業系統層的第一組日誌檔案已經刪除了，且第一組日誌狀態為inactive表示已經歸檔完成，而且沒有在進行例項恢復，因為已經歸檔了，所以可以刪除第一組日誌，假如其他事物等要恢復的話可以用歸檔日誌裡關於該組的資訊；沒開歸檔則就不完全恢復）

SYS@ORA11GR2>select group#,members,status,bytes/1024/1024 m from v$log;

 

    GROUP#    MEMBERS STATUS                    M

---------- ---------- ---------------- ----------

         2          2 INACTIVE                 50

         3          2 CURRENT                  50

 

SYS@ORA11GR2>alter database add logfile group 1('/u01/app/oracle/oradata/ORA11GR2/redo01_a.log','/u01/app/oracle/oradata/ORA11GR2/redo01.log') size 50m;

 

Database altered.

 

SYS@ORA11GR2>select group#,members,status,bytes/1024/1024 m from v$log;

 

    GROUP#    MEMBERS STATUS                    M

---------- ---------- ---------------- ----------

         1          2 UNUSED                   50

         2          2 INACTIVE                 50

         3          2 CURRENT                  50

 

SYS@ORA11GR2>

4.恢復完成

SYS@ORA11GR2>alter system switch logfile;

 

System altered.

 

SYS@ORA11GR2>select group#,members,status from v$log;

 

    GROUP#    MEMBERS STATUS

---------- ---------- ----------------

         1          2 CURRENT

         2          2 INACTIVE

         3          2 ACTIVE

 

———————————————————————————————————————

自測：資料庫處於歸檔模式，資料庫聯機的情況下，丟失了一個INACTIVE狀態的日誌檔案，此時資料庫還在使用，讀者可以模擬建表、插入資料，切換日誌，當然也可以切換到丟失的日誌組，多切換幾次，此時資料庫會hang住，原因是日誌無法歸檔，

———————————————————————————————————————

 

二、刪除INACTIVE日誌組，重啟資料庫後恢復

1.當前日誌組狀態

SYS@ORA11GR2>select group#,members,status from v$log

      GROUP#    MEMBERS STATUS
          ---------- ---------- ----------------
         1          2 CURRENT
          2          2 INACTIVE
          3          2 INACTIVE

2.刪除第3組日誌

——檢視：

SYS@ORA11GR2>!ls /u01/app/oracle/oradata/ORA11GR2/*.log

/u01/app/oracle/oradata/ORA11GR2/redo01_a.log

/u01/app/oracle/oradata/ORA11GR2/redo01.log

/u01/app/oracle/oradata/ORA11GR2/redo02_a.log

/u01/app/oracle/oradata/ORA11GR2/redo02.log

/u01/app/oracle/oradata/ORA11GR2/redo03_a.log

/u01/app/oracle/oradata/ORA11GR2/redo03.log

 

——執行刪除：

SYS@ORA11GR2>!rm /u01/app/oracle/oradata/ORA11GR2/redo03*

 

——驗證：

SYS@ORA11GR2>!ls /u01/app/oracle/oradata/ORA11GR2/*.log

/u01/app/oracle/oradata/ORA11GR2/redo01_a.log

/u01/app/oracle/oradata/ORA11GR2/redo01.log

/u01/app/oracle/oradata/ORA11GR2/redo02_a.log

/u01/app/oracle/oradata/ORA11GR2/redo02.log

3.重啟資料庫

一致性關閉沒問題，啟動的時候，只能啟動到mount狀態（其實只是看著應該是mount狀態，其實資料庫目前的狀態shutdown狀態），open時報錯，並沒有明確的指明是因為丟失了日誌檔案導致的資料庫open不了（oracle10g中會明確的指明）

SYS@ORA11GR2>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@ORA11GR2>

SYS@ORA11GR2>startup

ORACLE instance started.

 

Total System Global Area  730714112 bytes

Fixed Size                  2256832 bytes

Variable Size             452984896 bytes

Database Buffers          272629760 bytes

Redo Buffers                2842624 bytes

Database mounted.

ORA-03113: end-of-file on communication channel

Process ID: 13210

Session ID: 1 Serial number: 5

 

4.由於open的錯誤看不出問題，首先看一下alert日誌，我們會發現如下錯誤，可以很清楚的看出來3號日誌組找不到了

[oracle@wang trace]$ pwd

/u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace

[oracle@wang trace]$

[oracle@wang trace]$

ORA-00313: open failed for members of log group 3 of thread 1                                             

ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/ORA11GR2/redo03_a.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/ORA11GR2/redo03.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

----------------------------------------------------------------------------------------------------

5.對3號日誌組執行clear操作，open資料庫

 

SYS@ORA11GR2>conn / as sysdba

Connected to an idle instance.

SYS@ORA11GR2>startup mount;

ORACLE instance started.

 

Total System Global Area  730714112 bytes

Fixed Size                  2256832 bytes

Variable Size             452984896 bytes

Database Buffers          272629760 bytes

Redo Buffers                2842624 bytes

Database mounted.

SYS@ORA11GR2>

SYS@ORA11GR2>alter database clear logfile group 3;

（clear的動作相當於將在作業系統層重建了第三組日記，且裡面沒有記錄，也就是沒有使用過unused）

Database altered.

 

SYS@ORA11GR2>alter database open;

 

Database altered.

 

6.檢視日誌組狀態

SYS@ORA11GR2>select group#,members,status from v$log;

 

    GROUP#    MEMBERS STATUS

---------- ---------- ----------------

         1          2 CURRENT

         2          2 INACTIVE

         3          2 UNUSED

 

7.檢視物理檔案，已恢復完成

SYS@ORA11GR2>!ls /u01/app/oracle/oradata/ORA11GR2/*.log

/u01/app/oracle/oradata/ORA11GR2/redo01_a.log

/u01/app/oracle/oradata/ORA11GR2/redo01.log

/u01/app/oracle/oradata/ORA11GR2/redo02_a.log

/u01/app/oracle/oradata/ORA11GR2/redo02.log

/u01/app/oracle/oradata/ORA11GR2/redo03_a.log

/u01/app/oracle/oradata/ORA11GR2/redo03.log

修復完成，啟庫alter database open;