客户端链接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。

相关文章

无人叉车3d激光slam多房间建图定位异常处理方案-墙体画线地图切分方案

无人叉车3d激光slam多房间建图定位异常处理方案-墙体画线地图切分方案

墙体画线地图切分方案 针对问题：墙体两侧特征混淆误匹配，导致建图和定位偏差，表现为过门跳变、外月台走歪等 ·解决思路：预期的根治方案IGICP需要较长时间完成上线，先使用切分地图的工程化方案，即墙体两侧切分为不同地图，在某一侧只使用该侧地图进行定位 方案思路 切分原理：切分地图基于关键帧位置，而非点云。 理论基础：光照是直线的，一帧点云必定只能照射到墙的一侧，无法同时照到两侧实践考虑：关
阅读更多...
安卓链接正常显示，ios#符被转义%23导致链接访问404

安卓链接正常显示，ios#符被转义%23导致链接访问404

原因分析： url中含有特殊字符 中文未编码 都有可能导致URL转换失败，所以需要对url编码处理  如下： guard let allowUrl = webUrl.addingPercentEncoding(withAllowedCharacters: .urlQueryAllowed) else {return} 后面发现当url中有#号时，会被误伤转义为%23，导致链接无法访问
阅读更多...
【生成模型系列（初级）】嵌入（Embedding）方程——自然语言处理的数学灵魂【通俗理解】

【生成模型系列（初级）】嵌入（Embedding）方程——自然语言处理的数学灵魂【通俗理解】

【通俗理解】嵌入（Embedding）方程——自然语言处理的数学灵魂 关键词提炼 #嵌入方程 #自然语言处理 #词向量 #机器学习 #神经网络 #向量空间模型 #Siri #Google翻译 #AlexNet 第一节：嵌入方程的类比与核心概念【尽可能通俗】 嵌入方程可以被看作是自然语言处理中的“翻译机”，它将文本中的单词或短语转换成计算机能够理解的数学形式，即向量。 正如翻译机将一种语言
阅读更多...
Thymeleaf：生成静态文件及异常处理java.lang.NoClassDefFoundError: ognl/PropertyAccessor

Thymeleaf：生成静态文件及异常处理java.lang.NoClassDefFoundError: ognl/PropertyAccessor

我们需要引入包： <dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-thymeleaf</artifactId></dependency><dependency><groupId>org.springframework</groupId><artifactId>sp
阅读更多...
Java Websocket实例【服务端与客户端实现全双工通讯】

Java Websocket实例【服务端与客户端实现全双工通讯】

Java Websocket实例【服务端与客户端实现全双工通讯】 现很多网站为了实现即时通讯，所用的技术都是轮询(polling)。轮询是在特定的的时间间隔（如每1秒），由浏览器对服务器发 出HTTP request，然后由服务器返回最新的数据给客服端的浏览器。这种传统的HTTP request 的模式带来很明显的缺点 – 浏 览器需要不断的向服务器发出请求，然而HTTP
阅读更多...
jenkins 插件执行shell命令时，提示“Command not found”处理方法

jenkins 插件执行shell命令时，提示“Command not found”处理方法

首先提示找不到“Command not found，可能我们第一反应是查看目标机器是否已支持该命令，不过如果相信能找到这里来的朋友估计遇到的跟我一样，其实目标机器是没有问题的通过一些远程工具执行shell命令是可以执行。奇怪的就是通过jenkinsSSH插件无法执行，经一番折腾各种搜索发现是jenkins没有加载/etc/profile导致。 【解决办法】： 需要在jenkins调用shell脚
阅读更多...
Jenkins 插件 地址证书报错问题解决思路

Jenkins 插件 地址证书报错问题解决思路

问题提示摘要： SunCertPathBuilderException: unable to find valid certification path to requested target...... 网上很多的解决方式是更新站点的地址，我这里修改了一个日本的地址（清华镜像也好），其实发现是解决不了上述的报错问题的，其实，最终拉去插件的时候，会提示证书的问题，几经周折找到了其中一遍博文
阅读更多...
每日一练7：简写单词（含链接）

每日一练7：简写单词（含链接）

1.链接 简写单词_牛客题霸_牛客网 2.题目 3.代码1（错误经验） #include <iostream>#include <string>using namespace std;int main() {string s;string ret;int count = 0;while(cin >> s)for(auto a : s){if(count == 0){if( a <=
阅读更多...
【Python报错已解决】AttributeError: ‘list‘ object has no attribute ‘text‘

【Python报错已解决】AttributeError: ‘list‘ object has no attribute ‘text‘

🎬 鸽芷咕：个人主页  🔥 个人专栏: 《C++干货基地》《粉丝福利》 ⛺️生活的理想，就是为了理想的生活! 文章目录 前言一、问题描述1.1 报错示例1.2 报错分析1.3 解决思路 二、解决方法2.1 方法一：检查属性名2.2 步骤二：访问列表元素的属性 三、其他解决方法四、总结 前言 在Python编程中，属性错误（At
阅读更多...
明明的随机数处理问题分析与解决方案

明明的随机数处理问题分析与解决方案

明明的随机数处理问题分析与解决方案 引言问题描述解决方案数据结构设计具体步骤伪代码C语言实现详细解释读取输入去重操作排序操作输出结果复杂度分析 引言 明明生成了N个1到500之间的随机整数，我们需要对这些整数进行处理，删去重复的数字，然后进行排序并输出结果。本文将详细讲解如何通过算法、数据结构以及C语言来解决这个问题。我们将会使用数组和哈希表来实现去重操作，再利用排序算法对结果
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2