记一次离奇的TNS-12545 TNS-12560 TNS-00515

叁歲伎倆 2022-08-27 13:53 228阅读 0赞

最近reportDB监听无法随系统自启动，现象比较怪异。因为该服务器上的另一个实例的监听可以正常启动，这个不能自启动实例的监听手动启动又是正常的。因此记下这次离奇暂未找到原因的故障。

1、故障现象

Starting CRON daemondone
    Oracle 10g auto start/stop
    Startup "USMTHLY" listener.
    
    LSNRCTL for Linux: Version 10.2.0.3.0 - Production on 08-JUL-2014 17:05:49
    
    Copyright (c) 1991, 2006, Oracle.  All rights reserved.
    
    Starting /users/oracle/OraHome10g/bin/tnslsnr: please wait...
    
    TNSLSNR for Linux: Version 10.2.0.3.0 - Production
    System parameter file is /users/oracle/OraHome10g/network/admin/listener.ora
    Log messages written to /users/oracle/OraHome10g/network/log/listener_usmthly.log
    Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.101.1.106)(PORT=1520)))
    TNS-12545: Connect failed because target host or object does not exist
     TNS-12560: TNS:protocol adapter error
      TNS-00515: Connect failed because target host or object does not exist
       Linux Error: 99: Cannot assign requested address
    
    #上面的错误信息了来自系统启动日志，同样出现在该实例的监听器日志里。   
    
    #故障环境
    oracle@sysreportDB:/users/oracle> cat /etc/issue
    
    Welcome to SUSE Linux Enterprise Server 10 SP4  (x86_64) - Kernel \r (\l).
    
    oracle@sysreportDB:/users/oracle> sqlplus -v
    
    SQL*Plus: Release 10.2.0.3.0 - Production
    
    #服务器启动后，检查数据库实例，2个实例都已成功启动
    oracle@sysreportDB:~> ps -ef | grep pmon | grep -v grep
    oracle    4916     1  0 Jul08 ?        00:00:00 ora_pmon_USMTHLY
    oracle    5605     1  0 Jul08 ?        00:00:00 ora_pmon_USBO2
    
    oracle@sysreportDB:~> ps -ef | grep lsnr| grep -v grep      #仅仅只有usbo2的监听被启动
    oracle    5597     1  0 Jul09 ?        00:00:00 /users/oracle/OraHome10g/bin/tnslsnr listener_USBO2 -inherit

2、故障分析

oracle@sysreportDB:~> oerr tns 12545
    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.
    oracle@sysreportDB:~> oerr tns 12560
    12560, 00000, "TNS:protocol adapter error"
    // *Cause: A generic protocol adapter error occurred.
    // *Action: Check addresses used for proper protocol specification. Before
    // reporting this error, look at the error stack and check for lower level
    // transport errors. For further details, turn on tracing and reexecute the
    // operation. Turn off tracing when the operation is complete.
    oracle@sysreportDB:~> oerr tns 00515
    00515, 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.)
    
    #上面的三个错误号描述信息表明该故障与主机名或IP地址有关，需要检查是否为有效的IP或主机名
    #下面查看监听器及TNSNAMES的配置
    oracle@sysreportDB:/users/oracle/OraHome10g/network/admin> more listener.ora
    SID_LIST_LISTENER_USMTHLY =
       (SID_LIST =
        (SID_DESC =
            (sid_name=USMTHLY)
            (oracle_home = /users/oracle/OraHome10g)
        )
      )
    
    LISTENER_USMTHLY =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = TCP)(HOST = 10.101.1.106)(PORT = 1520))
          )
        )
      )
    
    SID_LIST_LISTENER_USBO2 =
       (SID_LIST =
        (SID_DESC =
            (sid_name=USBO2)
            (oracle_home = /users/oracle/OraHome10g)
        )
      )
    
    LISTENER_USBO2 =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = TCP)(HOST = 10.101.1.106)(PORT = 1504))
          )
        )
      )
    
    oracle@sysreportDB:/users/oracle/OraHome10g/network/admin> more tnsnames.ora
    USMTHLY=
      (DESCRIPTION=
        (ADDRESS=
          (PROTOCOL=TCP)
          (HOST=10.101.1.106)
          (PORT=1520)
        )
        (CONNECT_DATA=
          (SERVICE_NAME=USMTHLY)
        )
      )
    
    USBO2=
      (DESCRIPTION=
        (ADDRESS=
          (PROTOCOL=TCP)
          (HOST=10.101.1.106)
          (PORT=1504)
        )
        (CONNECT_DATA=
          (SERVICE_NAME=USBO2)
        )
      )
    #从上面的配置来看，配置无异常。
    #下面查看本机host及IP信息，从下面的描述来看，主机host配置无异常
    oracle@sysreportDB:~> more /etc/hosts
    127.0.0.1       localhost
    
    # special IPv6 addresses
    ::1             localhost ipv6-localhost ipv6-loopback
    
    fe00::0         ipv6-localnet
    ff00::0         ipv6-mcastprefix
    ff02::1         ipv6-allnodes
    ff02::2         ipv6-allrouters
    ff02::3         ipv6-allhosts
    10.101.1.106    sysreportDB.2goasp.com sysreportDB  
    
    oracle@sysreportDB:~> hostname
    sysreportDB
    # Author : Leshami
    # Blog   : http://blog.csdn.net/leshami
    
    #下面是其IP信息
    oracle@sysreportDB:/var/log> ifconfig
    eth0      Link encap:Ethernet  HWaddr 00:0C:29:70:82:63
              inet addr:10.101.1.106  Bcast:10.201.1.255  Mask:255.255.255.0
              UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
              RX packets:5763651 errors:0 dropped:0 overruns:0 frame:0
              TX packets:802254 errors:0 dropped:0 overruns:0 carrier:0
              collisions:0 txqueuelen:1000
              RX bytes:8102327746 (7726.9 Mb)  TX bytes:88597013 (84.4 Mb)
    
    lo        Link encap:Local Loopback      #有环路能正常TX,RX
              inet addr:127.0.0.1  Mask:255.0.0.0
              UP LOOPBACK RUNNING  MTU:16436  Metric:1
              RX packets:92972 errors:0 dropped:0 overruns:0 frame:0
              TX packets:92972 errors:0 dropped:0 overruns:0 carrier:0
              collisions:0 txqueuelen:0
              RX bytes:4993723 (4.7 Mb)  TX bytes:4993723 (4.7 Mb)
    
    #尝试手动启动监听，手动启动监听无异常    
    oracle@sysreportDB:/var/log> lsnrctl start listener_USMTHLY
    
    LSNRCTL for Linux: Version 10.2.0.3.0 - Production on 08-JUL-2014 17:10:49
    
    Copyright (c) 1991, 2006, Oracle.  All rights reserved.
    
    Starting /users/oracle/OraHome10g/bin/tnslsnr: please wait...
    
    TNSLSNR for Linux: Version 10.2.0.3.0 - Production
    System parameter file is /users/oracle/OraHome10g/network/admin/listener.ora
    Log messages written to /users/oracle/OraHome10g/network/log/listener_usmthly.log
    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sysreportDB.2goasp.com)(PORT=1520)))
    
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sysreportDB.2goasp.com)(PORT=1520)))
    STATUS of the LISTENER
    ------------------------
    Alias                     listener_USMTHLY
    Version                   TNSLSNR for Linux: Version 10.2.0.3.0 - Production
    Start Date                08-JUL-2014 17:10:50
    Uptime                    0 days 0 hr. 0 min. 0 sec
    Trace Level               off
    Security                  ON: Local OS Authentication
    SNMP                      OFF
    Listener Parameter File   /users/oracle/OraHome10g/network/admin/listener.ora
    Listener Log File         /users/oracle/OraHome10g/network/log/listener_usmthly.log
    Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sysreportDB.2goasp.com)(PORT=1520)))
    Services Summary...
    Service "USMTHLY" has 1 instance(s).
      Instance "USMTHLY", status UNKNOWN, has 1 handler(s) for this service...
    The command completed successfully
    
    #通过上面的测试可以看出，监听器能够正常启动也就是说监听器的配置应该是OK的。
    
    #另外一点发现，系统启动日志里边有127.0.0.2的环路，不知道怎么会有127.0.0.2
    Setting up network interfaces:
        lo
        lo        IP address: 127.0.0.1/8
                  IP address: 127.0.0.2/8
    
    oracle@sysreportDB:/var/log> ping 127.0.0.2  #环路127.0.0.2 OK
    PING 127.0.0.2 (127.0.0.2) 56(84) bytes of data.
    64 bytes from 127.0.0.2: icmp_seq=1 ttl=64 time=0.034 ms

3、故障解决

#对于上面的问题实在是百思不得其解，就差使用sqlnet trace。
    #下面尝试将监听器配置文件内IP地址使用主机名来代替，竟然成功鸟。
    #如下2种方式都可以将监听随系统启动而启动，一个是主机名，一个是full主机名
    
    #LISTENER_USMTHLY =
    #  (DESCRIPTION_LIST =
    #    (DESCRIPTION =
    #      (ADDRESS_LIST =
    #        (ADDRESS = (PROTOCOL = TCP)(HOST = sysreportDB)(PORT = 1520))
    #      )
    #    )
    #  )
    
    LISTENER_USMTHLY =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = TCP)(HOST = sysreportDB.2goasp.com)(PORT = 1520))
          )
        )
      )
    
    #高兴之余，查看系统日志，汗，还是有错误，这次的错误是TNS-12543，目标主机不可达。 
    #怀疑是否hosts文件中是否存在异常字符，于是尝试重建Hosts文件，重建后错误消息依旧如下。
    Starting CRON daemondone
    Oracle 10g auto start/stop
    Starting Oracle10g: Startup "USMTHLY" listener.
    
    LSNRCTL for Linux: Version 10.2.0.3.0 - Production on 09-JUL-2014 17:15:47
    
    Copyright (c) 1991, 2006, Oracle.  All rights reserved.
    
    Starting /users/oracle/OraHome10g/bin/tnslsnr: please wait...
    
    TNSLSNR for Linux: Version 10.2.0.3.0 - Production
    System parameter file is /users/oracle/OraHome10g/network/admin/listener.ora
    Log messages written to /users/oracle/OraHome10g/network/log/listener_usmthly.log
    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sysreportDB.2goasp.com)(PORT=1520)))
    
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sysreportDB)(PORT=1520)))
    TNS-12543: TNS:destination host unreachable
     TNS-12560: TNS:protocol adapter error
      TNS-00513: Destination host unreachable
       Linux Error: 101: Network is unreachable
    
    #最终，此题暂时无解，没有找到原因，不影响自启动而已。

4、小结  
a、对于TNS-12545，TNS-00515应首先考虑是否使用了合适的IP，主机名，应检查服务器host文件，网络配置等  
b、Step a检查完后应考虑是否正确的配置了监听以及tnsnames  
c、对于上述该类错误，应同时检查及分析监听器日志     
d、对于客户端出现TNS-12545，TNS-00515等，考虑客户端能否将主机转换成对应的IP(客户端tnsnames.ora使用主机名的情形时)  
e、有关Oracle网络配置相关可以参考：[http://blog.csdn.net/leshami/article/category/828434][http_blog.csdn.net_leshami_article_category_828434]

[http_blog.csdn.net_leshami_article_category_828434]: http://blog.csdn.net/leshami/article/category/828434