oracle通信通道的文件结尾_Oracle错误——ORA-03113:通信通道的文件结尾 解决办法...

由来

今天跟往常一样，登陆PL/SQL，确登陆失败，出现一个错误“ORA-01034”和“ORA-27101”如图：

然后就就通过命令提示符去登陆Oracle，去查看怎么回事，然后问题进一步出现，错误“ORA-03113:通信通道的文件结尾 进程 ID:6320 回话 ID :191 序列号：3”。

问题根源

Oracle出现错误，于是去错误日志里去找问题根源：在 e:\app\kang\diag\rdbms\oracle\oracle\trace\文件夹下找到oracle_ora_6320.trc文件，打开显示错误日志：

Trace filee:\app\kang\diag\rdbms\oracle\oracle\trace\oracle_ora_6320.trc

Oracle Database 11gEnterprise Edition Release 11.2.0.1.0 - 64bit Production

With thePartitioning, OLAP, Data Mining and Real Application Testing options

Windows NT VersionV6.1 Service Pack 1

CPU                : 4 - type 8664, 2 PhysicalCores

Process Affinity    : 0x0x0000000000000000

Memory (Avail/Total):Ph:2805M/6087M, Ph+PgF:6761M/12173M

Instance name: oracle

Redo thread mountedby this instance: 1

Oracle processnumber: 19

Windows thread id:6320, image: ORACLE.EXE (SHAD)

*** 2014-08-1608:18:55.461

*** SESSIONID:(191.3) 2014-08-16 08:18:55.461

*** CLIENT ID:()2014-08-16 08:18:55.461

*** SERVICE NAME:()2014-08-16 08:18:55.461

*** MODULENAME:(sqlplus.exe) 2014-08-16 08:18:55.461

*** ACTION NAME:()2014-08-16 08:18:55.461

ORA-19815: 警告:db_recovery_file_dest_size 字节 (共 4102029312 字节) 已使用 100.00%, 尚有 0 字节可用。

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

You have followingchoices to free up space from recovery area:

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

then consider changing RMAN ARCHIVELOGDELETION POLICY.

2. Back up files totertiary device such as tape using RMAN

BACKUP RECOVERY AREA command.

3. Add disk space andincrease db_recovery_file_dest_size parameter to

reflect the new space.

4. Delete unnecessaryfiles using RMAN DELETE command. If an operating

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

DELETE EXPIRED commands.

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

ORA-19809:超出了恢复文件数的限制

ORA-19804: 无法回收33961984 字节磁盘空间 (从 4102029312 限制中)

*** 2014-08-1608:18:55.502 4132 krsh.c

ARCH: Error 19809Creating archive log file to'E:\APP\KANG\FLASH_RECOVERY_AREA\ORACLE\ARCHIVELOG\2014_08_16\O1_MF_1_159_%U_.ARC'

*** 2014-08-1608:18:55.502 2747 krsi.c

krsi_dst_fail: dest:1err:19809 force:0 blast:1

DDE: Problem Key 'ORA312' was flood controlled (0x1) (no incident)

ORA-00312: 联机日志 3 线程1: 'E:\APP\KANG\ORADATA\ORACLE\REDO03.LOG'

ORA-16038: 日志 3sequence# 159 无法归档

ORA-19809:超出了恢复文件数的限制

ORA-00312: 联机日志 3 线程1: 'E:\APP\KANG\ORADATA\ORACLE\REDO03.LOG'

*** 2014-08-1608:18:55.565

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

从这里我们发现了问题的根源：“

ORA-19815: 警告: db_recovery_file_dest_size 字节 (共 4102029312 字节) 已使用100.00%, 尚有 0 字节可用。” 是db_recovery_file_dest_size也叫归档日志空间不足导致的，既然找到问题的根源，那解决起来也就容易了。