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