Oracle資料庫關閉時，出現ORA-03113錯誤

SQL> shutdown immediate

ORA-03113: end-of-file on communication channel
Process ID: 3437
Session ID: 125 Serial number: 5

SQL> startup
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL> 
SQL> 
SQL> startup nomount
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL> 
SQL> 
SQL> startup mount
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL> 
SQL> alter datafile '/bk/ts5.2.dbf' offline
  2  ;
ERROR:
ORA-03114: not connected to ORACLE

 

檢視日誌檔案：
/u01/app/diag/rdbms/orcl/orcl/trace/alert_orcl.log

16416 Errors in file /u01/app/diag/rdbms/orcl/orcl/trace/orcl_dbw0_4055.trc:
16417 ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
16418 ORA-01110: data file 6: '/bk/ts5.2.dbf'
16419 ORA-27037: unable to obtain file status
16420 Linux Error: 2: No such file or directory
16421 Additional information: 3
16422 Errors in file /u01/app/diag/rdbms/orcl/orcl/trace/orcl_ora_4097.trc:
16423 ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
16424 ORA-01110: data file 6: '/bk/ts5.2.dbf'
16425 ORA-1157 signalled during: ALTER DATABASE OPEN...
16426 Mon Oct 21 20:53:43 2013
16427 Checker run found 1 new persistent data failures
16428 Mon Oct 21 20:54:53 2013

發現是因為剛剛建立的表空間檔案丟失，導致的。
開始修復表空間檔案。

解決方法：
SQL> shutdown abort
ORACLE instance shut down.
SQL> 
SQL> 
SQL> 
SQL> startup
ORACLE instance started.

Total System Global Area  422670336 bytes
Fixed Size          1336960 bytes
Variable Size    314575232 bytes
Database Buffers  100663296 bytes
Redo Buffers      6094848 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/bk/ts5.2.dbf'

SQL> alter database datafile '/bk/ts5.2.dbf' offline drop;

Database altered.

SQL> 
SQL> 
SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  422670336 bytes
Fixed Size          1336960 bytes
Variable Size    314575232 bytes
Database Buffers  100663296 bytes
Redo Buffers      6094848 bytes
Database mounted.
Database opened.
SQL>

 

由於是表空間檔案丟失，最後檢視一下表空間：
SQL> 
SQL> select tablespace_name, file_name from dba_data_files;

TABLESPACE_NAME      FILE_NAME
-------------------- ----------------------------------------
EXAMPLE       /u01/app/oradata/orcl/example01.dbf
UNDOTBS1      /u01/app/oradata/orcl/undotbs01.dbf
SYSAUX      /u01/app/oradata/orcl/sysaux01.dbf
SYSTEM      /u01/app/oradata/orcl/system01.dbf
USERS      /u01/app/oradata/orcl/users01.dbf
TS5          /bk/ts5.2.dbf
TS5          /bk/ts5.dbf

7 rows selected.

SQL> select file#, name, status from v$datafile;

    FILE# NAME         STATUS
---------- ---------------------------------------- -------
  1 /u01/app/oradata/orcl/system01.dbf    SYSTEM
  2 /u01/app/oradata/orcl/sysaux01.dbf    ONLINE
  3 /u01/app/oradata/orcl/undotbs01.dbf    ONLINE
  4 /u01/app/oradata/orcl/users01.dbf    ONLINE
  5 /u01/app/oradata/orcl/example01.dbf    ONLINE
  6 /bk/ts5.2.dbf                  RECOVER
  7 /bk/ts5.dbf                    ONLINE

7 rows selected.

SQL>