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

相关文章

IDEA编译报错“java: 常量字符串过长”的原因及解决方法

IDEA编译报错“java: 常量字符串过长”的原因及解决方法

《IDEA编译报错“java:常量字符串过长”的原因及解决方法》今天在开发过程中,由于尝试将一个文件的Base64字符串设置为常量,结果导致IDEA编译的时候出现了如下报错java:常量字符串过长,... 目录一、问题描述二、问题原因2.1 理论角度2.2 源码角度三、解决方案解决方案①：StringBui
阅读更多...
使用Python快速实现链接转word文档

使用Python快速实现链接转word文档

《使用Python快速实现链接转word文档》这篇文章主要为大家详细介绍了如何使用Python快速实现链接转word文档功能,文中的示例代码讲解详细,感兴趣的小伙伴可以跟随小编一起学习一下... 演示代码展示from newspaper import Articlefrom docx import
阅读更多...
Python Jupyter Notebook导包报错问题及解决

Python Jupyter Notebook导包报错问题及解决

《PythonJupyterNotebook导包报错问题及解决》在conda环境中安装包后,JupyterNotebook导入时出现ImportError,可能是由于包版本不对应或版本太高,解决方... 目录问题解决方法重新安装Jupyter NoteBook 更改Kernel总结问题在conda上安装了
阅读更多...
Python安装时常见报错以及解决方案

Python安装时常见报错以及解决方案

《Python安装时常见报错以及解决方案》：本文主要介绍在安装Python、配置环境变量、使用pip以及运行Python脚本时常见的错误及其解决方案,文中介绍的非常详细,需要的朋友可以参考下... 目录一、安装 python 时常见报错及解决方案（一）安装包下载失败（二）权限不足二、配置环境变量时常见报错及
阅读更多...
使用C++将处理后的信号保存为PNG和TIFF格式

使用C++将处理后的信号保存为PNG和TIFF格式

《使用C++将处理后的信号保存为PNG和TIFF格式》在信号处理领域,我们常常需要将处理结果以图像的形式保存下来,方便后续分析和展示,C++提供了多种库来处理图像数据,本文将介绍如何使用stb_ima... 目录1. PNG格式保存使用stb_imagephp_write库1.1 安装和包含库1.2 代码解
阅读更多...
使用Java实现获取客户端IP地址

使用Java实现获取客户端IP地址

《使用Java实现获取客户端IP地址》这篇文章主要为大家详细介绍了如何使用Java实现获取客户端IP地址,文中的示例代码讲解详细,感兴趣的小伙伴可以跟随小编一起学习一下... 首先是获取 IP，直接上代码import org.springframework.web.context.request.Requ
阅读更多...
C#使用DeepSeek API实现自然语言处理,文本分类和情感分析

C#使用DeepSeek API实现自然语言处理,文本分类和情感分析

《C#使用DeepSeekAPI实现自然语言处理,文本分类和情感分析》在C#中使用DeepSeekAPI可以实现多种功能,例如自然语言处理、文本分类、情感分析等,本文主要为大家介绍了具体实现步骤,... 目录准备工作文本生成文本分类问答系统代码生成翻译功能文本摘要文本校对图像描述生成总结在C#中使用Deep
阅读更多...
MySQL报错sql_mode=only_full_group_by的问题解决

MySQL报错sql_mode=only_full_group_by的问题解决

《MySQL报错sql_mode=only_full_group_by的问题解决》本文主要介绍了MySQL报错sql_mode=only_full_group_by的问题解决,文中通过示例代码介绍的非... 目录报错信息DataGrip 报错还原Navicat 报错还原报错原因解决方案查看当前 sql mo
阅读更多...
Spring Boot 整合 ShedLock 处理定时任务重复执行的问题小结

Spring Boot 整合 ShedLock 处理定时任务重复执行的问题小结

《SpringBoot整合ShedLock处理定时任务重复执行的问题小结》ShedLock是解决分布式系统中定时任务重复执行问题的Java库,通过在数据库中加锁,确保只有一个节点在指定时间执行... 目录前言什么是 ShedLock？ShedLock 的工作原理：定时任务重复执行China编程的问题使用 Shed
阅读更多...
Redis如何使用zset处理排行榜和计数问题

Redis如何使用zset处理排行榜和计数问题

《Redis如何使用zset处理排行榜和计数问题》Redis的ZSET数据结构非常适合处理排行榜和计数问题,它可以在高并发的点赞业务中高效地管理点赞的排名,并且由于ZSET的排序特性,可以轻松实现根据... 目录Redis使用zset处理排行榜和计数业务逻辑ZSET 数据结构优化高并发的点赞操作ZSET 结
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2