客户端使用SCNAIP连接11G RAC数据库报错ORA

2023-05-08 01:36| 来源: 网络整理| 查看: 265

客户端使用SCNAIP连接11G RAC数据库报错ORA-12545解决原创

wb94a78wq170rt0 2023-05-03 02:20:36 博主文章分类：ORACLE 故障排查 ©著作权

文章标签 11G RAC ORA-12545 SCNAIP oracle TCP 文章分类 HarmonyOS 后端开发

©著作权归作者所有：来自51CTO博客作者wb94a78wq170rt0的原创作品，请联系作者获取转载授权，否则将追究法律责任

客户端配置及报错：

[oracle@bys3 admin]$ cat tnsnames.ora bysrac = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.228)(PORT = 1521)) --其中HOST = 192.168.1.228 这里的IP为RAC的SCANIP。 (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = bysrac) ) ) [oracle@bys3 admin]$ tnsping bysrac TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 01-JAN-2014 20:25:19 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.228)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = bysrac))) OK (10 msec) --tnsping可以正常联通。

客户端的使用sqlplus bys/bys@bysrac登陆时报错：ORA-12545: Connect failed because target host or object does not exist

[oracle@bys3 admin]$ sqlplus bys/bys @bysrac SQL*Plus: Release 11.2.0.4.0 Production on Wed Jan 1 20:10:31 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. ERROR: ORA-12545: Connect failed because target host or object does not exist ################

解决方法1：修改RAC的local_listener参数，将参数值中HOST=的值改为当前节点的VIP或者scanip

解决方法2：另一解决方法是在客户端的hosts文本中配置VIP/SCAN IP的解析条目

注意：HOST主机参数为当前节点vip的

ip地址时，客户端通过SCANIP或者VIP都可以连接到RAC数据库库

--我这里就是修改为VIP的IP。--其实应该修改为SCANIP，客户端统一使用SCANIP来连接。

HOST主机参数为scanip地址，则修改完参数后，scanip能够正常使用，但如果有客户端配置使用vip的话，连接时则会出现错误：ORA-12514: TNS:listener does not currently know of service requested in connect descriptor。

具体修改步骤：

查看RAC的SCANIP状态及监听状态

[oracle@bysrac1 ~]$ su - grid Password: [grid@bysrac1 ~]$ srvctl config scan SCAN name: bysrac-cluster-scan, Network: 1/192.168.1.128/255.255.255.128/eth0 SCAN VIP name: scan1, IP: /bysrac-cluster/192.168.1.228 [grid@bysrac1 ~]$ srvctl status scan SCAN VIP scan1 is enabled SCAN VIP scan1 is running on node bysrac1 [grid@bysrac1 ~]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 01-JAN-2014 20:10:13 Copyright (c) 1991, 2009, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production Start Date 28-DEC-2013 20:06:56 Uptime 4 days 0 hr. 3 min. 17 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /g01/app/11.2.0/grid/network/admin/listener.ora Listener Log File /g01/app/grid/diag/tnslsnr/bysrac1/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.226)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.221)(PORT=1521))) Services Summary... Service "+ASM" has 1 instance(s). Instance "+ASM1", status READY, has 1 handler(s) for this service... Service "bysrac" has 1 instance(s). Instance "bysrac1", status READY, has 1 handler(s) for this service... Service "caiwu" has 1 instance(s). Instance "bysrac1", status READY, has 1 handler(s) for this service... Service "jiaoyi" has 1 instance(s). Instance "bysrac1", status READY, has 1 handler(s) for this service... The command completed successfully [grid@bysrac1 ~]$ exit [oracle@bysrac1 ~]$ cat /etc/hosts # Do not remove the following line, or various programs # that require network functionality will fail. 127.0.0.1 localhost.localdomain localhost ::1 localhost6.localdomain6 localhost6 192.168.1.221 bysrac1 bysrac1.bys.com 192.168.1.226 bysrac1-vip 192.168.1.222 bysrac2 bysrac2.bys.com 192.168.1.227 bysrac2-vip 192.168.10.1 bysrac1-priv 192.168.10.2 bysrac2-priv 192.168.1.228 bysrac-cluster bysrac-cluster-scan

实验1：修改RAC的local_listener参数，将参数值中HOST=的值改为当前节点的VIP值--RAC多节点都需要改

[oracle@bysrac1 ~]$ sqlplus bys/bys BYS@ bysrac>select instance_name from v$instance; INSTANCE_NAME ---------------- bysrac1 BYS@ bysrac1>show parameter local_l -------查看local_listener 参数的值 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener string (DESCRIPTION=(ADDRESS_LIST=(AD DRESS=(PROTOCOL=TCP)( HOST=bysr ac1-vip)(PORT=1521)))) BYS@ bysrac1>alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP) (HOST=192.168.1.226)(PORT=1521))))' sid='bysrac1'; System altered. --这里HOST=192.168.1.226，修改为该节点VIP的IP值。我这里只修改了一个节点的，如果多个节点，照此方法在其它节点修改。 BYS@ bysrac1>show parameter local_l NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener string (DESCRIPTION=(ADDRESS_LIST=(AD DRESS=(PROTOCOL=TCP)(HOST=192. 168.1.226)(PORT=1521)))) BYS@ bysrac1>exit ---退出前可以手工注册下监听alter system register; 我这里没做也是可以的。

客户端tnsnames.ora 中使用SCANIP或者VIP任一都可以登陆到RAC数据库

1.使用配置 SCNAIP的方法可以登陆：

[oracle@bys3 admin]$ tnsping bysrac TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 01-JAN-2014 20:25:19 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)( HOST = 192.168.1.228)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = bysrac))) OK (10 msec) --tnsping可以正常联通，HOST = 192.168.1.228这里tnsnames.ora里已经修改为RAC的一个节点的SCANIP了。。节约篇幅，没贴tnsnames.ora [oracle@bys3 admin]$ sqlplus bys/bys@bysrac SQL*Plus: Release 11.2.0.4.0 Production on Wed Jan 1 20:34:53 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options BYS@ bysrac>select instance_name from v$instance; INSTANCE_NAME ---------------- bysrac1 BYS@ bysrac>exit

2.客户端使用VIP也可以连接到RAC数据库

[oracle@bys3 admin]$ tnsping bysrac TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 01-JAN-2014 20:52:54 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.226)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = bysrac))) OK (10 msec) -----tnsping可以正常联通，HOST = 192.168.1.226这里tnsnames.ora里已经修改为RAC的一个节点的VIP了。。节约篇幅，没贴tnsnames.ora [oracle@bys3 admin]$ sqlplus bys/bys@bysrac SQL*Plus: Release 11.2.0.4.0 Production on Wed Jan 1 20:52:57 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options BYS@ bysrac>select instance_name,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ bysrac1 OPEN BYS@ bysrac>exit ################实验2：修改RAC的local_listener参数，将参数值中HOST=的值改为scanip[oracle@bysrac1 ~]$ sqlplus bys/bys BYS@ bysrac1>show parameter local_listener NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener string (DESCRIPTION=(ADDRESS_LIST=(AD DRESS=(PROTOCOL=TCP)(HOST=192. 168.1.226)(PORT=1521)))) BYS@ bysrac1>alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.228)(PORT=1521))))'; System altered. BYS@ bysrac1>show parameter local_listener NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener string (DESCRIPTION=(ADDRESS_LIST=(AD DRESS=(PROTOCOL=TCP)(HOST=192.客户端使用VIP时报错ORA-12514: TNS:listener does not currently know of service requested in connect descriptor[oracle@bys3 admin]$ tnsping bysrac TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 01-JAN-2014 21:12:40 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.226)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = bysrac))) OK (10 msec) -----tnsping可以正常联通，HOST = 192.168.1.226，这里tnsnames.ora里已经修改为RAC的一个节点的VIP了。。节约篇幅，没贴tnsnames.ora [oracle@bys3 admin]$ sqlplus bys/bys@bysrac SQL*Plus: Release 11.2.0.4.0 Production on Wed Jan 1 21:12:44 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. ERROR: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor客户端使用SCANIP可以正常连接 [oracle@bys3 admin]$ tnsping bysrac TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 01-JAN-2014 21:13:01 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.228)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = bysrac))) OK (10 msec) --tnsping可以正常联通，HOST = 192.168.1.228这里tnsnames.ora里已经修改为RAC的一个节点的SCANIP了。。节约篇幅，没贴tnsnames.ora [oracle@bys3 admin]$ sqlplus bys/bys@bysrac BYS@ bysrac>select instance_name,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ bysrac1 OPEN Elapsed: 00:00:00.01 BYS@ bysrac>

赞收藏评论分享举报

上一篇：shared pool之二：free lists/shared pool lru list

下一篇：X$BH及V$BH中各字段意义

【本文地址】

公司简介

联系我们