Oracle11gR2RAC使用scanIP无法连接数据库的解决方法

这篇文章将为大家详细讲解有关Oracle11gR2 RAC使用scan IP无法连接数据库的解决方法，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。

成都创新互联公司坚持“要么做到，要么别承诺”的工作理念，服务领域包括：成都网站建设、做网站、企业官网、英文网站、手机端网站、网站推广等服务，满足客户于互联网时代的普洱网站设计、移动媒体设计的需求，帮助企业找到有效的互联网解决方案。努力成为您成熟可靠的网络建设合作伙伴！

环境：
[grid@rac1 ~]$ 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
# Public
192.168.2.101     rac1
192.168.2.102     rac2
# Private
192.168.0.101     rac1-priv
192.168.0.102     rac2-priv
# Virtual
192.168.2.111     rac1-vip
192.168.2.112     rac2-vip
# SCAN
192.168.2.200     rac-scan

现象：

在windows客户端sqlplus工具使用scan的IP无法连接，报错如下：

C:\Users\WJW>sqlplus system/qweasd@192.168.2.200:1521/orcl

SQL*Plus: Release 11.2.0.1.0 Production on Thu May 17 12:35:28 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-12545: Connect failed because target host or object does not exist


检查各组件状态，正常：
[grid@rac1 ~]$ crs_stat -t
Name           Type           Target    State     Host       
------------------------------------------------------------
ora.DATA.dg    ora....up.type ONLINE    ONLINE    rac1       
ora....ER.lsnr ora....er.type ONLINE    ONLINE    rac1       
ora....N1.lsnr ora....er.type ONLINE    ONLINE    rac1       
ora.asm        ora.asm.type   ONLINE    ONLINE    rac1       
ora.eons       ora.eons.type  ONLINE    ONLINE    rac1       
ora.gsd        ora.gsd.type   ONLINE    ONLINE    rac1       
ora....network ora....rk.type ONLINE    ONLINE    rac1       
ora.oc4j       ora.oc4j.type  ONLINE    ONLINE    rac2       
ora.ons        ora.ons.type   ONLINE    ONLINE    rac1       
ora.orcl.db    ora....se.type ONLINE    ONLINE    rac1       
ora....SM1.asm application    ONLINE    ONLINE    rac1       
ora....C1.lsnr application    ONLINE    ONLINE    rac1       
ora.rac1.gsd   application    ONLINE    ONLINE    rac1       
ora.rac1.ons   application    ONLINE    ONLINE    rac1       
ora.rac1.vip   ora....t1.type ONLINE    ONLINE    rac1       
ora....SM2.asm application    ONLINE    ONLINE    rac2       
ora....C2.lsnr application    ONLINE    ONLINE    rac2       
ora.rac2.gsd   application    ONLINE    ONLINE    rac2       
ora.rac2.ons   application    ONLINE    ONLINE    rac2       
ora.rac2.vip   ora....t1.type ONLINE    ONLINE    rac2       
ora.scan1.vip  ora....ip.type ONLINE    ONLINE    rac1


检查监听器状态，正常：
[grid@rac1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 17-MAY-2012 13:27:40

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                17-MAY-2012 12:19:51
Uptime                    0 days 1 hr. 7 min. 50 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.101)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.111)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl1", status READY, has 1 handler(s) for this service...
The command completed successfully


[grid@rac2 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 17-MAY-2012 13:28:23

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                17-MAY-2012 12:19:51
Uptime                    0 days 1 hr. 8 min. 32 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.112)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.102)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl2", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl2", status READY, has 1 handler(s) for this service...
The command completed successfully


[grid@rac1 ~]$ lsnrctl status listener_scan1

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 17-MAY-2012 13:29:09

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_SCAN1
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                17-MAY-2012 12:21:31
Uptime                    0 days 1 hr. 7 min. 37 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/log/diag/tnslsnr/rac1/listener_scan1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.200)(PORT=1521)))
Services Summary...
Service "orcl" has 2 instance(s).
  Instance "orcl1", status READY, has 1 handler(s) for this service...
  Instance "orcl2", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 2 instance(s).
  Instance "orcl1", status READY, has 1 handler(s) for this service...
  Instance "orcl2", status READY, has 1 handler(s) for this service...
The command completed successfully

原因：
metalink说明：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.
因此只要把数据库实例参数local_listener内的host改为vip的IP地址即可

解决办法：
rac1:
SQL> show parameter local_listener

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
local_listener                 string     (DESCRIPTION=(ADDRESS_LIST=(AD
                         DRESS=(PROTOCOL=TCP)(HOST=rac1
                         -vip)(PORT=1521))))
SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.111)(PORT=1521))))' scope=both sid='orcl1';
SQL> alter system register;

rac2:
SQL> show parameter local_listener

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
local_listener                 string     (DESCRIPTION=(ADDRESS_LIST=(AD
                         DRESS=(PROTOCOL=TCP)(HOST=rac2
                         -vip)(PORT=1521))))
SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.112)(PORT=1521))))' scope=both sid='orcl2';
SQL> alter system register;

随后使用windows客户端连接成功：
C:\Users\WJW>sqlplus system/qweasd@192.168.2.200:1521/orcl

SQL*Plus: Release 11.2.0.1.0 Production on Thu May 17 13:14:39 2012

Copyright (c) 1982, 2010, 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

关于Oracle11gR2 RAC使用scan IP无法连接数据库的解决方法就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。