解决oracle客户端可以连接11g rac vip,但是不能连接scan ip问题
来源:互联网 发布:百度知道 知乎 区别 编辑:程序博客网 时间:2024/05/21 13:57
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相同!
# 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问题
前段时间配置了一套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>
服务器端网络配置信息如下:
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>
回复 分享
顶,我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相同!
# 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问题
前段时间配置了一套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>
服务器端网络配置信息如下:
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>
回复 分享
顶,我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.
http://www.i
http://t.askmaclean.com/thread-1360-1-1.html
http://luoping.blog.51cto.com/534596/1053873
http://luopi