客户端链接RAC报错ORA-12545 的处理

2024-03-02 23:58
文章标签 处理 报错 客户端 链接 rac ora 12545

本文主要是介绍客户端链接RAC报错ORA-12545 的处理，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

客户端链接RAC报错ORA-12545 的处理
转载

连接到RAC数据库的时候经常会出现ORA-12545错误
针对这个问题 Oracle没有认为这个是bug，只是认为是PROBLEM。解决办法可以有两种：修改客户端的hosts和tnsnames.ora或者修改oracle服务器数据库。前者称为客户端解决办法后者称为彻底的解决办法。两者都行。彻底的解决办法可以彻底的解决这个问题，而且对所有的连接都是有效的。不过缺点也是很明显的。需要修改初始化参数，重启实例，重启监听。
 
客户端解决办法：
解决这个问题步骤如下：
1.        修改客户端 tnsnames.ora文件：
RACDB =
(DESCRIPTION =
(ADDRESS_LIST =(ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.32.143.1)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.32.143.2)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = racdb)
    )
)
2.       查看数据库服务器lsnrctl配置：
$ lsnrctl service
LSNRCTL for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Production on 11-JUL-2007 10:57:12
Copyright (c) 1991, 2006, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "dbsr" has 2 instance(s).
Instance "racdb1", status READY, has 2 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=shp_db1)(PORT=1521))
      "DEDICATED" established:87 refused:0 state:ready
         LOCAL SERVER
Instance "racdb2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:43 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=shp_db2)(PORT=1521))
Service "racdb" has 2 instance(s).
Instance "racdb1", status READY, has 2 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=shp_db1)(PORT=1521))
      "DEDICATED" established:87 refused:0 state:ready
         LOCAL SERVER
Instance "racdb2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:43 refused:0 state:ready
         REMOTE SERVER
        (ADDRESS=(PROTOCOL=TCP)(HOST=shp_db2)(PORT=1521))
Service "racdbXDB" has 2 instance(s).
Instance "racdb1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: shp_db1, pid: 250102>
         (ADDRESS=(PROTOCOL=tcp)(HOST=shp_db1)(PORT=32805))
Instance "racdb2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: shp_db2, pid: 185054>
         (ADDRESS=(PROTOCOL=tcp)(HOST=shp_db2)(PORT=32811))
Service "racdb_XPT" has 2 instance(s).
Instance "racdb1", status READY, has 2 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=shp_db1)(PORT=1521))
      "DEDICATED" established:87 refused:0 state:ready
         LOCAL SERVER
Instance "racdb2", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:43 refused:0 state:ready
         REMOTE SERVER
         (ADDRESS=(PROTOCOL=TCP)(HOST=shp_db2)(PORT=1521))
The command completed successfully
3.       修改客户端hosts文件
Windows系统：C:\WINDOWS\system32\drivers\etc\hosts
Linux系统：/etc/hosts
增加相应项：
10.32.143.1                  db1_vip shp_db1
10.32.143.2                  db2_vip shp_db2
此时oracle客户端可以正常通过racdb链接oracle而不报错了。

彻底的解决方法：

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

安装环境：
OS: linux AS4.7
DB: oracle 10.2.0.4
DB_name: orcl
node1: rac1
node2: rac2

首先查看RAC安装完成后默认的各节点的tnsnames.ora、listener.ora配置
node rac1:
[oracle@rac1 admin]$ cat tnsnames.ora
 # tnsnames.ora Network Configuration File: /u01/oracle/product/10g/db1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_ORCL2 =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.lgcns.com)(PORT = 1521))

LISTENER_ORCL1 =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.lgcns.com)(PORT = 1521))

LISTENERS_ORCL =
(ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.lgcns.com)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.lgcns.com)(PORT = 1521))
)
ORCL2 =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.lgcns.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
      (INSTANCE_NAME = orcl2)
    )
)
ORCL1 =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.lgcns.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
      (INSTANCE_NAME = orcl1)
    )
)
ORCL =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.lgcns.com)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.lgcns.com)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
)
[oracle@rac1 admin]$ cat listener.ora
 # listener.ora.rac1 Network Configuration File: /u01/oracle/product/10g/db1/network/admin/listener.ora.rac1
# Generated by Oracle configuration tools.
LISTENER_RAC1 =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.lgcns.com)(PORT = 1521)(IP = FIRST))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521)(IP = FIRST))
    )
)

SID_LIST_LISTENER_RAC1 =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/oracle/product/10g/db1)
      (PROGRAM = extproc)
    )
)

node rac2:
[oracle@rac2 admin]$ cat tnsnames.ora
 # tnsnames.ora.rac2 Network Configuration File: /u01/oracle/product/10g/db1/network/admin/tnsnames.ora.rac2
# Generated by Oracle configuration tools.
LISTENER_ORCL2 =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.lgcns.com)(PORT = 1521))

LISTENER_ORCL1 =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.lgcns.com)(PORT = 1521))

LISTENERS_ORCL =
(ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.lgcns.com)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.lgcns.com)(PORT = 1521))
)
ORCL2 =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.lgcns.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
      (INSTANCE_NAME = orcl2)
    )
)
ORCL1 =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.lgcns.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
      (INSTANCE_NAME = orcl1)
    )
)
ORCL =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.lgcns.com)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.lgcns.com)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
)
[oracle@rac2 admin]$ cat listener.ora
 # listener.ora.rac2 Network Configuration File: /u01/oracle/product/10g/db1/network/admin/listener.ora.rac2
# Generated by Oracle configuration tools.
LISTENER_RAC2 =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.lgcns.com)(PORT = 1521)(IP = FIRST))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.102)(PORT = 1521)(IP = FIRST))
    )
)

SID_LIST_LISTENER_RAC2 =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/oracle/product/10g/db1)
      (PROGRAM = extproc)
    )
)

查看各节点参数的设置：
rac1:
[oracle@rac1 ~]$ export ORACLE_SID=orcl1
[oracle@rac1 ~]$ sqlplus "/as sysdba"
SQL> show parameter list
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string     
remote_listener                   string      LISTENERS_ORCL
rac2:
[oracle@rac1 ~]$ export ORACLE_SID=orcl2
[oracle@rac1 ~]$ sqlplus "/as sysdba"
SQL> show parameter list

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string     
remote_listener                    string      LISTENERS_ORCL

解决步骤：
通过查看我们可以看到，在上面的各节点的tnsnames.ora中都有LISTENERS_ORCL1、LISTENERS_ORCL2的配置，所以我们只需要设置local_listener，remote_listener参数相应的值即可
rac1:
[oracle@rac1 ~]$ export ORACLE_SID=orcl1
[oracle@rac1 ~]$ sqlplus "/as sysdba"
SQL>alter system set remote_listener='' sid='orcl1';
system altered

SQL> alter system set local_listener='LISTENER_ORCL1' sid='orcl1';
system altered
SQL> show parameter list
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      LISTENER_ORCL1
remote_listener                      string   
rac2:
[oracle@rac2 ~]$ export ORACLE_SID=orcl2
[oracle@rac2 ~]$ sqlplus "/as sysdba"
SQL> alter system set remote_listener='' sid='orcl2';
system altered
SQL> alter system set local_listener='LISTENER_ORCL2' sid='orcl2';
system altered
SQL> show parameter list

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      LISTENER_ORCL2
remote_listener                      string   


或者也可以设置local_listener参数下面的值：
racl:
SQL> ALTER SYSTEM SET LOCAL_LISTENER = '(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.111)(PORT = 1521))' SID = 'orcl1';

rac2:
SQL>ALTER SYSTEM SET LOCAL_LISTENER = '(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.112)(PORT = 1521))' SID = 'orcl2';

这样再通过客户端连接就是不会报错了！

metalink文档解释如下：

Subject: RAC Connection Redirected To Wrong Host/IP ORA-12545
Doc ID: Note:364855.1 Type: PROBLEM
Last Revision Date: 23-APR-2007 Status: PUBLISHEDIn this Document
Symptoms
Cause
Solution
References
--------------------------------------------------------------------------------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:local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = <VIP_address>) (PORT = 1521))'
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).
References
Note 235562.1 - Issues affecting Automatic Service Registration
Note 256275.1 - Dynamic Registration Fails On Multiple Network Interface ServerErrors
ORA-12541 TNS:no listener
ORA-12545 Connect failed because target host or object does not existKeywords
'RAC'   'LOCAL_LISTENER'   'IP~ADDRESS'   'REDIRECT'   'VIP'   'PMON'   'RAC'   'SERVICE~REGISTRATION'

这篇关于客户端链接RAC报错ORA-12545 的处理的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/767825。

相关文章

Python使用vllm处理多模态数据的预处理技巧

Python使用vllm处理多模态数据的预处理技巧

《Python使用vllm处理多模态数据的预处理技巧》本文深入探讨了在Python环境下使用vLLM处理多模态数据的预处理技巧,我们将从基础概念出发,详细讲解文本、图像、音频等多模态数据的预处理方法,... 目录1. 背景介绍1.1 目的和范围1.2 预期读者1.3 文档结构概述1.4 术语表1.4.1 核
阅读更多...
Spring Boot @RestControllerAdvice全局异常处理最佳实践

Spring Boot @RestControllerAdvice全局异常处理最佳实践

《SpringBoot@RestControllerAdvice全局异常处理最佳实践》本文详解SpringBoot中通过@RestControllerAdvice实现全局异常处理,强调代码复用、统... 目录前言一、为什么要使用全局异常处理？二、核心注解解析1. @RestControllerAdvice2
阅读更多...
qt5cored.dll报错怎么解决? 电脑qt5cored.dll文件丢失修复技巧

qt5cored.dll报错怎么解决? 电脑qt5cored.dll文件丢失修复技巧

《qt5cored.dll报错怎么解决?电脑qt5cored.dll文件丢失修复技巧》在进行软件安装或运行程序时，有时会遇到由于找不到qt5core.dll,无法继续执行代码，这个问题可能是由于该文... 遇到qt5cored.dll文件错误时，可能会导致基于 Qt 开发的应用程序无法正常运行或启动。这种错
阅读更多...
电脑提示xlstat4.dll丢失怎么修复? xlstat4.dll文件丢失处理办法

电脑提示xlstat4.dll丢失怎么修复? xlstat4.dll文件丢失处理办法

《电脑提示xlstat4.dll丢失怎么修复?xlstat4.dll文件丢失处理办法》长时间使用电脑，大家多少都会遇到类似dll文件丢失的情况，不过，解决这一问题其实并不复杂，下面我们就来看看xls... 在Windows操作系统中，xlstat4.dll是一个重要的动态链接库文件，通常用于支持各种应用程序
阅读更多...
SQL Server数据库死锁处理超详细攻略

SQL Server数据库死锁处理超详细攻略

《SQLServer数据库死锁处理超详细攻略》SQLServer作为主流数据库管理系统,在高并发场景下可能面临死锁问题,影响系统性能和稳定性,这篇文章主要给大家介绍了关于SQLServer数据库死... 目录一、引言二、查询 Sqlserver 中造成死锁的 SPID三、用内置函数查询执行信息1. sp_w
阅读更多...
Java对异常的认识与异常的处理小结

Java对异常的认识与异常的处理小结

《Java对异常的认识与异常的处理小结》Java程序在运行时可能出现的错误或非正常情况称为异常,下面给大家介绍Java对异常的认识与异常的处理,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参... 目录一、认识异常与异常类型。二、异常的处理三、总结 一、认识异常与异常类型。（1）简单定义-什么是
阅读更多...
MYSQL查询结果实现发送给客户端

MYSQL查询结果实现发送给客户端

《MYSQL查询结果实现发送给客户端》：本文主要介绍MYSQL查询结果实现发送给客户端方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录mysql取数据和发数据的流程（边读边发）Sending to clientSending DataLRU(Least Rec
阅读更多...
Golang 日志处理和正则处理的操作方法

Golang 日志处理和正则处理的操作方法

《Golang日志处理和正则处理的操作方法》：本文主要介绍Golang日志处理和正则处理的操作方法,本文通过实例代码给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考... 目录1、logx日志处理1.1、logx简介1.2、日志初始化与配置1.3、常用方法1.4、配合defer
阅读更多...
springboot加载不到nacos配置中心的配置问题处理

springboot加载不到nacos配置中心的配置问题处理

《springboot加载不到nacos配置中心的配置问题处理》：本文主要介绍springboot加载不到nacos配置中心的配置问题处理,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑... 目录springboot加载不到nacos配置中心的配置两种可能Spring Boot 版本Nacos
阅读更多...
Python FastMCP构建MCP服务端与客户端的详细步骤

Python FastMCP构建MCP服务端与客户端的详细步骤

《PythonFastMCP构建MCP服务端与客户端的详细步骤》MCP（Multi-ClientProtocol）是一种用于构建可扩展服务的通信协议框架,本文将使用FastMCP搭建一个支持St... 目录简介环境准备服务端实现（server.py）客户端实现（client.py）运行效果扩展方向常见问题结
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2