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

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-1848837/