oracle RAC ORA-03113 错误解决
来源:互联网 发布:淘宝禁售电话卡 编辑:程序博客网 时间:2024/05/21 14:53
1 错误现象
数据库 客户端连接不正常,,频繁报 ORA-03113 错误。
oracle 文档中对这个错误这样解释:
ORA-03113 错误就是说连接到数据库的网络中断了。
有些错误由于频繁出现、原因复杂而被 Oracle DBA 们戏称之为"经典的错误"。
其中ORA-3113 "end of file on communication channel" 就是这样的一个。
我们可以简单的把这个错误理解为Oracle客户端进程和数据库后台进程连接中断。
不过,导致这个错误的原因实际上有很多种:对数据库设置不当、任何能导致数据库后台进程崩溃的行为都可能产生这个错误。
此外,该错误出现的场景复杂,可能出现在:
1 启动的Oracle的时侯
2 试图创建数据库的时侯
3 试图对数据库进行连接的时侯
4 在客户端正在运行SQL/PL/SQL的时侯
5 备份/恢复数据库的时侯
6 其它一些情况下....
错误原因种种
根据网络上大家反映的情况来看,错误原因大约有这些:
? Unix核心参数设置不当
? Oracle执行文件权限不正确/环境变量问题
? 客户端通信不能正确处理
? 数据库服务器崩溃/操作系统崩溃/进程被kill
? Oracle 内部错误
? 特定SQL、PL/SQL引起的错误
? 空间不够
? 防火墙的问题
? 其它原因
2 解决问题----查看 数据库状态
oracle RAC 状态,,,如下,,节点正常
crs_stat -t
名称 类型 目标 状态 主机
------------------------------------------------------------
ora....SM2.asm application ONLINE ONLINE airportb
ora....TB.lsnr application ONLINE ONLINE airportb
ora....rtb.gsd application ONLINE ONLINE airportb
ora....rtb.ons application ONLINE ONLINE airportb
ora....rtb.vip application ONLINE ONLINE airportb
ora....SM1.asm application ONLINE ONLINE airportc
ora....TC.lsnr application ONLINE ONLINE airportc
ora....rtc.gsd application ONLINE ONLINE airportc
ora....rtc.ons application ONLINE ONLINE airportc
ora....rtc.vip application ONLINE ONLINE airportc
ora....s1.inst application ONLINE ONLINE airportc
ora....s2.inst application ONLINE ONLINE airportb
ora.bgsdbs.db application ONLINE ONLINE airportc
查看 oracle 监听状态,,正常。。
[oracle@airportc ~]$ lsnrctl status
LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 01-6月 -2011 16:47:59
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER_AIRPORTC
Version TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date 01-6月 -2011 22:56:06
Uptime 0 days 17 hr. 51 min. 53 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/product/10.2.0/db_2/network/admin/listener.ora
Listener Log File /oracle/product/10.2.0/db_2/network/log/listener_airportc.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.32.128.20)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.32.128.18)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status BLOCKED, has 1 handler(s) for this service...
Service "+ASM_XPT" has 1 instance(s).
Instance "+ASM1", status BLOCKED, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "bgsdbs" has 2 instance(s).
Instance "bgsdbs1", status READY, has 2 handler(s) for this service...
Instance "bgsdbs2", status READY, has 1 handler(s) for this service...
Service "bgsdbs_XPT" has 2 instance(s).
Instance "bgsdbs1", status READY, has 2 handler(s) for this service...
Instance "bgsdbs2", status READY, has 1 handler(s) for this service...
The command completed successfully
3 查找问题
查看 监听参数,,,发现local_listener 参数 指定了IP地址
SQL> show parameter local;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (ADDRESS = (PROTOCOL = TCP)(HO
ST = 10.32.128.20)(PORT = 1521
))
log_archive_local_first boolean TRUE
根据经验 尝试修改 local_listener ,,设置为空 ,这样减少节点切换次数,是否可以解决这个问题。
SQL>alter system reset local_listener scope=spfile sid='bgsdbs1';
4 修改成功,由于修改了 spfile,需要重启。。
SQL>shutdown immediate;
SQL> startup
之后由于有回滚段 问题,长时间不能启动,,怀疑内存缓存问题,,reboot 了 操作系统。
5 操作系统重新启动,数据库重启。
数据库启动成功。
CRS 节点错误,,如下,有一个节点监听不能启动
名称 类型 目标 状态 主机
------------------------------------------------------------
ora....SM2.asm application ONLINE ONLINE airportb
ora....TB.lsnr application ONLINE ONLINE airportb
ora....rtb.gsd application ONLINE ONLINE airportb
ora....rtb.ons application ONLINE ONLINE airportb
ora....rtb.vip application ONLINE ONLINE airportb
ora....SM1.asm application ONLINE ONLINE airportc
ora....TC.lsnr application OFFLINE OFFLINE airportc
ora....rtc.gsd application ONLINE ONLINE airportc
ora....rtc.ons application ONLINE ONLINE airportc
ora....rtc.vip application ONLINE ONLINE airportc
ora....s1.inst application ONLINE ONLINE airportc
ora....s2.inst application ONLINE ONLINE airportb
ora.bgsdbs.db application ONLINE ONLINE airportc
6 查看 监听 配载文件,,
cd /oracle/product/10.2.0/db_2/network/admin
cat listener.ora
# listener.ora.airportc Network Configuration File: /oracle/product/10.2.0/db_2/network/admin/listener.ora.airportc
# Generated by Oracle configuration tools.
SID_LIST_LISTENER_AIRPORTC =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/product/10.2.0/db_2)
(PROGRAM = extproc)
)
)
LISTENER_AIRPORTC =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = airportc-vip)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.32.128.18)(PORT = 1521)(IP = FIRST))
)
)
7 发现 LISTENER_AIRPORTC 参数 IP地址 不正确,,,修改为正确的 IP地址
启动 CRS。。启动监听。
8 ORA-03113 错误消除。
=====================================================================================
故障提示:
生产数据库最好有固定的维护人员定期检查,,否则不同的人员都登录操作,有很多
人为的问题带来的麻烦很大,人为的增加了不必要的工作量。
- oracle RAC ORA-03113 错误解决
- oracle 12c rac ora-12545错误解决
- [Oracle] ORA-03113错误分析与解决
- 【Oracle】ORA-03113错误分析与解决
- oracle 10g RAC添加节点遭遇ORA-02174错误解决
- oracle 错误解决 ORA-06502
- Oracle ORA-29538错误解决
- ORACLE ORA-01652 错误解决
- ORACLE ora-00942 错误解决
- ORACLE ORA-01722 错误解决
- ORACLE ORA-12560 错误解决
- ORACLE ORA-00600 错误解决
- oracle ORA-01019错误解决
- 【ORACLE】ORA-01157错误解决
- Oracle错误ORA-29861解决
- oracle--错误解决ORA-12505
- ORACLE 常见错误及解决汇总 ORA-20000 ORA-03113 ORA-14452 ORA-08103
- oracle 10G r2 RAC连接ORA-12545错误
- latex设置表格字体大小
- 【solr专题之一】Solr快速入门
- MongoDB学习04之用户认证
- Invalid byte 3 of 3-byte UTF-8 sequence.的原因
- 介绍几个cmd的替代品
- oracle RAC ORA-03113 错误解决
- android布局tips,基础知识搜集
- CSDN网站六百万用户信息外泄
- Android Hal 分析
- 使用Maven构建Web项目
- 虚拟机中Linux对于虚拟的磁盘的挂载
- opnet关于pipeline
- OSGi第一个例子
- linux-cat命令 ——2014.7.2