Oracle 11gR2 RAC 使用scan IP无法连接数据库(ORA-12545)

69 篇文章 3 订阅

Oracle 11gR2 RAC 使用scan IP无法连接数据库(ORA-12545)

现象:使用PL/SQL客户端scan_ip连接rac 报错 ERROR: ORA-12545: Connect failed because target host or object does not exist

[oracle@racnode1 ~]$ cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localdomain localhost.localdomain localhost
# Public Network - (eth0)
10.236.101.230    racnode1
10.236.101.231    racnode2
# Private Interconnect - (eth1)
10.236.23.200    racnode1-priv
10.236.23.201    racnode2-priv
# Public Virtual IP (VIP) addresses - (eth0:1)
10.236.101.232    racnode1-vip
10.236.101.233    racnode2-vip
# Single Client Access Name (SCAN)
10.236.101.234    racnode-cluster-scan
# Private Storage Network for Openfiler - (eth1)
10.236.101.236    openfiler1
10.236.23.202    openfiler1-priv

检查各组件状态,正常:
[grid@racnode1 ~]$ crs_stat -t -v
Name           Type           R/RA   F/FT   Target    State     Host        
----------------------------------------------------------------------
ora.CRS.dg     ora....up.type 0/5    0/     ONLINE    ONLINE    racnode1    
ora.FRA.dg     ora....up.type 0/5    0/     ONLINE    ONLINE    racnode1    
ora....ER.lsnr ora....er.type 0/5    0/     ONLINE    ONLINE    racnode1    
ora....N1.lsnr ora....er.type 0/5    0/0    ONLINE    ONLINE    racnode1    
ora....DATA.dg ora....up.type 0/5    0/     ONLINE    ONLINE    racnode1    
ora.asm        ora.asm.type   0/5    0/     ONLINE    ONLINE    racnode1    
ora.eons       ora.eons.type  0/3    0/     ONLINE    ONLINE    racnode1    
ora.gsd        ora.gsd.type   0/5    0/     OFFLINE   OFFLINE               
ora....network ora....rk.type 0/5    0/     ONLINE    ONLINE    racnode1    
ora.oc4j       ora.oc4j.type  0/5    0/0    OFFLINE   OFFLINE               
ora.ons        ora.ons.type   0/3    0/     ONLINE    ONLINE    racnode1    
ora.racdb.db   ora....se.type 0/2    0/1    OFFLINE   OFFLINE               
ora....SM1.asm application    0/5    0/0    ONLINE    ONLINE    racnode1    
ora....E1.lsnr application    0/5    0/0    ONLINE    ONLINE    racnode1    
ora....de1.gsd application    0/5    0/0    OFFLINE   OFFLINE               
ora....de1.ons application    0/3    0/0    ONLINE    ONLINE    racnode1    
ora....de1.vip ora....t1.type 0/0    0/0    ONLINE    ONLINE    racnode1    
ora....SM2.asm application    0/5    0/0    ONLINE    ONLINE    racnode2    
ora....E2.lsnr application    0/5    0/0    ONLINE    ONLINE    racnode2    
ora....de2.gsd application    0/5    0/0    OFFLINE   OFFLINE               
ora....de2.ons application    0/3    0/0    ONLINE    ONLINE    racnode2    
ora....de2.vip ora....t1.type 0/0    0/0    ONLINE    ONLINE    racnode2    
ora.scan1.vip  ora....ip.type 0/0    0/0    ONLINE    ONLINE    racnode1

检查监听器状态,正常:
[grid@racnode1 ~]$ lsnrctl status


LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 28-JUL-2017 11:19:47


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                27-JUL-2017 16:34:30
Uptime                    0 days 18 hr. 46 min. 24 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/racnode1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.236.101.230)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.236.101.232)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "racdb.path_finder.info" has 1 instance(s).
  Instance "racdb1", status READY, has 1 handler(s) for this service...
Service "racdbXDB.path_finder.info" has 1 instance(s).
  Instance "racdb1", status READY, has 1 handler(s) for this service...
The command completed successfully

[grid@racnode2 ~]$ lsnrctl status


LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 28-JUL-2017 11:20:35


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                27-JUL-2017 16:31:20
Uptime                    0 days 18 hr. 50 min. 19 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/racnode2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.236.101.231)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.236.101.233)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "racdb.path_finder.info" has 1 instance(s).
  Instance "racdb2", status READY, has 1 handler(s) for this service...
Service "racdbXDB.path_finder.info" has 1 instance(s).
  Instance "racdb2", status READY, has 1 handler(s) for this service...
The command completed successfully

[grid@racnode1 ~]$ lsnrctl status listener_scan1


LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 28-JUL-2017 11:45:38


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                27-JUL-2017 15:05:08
Uptime                    0 days 20 hr. 40 min. 30 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/11.2.0/grid/log/diag/tnslsnr/racnode1/listener_scan1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.236.101.234)(PORT=1521)))
Services Summary...
Service "racdb.path_finder.info" has 2 instance(s).
  Instance "racdb1", status READY, has 1 handler(s) for this service...
  Instance "racdb2", status READY, has 1 handler(s) for this service...
Service "racdbXDB.path_finder.info" has 2 instance(s).
  Instance "racdb1", status READY, has 1 handler(s) for this service...
  Instance "racdb2", status READY, has 1 handler(s) for this service...
The command completed successfully

根据http://www.oracleonlinux.cn/2012/03/resolve-11gr2-rac-client-ora-12545/ 

这篇文章解决了问题,原来虽然客户端能解析全域名称scan和vip(虚拟ip)但是端主机名却是在不同的区域中,即使在安装grid infrastructure 过程中使用了fqdn形式就是主机名+域名的方式,由于错误9150053的规定导致了DBCA在创建数据库时是用主机的段号虚拟IP来设置数据库参数local_listener的,因此只要把数据库实例参数local_listener内的host改为vip的IP地址即可;


bug-9150053这是11.2.0.1的小bug:“ORA-12545 or ORA-12537 while connecting to RAC through SCAN name[ID 970619.1]”,
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.


11.2.0.2及之后的版本就修复好了 ;

解决方法:

[oracle@racnode1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Jul 28 11:49:45 2017
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options


SQL> show parameter local_listener;


NAME      TYPE  VALUE
------------------------------------ ----------- ------------------------------
local_listener      string  (DESCRIPTION=(ADDRESS_LIST=(AD
 DRESS=(PROTOCOL=TCP)(HOST=rac
 node1-vip)(PORT=1521))))


[oracle@racnode2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Jul 28 11:58:00 2017
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> show parameter local_listener;


NAME      TYPE  VALUE
------------------------------------ ----------- ------------------------------
local_listener      string  (DESCRIPTION=(ADDRESS_LIST=(AD
 DRESS=(PROTOCOL=TCP)(HOST=rac
 node2-vip)(PORT=1521))))

ranode1:

SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.236.101.232)(PORT=1521))))' scope=both sid='racdb1';
SQL> alter system register;


racnode2:

SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.236.101.233)(PORT=1521))))' scope=both sid='racdb2';
SQL> alter system register;


随后使用windows pl/sql客户端连接成功:

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值