“ORA-03113: 通信通道的文件结尾”报错处理

最新推荐文章于 2024-04-21 02:45:32 发布

转载最新推荐文章于 2024-04-21 02:45:32 发布 · 352 阅读

本文记录了一次Oracle数据库启动失败的问题排查过程。问题表现为实例启动后自动关闭，通过查看日志发现是由于未能找到REDO01.LOG文件导致。最终通过备份恢复解决了该问题。

今天小雨很闷热，晚上启自己电脑ORACLE数据报如下错误：

SQL> startup
ORACLE 例程已经启动。

Total System Global Area 535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             222298576 bytes
Database Buffers          306184192 bytes
Redo Buffers                5804032 bytes
数据库装载完毕。
ORA-03113: 通信通道的文件结尾
进程 ID: 3288
会话 ID: 9 序列号: 3

SQL> alter database open;
ERROR:
ORA-03114: 未连接到 ORACLE

SQL> conn / sysdba
SP2-0306: 选项无效。
用法: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]]
其中 ::= [/][@]
::= [][/][@]
SQL> conn / as sysdba
ERROR:
ORA-01031: insufficient privileges

C:\Users\deitylee>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期五 11月 4 19:45:53 2011

已连接到空闲例程。

SQL> show user;
USER 为 "SYS"
SQL> select * from v$database;
select * from v$database
*
第 1 行出现错误:
ORA-01034: ORACLE not available
进程 ID: 0
会话 ID: 0 序列号: 0

SQL> alter databae open;
alter databae open
*
第 1 行出现错误:
ORA-01034: ORACLE not available
进程 ID: 0
会话 ID: 0 序列号: 0

SQL> startup mount;
ORACLE 例程已经启动。

Total System Global Area 535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             222298576 bytes
Database Buffers          306184192 bytes
Redo Buffers                5804032 bytes
数据库装载完毕。
SQL> alter database open;
alter database open
      *
第 1 行出现错误:
ORA-03113: 通信通道的文件结尾
进程 ID: 1292
会话 ID: 9 序列号: 5

SQL> startup
ORA-24324: 未初始化服务句柄
ORA-01041: 内部错误, hostdef 扩展名不存在

通过以上验证可以看出实例在启动后会自动关闭，查看alert_orcl.log得到如下结果：

Errors in file e:\app\deitylee\diag\rdbms\orcl\orcl\trace\orcl_lgwr_4028.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'E:\APP\DEITYLEE\ORADATA\ORCL\REDO01.LOG'
ORA-27041: unable to open file
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。
Errors in file e:\app\deitylee\diag\rdbms\orcl\orcl\trace\orcl_lgwr_4028.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'E:\APP\DEITYLEE\ORADATA\ORCL\REDO01.LOG'
ORA-27041: unable to open file
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。
Errors in file e:\app\deitylee\diag\rdbms\orcl\orcl\trace\orcl_ora_1292.trc:
ORA-00313: 无法打开日志组 1 (用于线程 ) 的成员
ORA-00312: 联机日志 1 线程 1: 'E:\APP\DEITYLEE\ORADATA\ORCL\REDO01.LOG'
USER (ospid: 1292): terminating the instance due to error 313
Fri Nov 04 19:47:19 2011
ARC3 started with pid=24, OS id=2120
Instance terminated by USER, pid = 1292

再查看orcl_ora_1292.trc文件：

Trace file e:\app\deitylee\diag\rdbms\orcl\orcl\trace\orcl_ora_1292.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows NT Version V6.1 Service Pack 1
CPU : 2 - type 586, 2 Physical Cores
Process Affinity : 0x0x00000000
Memory (Avail/Total): Ph:1202M/2046M, Ph+PgF:2529M/4092M, VA:1214M/2047M
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 20
Windows thread id: 1292, image: ORACLE.EXE (SHAD)

*** 2011-11-04 19:47:19.437
*** SESSION ID:(9.5) 2011-11-04 19:47:19.437
*** CLIENT ID:() 2011-11-04 19:47:19.437
*** SERVICE NAME:() 2011-11-04 19:47:19.437
*** MODULE NAME:(sqlplus.exe) 2011-11-04 19:47:19.437
*** ACTION NAME:() 2011-11-04 19:47:19.437

DDE: Problem Key 'ORA 312' was flood controlled (0x1) (no incident)
ORA-00312: 联机日志 1 线程 1: 'E:\APP\DEITYLEE\ORADATA\ORCL\REDO01.LOG'
DDE: Problem Key 'ORA 313' was flood controlled (0x1) (no incident)
ORA-00313: 无法打开日志组 1 (用于线程 ) 的成员
ORA-00312: 联机日志 1 线程 1: 'E:\APP\DEITYLEE\ORADATA\ORCL\REDO01.LOG'
ORA-00313: 无法打开日志组 1 (用于线程 ) 的成员
ORA-00312: 联机日志 1 线程 1: 'E:\APP\DEITYLEE\ORADATA\ORCL\REDO01.LOG'

问题很清楚了，oracle找不到REDO01.LOG文件，使用备份恢复即可。

SQL> startup
ORACLE 例程已经启动。

Total System Global Area 535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             222298576 bytes
Database Buffers          306184192 bytes
Redo Buffers                5804032 bytes
数据库装载完毕。
数据库已经打开。

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

Cause: The online log cannot be opened. May not be able to find file.

Action: See accompanying errors and make log available.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16381228/viewspace-710254/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16381228/viewspace-710254/