Table of Contents
ora03113:end-of-file on communication channel
情况描述：业务测试库起不来，说是删物理文件的时候可能不小心误删了什么文件
情况查看
SQL> startup ORACLE instance started. Total System Global Area 3206836224 bytes Fixed Size 2257520 bytes Variable Size 2365590928 bytes Database Buffers 822083584 bytes Redo Buffers 16904192 bytes Database mounted. ERROR at line 1: ORA-03113: File end of the communication channel Process ID: 14199 Session ID: 853 Serial number: 5复制
问题查看
1. 根据报错中的ProcessID查看对应的日志文件
$ cd $ORACLE_BASE/diag/rdbms/orcl/orcl/trace
$ ll|grep 14199
-rw-r----- 1 oracle oinstall 1984 Mar 1 09:49 orcl_ora_14199.trc
-rw-r----- 1 oracle oinstall 98 Mar 1 09:49 orcl_ora_14199.trm
$ more orcl_ora_14199.trc
Trace file /oraapp/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_14199.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oraapp/app/oracle/product/11.2.0/db_1
System name: Linux
Node name: sit2db11gutf
Release: 4.1.12-61.1.28.el6uek.x86_64
Version: #2 SMP Thu Feb 23 20:03:53 PST 2017
Machine: x86_64
VM name: VMWare Version: 6
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 19
Unix process pid: 14199, image: oracle@sit2db11gutf (TNS V1-V3)
*** 2023-03-01 09:49:20.928
*** SESSION ID:(1705.5) 2023-03-01 09:49:20.928
*** CLIENT ID:() 2023-03-01 09:49:20.928
*** SERVICE NAME:() 2023-03-01 09:49:20.928
*** MODULE NAME:(sqlplus@sit2db11gutf (TNS V1-V3)) 2023-03-01 09:49:20.928
*** ACTION NAME:() 2023-03-01 09:49:20.928
DDE: Problem Key 'ORA 312' was flood controlled (0x1) (no incident)
ORA-00312: online log 1 thread 1: '/oradata/ORCL/onlinelog/o1_mf_1_g26bhjwb_.log'
DDE: Problem Key 'ORA 313' was flood controlled (0x1) (no incident)
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 1 thread 1: '/oradata/ORCL/onlinelog/o1_mf_1_g26bhjwb_.log'
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 1 thread 1: '/oradata/ORCL/onlinelog/o1_mf_1_g26bhjwb_.log'
kjzduptcctx: Notifying DIAG for crash event
----- Abridged Call Stack Trace -----
ksedsts()+465<-kjzdssdmp()+267<-kjzduptcctx()+232<-kjzdicrshnfy()+63<-ksuitm()+5594<-kcfopd()+6046<-adbdrv()+57191<-opiexe()+18724<-opiosq0()+4303<-kpooprx()+274<-kpoal8()+842<-opiodr()+917<-ttcpip()+2183<-opitsk()+1710<-opiino()+969<-o
piodr()+917<-opidrv()+570
<-sou2o()+103<-opimai_real()+133<-ssthrdmain()+265
----- End of Abridged Call Stack Trace -----
*** 2023-03-01 09:49:20.958
USER (ospid: 14199): terminating the instance due to error 313
ksuitm: waiting up to [5] seconds before killing DIAG(14169)
[oracle@sit2db11gutf trace]$ date
Wed Mar 1 09:58:40 CST 2023
复制由报错日志中可以看出数据库打开日志文件失败，进一步确认是否是该文件丢失
ORA-00313: open failed for members of log group 1 of thread ORA-00312: online log 1 thread 1: '/oradata/ORCL/onlinelog/o1_mf_1_g26bhjwb_.log'复制
$ more /oradata/ORCL/onlinelog/o1_mf_1_g26bhjwb_.log
/oradata/ORCL/onlinelog/o1_mf_1_g26bhjwb_.log: No such file or directory
You have mail in /var/spool/mail/oracle
$ cd /oradata/ORCL/onlinelog/
$ ll
total 1048592
-rw-r----- 1 oracle oinstall 536871424 Feb 27 12:50 o1_mf_2_g26bhmgp_.log
-rw-r----- 1 oracle oinstall 536871424 Mar 1 09:48 o1_mf_3_g26bhoxr_.log
复制由此可见，问题原因是联机日志的第一日志组的物理文件被误删
问题解决
$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 1 10:06:03 2023 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> startup nomount; SQL> desc v$log; Name Null? Type ----------------------------------------- -------- ---------------------------- GROUP# NUMBER THREAD# NUMBER SEQUENCE# NUMBER BYTES NUMBER BLOCKSIZE NUMBER MEMBERS NUMBER ARCHIVED VARCHAR2(3) STATUS VARCHAR2(16) FIRST_CHANGE# NUMBER FIRST_TIME DATE NEXT_CHANGE# NUMBER NEXT_TIME DATE SQL> alter database mount; Database altered. SQL> select status from v$instance; STATUS ------------ MOUNTED SQL> select GROUP#,SEQUENCE#,ARCHIVED,STATUS from v$log; GROUP# SEQUENCE# ARC STATUS ---------- ---------- --- ---------------- 1 35872 NO INACTIVE 3 35874 NO CURRENT 2 35873 NO INACTIVE SQL> desc v$logfile; Name Null? Type ----------------------------------------- -------- ---------------------------- GROUP# NUMBER STATUS VARCHAR2(7) TYPE VARCHAR2(7) MEMBER VARCHAR2(513) IS_RECOVERY_DEST_FILE VARCHAR2(3) SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- -------------------------------------------------- --- 3 ONLINE /oradata/ORCL/onlinelog/o1_mf_3_g26bhoxr_.log NO 2 ONLINE /oradata/ORCL/onlinelog/o1_mf_2_g26bhmgp_.log NO 1 ONLINE /oradata/ORCL/onlinelog/o1_mf_1_g26bhjwb_.log NO SQL> select GROUP#,SEQUENCE#,ARCHIVED,STATUS from v$log; GROUP# SEQUENCE# ARC STATUS ---------- ---------- --- ---------------- 1 35872 NO INACTIVE 3 35874 NO CURRENT 2 35873 NO INACTIVE复制
查看v$log可知丢失的日志组1不是current日志且未归档，所以需要强行clear日志组1
SQL> alter database clear unarchived logfile group 1;
Database altered.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$ ll
total 1572888
-rw-r----- 1 oracle oinstall 536871424 Mar 1 10:14 o1_mf_1_kzxf8kph_.log
-rw-r----- 1 oracle oinstall 536871424 Feb 27 12:50 o1_mf_2_g26bhmgp_.log
-rw-r----- 1 oracle oinstall 536871424 Mar 1 09:48 o1_mf_3_g26bhoxr_.log
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 1 10:15:08 2023
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> alter database open;
Database altered.
SQL> select status from v$instance;
STATUS
------------
OPEN
复制问题扩展：联机日志物理文件丢失
- 丢失日志为非当前日志且有归档
SQL> alter database clear logfile group 1；
复制- 丢失日志为非当前日志且无归档
SQL> alter database clear unarchived logfile group 1;
复制-
丢失日志为当前日志但有归档
待补充
-
丢失日志为当前日志且无归档
待补充
最后修改时间：2023-01-16 18:50:50
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
目录
