ITPub博客

首页 > 数据库 > Oracle > Q群友问题---tnsnames.ora中指定sid参数仍无法连接到指定的rac实例

Q群友问题---tnsnames.ora中指定sid参数仍无法连接到指定的rac实例

原创 Oracle 作者:wisdomone1 时间:2015-11-29 00:34:27 0 删除 编辑

Q群友问题



群主,请教个问题
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = nbubackup1vip)(PORT = 1521)
    (ADDRESS = (PROTOCOL = TCP)(HOST = nbubackup2vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )


test =
  (DESCRIPTION =
    (CONNECT_DATA =
     (SID=orcl))
     (SERVER = DEDICATED)
     (SERVICE_NAME = orcl)
     (ADDRESS = (PROTOCOL = TCP)(HOST = nbubackup2vip)(PORT = 1521)
     (ADDRESS = (PROTOCOL = TCP)(HOST = nbubackup1vip)(PORT = 1521))
  )
这2个连接串有什么不同,为什么在test中注明了sid=orcl,还是可以正常连接到rac的2个节点


结论

本文测试环境为oracle 10.2.0.1
1,sid不是tnsnames.ora中有效的参数,无法实现Q友说的会话连接到指定RAC实例的参数
2,Q友说的会话连接到指定RAC实例的参数是instance_name
3,我分析的依据是oracle官方手册 Oracle? Database Net Services Reference ,请见下述测试之 
Oracle? Database Net Services Reference
11g Release 2 (11.2)
Part Number E10835-09 


扩展引申

1,学习参数local_listner
2,remote_listener的含义
3,更进一步要灵活且熟悉oracle listener.ora及tnsnmames.ora中各个节及参数的灵活运用


分析思路





测试



SQL> select * from v$version where rownum=1;


BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi


---测试Q友问题,可见sid参数无效


JINGFA =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = jingfa1-vip.redhat.com)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = jingfa2-vip.redhat.com)(PORT = 1521))
    (CONNECT_DATA =
      (sid=jingfa2)
      (SERVER = DEDICATED)
      (SERVICE_NAME = jingfa)
    )
  )


SQL> select inst_id,count(*) from gv$session group by inst_id;


   INST_ID   COUNT(*)
---------- ----------
         1         33
         2         29


--连发4个会话
[oracle@jingfa1 admin]$ sqlplus tbs_zxy/system@jingfa


SQL*Plus: Release 10.2.0.1.0 - Production on Sun Nov 29 00:34:55 2015


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




Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options


SQL> host




SQL> select inst_id,count(*) from gv$session group by inst_id;


   INST_ID   COUNT(*)
---------- ----------
         1         32
         2         31


经查官方手册
Oracle? Database Net Services Reference
11g Release 2 (11.2)
Part Number E10835-09   


Local Naming Parameters (tnsnames.ora)


Connection Data Section


INSTANCE_NAME


Purpose


To identify the database instance to access. Set the value to the value specified by the INSTANCE_NAME parameter in the initialization parameter file.


Put this parameter under the CONNECT_DATA parameter.




可见Q友说的是上述INSTANCE_NAME可以控制会话连接到指定的RAC实例,经测试确实如此




---我们修改sid为instance_name
JINGFA =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = jingfa1-vip.redhat.com)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = jingfa2-vip.redhat.com)(PORT = 1521))
    (CONNECT_DATA =
      (instance_name=jingfa2)
      (SERVER = DEDICATED)
      (SERVICE_NAME = jingfa)
    )
  )


连发4个会话
[oracle@jingfa1 admin]$ sqlplus tbs_zxy/system@jingfa


SQL*Plus: Release 10.2.0.1.0 - Production on Sun Nov 29 00:38:25 2015


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




Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options


SQL> host


SQL> select inst_id,count(*) from gv$session group by inst_id;


   INST_ID   COUNT(*)
---------- ----------
         1         32
         2         35






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

请登录后发表评论 登录
全部评论
提供针对oracle初学者及进阶的数据库培训,欢迎大家咨询: 微信: wisdomone 微信公众号: lovedb qq: 305076427 微博: wisdomone9

注册时间:2008-04-04

  • 博文量
    2149
  • 访问量
    11890795