Oracle資料庫聯機日誌檔案丟失處理方法(5)

試驗五：丟失當前日誌組的成員1、SQL>； select * from v$logfile；

GROUP# STATUS TYPE

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

MEMBER

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

3 ONLINE /T3/ORACLE/oradata/ORA9/redo03.log

2 ONLINE /T3/ORACLE/oradata/ORA9/redo02.log

1 ONLINE /T3/ORACLE/oradata/ORA9/redo01.log

GROUP# STATUS TYPE

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

MEMBER

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

1 ONLINE /T3/ORACLE/oradata/ORA9/redo01a.log

2 ONLINE /T3/ORACLE/oradata/ORA9/redo02a.log

3 ONLINE /T3/ORACLE/oradata/ORA9/redo03a.log

SQL>； select * from v$log；

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS

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

FIRST_CHANGE# FIRST_TIME

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

1 1 2 104857600 2 YES INACTIVE 554599 02-9月 -05

2 1 3 104857600 2 YES INACTIVE 554601 02-9月 -05

3 1 4 104857600 2 NO CURRENT 554603 02-9月 -05 SQL>；3、 模擬插入資料SQL>； conn test/test Connected. SQL>； select * from test；


TEL

----------

1 2 3 4

SQL>； insert into test values（5）；

1 row created.

SQL>； commit 2 ；

Commit complete. 4、shutdown db，模擬刪除一個當前日誌成員$ cd oradata/ORA9 $ ls redo03* redo03.log redo03a.log $ rm redo03a.log 5、啟動db，表面沒有錯誤SQL>； startup ORACLE instance started.

Total System Global Area 353862792 bytes Fixed Size 730248 bytes Variable Size 285212672 bytes Database Buffers 67108864 bytes Redo Buffers 811008 bytes Database mounted. Database opened. SQL>；6、 檢視日至成員SQL>； select * from v$logfile；

GROUP# STATUS TYPE

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

MEMBER

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

3 ONLINE /T3/ORACLE/oradata/ORA9/redo03.log

2 ONLINE /T3/ORACLE/oradata/ORA9/redo02.log

1 ONLINE /T3/ORACLE/oradata/ORA9/redo01.log

GROUP# STATUS TYPE

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

MEMBER

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

1 ONLINE /T3/ORACLE/oradata/ORA9/redo01a.log

2 ONLINE /T3/ORACLE/oradata/ORA9/redo02a.log

3 INVALID ONLINE /T3/ORACLE/oradata/ORA9/redo03a.log 7、刪除出問題的聯機日誌檔案SQL>； alter database drop logfile member '/T3/ORACLE/oradata/ORA9/redo03a.log'；alter database drop logfile member '/T3/ORACLE/oradata/ORA9/redo03a.log' * ERROR at line 1：ORA-01609： log 3 is the current log for thread 1 - cannot drop members ORA-00312： online log 3 thread 1： '/T3/ORACLE/oradata/ORA9/redo03.log' ORA-00312： online log 3 thread 1： '/T3/ORACLE/oradata/ORA9/redo03a.log'

SQL>；看來當前日誌成員是不允許刪除的SQL>； alter system switch logfile；

System altered.

SQL>； select * from v$log；

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS

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

FIRST_CHANGE# FIRST_TIME

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

1 1 5 104857600 2 NO CURRENT 557687 02-9月 -05

2 1 3 104857600 2 YES INACTIVE 554601 02-9月 -05

3 1 4 104857600 2 YES ACTIVE 554603 02-9月 -05

SQL>； alter database drop logfile member '/T3/ORACLE/oradata/ORA9/redo03a.log'；

Database altered.

SQL>； alter database add logfile member '/T3/ORACLE/oradata/ORA9/redo03a.log' to group 3；


Database altered. SQL>； select * from v$logfile；

GROUP# STATUS TYPE

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

MEMBER

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

3 ONLINE /T3/ORACLE/oradata/ORA9/redo03.log

2 ONLINE /T3/ORACLE/oradata/ORA9/redo02.log

1 ONLINE /T3/ORACLE/oradata/ORA9/redo01.log

GROUP# STATUS TYPE

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

MEMBER

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

1 ONLINE /T3/ORACLE/oradata/ORA9/redo01a.log

2 ONLINE /T3/ORACLE/oradata/ORA9/redo02a.log

3 INVALID ONLINE /T3/ORACLE/oradata/ORA9/redo03a.log看來還得切換一下日至SQL>； alter system switch logfile；

System altered. SQL>； select * from v$logfile；

GROUP# STATUS TYPE

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

MEMBER

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

3 ONLINE /T3/ORACLE/oradata/ORA9/redo03.log

2 ONLINE /T3/ORACLE/oradata/ORA9/redo02.log

1 ONLINE /T3/ORACLE/oradata/ORA9/redo01.log

GROUP# STATUS TYPE

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

MEMBER

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

1 ONLINE /T3/ORACLE/oradata/ORA9/redo01a.log

2 ONLINE /T3/ORACLE/oradata/ORA9/redo02a.log

3 ONLINE /T3/ORACLE/oradata/ORA9/redo03a.log

至此，大功告成……………。

結論：

　只要日誌組的member不是一個，出現前面的4種可能性是非常小的，即使出現了也有相應的恢復方法，所以不必驚慌；如果memer多於1個，即使壞了其中的幾個，也不會 影響資料庫的正常啟動，啟動後，再進行相應的操作即可， 所以這個時候每天察看alert.log就顯得非常重要了。[@more@]