配置Oracle共用伺服器

使用者串連oracle資料庫伺服器主要有兩種方式：專有伺服器串連和共用伺服器串連

在專有伺服器模式中，當用戶端發出串連資料庫的請求時，監聽器負責把用戶端串連到專有伺服器處理序，該專有伺服器處理序只為該會話的使用者進程服務，即使處於空閑狀態。專有伺服器處理序與使用者進程之間是一一對應的，每個伺服器處理序都會使用系統資源，包括CPU周期和記憶體。

在負荷很高的系統中，由於專有伺服器處理序佔用了記憶體和CPU資源，因而會對系統的延展性產生負面影響，所以一般用戶端要通過中介軟體的串連池串連到資料庫的專有伺服器，或者串連到資料庫的共用伺服器。

650) this.width=650;" src="http://img1.51cto.com/attachment/201309/154517541.jpg" title="dedicate.jpg" alt="154517541.jpg" />

在共用伺服器模式中，用戶端使用者進程的串連請求將被監聽器傳送至負載最小的調度進程(dispatcher)中，調度進程負責將使用者請求傳遞到SGA中一個公用的請求隊列。閒置共用伺服器處理序將完成請求隊列中的使用者請求，並將結果放到響應隊列中，最後調度進程將取出響應隊列中的結果返回給使用者進程。

會話與調度進程的串連在會話期間持久存在，而與監聽器的串連是短暫的。且所有的調度進程共用一個公用的請求隊列，但是每個調度進程都具有獨立的響應隊列。

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/213I42008-1.jpg" title="shared_server.jpg" alt="154559197.jpg" />

配置共用伺服器

在伺服器端，共用伺服器與資料庫無關，而只與執行個體有關。通過執行個體動態註冊，會為共用伺服器自動設定監聽器。

共用伺服器需要配置兩個必要參數：dispatchers和shared_servers

dispatchers參數控制在執行個體中啟動的調度進程數以及這些進程的行為(協議類型等)，max_dispatchers參數指定了能夠啟動的調度進程數的上限。

shared_servers參數控制在執行個體中啟動的共用伺服器處理序的數量。max_shared_servers參數指定了能夠啟動共用伺服器處理序數的上限，預設為processers參數值的1/8。

$grep -A 5 APPLE $TNS_ADMIN/tnsnames.oraAPPLE =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.90)(PORT = 1522))  )SQL> !ps aux | grep ora_s0.._orcl | grep -v greporacle    5223  0.0  1.0 666136 15028 ?        Ss   10:21   0:00 ora_s000_orclSQL> show parameter dispatchersNAME                                 TYPE        VALUE------------------------------------ ----------- ----------------------------------dispatchers                          string      (PROTOCOL=TCP) (SERVICE=orclXDB)max_dispatchers                      integerSQL> alter system set dispatchers='(PROTOCOL=TCP) (dispatchers=3)';                  //建立3個調度進程D000、D001、D002SQL> select name,network,paddr,status from v$dispatcher;NAME  NETWORK                                                           PADDR                STATUS----- ----------------------------------------------------------------- -------------------- ----------------D000  (ADDRESS=(PROTOCOL=tcp)(HOST=ora11g.example.com)(PORT=62238))     000000007AC8AFF0     WAITD001  (ADDRESS=(PROTOCOL=tcp)(HOST=ora11g.example.com)(PORT=54626))     000000007AC8C030     WAITD002  (ADDRESS=(PROTOCOL=tcp)(HOST=ora11g.example.com)(PORT=16374))     000000007AC8D070     WAITSQL> !ps aux | grep ora_d0.._orcl | grep -v greporacle    9303  0.0  1.0 666976 15992 ?        Ss   15:35   0:00 ora_d000_orcloracle    9392  0.0  1.0 666976 15976 ?        Ss   15:38   0:00 ora_d001_orcloracle    9396  0.0  1.0 666976 16004 ?        Ss   15:38   0:00 ora_d002_orclSQL> alter system set shared_servers=6;                                  //建立6個響應隊列S000~S005(一般為dispatchers的倍數)SQL> select name,paddr,status,circuit from v$shared_server;NAME PADDR            STATUS           CIRCUIT---- ---------------- ---------------- ----------------S000 000000007AC8E0B0 WAIT(COMMON)     00S001 000000007AC8F0F0 WAIT(COMMON)     00S002 000000007AC90130 WAIT(COMMON)     00S003 000000007AC91170 WAIT(COMMON)     00S004 000000007AC921B0 WAIT(COMMON)     00S005 000000007AC94230 WAIT(COMMON)     00SQL> !ps aux | grep ora_s0.._orcl | grep -v greporacle    5223  0.0  1.0 666136 15028 ?        Ss   10:21   0:00 ora_s000_orcloracle    6692  0.1  1.0 666136 15028 ?        Ss   11:13   0:00 ora_s001_orcloracle    6696  0.1  1.0 666136 15028 ?        Ss   11:13   0:00 ora_s002_orcloracle    6700  0.0  1.0 666136 15032 ?        Ss   11:13   0:00 ora_s003_orcloracle    6704  0.1  1.0 666136 15044 ?        Ss   11:13   0:00 ora_s004_orcloracle    6708  0.1  1.0 666136 15032 ?        Ss   11:13   0:00 ora_s005_orclSQL> alter system set dispatchers='(PROTOCOL=TCP) (dispatchers=3) (listener=apple)';         //單獨將dispathers註冊到LISTENER1的監聽器上SQL> !lsnrctl service listener1---------------省略輸出---------------Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.90)(PORT=1522)))Services Summary...Service "orcl.example.com" has 1 instance(s).  Instance "orcl", status READY, has 4 handler(s) for this service...    Handler(s):      "DEDICATED" established:0 refused:0 state:ready         LOCAL SERVER      "D002" established:0 refused:0 current:0 max:972 state:ready         DISPATCHER <machine: ora11g.example.com, pid: 4987>         (ADDRESS=(PROTOCOL=tcp)(HOST=ora11g.example.com)(PORT=16374))      "D001" established:0 refused:0 current:0 max:972 state:ready         DISPATCHER <machine: ora11g.example.com, pid: 4983>         (ADDRESS=(PROTOCOL=tcp)(HOST=ora11g.example.com)(PORT=54626))      "D000" established:0 refused:0 current:1 max:972 state:ready         DISPATCHER <machine: ora11g.example.com, pid: 4979>         (ADDRESS=(PROTOCOL=tcp)(HOST=ora11g.example.com)(PORT=62238))The command completed successfully

用戶端串連到共用伺服器

在專有伺服器模式和共用伺服器模式共同存在的情況下，EZCONNECT一般選擇共用伺服器模式進行串連

$ sqlplus /nologSQL*Plus: Release 11.2.0.1.0 Production on Thu Sep 12 16:07:18 2013Copyright (c) 1982, 2009, Oracle.  All rights reserved.SQL> conn / as sysdbaConnected.SQL> alter system set local_listener='';                                         //將監聽改回預設的"LISTENER"System altered.SQL> alter system set dispatchers='(PROTOCOL=TCP) (dispatchers=3)';                      //將調度器註冊到"LISTENER"的監聽上System altered.SQL> !lsnrctl service                                                    //LISTENER監聽上存在共用伺服器模式和專用伺服器模式---------------省略輸出---------------Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora11g.example.com)(PORT=1521)))Services Summary...Service "+ASM" has 1 instance(s).  Instance "+ASM", status READY, has 1 handler(s) for this service...    Handler(s):      "DEDICATED" established:0 refused:0 state:ready         LOCAL SERVERService "orcl.example.com" has 1 instance(s).  Instance "orcl", status READY, has 4 handler(s) for this service...    Handler(s):      "DEDICATED" established:3 refused:0 state:ready         LOCAL SERVER      "D002" established:0 refused:0 current:0 max:972 state:ready         DISPATCHER <machine: ora11g.example.com, pid: 4987>         (ADDRESS=(PROTOCOL=tcp)(HOST=ora11g.example.com)(PORT=16374))      "D001" established:0 refused:0 current:0 max:972 state:ready         DISPATCHER <machine: ora11g.example.com, pid: 4983>         (ADDRESS=(PROTOCOL=tcp)(HOST=ora11g.example.com)(PORT=54626))      "D000" established:0 refused:0 current:0 max:972 state:ready         DISPATCHER <machine: ora11g.example.com, pid: 4979>         (ADDRESS=(PROTOCOL=tcp)(HOST=ora11g.example.com)(PORT=62238))SQL> !grep -A 10 ORCL $TNS_ADMIN/tnsnames.oraORCL =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.90)(PORT = 1521))    )    (CONNECT_DATA =      (SERVER = SHARED)                                         //設定TNSNAMES串連方式為共用伺服器模式      (SERVICE_NAME = orcl.example.com)    )  )

以TNSNAMES方式串連：

SQL> conn sys/oracle_4U@orcl as sysdbaConnected.SQL> !ps  PID TTY          TIME CMD 6307 pts/1    00:00:00 sqlplus 6528 pts/1    00:00:00 psSQL> select server,port from v$session where terminal='pts/1';                       //當前會話的串連方式為shared和源連接埠為15707SERVER          PORT--------- ----------SHARED         15707SQL> select p.spid,p.pname from v$process p,v$circuit c where c.dispatcher=p.addr;           //產生了一條虛擬迴路串連到調度器D000SPID                     PNAME------------------------ -----4979                     D000

以EZCONNECT方式串連：

SQL> conn sys/oracle_4U@192.168.0.90:1521/orcl.example.com as sysdbaConnected.SQL> select server,port from v$session where terminal='pts/1';                       //當前會話的串連方式為shared和源連接埠為15784SERVER          PORT--------- ----------SHARED         15784SQL> select p.spid,p.pname from v$process p,v$circuit c where c.dispatcher=p.addr;           //產生了一條虛擬迴路串連到調度器D001SPID                     PNAME------------------------ -----4983                     D001SQL> !ps aux | grep 4983 | grep -v greporacle    4983  0.0  1.1 666908 17212 ?        Ss   09:23   0:00 ora_d001_orclSQL> ! netstat -ntp | grep 15784(Not all processes could be identified, non-owned process info will not be shown, you would have to be root to see it all.)tcp        0      0 192.168.0.90:1521           192.168.0.90:15784          ESTABLISHED -                  tcp        0      0 192.168.0.90:15784          192.168.0.90:1521           ESTABLISHED 7420/sqlplus

本文出自 “ゞ煙花っ笑” 部落格，請務必保留此出處http://vnimos.blog.51cto.com/2014866/1296455