ora-12545连接错误解决一例

在远端客户端连接RAC数据库时，通过统一的服务名连接时经常会出现ORA-12545错误。在METALINK上查询了一下，竟是Oracle的一个小bug。环境：ORACLE11GR2RAC数据库

在远端客户端连接RAC数据库时，通过统一的服务名连接时经常会出现ORA-12545错误。在METALINK上查询了一下，竟是Oracle的一个小bug。
环境：ORACLE 11G R2 RAC数据库，两节点。
现象：
[oracle@ted35 ~]$sqlplus /nolog
 
SQL*Plus: Release 11.2.0.1.0 Production on 星期五 6月 15 11:57:00 2012
 
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
 
SQL> conn sys/ted@123e@prodb as sysdba;
ERROR:
ORA-12545: 因目标主机或对象不存在, 连接失败

故障分析：用tnsping检查一下，发现可以ping通，这是因为tnsping只检查IP地址和端口是否能连通，至于数据库实例状态，监听注册了哪些服务这些，它是不检查的。

我们看一下本地数据库TNSNAMES的配置：
TESTRAC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.58.12.124)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.58.12.125)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = 10.58.12.124)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
)
)

Oracle在文档Note:364855.1：RAC Connection Redirected To Wrong Host/IP ORA-12545中进行了描述，并给出了解决方法：修改数据库中的初始化参数LOCAL_LISTENER（注：这种方法在修改初始化参数后，需要重启监听，香港空间，服务器空间，重启实例）
SQL>conn / as sysdba;
    Connected.
SQL> ALTER SYSTEM SET LOCAL_LISTENER = '(ADDRESS = (PROTOCOL = TCP)(HOST = 10.58.12.124)(PORT = 1521)) 'SID = 'prodb1';
系统已更改。
登录另外一个节点
SQL>conn / as sysdba;
    Connected.
SQL> ALTER SYSTEM SET LOCAL_LISTENER = '(ADDRESS = (PROTOCOL = TCP)(HOST = 10.58.12.125)(PORT = 1521))' SID = 'prodb2';
系统已更改。

设置之后，再次尝试连接数据库：
SQL> conn sys/ted@123e@prodb as sysdba;
     Connected.
SQL> conn sys/ted@789e@prodb as sysdba;
     Connected.

修改之后，没有再次出现同样的错误。

我们也可以通过只修改客户端hosts文件的方式，来解决这个问题。
首先在linux主机上修改HOSTS文件
root@prodb1 # vi /etc/hosts
#
10.58.12.120            prodb1-public   # Public
10.58.12.124            prodb1          # Virtual
23.23.23.120            prodb1-priv     # Private

10.58.12.121            prodb2-public   # Public
10.58.12.125            prodb2          # Virtual
23.23.23.121            prodb2-priv     # Private

10.58.12.126            prodb-scan      # SCAN

然后客户端的tnsnames.ora中的配置如下：
$ more $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /data/oracle/product/11.2/database/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENERS_prodb =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.58.12.124)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.58.12.125)(PORT = 1521))
)
LISTENER_prodb1 =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.58.12.124)(PORT = 1521))
LISTENER_prodb2 =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.58.12.125)(PORT = 1521))

prodb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.58.12.124)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.58.12.125)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prodb)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
)
)
)
prodb1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.58.12.124)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prodb)
(INSTANCE_NAME = prodb1)
)
)
prodb2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.58.12.125)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prodb)
(INSTANCE_NAME = prodb2)
)
)

下面尝试连接远端RAC数据库：

SQL> conn sys/ted@123e@prodb as sysdba;已连接。
SQL> conn sys/ted@789e@prodb as sysdba;已连接。

其实在本地hosts文件中加上RAC实例所在服务器的ip和主机名信息，香港虚拟主机，就可以避免这个错误。
另外注意在RAC环境中，任何修改都一定要谨慎。RAC 环境一旦安装好后，主机名就不能随意修改，除非先删除节点，修改Hostname，再添加节点。还有一点主机名必须和public名一致，这一点也非常重要。

本文出自 “滴水穿石” 博客，请务必保留此出处