歸檔問題導緻的資料庫無法啟動

本地有一個小的環境，今天照例登上sqlplus，突然發現報了如下的錯誤。一看原來歸檔滿了。我記得前幾天做一個批量操作臨時把temp檔案resize了很大，限于本地空間有限。準備改回去。把多餘的歸檔删除了。

sqlplus n1/n1

....

ERROR:

ORA-00257: archiver error. Connect internal only, until freed.

這個時候sysdba的權限發揮作用了，它可以照常登入。然後開始做resize操作。

SQL> alter database tempfile '/u03/ora11g/oradata/TEST01/temp01.dbf' resize 100M;

但是等了好一會兒，一直沒有輸出。是不是Hang住了。

看來得删除歸檔檔案了。然後到歸檔目錄下嘗試删除，結果這個時候rm似乎不管用了，指令執行了，但是歸檔檔案還在那。我想是不是有系統級的問題了。

決定重新開機 ，重新開機以後，Mount狀态過後就很不留情的報了一個03113的錯誤。

SQL> startup

ORACLE instance started.

Total System Global Area  313159680 bytes

Fixed Size                  2227944 bytes

Variable Size             255852824 bytes

Database Buffers           50331648 bytes

Redo Buffers                4747264 bytes

Database mounted.

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

Process ID: 8220

Session ID: 237 Serial number: 5

檢視alert日志檔案。看到重做日志歸檔失敗。

ARC3 started with pid=23, OS id=8232

ARC1: Archival started

ARC2: Archival started

ARC2: Becoming the 'no FAL' ARCH

ARC2: Becoming the 'no SRL' ARCH

ARC1: Becoming the heartbeat ARCH

ARCH: Encountered disk I/O error 19502

ARCH: Closing local archive destination LOG_ARCHIVE_DEST_1: '/u03/ora11g/product/11.2.0/dbhome_1/dbs/arch1_216_837590339.dbf' (error 19502) (TEST01)

ARC2: Encountered disk I/O error 19502

ARC2: Closing local archive destination LOG_ARCHIVE_DEST_1: '/u03/ora11g/product/11.2.0/dbhome_1/dbs/arch1_217_837590339.dbf' (error 19502) (TEST01)

ARC2: I/O error 19502 archiving log 1 to '/u03/ora11g/product/11.2.0/dbhome_1/dbs/arch1_217_837590339.dbf'

ARCH: I/O error 19502 archiving log 3 to '/u03/ora11g/product/11.2.0/dbhome_1/dbs/arch1_216_837590339.dbf'

ARCH: Archival stopped, error occurred. Will continue retrying

ORACLE Instance TEST01 - Archival Error

ORA-16038: log 1 sequence# 217 cannot be archived

ORA-19502: write error on file "", block number  (block size=)

ORA-00312: online log 1 thread 1: '/u03/ora11g/oradata/TEST01/redo01.log'

Errors in file /u03/ora11g/diag/rdbms/test01/TEST01/trace/TEST01_ora_8220.trc:

ORA-16038: log 3 sequence# 216 cannot be archived

ORA-00312: online log 3 thread 1: '/u03/ora11g/oradata/TEST01/redo03.log'

USER (ospid: 8220): terminating the instance due to error 16038

Wed May 21 17:48:18 2014

System state dump requested by (instance=1, osid=8220), summary=[abnormal instance termination].

System State dumped to trace file /u03/ora11g/diag/rdbms/test01/TEST01/trace/TEST01_diag_8185.trc

Dumping diagnostic data in directory=[cdmp_20140521174818], requested by (instance=1, osid=8220), summary=[abnormal instance termination].

Instance terminated by USER, pid = 8220

因為是測試環境，是以采用了如下的方法，啟動到Mount階段之後

SQL> startup mount

SQL> alter database clear unarchived logfile '/u03/ora11g/oradata/TEST01/redo01.log';

Database altered.

SQL> alter database clear unarchived logfile '/u03/ora11g/oradata/TEST01/redo03.log';

SQL> alter database open;

檢視日志檔案情況

SQL> select group#,sequence#,members,status,archived from v$log;

    GROUP#  SEQUENCE#    MEMBERS STATUS           ARC

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

         1        219          1 CURRENT          NO

         2        218          1 INACTIVE         NO

         3          0          1 UNUSED           YES

  1* select *from v$logfile

SQL> /

    GROUP# STATUS  TYPE    MEMBER                                             IS_RECOVER

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

         1         ONLINE  /u03/ora11g/oradata/TEST01/redo01.log              NO

         2         ONLINE  /u03/ora11g/oradata/TEST01/redo02.log              NO

         3         ONLINE  /u03/ora11g/oradata/TEST01/redo03.log              NO

這個時候重新執行temp檔案的resize，就沒有問題了。