模擬線上重做日誌被刪除的情況恢復

SQL> alter database add logfile group 4 ('e:redo4.log') size 10M;

資料庫已更改。

SQL> select * from v$log

2 ;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS

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

1 1 165 10240000 1 NO CURRENT

2 1 162 10240000 1 YES INACTIVE

3 1 164 10240000 1 YES INACTIVE

4 1 0 10485760 1 YES UNUSED

SQL> host

Microsoft Windows XP [版本 5.1.2600]

(C) 版權所有 1985-2001 Microsoft Corp.

E:>del redo4.log

E:>exit

SQL> alter system switch logfile;

alter system switch logfile

*

第 1 行出現錯誤:

ORA-00313: 無法開啟日誌組 (用於執行緒 ) 的成員

SQL> conn /as sysdba

已連線到空閒例程。

Wed May 09 10:33:21 2007

Errors in file e:oracleadminora10bdumpora10_lgwr_1608.trc:

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

ORA-00312: online log 4 thread 1: 'E:REDO4.LOG'

ORA-27041: unable to open file

OSD-04002: 無法開啟檔案

O/S-Error: (OS 2) 系統找不到指定的檔案。

Wed May 09 10:33:21 2007

Errors in file e:oracleadminora10bdumpora10_lgwr_1608.trc:

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

ORA-00312: online log 4 thread 1: 'E:REDO4.LOG'

ORA-27041: unable to open file

OSD-04002: 無法開啟檔案

O/S-Error: (OS 2) 系統找不到指定的檔案。

Wed May 09 10:33:21 2007

LGWR: terminating instance due to error 313

Wed May 09 10:33:21 2007

Errors in file e:oracleadminora10bdumpora10_q001_424.trc:

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

Wed May 09 10:33:21 2007

Errors in file e:oracleadminora10bdumpora10_pmon_1104.trc:

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

Wed May 09 10:33:22 2007

Errors in file e:oracleadminora10bdumpora10_dbw0_3036.trc:

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

Wed May 09 10:33:23 2007

Errors in file e:oracleadminora10bdumpora10_reco_2184.trc:

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

Wed May 09 10:33:23 2007

Errors in file e:oracleadminora10bdumpora10_ckpt_2980.trc:

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

Wed May 09 10:33:23 2007

Errors in file e:oracleadminora10bdumpora10_smon_2576.trc:

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

Wed May 09 10:33:23 2007

Errors in file e:oracleadminora10bdumpora10_psp0_2872.trc:

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

Wed May 09 10:33:23 2007

Errors in file e:oracleadminora10bdumpora10_mman_1980.trc:

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

Instance terminated by LGWR, pid = 1608

SQL> startup

ORACLE 例程已經啟動。

Total System Global Area 159383552 bytes

Fixed Size 1247876 bytes

Variable Size 79693180 bytes

Database Buffers 75497472 bytes

Redo Buffers 2945024 bytes

資料庫裝載完畢。

ORA-00313: 無法開啟日誌組 4 (用於執行緒 1) 的成員

ORA-00312: 聯機日誌 4 執行緒 1: 'E:REDO4.LOG'

SQL> alter database drop logfile group 4;

資料庫已更改。

SQL> alter database open;

資料庫已更改。

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS

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

1 1 165 10240000 1 YES INACTIVE

2 1 166 10240000 1 NO CURRENT

3 1 164 10240000 1 YES INACTIVE

SQL> alter database clear logfile group 4;

資料庫已更改。