RAC连接的问题ORA-12545

最新推荐文章于 2021-05-26 05:58:45 发布

cucuan4341

最新推荐文章于 2021-05-26 05:58:45 发布

阅读量182

点赞数

连接到RAC数据库的时候经常会出现ORA-12545错误，在METALINK上查询了一下，是Oracle的一个小bug。

在远端客户端连接RAC数据库时，通过统一的服务名连接时经常会出现ORA-12545错误。

SQL> CONN NDMAIN@TESTRAC输入口令: ******已连接。
SQL> CONN NDMAIN@TESTRAC输入口令: ******
ERROR:
ORA-12545: 因目标主机或对象不存在, 连接失败

警告: 您不再连接到 ORACLE。
SQL> CONN NDMAIN@TESTRAC输入口令: ******已连接。
SQL> CONN NDMAIN@TESTRAC输入口令: ******已连接。
SQL> CONN NDMAIN@TESTRAC输入口令: ******
ERROR:
ORA-12545: 因目标主机或对象不存在, 连接失败

警告: 您不再连接到 ORACLE。

本地数据库TNSNAMES的配置：

TESTRAC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.198.224)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.198.225)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testrac)
(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 SYS@TESTRAC1 AS SYSDBA输入口令: ****已连接。
SQL> ALTER SYSTEM
2 SET LOCAL_LISTENER = '(ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.198.224)(PORT = 1521))'
3 SID = 'testrac1';

系统已更改。

SQL> CONN SYS@TESTRAC2 AS SYSDBA输入口令: ****已连接。
SQL> ALTER SYSTEM
2 SET LOCAL_LISTENER = '(ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.198.225)(PORT = 1521))'
3 SID = 'testrac2';

系统已更改。

设置之后，再次尝试连接数据库：

SQL> CONN NDMAIN@TESTRAC输入口令: ******已连接。
SQL> CONN NDMAIN@TESTRAC输入口令: ******已连接。
SQL> CONN NDMAIN@TESTRAC输入口令: ******已连接。
SQL> CONN NDMAIN@TESTRAC输入口令: ******已连接。
SQL> CONN NDMAIN@TESTRAC输入口令: ******已连接。
SQL> CONN NDMAIN@TESTRAC输入口令: ******已连接。
SQL> CONN NDMAIN@TESTRAC输入口令: ******已连接。
SQL> CONN NDMAIN@TESTRAC输入口令: ******已连接。
SQL> CONN NDMAIN@TESTRAC输入口令: ******已连接。
SQL> CONN NDMAIN@TESTRAC输入口令: ******已连接。
SQL> CONN NDMAIN@TESTRAC输入口令: ******已连接。
SQL> CONN NDMAIN@TESTRAC输入口令: ******已连接。

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

不过Oracle没有认为这个是bug，只是认为是PROBLEM。

文档一中给出的解决方法可以彻底的解决这个问题，而且对所有的连接都是有效的。
不过缺点也是很明显的。需要修改初始化参数，重启实例，重启监听。
有的时候，这个操作的代价太大了。
这篇文章介绍的方法可以通过只修改客户端hosts文件的方式，来解决这个问题。

客户端的tnsnames.ora中的配置如下：

SQL> host
$ more $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /data/oracle/product/10.2/database/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_TESTRAC2 =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.198.225)(PORT = 1521))

LISTENER_TESTRAC1 =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.198.224)(PORT = 1521))

TESTRAC2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.198.225)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testrac)
(INSTANCE_NAME = testrac2)
)
)

TESTRAC1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.198.224)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testrac)
(INSTANCE_NAME = testrac1)
)
)

LISTENERS_TESTRAC =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.198.224)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.198.225)(PORT = 1521))
)

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

$ exit

SQL> CONN NDMAIN/NDMAIN@TESTRAC已连接。
SQL> CONN NDMAIN/NDMAIN@TESTRAC已连接。
SQL> CONN NDMAIN/NDMAIN@TESTRAC已连接。
SQL> CONN NDMAIN/NDMAIN@TESTRAC
ERROR:
ORA-12545: 因目标主机或对象不存在, 连接失败

警告: 您不再连接到 ORACLE。
SQL> CONN NDMAIN/NDMAIN@TESTRAC
ERROR:
ORA-12545: 因目标主机或对象不存在, 连接失败

SQL> CONN NDMAIN/NDMAIN@TESTRAC已连接。
SQL> CONN NDMAIN/NDMAIN@TESTRAC
ERROR:
ORA-12545: 因目标主机或对象不存在, 连接失败

警告: 您不再连接到 ORACLE。
SQL> CONN NDMAIN/NDMAIN@TESTRAC
ERROR:
ORA-12545: 因目标主机或对象不存在, 连接失败

可以看到，连接失败的概率还是很高的。其实只有在本地hosts文件中加上RAC实例所在服务器的ip和主机名信息，就可以避免这个错误：

SQL> host
$ su -
Password:
Sun Microsystems Inc. SunOS 5.10 Generic January 2005
Sourcing //.profile-EIS.....
root@ahrac1 # vi /etc/hosts
#
# Internet host table
#
172.25.198.224 racnode1-vip
172.25.198.225 racnode2-vip

root@ahrac1 # exit
$ exit

SQL>

下面再次尝试连接RAC数据库：

SQL> CONN NDMAIN/NDMAIN@TESTRAC已连接。
SQL> CONN NDMAIN/NDMAIN@TESTRAC已连接。
SQL> CONN NDMAIN/NDMAIN@TESTRAC已连接。
SQL> CONN NDMAIN/NDMAIN@TESTRAC已连接。
SQL> CONN NDMAIN/NDMAIN@TESTRAC已连接。
SQL> CONN NDMAIN/NDMAIN@TESTRAC已连接。
SQL> CONN NDMAIN/NDMAIN@TESTRAC已连接。
SQL> CONN NDMAIN/NDMAIN@TESTRAC已连接。
SQL> CONN NDMAIN/NDMAIN@TESTRAC已连接。
SQL> CONN NDMAIN/NDMAIN@TESTRAC已连接。
SQL> CONN NDMAIN/NDMAIN@TESTRAC已连接。
SQL> CONN NDMAIN/NDMAIN@TESTRAC已连接。

问题已经解决。不过由于只修改当前客户端的配置，所以这种方法也只对当前客户端有效。

http://yangtingkun.itpub.net/post/468/273645

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Applies to:

Oracle Net Services - Version: 9.1 to 10.2
This problem can occur on any platform.

Symptoms

When we try to connect to a RAC service name we sometimes get redirected by the first node's listener to the public address/hostname of the second node instead of its VIP address. An ORA-12545 error may be generated if that public hostname is not configured in DNS.

We were expecting the connection to eventually be redirected to the VIP of the other node.

Cause

The Database on one RAC node remote registers with the wrong local IP address to the listener on the other RAC node (e.g. the public IP address instead of the wanted VIP address).

The PMON process handles database registration to the local and remote listeners. For remote listeners registration PMON will have to find out what is the IP address of the local system in order to present it to the remote listener as database contact address.

In the default Oracle configuration, for hosts which have more than one IP address configured on the network interfaces, it is undefined which IP address will be selected for remote registration.

Solution

Modify the local_listener database parameter to point to the local VIP address. For the parameter value use either an alias name which contains in the DESCRIPTION field only the VIP address or use an explicit connection statement like the following:

alter system set LOCAL_LISTENER="(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521))" scope=both sid='instance_name';

Where "instance_name" is the unique instance name. Issue this statement for all instances in the cluster.

The LOCAL_LISTENER database parameter will give PMON a hint in respect of which IP address it should use for remote registration with other nodes' listener(s).

======================================================

2.1 ORA-12514 errors connecting to registered instances
-------------------------------------------------------

When attempting to connect to Oracle8i using a connect descriptor containing a
SERVICE_NAME entry, you may receive the following error:

ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect
descriptor

The error means that the client has connected to the Listener but is
requesting a connection to SERVICE_NAME that the Listener does not have a
service handler for. This is usually not a problem with Service Registration,
but since the SERVICE_NAME entry that the client is attempting to connect to
is almost always a dynamically registered service, it needs to be addressed
here.

There are several reasons that error ORA-12514 might occur:

1) The instance that the client is attempting to connect to is not registered
   with the Listener because the instance cannot locate the correct Listener
   address in order to register itself.

   If the LOCAL_LISTENER parameter is present in the INIT.ORA file, it will
   attempt to resolve to an address. The instance will attempt to connect to
   a Listener on that address to register itself.

   If the LOCAL_LISTENER parameter is not present in the INIT.ORA file, the
   instance will attempt to connect to the Default Listener using the default
   address of TCP/IP port 1521 or IPC key PNPKEY.

2) The instance has registered with the listener, but the SERVICE_NAME that
   the client is attempting to connect to does not match the SERVICE that
   PMON has registered with the Listener.

   The following steps show which SERVICE_NAME is registering with the
   Listener:

- Run the listener control utility (lsnrctl).
- At the LSNRCTL prompt, type "set displaymode verbose" and press Enter.
- At the LSNRCTL prompt, type "services" and press Enter.

Ensure that the TNSNAMES.ORA entry for the SERVICE_NAME parameter exactly
matches a service that is registered with the Listener.

3) The instance is down.

One of the benefits of using service registration is that the Listener is
aware of the state of the instance.

When the instance is up, it registers itself with the Listener, allowing
the Listener to service incoming requests for it.

When the instance is down, it will not be registered with the Listener. If
the instance is not registered with the Listener, the Listener will refuse
incoming requests for it, and clients receive error ORA-12514.

4) The instance and Listener are both up, but the instance has not yet
   registered with the Listener.

When the instance is started, it attempts to connect to register with the
local Listener. It continues to check for the Listener about once every
60 seconds.

If the Listener is up when the instance is started, service registration
should take place as the instance starts. If the Listener comes up after
the instance, it will take up to a minute or so for the instance to
register itself with the Listener. This is usually not an issue, but if
the Listener is stopped then started, users may fail to connect until the
the instance successfully re-registers with the Listener.

You can check to see whether the instance has registered itself using the
lsnrctl commands described in 2).

To resolve the ORA-12514 errors, you need to either resolve the registration
problems, resolve the problems with the SERVICE_NAME not matching what the name
that is actually registering with the listener, or define a static service
handler in the LISTENER.ORA file and modify the TNSNAMES.ORA file to connect
to the newly defined SID instead of the SERVICE_NAME.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11134237/viewspace-625496/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11134237/viewspace-625496/

cucuan4341

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
RAC连接的问题ORA-12545

连接到RAC数据库的时候经常会出现ORA-12545错误，在METALINK上查询了一下，是Oracle的一个小bug。在远端客户端连接RAC数据库时，通过统一的服务名连接时经常会出现ORA-12545错误。SQL&gt...
复制链接

扫一扫