解决oracle客户端可以连接11g rac vip，但是不能连接scan ip问题

最新推荐文章于 2024-08-09 13:47:13 发布

转载最新推荐文章于 2024-08-09 13:47:13 发布 · 963 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://yq.aliyun.com/articles/45890

文章标签：

#数据库 #网络

本文描述了如何解决Oracle RAC集群中客户端仅能连接VIP IP而无法通过SCAN IP连接的问题。通过修改数据库参数local_listener以包含SCAN IP，确保客户端能够正确连接到数据库。同时，提供了解决方案的步骤和相关配置信息，帮助用户解决类似问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

local不用动，remote_listern按我说的设置一下，应该就可以了！

# Public Network - (eth0)
10.4.56.39 syquadb1
10.4.56.40 syquadb2

# Public Virtual IP (VIP) addresses - (eth1)
10.4.56.41 syquadb1-vip
10.4.56.42 syquadb2-vip

# Private Interconnect - (bond0:eth2 eth3)
192.168.1.39 syquadb1-priv
192.168.1.40 syquadb2-priv

# Single Client Access Name (SCAN)
10.4.56.43 syquadb-cluster-scan

SQL> show parameter remote_listener

NAME                               TYPE       VALUE
------------------------------------ ----------- ------------------------------
remote_listener                   string    syquadb-cluster-scan:1521

SQL> show parameter local_listener

NAME                               TYPE       VALUE
------------------------------------ ----------- ------------------------------
local_listener                      string    (DESCRIPTION=(ADDRESS_LIST=(AD
                                             DRESS=(PROTOCOL=TCP)(HOST=10.4
                                             .56.42)(PORT=1521))))
另一台差不多，只有local不同，remote相同！

使用道具举报

解决oracle客户端可以连接11g rac vip，但是不能连接scan ip问题

发表于 2012-7-5 10:53:34 | 查看: 1389 | 回复: 1

前段时间配置了一套hpux11.31 oracle11g r2 rac，配置完成后，本机的客户端可以连接vip和scan ip连接数据库，远程客户端只能连接vip访问数据库，通过SCAN IP访问数据库时就无法连接，但是可以ping通scan ip，telnet scan ip 1521也是没问题

服务器端网络配置信息如下：

01 [root@rac1 ~]# cat /etc/hosts
02 # Do not remove the following line, or various programs
03 # that require network functionality will fail.
04 #127.0.0.1             localhost.localdomain localhost
05 127.0.0.1             localhost
06 ::1          localhost6.localdomain6 localhost6
07
08 172.16.0.191          rac1.localdomain       rac1
09 172.16.0.193          rac1-vip.localdomain rac1-vip
10 192.168.93.1          rac1-priv.localdomain rac1-priv
11
12 172.16.0.192          rac2.localdomain       rac2
13 172.16.0.194          rac2-vip.localdomain rac2-vip
14 192.168.93.2          rac2-priv.localdomain rac2-priv
15
16 172.16.0.203          rac-scan.localdomain rac-scan
17 [root@rac1 ~]#
服务器端SCAN IP信息如下：

01 [root@rac1 ~]# /u01/app/11.2.0/grid/bin/srvctl config scan
02 SCAN name: 172.16.0.203, Network: 1/172.16.0.0/255.255.240.0/eth0
03 SCAN VIP name: scan1, IP: /rac-scan.localdomain/172.16.0.203
04 [root@rac1 ~]# /u01/app/11.2.0/grid/bin/srvctl status scan
05 SCAN VIP scan1 is enabled
06 SCAN VIP scan1 is running on node rac2
07 [root@rac1 ~]# /u01/app/11.2.0/grid/bin/srvctl status scan_listener
08 SCAN Listener LISTENER_SCAN1 is enabled
09 SCAN listener LISTENER_SCAN1 is running on node rac2
10 [root@rac1 ~]#
服务器端集群信息如下：

01 [root@rac1 ~]# /u01/app/11.2.0/grid/bin/crs_stat -t
02 Name          Type          Target State    Host
03 ------------------------------------------------------------
04 ora.DATADG.dg  ora....up.type ONLINE ONLINE rac1
05 ora.GRIDDG.dg  ora....up.type ONLINE ONLINE rac1
06 ora....ER.lsnr ora....er.type ONLINE ONLINE rac1
07 ora....N1.lsnr ora....er.type ONLINE ONLINE rac2
08 ora.asm       ora.asm.type ONLINE ONLINE rac1
09 ora.devdb.db ora....se.type ONLINE ONLINE rac1
10 ora.eons    ora.eons.type  ONLINE ONLINE rac1
11 ora.gsd       ora.gsd.type OFFLINE OFFLINE
12 ora....network ora....rk.type ONLINE ONLINE rac1
13 ora.oc4j    ora.oc4j.type  OFFLINE OFFLINE
14 ora.ons       ora.ons.type ONLINE ONLINE rac1
15 ora....SM1.asm application ONLINE ONLINE rac1
16 ora....C1.lsnr application ONLINE ONLINE rac1
17 ora.rac1.gsd application OFFLINE OFFLINE
18 ora.rac1.ons application ONLINE ONLINE rac1
19 ora.rac1.vip ora....t1.type ONLINE ONLINE rac1
20 ora....SM2.asm application ONLINE ONLINE rac2
21 ora....C2.lsnr application ONLINE ONLINE rac2
22 ora.rac2.gsd application OFFLINE OFFLINE
23 ora.rac2.ons application ONLINE ONLINE rac2
24 ora.rac2.vip ora....t1.type ONLINE ONLINE rac2
25 ora....ry.acfs ora....fs.type ONLINE ONLINE rac1
26 ora.scan1.vip  ora....ip.type ONLINE ONLINE rac2
27 [root@rac1 ~]#
通过查找MetaLink：ORA-12545 or ORA-12537 While Connecting to RAC through SCAN name [ID 970619.1]得到解决方法，修改数据库的local_listener参数：

修改之前：

01 [root@rac1 ~]# su - oracle
02 rac1-> sqlplus
03
04 SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 13 10:42:58 2012
05
06 Copyright (c) 1982, 2009, Oracle.  All rights reserved.
07
08
09 Connected to:
10 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
11 With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
12 Data Mining and Real Application Testing options
13
14 SQL> set line 160
15 SQL> show parameter spfile;
16
17 NAME                               TYPE       VALUE
18 ------------------------------------ ----------- ------------------------------
19 spfile                            string    +DATADG/devdb/spfiledevdb.ora
20 SQL> show parameter instance_name;
21
22 NAME                               TYPE       VALUE
23 ------------------------------------ ----------- ------------------------------
24 instance_name                      string    devdb1
25 SQL> show parameter listener
26
27 NAME                               TYPE       VALUE
28 ------------------------------------ ----------- ------------------------------
29 listener_networks                   string
30 local_listener                      string    (DESCRIPTION=(ADDRESS_LIST=(AD
31                                                    DRESS=(PROTOCOL=TCP)(HOST=rac1
32                                                    -vip)(PORT=1521))))
33 remote_listener                   string    rac-scan:1521
34 SQL>
修改local_listener参数：

01 SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.0.193)(PORT=1521))))' sid='devdb1';
02
03 System altered.
04 SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.0.194)(PORT=1521))))' sid='devdb2';
05
06 System altered.
07
08 SQL> show parameter listener
09
10 NAME                               TYPE       VALUE
11 ------------------------------------ ----------- ------------------------------
12 listener_networks                   string
13 local_listener                      string    (DESCRIPTION=(ADDRESS_LIST=(AD
14                                                    DRESS=(PROTOCOL=TCP)(HOST=172.
15                                                    16.0.193)(PORT=1521))))
16 remote_listener                   string    rac-scan:1521
17 SQL>
修改之后,客户端连接正常：

01 C:\Users\huangw.GILLION>sqlplus hr/hr@11grac
02
03 SQL*Plus: Release 11.2.0.2.0 Production on Tue Mar 13 11:04:00 2012
04
05 Copyright (c) 1982, 2010, Oracle.  All rights reserved.
06
07
08 Connected to:
09 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
10 With the Partitioning, Real Application Clusters, Automatic Storage Management,
11 Data Mining and Real Application Testing options
12
13 SQL> show user;
14 USER is "HR"
15 SQL> select * from tab;
16
17 TNAME                         TABTYPE  CLUSTERID
18 ------------------------------ ------- ----------
19 COUNTRIES                   TABLE
20 DEPARTMENTS                   TABLE
21 EMPLOYEES                   TABLE
22 EMP_DETAILS_VIEW             VIEW
23 JOBS                         TABLE
24 JOB_HISTORY                   TABLE
25 LOCATIONS                   TABLE
26 REGIONS                      TABLE
27
28 8 rows selected.
29
30 SQL>
至此，问题解决。

产生问题原因：

Client is able to resolve all Fully Qualified Domain Name (FQDN) SCAN and VIP name but not short ones(without domain name) as its in different domain; even though FQDN names were specified during Grid Infrastructure setup, due to bug 9150053 by default DBCA set database parameter local_listener to short node VIP name while database is created.

解决方法除了上述之外，我们也可以按照下面的方法解决，其实，都是换汤不换药，个人倾向于直接在监听里配置IP地址：

01 SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1-vip.localdomain)(PORT=1521))))' sid='devdb1';
02
03 System altered.
04
05 SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=rac2-vip.localdomain)(PORT=1521))))' sid='devdb2';
06
07 System altered.
08
09 SQL> alter system register;
10
11 System altered.
12
13 SQL>

发表于 2013-5-23 11:32:03

顶，我RAC+dg切换后，连接不上SCAN-IP，照你这样，解决了。谢谢！

补充：

grid 用户上 oracle用户上

[grid@11rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu Nov 8 12:07:09 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options
SQL> show parameter remote
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_listener string
remote_login_passwordfile string EXCLUSIVE
remote_os_authent boolean FALSE
remote_os_roles boolean FALSE
SQL> alter system set remote_listener='scan:1521' scope=both sid='*';
System altered.
SQL> alter system register;
System altered.

参考：

我贴我的，你参考下！local不用动，remote_listern按我说的设置一下，应该就可以了！

# Public Network - (eth0)
10.4.56.39 syquadb1
10.4.56.40 syquadb2

# Public Virtual IP (VIP) addresses - (eth1)
10.4.56.41 syquadb1-vip
10.4.56.42 syquadb2-vip

# Private Interconnect - (bond0:eth2 eth3)
192.168.1.39 syquadb1-priv
192.168.1.40 syquadb2-priv

# Single Client Access Name (SCAN)
10.4.56.43 syquadb-cluster-scan

SQL> show parameter remote_listener

NAME                               TYPE       VALUE
------------------------------------ ----------- ------------------------------
remote_listener                   string    syquadb-cluster-scan:1521

SQL> show parameter local_listener

NAME                               TYPE       VALUE
------------------------------------ ----------- ------------------------------
local_listener                      string    (DESCRIPTION=(ADDRESS_LIST=(AD
                                             DRESS=(PROTOCOL=TCP)(HOST=10.4
                                             .56.42)(PORT=1521))))
另一台差不多，只有local不同，remote相同！

使用道具举报