墨墨导读：一个诡异的案例：ORA-12547: TNS:lost contact导致数据库无法启动，甚至sqlplus都无法登录，让我们一一来解开这个案例的真面目。

1. 背景概述

某客户出现数据库无法启动的情况，申请云和恩墨协助分析和处置。

云和恩墨工程师快速响应，组织相关人员进行故障诊断分析、指出故障原因，提出解决措施并处置，快速恢复了业务。

以下是详细的故障分析诊断过程，以及详细的解决方案描述。

2. 故障分析

2.1. 故障现象

数据库无法启动，数据库监听状态异常。

Thu Apr 30 15:40:20 2020NOTE: ASMB terminatingErrors in file /oracle/app/oracle/diag/rdbms/****/****/trace/****_asmb_8258020.trc:ORA-15064: communication failure with ASM instanceORA-03113: end-of-file on communication channelProcess ID:Session ID: 595 Serial number: 9Errors in file /oracle/app/oracle/diag/rdbms/****/****/trace/****_asmb_8258020.trc:ORA-15064: communication failure with ASM instanceORA-03113: end-of-file on communication channelProcess ID:Session ID: 595 Serial number: 9ASMB (ospid: 8258020): terminating the instance due to error 15064Thu Apr 30 15:40:20 2020System state dump requested by (instance=1, osid=8258020 (ASMB)), summary=[abnormal instance termination].System State dumped to trace file /oracle/app/oracle/diag/rdbms/****/****/trace/****_diag_8389092_20200430154020.trcDumping diagnostic data in directory=[cdmp_20200430154020], requested by (instance=1, osid=8258020 (ASMB)), summary=[abnormal instance termination].Instance terminated by ASMB, pid = 8258020

发现数据库的asm实例也出现异常。

2.2. 故障根源

去分析asm的alert日志

SQL> ALTER DISKGROUP DATA DISMOUNT /* asm agent *//* {0:0:49022} */Thu Apr 30 15:40:19 2020Errors in file /oracle/app/oracle/diag/asm/+asm/+ASM/trace/+ASM_gmon_7405636.trc:ORA-29746: Cluster Synchronization Service is being shut down.GMON (ospid: 7405636): terminating the instance due to error 29746Thu Apr 30 15:40:20 2020System state dump requested by (instance=1, osid=7405636 (GMON)), summary=[abnormal instance termination].System State dumped to trace file /oracle/app/oracle/diag/asm/+asm/+ASM/trace/+ASM_diag_7406038_20200430154020.trcDumping diagnostic data in directory=[cdmp_20200430154020], requested by (instance=1, osid=7405636 (GMON)), summary=[abnormal instance termination].Instance terminated by GMON, pid = 7405636

ASM实例异常。

Ora.asm的资源是offline状态。

尝试关闭has，然后重新启动has，再看看asm是否可以启动

#/oracle/grid/bin/crsctl start hasCould not send msg exec /oracle/grid/perl/bin/perl -I/oracle/grid/perl/lib /oracle/grid/bin/crswrapexece.pl /oracle/grid/crs/install/s_crsconfig_***_env.txt /oracle/grid/bin/ohasd.bin "reboot" .Please retry 2020-05-02 11:16:25Changing directory to /oracle/grid/log/***/ohasd

has启动失败，查看asm agent日志

2020-05-02 11:18:57.898: [ora.asm][3343]{0:0:2} [clean] InstAgent::stop: connect2 oracleHome /oracle/grid oracleSid +ASM2020-05-02 11:18:57.898: [ora.asm][3343]{0:0:2} [clean] InstConnection::connectInt: server not attached2020-05-02 11:18:57.936: [ora.asm][3343]{0:0:2} [clean] ORA-12547: TNS:lost contact 2020-05-02 11:18:57.936: [ora.asm][3343]{0:0:2} [clean] InstConnection::connectInt (1) Exception OCIException2020-05-02 11:18:57.936: [ora.asm][3343]{0:0:2} [clean] InstConnection:connect:excp OCIException OCI error 125472020-05-02 11:18:57.937: [ora.asm][3343]{0:0:2} [clean] InstConnection::connectInt: server not attached2020-05-02 11:18:57.975: [ora.asm][3343]{0:0:2} [clean] ORA-12547: TNS:lost contact 2020-05-02 11:18:57.975: [ora.asm][3343]{0:0:2} [clean] InstConnection::connectInt (1) Exception OCIException2020-05-02 11:18:57.975: [ora.asm][3343]{0:0:2} [clean] InstAgent::stop: connect2 errcode 125472020-05-02 11:18:57.976: [ora.asm][3343]{0:0:2} [clean] clsnUtils::error Exception type=2 string=ORA-12547: TNS:lost contact 2020-05-02 11:18:57.976: [ AGFW][3343]{0:0:2} sending status msg [ORA-12547: TNS:lost contact] for clean for resource: ora.asm **** 12020-05-02 11:18:57.976: [ora.asm][3343]{0:0:2} [clean] ConnectionPool::removeConnection connection count 12020-05-02 11:18:57.976: [ora.asm][3343]{0:0:2} [clean] ConnectionPool::removeConnection sid +ASM, InstConnection 11471d302020-05-02 11:18:57.976: [ USRTHRD][3343]{0:0:2} InstConnection::breakCall pConnxn:11471d30 DetachLock:1059c2f0 m_pSvcH:000000002020-05-02 11:18:57.976: [ USRTHRD][3343]{0:0:2} InstConnection:~InstConnection: this 11471d30

Asm启动过程中报InstConnection:connect:excp OCIException OCI error 12547之后启动失败。

我们发现sqlplus / as sysdba登录也会出现TNS 12547的报错。

通过truss 去跟踪sqlplus

发现在读写sqlnet.log 时候报错，怀疑是oracle本身有问题，查询metalink证实了这个想法：Troubleshooting ORA-12547 TNS: Lost Contact [ID 555565.1]。 然后尝试去relink。

relink的日志出现ksh: /dev/null: 0403-005 Cannot create the specified file.的报错。根据IBM官方文章:https://www.ibm.com/support/pages/file-access-permissions-do-not-allow-specified-action

执行chmod 660 /dev/null，sqlplus / as sysdba不再显示ORA-12547: TNS:lost contact的错误。

2.3. 故障处置

尝试重新启动has，ASM实例正常启动，但是数据库实例无法启动。

手工启动数据库。又出现CRS-5016: Process "/oracle/grid/bin/setasmgidwrap等错误

020-05-02 13:25:00.251: [ora.****.db][1800]{0:0:2} [start] InstConnection::connectInt (2) Exception OCIException2020-05-02 13:25:00.251: [ora.****.db][1800]{0:0:2} [start] InstConnection:connect:excp OCIException OCI error 10342020-05-02 13:25:00.251: [ora.****.db][1800]{0:0:2} [start] InstAgent::stop: connect1 errcode 10342020-05-02 13:25:00.251: [ora.****.db][1800]{0:0:2} [start] InstAgent::stop: connect2 oracleHome /oracle/app/oracle/product/11.2.0/dbhome_1 oracleSid ****2020-05-02 13:25:00.251: [ora.****.db][1800]{0:0:2} [start] InstConnection::connectInt: server not attached2020-05-02 13:25:00.319: [ora.****.db][1800]{0:0:2} [start] ORA-01017: invalid username/password; logon denied 2020-05-02 13:25:00.319: [ora.****.db][1800]{0:0:2} [start] InstConnection::connectInt (2) Exception OCIException2020-05-02 13:25:00.319: [ora.****.db][1800]{0:0:2} [start] InstConnection:connect:excp OCIException OCI error 10172020-05-02 13:25:00.319: [ora.****.db][1800]{0:0:2} [start] InstAgent::stop: connect2 errcode 10172020-05-02 13:25:00.319: [ora.****.db][1800]{0:0:2} [start] clsnUtils::error Exception type=2 string=ORA-01017: invalid username/password; logon denied 2020-05-02 13:25:00.319: [ AGFW][1800]{0:0:2} sending status msg [ORA-01017: invalid username/password; logon denied] for start for resource: ora.****.db 1 1

看来问题是ORA-01017: invalid username/password; logon denied

手工执行sqlplus / as sysdba也出现这样的报错

解决办法：

执行sqlplus sys as sysdba登录。执行startup，手动启动数据库

3. 根本解决方案及建议

根本原因是由于/dev/null权限的问题

解决办法：

chmod 660  /dev/null

墨天轮原文链接：https://www.modb.pro/db/26889（复制到浏览器中打开或者点击“阅读原文”）

推荐阅读：144页！分享珍藏已久的数据库技术年刊

数据和云

ID：OraNews

如有收获，请划至底部，点击“在看”，谢谢！

点击下图查看更多 ↓

云和恩墨大讲堂 | 一个分享交流的地方

长按，识别二维码，加入万人交流社群

请备注：云和恩墨大讲堂

  点个“在看”

你的喜欢会被看到❤