【Oracle】service_name和service_names的關系

<b>從oracle9i開始，背景程序pmon自動在監聽器中注冊初始化參數service_names中定義的服務名，service_names預設為db_name.db_dimain。用戶端tns配置中service_name的名稱必須是service_names或其中的一個name。</b>

<b>1 listenter.ora檔案中的 global_dbname和tnsnames.ora檔案service_name的配置一樣時，global_dbname = a, 連接配接串中的service_name = a,此時就可以連接配接上資料庫。根據tnsnames.ora檔案中的連接配接串中的service_name = a 找到監聽器listener.ora中的sid_desc = (global_dbname= a)，之後在找到sid_name, 來連接配接到資料庫執行個體。</b>

<b>2</b><b>listenter.ora檔案中的 global_dbname和tnsnames.ora檔案的</b><b>service_name的配置一樣時</b><b>，tnsnames.ora中的service_name要與參數檔案中的service_names 參數一樣或者是其中之一，否則無法通過tns連接配接到資料庫。</b>

<b>tnsnames.ora檔案中的service_name 和 listener.oar檔案中的global_dbname一樣的情況：</b>

檢視tnsnames.ora檔案的配置資訊：

oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin&gt;cat tnsnames.ora 

# tnsnames.ora network configuration file: /opt/oracle/11.2.0/alifpre/network/admin/tnsnames.ora

# generated by oracle configuration tools.

yangdb =

  (description =

    (address = (protocol = tcp)(host = rac3)(port = 1521))

    (connect_data =

      (server = dedicated)

      (service_name = yangdb)

    )

  )

yangdb1 =

      (service_name = yangdb1)

yangdb2 =

      (service_name = yangdb2)

yangdb3 =

      (service_name = yangdb3)

rac =

    (address_list =

      (address = (protocol = tcp)(host = 10.250.7.200)(port = 1521))

      (service_name = rac)

<b> 監聽檔案中的配置資訊：</b>

oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin&gt;cat listener.ora 

# listener.ora network configuration file: /opt/oracle/11.2.0/alifpre/network/admin/listener.ora

sid_list_listener =

      (sid_list =

         (sid_desc =

           (sid_name = plsextproc)

             (oracle_home = /opt/oracle/11.2.0/alifpre)

           (program = extproc)

         )

        (sid_desc =

          (global_dbname = yangdb)

           (oracle_home = /opt/oracle/11.2.0/alifpre)

          (sid_name = yangdb)

     ) 

  (description_list =

    (description =

      (address = (protocol = tcp)(host = rac3)(port = 1521))

adr_base_listener = /opt/oracle

<b>參數service_names是靜态的，必須重新開機機器才能夠生效。</b>

oracle@rac3:/home/oracle&gt;sqlplus "/as sysdba"

sys@yangdb-rac3&gt; <b>alter system set service_names='yangdb,yangdb1,yangdb2,yangdb3' scope=both;</b>

system altered.

sys@yangdb-rac3&gt; shutdown immediate

database closed.

database dismounted.

oracle instance shut down.

sys@yangdb-rac3&gt; startup 

oracle instance started.

database mounted.

database opened.

sys@yangdb-rac3&gt; show parameter service_names

name                                 type        value

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

service_names                        string     <b> yangdb,yangdb1,yangdb2,yangdb3</b>

進行連接配接測試：

oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin&gt;sqlplus yang/yang<b>@yangdb1</b>

sql*plus: release 11.2.0.1.0 production on thu oct 20 11:12:13 2011

copyright (c) 1982, 2009, oracle.  all rights reserved.

yang@yangdb-rac3&gt; show parameter service_ 

service_names                        string      yangdb,yangdb1,yangdb2,yangdb3

yang@yangdb-rac3&gt; exit

oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin&gt;

oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin&gt;sqlplus yang/yang<b>@yangdb2</b>

sql*plus: release 11.2.0.1.0 production on thu oct 20 11:57:26 2011

yang@yangdb-rac3&gt; show parameter service_

oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin&gt;sqlplus yang/yang<b>@yangdb3</b>

sql*plus: release 11.2.0.1.0 production on thu oct 20 11:57:38 2011

connected to:

<b>檢視監聽服務狀态，可以知道共有6個監聽服務名</b>。

oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin&gt;lsnrctl service

lsnrctl for linux: version 11.2.0.1.0 - production on 20-oct-2011 11:59:11

copyright (c) 1991, 2009, oracle.  all rights reserved.

connecting to (address=(protocol=tcp)(host=)(port=1521))

services summary...

<b>service "plsextproc"</b> has 1 instance(s).

  instance "plsextproc", status unknown, has 1 handler(s) for this service...

    handler(s):

      "dedicated" established:0 refused:0

         local server

<b>service "yangdb</b>" has 2 instance(s).

  instance "yangdb", status unknown, has 1 handler(s) for this service...

  instance "yangdb", status ready, has 1 handler(s) for this service...

      "dedicated" established:4 refused:0 state:ready

<b>service "yangdb1"</b> has 1 instance(s).

<b>service "yangdb2"</b> has 1 instance(s).

<b>service "yangdb3"</b> has 1 instance(s).

<b>service "yangdbxdb"</b> has 1 instance(s).

      "d000" established:0 refused:0 current:0 max:1022 state:ready

         dispatcher

         (address=(protocol=tcp)(host=rac3)(port=45357))

the command completed successfully

<b>tnsnames.ora檔案中的service_name 和 參數檔案中的service_name不一樣的情況：</b>

oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin&gt;vi tnsnames.ora 

<b>lily</b> =   ----連接配接辨別，可以為任意值

      (<b>service_name = lily</b>)

"tnsnames.ora" 60l, 1198c 已寫入                            

oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin&gt;vi listener.ora 

          (<b>global_dbname</b> =<b> lily</b>)

     )

oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin&gt;lsnrctl status

lsnrctl for linux: version 11.2.0.1.0 - production on 20-oct-2011 10:58:30

status of the listener

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

alias                     listener

version                   tnslsnr for linux: version 11.2.0.1.0 - production

start date                27-sep-2011 21:47:16

uptime                    22 days 13 hr. 11 min. 14 sec

trace level               off

security                  on: local os authentication

snmp                      off

listener parameter file   /opt/oracle/11.2.0/alifpre/network/admin/listener.ora

listener log file         /opt/oracle/diag/tnslsnr/rac3/listener/alert/log.xml

listening endpoints summary...

  (description=(address=(protocol=tcp)(host=rac3)(port=1521)))

service "plsextproc" has 1 instance(s).

service "yangdb" has 2 instance(s).

service "yangdb1" has 1 instance(s).

service "yangdb2" has 1 instance(s).

service "yangdb3" has 1 instance(s).

service "yangdbxdb" has 1 instance(s).

lsnrctl for linux: version 11.2.0.1.0 - production on 20-oct-2011 10:58:37

oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin&gt;lsnrctl reload

lsnrctl for linux: version 11.2.0.1.0 - production on 20-oct-2011 10:58:45

<b>更改tnsnames.ora檔案中的service_name的值 yangdb為lily，并相應的修改listener.ora檔案中的global_dbname的值為lily ，也可以成功連接配接資料庫。</b>

lily =

      (service_name = lily)

"tnsnames.ora" 60l, 1196c 已寫入

oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin&gt;sqlplus yang/yang<b>@lily </b>

sql*plus: release 11.2.0.1.0 production on thu oct 20 11:00:11 2011

oracle database 11g enterprise edition release 11.2.0.1.0 - 64bit production

with the partitioning, olap, data mining and real application testing options

yang@yangdb-rac3&gt; show parameter service_        

service_names                        string      yangdb,yangdb1,yangdb2,yangdb3  --動态監聽裡面沒有lily 這樣的服務

<b>說明：如果 參數檔案中的service_names沒有lily這個值，但是listener.ora檔案中有與之對應的global_dbname，并且已經啟動了靜态監聽服務，則sqlplus通過@lily也可以連接配接資料庫。</b>

<b>如果沒有啟動靜态監聽，即沒有lily 這樣的service,則不可以連接配接資料庫，(當然修改過之後沒有執行reload 或沒有重新開機監聽，則還可以連接配接，因為之前存在，這樣的例子不讨論)</b>

oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin&gt;vi listener.ora     

# sid_list_listener =

#      (sid_list =

#         (sid_desc =

#           (sid_name = plsextproc)

#             (oracle_home = /opt/oracle/11.2.0/alifpre)

#           (program = extproc)

#         )

#        (sid_desc =

#          (global_dbname = lily)

#           (oracle_home = /opt/oracle/11.2.0/alifpre)

#          (sid_name = yangdb)

#     ) 

adr_base_listener = /opt/oracle    

<b>編輯過之後重新啟動資料庫（可選），重新開機監聽，目的重新注冊服務，去掉靜态監聽的服務。</b>

 sys@yangdb-rac3&gt; shutdown immediate

sys@yangdb-rac3&gt;  startup 

database opened. 

sys@yangdb-rac3&gt; 

oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin&gt;<b>lsnrctl start</b>

lsnrctl for linux: version 11.2.0.1.0 - production on 20-oct-2011 11:11:24

starting /opt/oracle/11.2.0/alifpre/bin/tnslsnr: please wait...

tnslsnr for linux: version 11.2.0.1.0 - production

system parameter file is /opt/oracle/11.2.0/alifpre/network/admin/listener.ora

log messages written to /opt/oracle/diag/tnslsnr/rac3/listener/alert/log.xml

listening on: (description=(address=(protocol=tcp)(host=rac3)(port=1521)))

start date                20-oct-2011 11:11:24

uptime                    0 days 0 hr. 0 min. 0 sec

the listener supports no services <b> --沒有靜态監聽服務，這時隻能通過動态監聽的服務來連接配接到資料庫。</b>

oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin&gt;sqlplus yang/yang@lily

sql*plus: release 11.2.0.1.0 production on thu oct 20 11:11:35 2011

error:

ora-12514: tns:listener does not currently know of service requested in connect 

descriptor <b>--報錯，監聽沒有lily這樣的服務</b>

enter user-name: ^[[ 

<b>--通過pmon動态監聽的服務名來連接配接資料庫。</b>

oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin&gt;sqlplus yang/yang@yangdb1