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

相关文章

浅析Java中如何优雅地处理null值

浅析Java中如何优雅地处理null值

《浅析Java中如何优雅地处理null值》这篇文章主要为大家详细介绍了如何结合Lambda表达式和Optional,让Java更优雅地处理null值,感兴趣的小伙伴可以跟随小编一起学习一下... 目录场景 1：不为 null 则执行场景 2：不为 null 则返回，为 null 则返回特定值或抛出异常场景
阅读更多...
深入理解Apache Kafka(分布式流处理平台)

深入理解Apache Kafka(分布式流处理平台)

《深入理解ApacheKafka(分布式流处理平台)》ApacheKafka作为现代分布式系统中的核心中间件,为构建高吞吐量、低延迟的数据管道提供了强大支持,本文将深入探讨Kafka的核心概念、架构... 目录引言一、Apache Kafka概述1.1 什么是Kafka？1.2 Kafka的核心概念二、Ka
阅读更多...
resultMap如何处理复杂映射问题

resultMap如何处理复杂映射问题

《resultMap如何处理复杂映射问题》：本文主要介绍resultMap如何处理复杂映射问题,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录resultMap复杂映射问题Ⅰ 多对一查询：学生——老师Ⅱ 一对多查询：老师——学生总结resultMap复杂映射问题
阅读更多...
解决SpringBoot启动报错:Failed to load property source from location 'classpath:/application.yml'

解决SpringBoot启动报错:Failed to load property source from location 'classpath:/application.yml'

《解决SpringBoot启动报错:Failedtoloadpropertysourcefromlocationclasspath:/application.yml问题》这篇文章主要介绍... 目录在启动SpringBoot项目时报如下错误原因可能是1.yml中语法错误2.yml文件格式是GBK总结在启动S
阅读更多...
idea maven编译报错Java heap space的解决方法

idea maven编译报错Java heap space的解决方法

《ideamaven编译报错Javaheapspace的解决方法》这篇文章主要为大家详细介绍了ideamaven编译报错Javaheapspace的相关解决方法,文中的示例代码讲解详细,感兴趣的... 目录1.增加 Maven 编译的堆内存2. 增加 IntelliJ IDEA 的堆内存3. 优化 Mave
阅读更多...
如何解决mmcv无法安装或安装之后报错问题

如何解决mmcv无法安装或安装之后报错问题

《如何解决mmcv无法安装或安装之后报错问题》：本文主要介绍如何解决mmcv无法安装或安装之后报错问题,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录mmcv无法安装或安装之后报错问题1.当我们运行YOwww.chinasem.cnLO时遇到2.找到下图所示这里3.
阅读更多...
浅谈配置MMCV环境,解决报错,版本不匹配问题

浅谈配置MMCV环境,解决报错,版本不匹配问题

《浅谈配置MMCV环境,解决报错,版本不匹配问题》：本文主要介绍浅谈配置MMCV环境,解决报错,版本不匹配问题,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录配置MMCV环境,解决报错,版本不匹配错误示例正确示例总结配置MMCV环境,解决报错,版本不匹配在col
阅读更多...
Python FastAPI+Celery+RabbitMQ实现分布式图片水印处理系统

Python FastAPI+Celery+RabbitMQ实现分布式图片水印处理系统

《PythonFastAPI+Celery+RabbitMQ实现分布式图片水印处理系统》这篇文章主要为大家详细介绍了PythonFastAPI如何结合Celery以及RabbitMQ实现简单的分布式... 实现思路FastAPI 服务器Celery 任务队列RabbitMQ 作为消息代理定时任务处理完整
阅读更多...
C#使用SQLite进行大数据量高效处理的代码示例

C#使用SQLite进行大数据量高效处理的代码示例

《C#使用SQLite进行大数据量高效处理的代码示例》在软件开发中,高效处理大数据量是一个常见且具有挑战性的任务,SQLite因其零配置、嵌入式、跨平台的特性,成为许多开发者的首选数据库,本文将深入探... 目录前言准备工作数据实体核心技术批量插入：从乌龟到猎豹的蜕变分页查询：加载百万数据异步处理：拒绝界面
阅读更多...
Springboot处理跨域的实现方式(附Demo)

Springboot处理跨域的实现方式(附Demo)

《Springboot处理跨域的实现方式(附Demo)》：本文主要介绍Springboot处理跨域的实现方式(附Demo),具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不... 目录Springboot处理跨域的方式1. 基本知识2. @CrossOrigin3. 全局跨域设置4.
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2