ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle11gR2 RAC 使用scan IP无法连接数据库(ORA-12545)问题解决

Oracle11gR2 RAC 使用scan IP无法连接数据库(ORA-12545)问题解决

原创 Linux操作系统 作者:alsrt 时间:2012-06-13 15:03:16 0 删除 编辑

现象:

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

C:\Users\WJW>sqlplus system/qweasd@192.168.100.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 13-JUN-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. 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.100.101)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.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.100.112)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.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.100.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.100.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.100.112)(PORT=1521))))' scope=both sid='orcl2';
SQL> alter system register;

随后使用windows客户端连接成功:
C:\Users\WJW>sqlplus system/qweasd@192.168.100.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

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8410760/viewspace-732683/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-05-02

  • 博文量
    34
  • 访问量
    35300