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

故障現象：

ORALCE啟動時報如下錯誤：

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

SQL> startup

ORACLE instance started.

Total System Global Area 3657797632 bytes

Fixed Size                  2258600 bytes

Variable Size            2013268312 bytes

Database Buffers         1627389952 bytes

Redo Buffers               14880768 bytes

Database mounted.

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

Process ID: 2118

Session ID: 401 Serial number: 5

SQL> startup mount; 

ORA-24324: service handle not initialized

ORA-01041: internal error. hostdef extension doesn't exist

原因查找：

1. 查看orcle啟動日志，確定具體是什么原因引起的錯誤。

    cd $ORACLE_HOME/diag/rdbms/ncdbemu/ncdbemu/trace/

     ls -alcr | grep alert (c時間排序、r倒序) 定位啟動日志。打開很長內容，不好找。因此用跟蹤來找。

2. tail -f -n 500 alert_ncdbemu.log 然后重啟運行上面的startup命令，跟蹤得到如下內容。

ARC3 started with pid=23, OS id=2126 

Errors in file /oracle/app/oracle/diag/rdbms/ncdbemu/ncdbemu/trace/ncdbemu_ora_2118.trc:

ORA-19815: WARNING: db_recovery_file_dest_size of 20971520000 bytes is 100.00% used, and has 0 remaining bytes available.

************************************************************************

You have following choices to free up space from recovery area:

1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,

   then consider changing RMAN ARCHIVELOG DELETION POLICY.

2. Back up files to tertiary device such as tape using RMAN

   BACKUP RECOVERY AREA command.

3. Add disk space and increase db_recovery_file_dest_size parameter to

   reflect the new space.

4. Delete unnecessary files using RMAN DELETE command. If an operating

   system command was used to delete files, then use RMAN CROSSCHECK and

   DELETE EXPIRED commands.

************************************************************************

ARCH: Error 19809 Creating archive log file to '/backup/fast_recovery_area/NCDBEMU/archivelog/2016_02_01/o1_mf_1_603_%u_.arc'

Errors in file /oracle/app/oracle/diag/rdbms/ncdbemu/ncdbemu/trace/ncdbemu_ora_2118.trc:

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

ORA-19809: limit exceeded for recovery files

ORA-00312: online log 3 thread 1: '/dbdata/oradata/ncdbemu/redog03m01.rdo'

ORA-00312: online log 3 thread 1: '/dbdata/oradata/ncdbemu/redog03m02.rdo'

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

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

System State dumped to trace file /oracle/app/oracle/diag/rdbms/ncdbemu/ncdbemu/trace/ncdbemu_diag_2087_20160201162949.trc

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

Instance terminated by USER, pid = 2118

Mon Feb 01 16:33:49 2016

Starting ORACLE instance (normal)

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

Initial number of CPU is 2

CELL communication is configured to use 0 interface(s):

CELL IP affinity details:

    NUMA status: non-NUMA system

    cellaffinity.ora status: N/A

CELL communication will use 1 IP group(s):

    Grp 0: 

Picked latch-free SCN scheme 3

Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST

Autotune of undo retention is turned on. 

IMODE=BR

ILAT =88

LICENSE_MAX_USERS = 0

SYS auditing is disabled

Starting up:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options.

ORACLE_HOME = /oracle/app/oracle/dbhome

System name:    Linux

Node name:      NcDbEmulator

Release:        2.6.32-431.el6.x86_64

Version:        #1 SMP Sun Nov 10 22:19:54 EST 2013

Machine:        x86_64

Using parameter settings in server-side spfile /oracle/app/oracle/dbhome/dbs/spfilencdbemu.ora

System parameters with non-default values:

  processes                = 500

  sessions                 = 800

  memory_target            = 3504M

  control_files            = "/dbdata/oradata/ncdbemu/control01.ctl"

  control_files            = "/backup/fast_recovery_area/ncdbemu/control02.ctl"

  control_files            = "/dbdata/oradata/ncdbemu/control03.ctl"

  db_block_size            = 8192

  compatible               = "11.2.0.4.0"

  log_archive_format       = "%t_%s_%r.dbf"

  cluster_database         = FALSE

  db_recovery_file_dest    = "/backup/fast_recovery_area"

  db_recovery_file_dest_size= 20000M

  undo_tablespace          = "UNDOTBS1"

  remote_login_passwordfile= "EXCLUSIVE"

  db_domain                = ""

  dispatchers              = "(PROTOCOL=TCP) (SERVICE=ncdbemuXDB)"

  job_queue_processes      = 1000

  audit_file_dest          = "/oracle/app/oracle/admin/ncdbemu/adump"

  audit_trail              = "DB"

  db_name                  = "ncdbemu"

  open_cursors             = 300

  diagnostic_dest          = "/oracle/app/oracle"

4、解決辦法

有三個：（其實上面日志中已經給出4個解決方法了。）

1）將歸檔設置到其他目錄，修改alter system set log_archive_dest = 其他路徑

2）轉移或者刪除閃回恢復區里的歸檔日志。

3）增大閃回恢復區。alter system set db_recovery_file_dest_size=30G;

第二個錯誤（24324和01041）的解決：

只要退出sqlplus然后重新登錄就可以了。

5、解決步驟：

1）啟動到mount狀態下，

SQL> startup mount; 

ORA-24324: service handle not initialized

ORA-01041: internal error. hostdef extension doesn't exist

SQL> startup nomount

ORA-24324: service handle not initialized

ORA-01041: internal error. hostdef extension doesn't exist

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

[oracle@NcDbEmulator ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 1 16:33:44 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 3657797632 bytes

Fixed Size                  2258600 bytes

Variable Size            2013268312 bytes

Database Buffers         1627389952 bytes

Redo Buffers               14880768 bytes

Database mounted.

2）查看恢復區（閃回區）位置及大小：

SQL> show parameter db_recovery;

NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string      /backup/fast_recovery_area

db_recovery_file_dest_size           big integer 20000M

SQL> show parameter flashback;

NAME                                 TYPE        VALUE

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

db_flashback_retention_target        integer     1440

SQL> select file_type,PERCENT_SPACE_USED,NUMBER_OF_FILES from v$flash_recovery_area_usage;

FILE_TYPE            PERCENT_SPACE_USED NUMBER_OF_FILES

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

CONTROL FILE                          0               0

REDO LOG                              0               0

ARCHIVED LOG                      99.85             595

BACKUP PIECE                          0               0

IMAGE COPY                            0               0

FLASHBACK LOG                         0               0

FOREIGN ARCHIVED LOG                  0               0

7 rows selected.

3）方法一，修改恢復區大小：

SQL> alter system set db_recovery_file_dest_size=30G;

System altered.

SQL>  show parameter db_recovery_file_dest_size;

NAME                                 TYPE        VALUE

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

db_recovery_file_dest_size           big integer 30G

4）開啟數據庫

SQL> alter database open;

Database altered.