Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2824211
  • 博文数量: 200
  • 博客积分: 2413
  • 博客等级: 大尉
  • 技术积分: 3067
  • 用 户 组: 普通用户
  • 注册时间: 2011-04-01 22:07
文章分类

全部博文(200)

文章存档

2018年(2)

2017年(8)

2016年(35)

2015年(14)

2014年(20)

2013年(24)

2012年(53)

2011年(44)

分类: Oracle

2016-03-23 15:08:39


为现有RAC搭建了一套RAC stanby备库,在客户端用VIP可以正常连接,但是通过SCAN IP则无法连接。

客户端无法登录

$ sqlplus  TRADE/xxxxx@10.0.2.110/trade34

SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 23 13:56:41 2016

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

 

ERROR:

ORA-12514: TNS:listener does not currently know of service requested in connect

descriptor



问题原因
服务端是RAC,没有设置remote_listener。
11gR2 数据库,默认设置local_listener 参数为local LISTENER(本地监听器,ip地址为vip),为VIP listener提供服务;
remote_listener参数设置为SCAN listener,为SCAN IP提供服务。

SQL> show parameter remote_listener

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

remote_listener                      string



查看scan ip的listener,发现没有提供服务。(注意RAC 中 db的listener配置文件在grid用户下)

$ lsnrctl status LISTENER_SCAN1

 

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-MAR-2016 14:43:05

 

Copyright (c) 1991, 2013, 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.4.0 - Production

Start Date                26-FEB-2016 17:14:35

Uptime                    25 days 21 hr. 28 min. 29 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /u01/grid/network/admin/listener.ora

Listener Log File         /u01/grid/log/diag/tnslsnr/SL010A-IVDB04/listener_scan1/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.2.110)(PORT=1521)))

The listener supports no services

The command completed successfully




解决办法

SQL> alter system set remote_listener='trade34-scan:1521' scope=both sid='*';

SQL> alter system register;


SQL> show parameter remote_listener

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

remote_listener                      string      trade34-scan:1521




scan ip的监听开始提供服务

$ lsnrctl status LISTENER_SCAN1

 

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-MAR-2016 14:45:48

 

Copyright (c) 1991, 2013, 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.4.0 - Production

Start Date                26-FEB-2016 17:14:35

Uptime                    25 days 21 hr. 31 min. 13 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /u01/grid/network/admin/listener.ora

Listener Log File         /u01/grid/log/diag/tnslsnr/SL010A-IVDB04/listener_scan1/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.2.110)(PORT=1521)))

Services Summary...

Service "trade34" has 2 instance(s).

  Instance "trade3", status READY, has 1 handler(s) for this service...

  Instance "trade4", status READY, has 1 handler(s) for this service...

The command completed successfully




再次测试,成功通过SCAN IP连接到数据库

$ sqlplus  TRADE/xxxxxx@10.0.2.110/trade34


SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 23 14:16:39 2016

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

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options



参考:
MOS TNS-12514 Unable to connect to RAC database using SCAN name (文档 ID 1459768.1) 
见 http://blog.chinaunix.net/uid-23284114-id-5680804.html



转载请注明:
十字螺丝钉
http://blog.chinaunix.net/uid/23284114.html

QQ:463725310
E-MAIL:houora#gmail.com(#请自行替换为@)
阅读(7314) | 评论(0) | 转发(1) |
给主人留下些什么吧!~~