常见数据库连接失败错误

一、ORA-12545/TNS-12545 Connect failed because target host or object does not exist

当Oracle无法解析主机名时,通常会发生此错误
OracleNet/SQLNet由于底层“无法解析”主机名到IP地址而失败。
大多数情况下，将主机名替换为适当的 IP 地址后,问题就会得到解决。有时,当存在设置/配置问题时,会遇到此错误。

1、原因

12545, 00000, "Connect failed because target host or object does not exist"
// *Cause: The address specified is not valid, or the program being
// connected to does not exist.
// *Action: Ensure the ADDRESS parameters have been entered correctly; the
// most likely incorrect parameter is the node name.  Ensure that the
// executable for the server exists (perhaps "oracle" is missing.)
// If the protocol is TCP/IP, edit the TNSNAMES.ORA file to change the
// host name to a numeric IP address and try again.

2、模拟$ORACLE_HOME/bin目录下缺少oracle文件

[oracle@rac01:/u01/app/oracle/product/19.0.0/db_1/bin]$mv oracle oracle.bak
[oracle@rac01:/u01/app/oracle/product/19.0.0/db_1/bin]$sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Nov 15 11:54:39 2023
Version 19.9.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

ERROR:
ORA-12545: Connect failed because target host or object does not exist

[oracle@rac01:/u01/app/oracle/product/19.0.0/db_1/bin]$mv oracle.bak oracle

[oracle@rac01:/u01/app/oracle/product/19.0.0/db_1/bin]$sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Nov 15 11:54:54 2023
Version 19.9.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0

SQL>

[oracle@rac01:/u01/app/oracle/product/19.0.0/db_1/bin]$ls -lsa oracle
433204 -rwsr-s--x 1 oracle asmadmin 443595488 Sep 25 17:24 oracle

二、ORA-12518/TNS-12518

1、原因

12518, 00000, "TNS:listener could not hand off client connection"
// *Cause: The process of handing off a client connection to another process
// failed.
// *Action: Turn on listener tracing and re-execute the operation. Verify
// that the listener and database instance are properly configured for
// direct handoff.  If problem persists, call Oracle Support.
// *Comment: The problem can be worked around by configuring dispatcher(s)
// to specifically handle the desired presentation(s), and connecting
// directly to the dispatcher, bypassing the listener.

ORA-12518/TNS-12518 表示侦听器将客户端连接移交给服务器进程或调度程序进程时出现问题。TNS-12518 记录在侦听器日志中。客户端可能会收到 ORA-12518 或其他一些断开连接错误，例如 ORA-12537。

在专用模式下，数据库客户端联系侦听器并提供数据库的服务名称。然后侦听器生成一个专用服务器进程，并将客户端连接移交给该专用服务器进程。TNS-12518 表示将客户端连接移交给服务器进程时出现问题。

虽然此错误记录在侦听器日志中，但侦听器只是信使，ORA-12518/TNS-12518 主要与 RDBMS 和操作系统资源有关。

2、处置方式

1、排查步骤

1. 检查您使用的侦听器版本是否与数据库版本兼容。
2. 检查监听日志

Listener Parameter File   /u01/app/19.0.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/rac01/listener/alert/log.xml

3.检查监听日志错误堆栈
寻找堆栈中最低的错误。这是我们必须集中精力并努力解决的错误
4. 检查服务句柄是否是阻塞状态

[oracle@19c01 admin]$ lsnrctl services

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 15-NOV-2023 14:51:16

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=19c01)(PORT=1526)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:4 state:ready
         LOCAL SERVER
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: 19c01, pid: 31678>
         (ADDRESS=(PROTOCOL=tcp)(HOST=19c01)(PORT=28033))
The command completed successfully

2、常见原因

1. processes到达限制，当processes进程数过多，可以在alert日志中看到类似

ORA-00020: maximum number of processes (52) exceeded
 ORA-20 errors will not be written to the alert log for
 the next minute. Please look at trace files to see all
 the ORA-20 errors.

SQL> select * from v$resource_limit;

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION             LIMIT_VALUE              CON_ID
------------------------------ ------------------- --------------- ------------------------------ -------------------- ----------
processes                                       54              56        100                            100                    0
sessions                                        66              66        172                            172                    0

2. 数据库处于启动或关闭过程中

3. 操作系统进程参数限制
   数据库由单个用户操作，通常是“ oracle ”用户。在操作系统级别，用户生成的进程数量是有限制的。而且整个操作系统上运行的进程总数也有限制。

4. 检查内存占用

三、ORA-12537/ORA-12547或TNS-12518

在监听器主目录（包括驻留在 GRID 基础设施/ASM主目录中的SCAN监听器)和数据库主目录由不同操作系统用户拥有的环境中，通过监听器连接、通过DBCA创建数据库或安装数据库软件
和安装数据库时，可能会发生 ORA-12537。

12537, 00000, "TNS:connection closed"
// *Cause: "End of file" condition has been reached; partner has disconnected.
// *Action: None needed; this is an information message.

常见原因

1. 在监听器与数据库所在的ORACLE_HOME不同的环境中,监听所有者(包括SCAN侦听器)可能无法访问数据库主目录中的oracle二进制文件。
2. 数据库主目录中的Oracle二进制文件权限错误
   权限、属主错误都有可能，正确应该是如下

433204 -rwsr-s--x 1 oracle asmadmin 443595488 Sep 25 17:24 oracle

3. RDBMS_HOME/lib的所有权/权限错误
4. CRS用户需要访问的RDBMS目录结构或主目录的权限

四、ORA-12170 / TNS-12170“TNS:发生连接超时

12170, 00000, "TNS:Connect timeout occurred"
// *Cause:  The server shut down because connection establishment or
// communication with a client failed to complete within the allotted time
// interval. This may be a result of network or system delays; or this may
// indicate that a malicious client is trying to cause a Denial of Service
// attack on the server.
// *Action: If the error occurred because of a slow network or system,
// reconfigure one or all of the parameters SQLNET.INBOUND_CONNECT_TIMEOUT,
// SQLNET.SEND_TIMEOUT, SQLNET.RECV_TIMEOUT in sqlnet.ora to larger values.
// If a malicious client is suspected, use the address in sqlnet.log to
// identify the source and restrict access. Note that logged addresses may
// not be reliable as they can be forged (e.g. in TCP/IP).

可能原因
1、错误通常是由于使用、应用程序或配置问题造成的，但在某些情况下，它们可能是由错误问题引起的,在11g版本中，这个错误也可能表示监听未启动

2、对于12c容器数据库中，需要将”_pdb_service_on_root_listener“设置为false。否则连接pdb可能报错ora-12170

3、数据库服务器文件系统占用100%
处置方式

检查每个文件系统占用
df -h 
检查对应文件大小
du -sh *
检查被删除但是还在占用的文件和占用进程
lsof | grep '(deleted)$' | sort -rnk 7

如果删除文件，但空间没有被释放，通常是因为该文件仍然保持打开状态，或者存在其他硬链接。删除该文件不会释放空间，除非删除对该文件打开句柄的进程。
4. 网络延迟与QLNET.INBOUND_CONNECT_TIMEOUT、SQLNET.SEND_TIMEOUT、SQLNET.RECV_TIMEOUT配置，可以适当调大

5. 网络连接中断

6. 网络防火墙和sqlnet白名单

五、ORA-12541: TNS:no listener

原因

12541, 00000, "TNS:no listener"
// *Cause: The connection request could not be completed because the listener
// is not running.
// *Action: Ensure that the supplied destination address matches one of
// the addresses used by the listener - compare the TNSNAMES.ORA entry with
// the appropriate LISTENER.ORA file (or TNSNAV.ORA if the connection is to
// go by way of an Interchange). Start the listener on the remote machine.

监听没启动

六、TNS-12535或ORA-12535或ORA-12170

[oracle@19c01 ~]$ oerr tns 12535
12535, 00000, "TNS:operation timed out"
// *Cause: The requested operation could not be completed within the time out
// period.
// *Action: Look at the documentation on the secondary errors for possible
// remedy. See SQLNET.LOG to find secondary error if not provided explicitly.
// Turn on tracing to gather more information.

TNS-12535 或 ORA-12535 错误通常是与防火墙或慢速网络相关的超时错误。当主机名未在允许的时间内（TCP 或 Oracle）解析为 IP 地址时，也可能会发生这种情况。

本质上，ORA-12535/TNS-12535 是客户端和服务器之间的计时问题

也可能（最不常见）是由于以下文件的“超时”参数设置不正确：

INBOUND_CONNECT_TIMEOUT_<listener_name>

SQLNET.INBOUND_CONNECT_TIMEOUT

处置方式

ping ip(ip  vip scanip)
telnet ip 端口

七、ORA-12514:侦听器当前不知道连接中请求的服务

12535, 00000, "TNS:operation timed out"
// *Cause: The requested operation could not be completed within the time out
// period.
// *Action: Look at the documentation on the secondary errors for possible
// remedy. See SQLNET.LOG to find secondary error if not provided explicitly.
// Turn on tracing to gather more information.
[oracle@19c01 ~]$ oerr ora 12154
12154, 00000, "TNS:could not resolve the connect identifier specified"
// *Cause:  A connection to a database or other service was requested using
// a connect identifier, and the connect identifier specified could not
// be resolved into a connect descriptor using one of the naming methods
// configured. For example, if the type of connect identifier used was a
// net service name then the net service name could not be found in a
// naming method repository, or the repository could not be
// located or reached.
// *Action:
//   - If you are using local naming (TNSNAMES.ORA file):
//      - Make sure that "TNSNAMES" is listed as one of the values of the
//        NAMES.DIRECTORY_PATH parameter in the Oracle Net profile
//        (SQLNET.ORA)
//      - Verify that a TNSNAMES.ORA file exists and is in the proper
//        directory and is accessible.
//      - Check that the net service name used as the connect identifier
//        exists in the TNSNAMES.ORA file.
//      - Make sure there are no syntax errors anywhere in the TNSNAMES.ORA
//        file.  Look for unmatched parentheses or stray characters. Errors
//        in a TNSNAMES.ORA file may make it unusable.
//   - If you are using directory naming:
//      - Verify that "LDAP" is listed as one of the values of the
//        NAMES.DIRETORY_PATH parameter in the Oracle Net profile
//        (SQLNET.ORA).
//      - Verify that the LDAP directory server is up and that it is
//        accessible.
//      - Verify that the net service name or database name used as the
//        connect identifier is configured in the directory.
//      - Verify that the default context being used is correct by
//        specifying a fully qualified net service name or a full LDAP DN
//        as the connect identifier
//   - If you are using easy connect naming:
//      - Verify that "EZCONNECT" is listed as one of the values of the
//        NAMES.DIRETORY_PATH parameter in the Oracle Net profile
//        (SQLNET.ORA).
//      - Make sure the host, port and service name specified
//        are correct.
//      - Try enclosing the connect identifier in quote marks.
//
//   See the Oracle Net Services Administrators Guide or the Oracle
//   operating system specific guide for more information on naming.

最常见的问题

1. 连接串写错
2. sqlnet.ora文件中NAMES.DIRETORY_PATH参数有误