代码改变世界

RAC 10.2.0.5,客户端登陆间断遭遇ORA-12545

2014-05-22 00:00  AlfredZhao  阅读(...)  评论(...编辑  收藏
实验环境:
服务端:OEL 5.7 + Oracle 10.2.0.5 RAC 
客户端:Windows 7 + Oracle 11.2.0.1 Client
 
1.客户端登陆间断遭遇ORA-12545,现象如下:
C:\Users\xiaoyu>sqlplus system/oracle@192.168.1.171/jy.oracle.com

SQL*Plus: Release 11.2.0.1.0 Production on 星期二 5月 20 19:43:52 2014

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

ERROR:
ORA-12545: 因目标主机或对象不存在, 连接失败


请输入用户名:
C:\Users\xiaoyu>sqlplus system/oracle@192.168.1.171/jy.oracle.com

SQL*Plus: Release 11.2.0.1.0 Production on 星期二 5月 20 19:44:03 2014

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


连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> exit
从 Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options 断开

C:\Users\xiaoyu>sqlplus system/oracle@192.168.1.171/jy.oracle.com

SQL*Plus: Release 11.2.0.1.0 Production on 星期二 5月 20 19:44:06 2014

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

ERROR:
ORA-12545: 因目标主机或对象不存在, 连接失败


请输入用户名:
 
2.检查相关配置信息:
2.1.检查网络:
ping 192.168.1.171 -t 
网络没有丢包(实验过程遇到的网络是有丢包的是路由问题,已经事先将网络问题解决,但还报错)
tnsping 192.168.1.171 1000 
也很顺畅
 
2.2.检查RAC节点1主机的/etc/hosts内容:
[oracle@rac1-server ~]$ more /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1               localhost.localdomain localhost
::1             localhost6.localdomain6 localhost6
192.168.1.171   rac1-server
192.168.1.172   rac1-server-vip

192.168.1.173   rac2-server
192.168.1.174   rac2-server-vip

10.10.10.171    rac1-server-priv
10.10.10.173    rac2-server-priv
[oracle@rac1-server ~]$ 
 
2.3.检查确认RAC节点1数据库的各种名字
[oracle@rac1-server admin]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Tue May 20 08:19:43 2014

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> show parameter name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
db_name                              string      jy
db_unique_name                       string      jy
global_names                         boolean     FALSE
instance_name                        string      jy1
lock_name_space                      string
log_file_name_convert                string
service_names                        string      jy.oracle.com
SQL> 
 
2.4.检查RAC节点1主机的监听配置文件:
[oracle@rac1-server ~]$ cd /s01/oracle/product/10.2.0/db_1/network/admin
[oracle@rac1-server admin]$ ls
listener.ora  samples  shrept.lst  tnsnames.ora
[oracle@rac1-server admin]$ more listener.ora
# listener.ora.rac1-server Network Configuration File: /s01/oracle/product/10.2.0/db_1/network/admin/listener.ora.rac1-server
# Generated by Oracle configuration tools.

LISTENER_RAC1-SERVER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-server-vip)(PORT = 1521)(IP = FIRST))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.171)(PORT = 1521)(IP = FIRST))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
    )
  )

[oracle@rac1-server admin]$
 
2.5.检查RAC节点1的监听状态:
[oracle@rac1-server admin]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 20-MAY-2014 07:58:46

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_RAC1-SERVER
Version                   TNSLSNR for Linux: Version 10.2.0.5.0 - Production
Start Date                20-MAY-2014 07:06:32
Uptime                    0 days 0 hr. 52 min. 14 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /s01/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /s01/oracle/product/10.2.0/db_1/network/log/listener_rac1-server.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.172)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.171)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
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 "jy.oracle.com" has 2 instance(s).
  Instance "jy1", status READY, has 2 handler(s) for this service...
  Instance "jy2", status READY, has 1 handler(s) for this service...
Service "jyXDB.oracle.com" has 2 instance(s).
  Instance "jy1", status READY, has 1 handler(s) for this service...
  Instance "jy2", status READY, has 1 handler(s) for this service...
Service "jy_XPT.oracle.com" has 2 instance(s).
  Instance "jy1", status READY, has 2 handler(s) for this service...
  Instance "jy2", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac1-server admin]$  
 
3.解决思路:
3.1查看ora-12545错误号
[oracle@rac1-server admin]$ oerr ora 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@rac1-server admin]$ 
 
3.2检查Windows客户机的hosts配置
添加RAC集群环境对外的public地址和网络名的对应关系:
# Copyright (c) 1993-2009 Microsoft Corp.
#
# This is a sample HOSTS file used by Microsoft TCP/IP for Windows.
#
# This file contains the mappings of IP addresses to host names. Each
# entry should be kept on an individual line. The IP address should
# be placed in the first column followed by the corresponding host name.
# The IP address and the host name should be separated by at least one
# space.
#
# Additionally, comments (such as these) may be inserted on individual
# lines or following the machine name denoted by a '#' symbol.
#
# For example:
#
#      102.54.94.97     rhino.acme.com          # source server
#       38.25.63.10     x.acme.com              # x client host

# localhost name resolution is handled within DNS itself.
#     127.0.0.1       localhost
#     ::1             localhost
#127.0.0.1     webiq-cdn.appspot.com
192.168.1.171   rac1-server
192.168.1.172   rac1-server-vip

192.168.1.173   rac2-server
192.168.1.174   rac2-server-vip
添加成功后测试客户端连接不会再遭遇ORA-12545。
 
4.延伸:
针对RAC环境,客户端tnsnames.ora配置参考:
路径:F:\app\xiaoyu\product\11.2.0\client_1\network\admin\tnsnames.ora
JY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.172)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.174)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = jy.oracle.com)
      (FAILOVER_MODE =
           (TYPE = session)
           (METHOD = basic)
           (RETRIES = 180)
           (DELAY = 5)  
      )
    )
  )
 
5.总结:
客户端连接RAC环境,客户端建议配置hosts文件。否则可能遭遇ORA-12545。