[20171106]配置客戶端連線注意.txt
[20171106]配置客戶端連線注意.txt
--//在配置客戶端連線時一般建議使用Net Manager工具,windows下呼叫執行Net Manager.
--//linux下執行 netmgr,這樣能一定程度避免copy & paste的 錯誤.
--//我這裡想說的是在連線型別選擇上一定要注意,一般存在4中選擇:
資料庫預設設定
專用伺服器
共享伺服器
池中伺服器.
--//最好明確設定那種模式,而不是選擇"資料庫預設設定"模式,這樣會導致以後配置啟用"共享伺服器"出現問題.
--//最近我們生產系統就遭遇這樣的問題,還是透過例子說明:
1.環境:
SYS@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
--//我在我的client配置如下:
R:\>cat tnsnames.ora
# tnsnames.ora Network Configuration File: r:\tnsnames.ora
# Generated by Oracle configuration tools.
BOOK =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.78)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = book)
)
)
BOOK1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.78)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = book)
)
)
BOOK2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.78)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = book)
)
)
--//注第一種情況連線串BOOK就是"資料庫預設設定".沒有明確引數SERVER的值.許多開發包括我們下發的程式都是這樣設定的.
2.我開啟共享服務模式:
SYS@book> show parameter dispatchers
NAME TYPE VALUE
---------------- -------- -------------------------------------
dispatchers string (PROTOCOL=TCP) (SERVICE=book,bookXDB)
max_dispatchers integer
SYS@book> show parameter shared_server
NAME TYPE VALUE
---------------------- ------- -----
max_shared_servers integer 1
shared_server_sessions integer
shared_servers integer 1
--//我開啟2個會話:
R:\>sqlplus scott/book@book
R:\>sqlplus scott/book@book
--//開啟另外會話以sys使用者執行如下(session 3):
--//session 3:
SELECT s.sid
,s.serial#
,p.spid
,p.pid
,p.serial# p_serial#
,s.SERVER
,s.status
,s.username
, 'alter system kill session '''
|| s.sid
|| ','
|| s.serial#
|| ''''
|| ' immediate;'
c50
FROM v$session s, v$process p
WHERE s.paddr = p.addr and s.username='SCOTT';
SID SERIAL# SPID PID P_SERIAL# SERVER STATUS USERNAME C50
----- ------- ------ ------- ---------- --------- -------- ---------- --------------------------------------------------
262 241 5750 19 1 NONE INACTIVE SCOTT alter system kill session '262,241' immediate;
263 77 5750 19 1 NONE INACTIVE SCOTT alter system kill session '263,77' immediate;
--//你可以發現現在2個會話沒有執行任何語句,status='INACTIVE',server='NONE'.如果你在其中會話執行語句.
--//session 1:
select count(*) from emp,emp,emp,emp,emp,emp,emp,emp;
--//session 3:
SID SERIAL# SPID PID P_SERIAL# SERVER STATUS USERNAME C50
----- ------- ------ ------- ---------- --------- -------- ---------- --------------------------------------------------
263 77 5750 19 1 NONE INACTIVE SCOTT alter system kill session '263,77' immediate;
262 241 5752 20 1 SHARED ACTIVE SCOTT alter system kill session '262,241' immediate;
--//你可以發現其中1個會話status從'INACTIVE'->'ACTIVE',server從'NONE'=>SHARED.
--//在sessiono 1沒有執行結束時,在session 2執行:
SCOTT@book> select sysdate from dual ;
--//session 2會掛起,這個是因為我沒有設定dispatchers引數D000程式太少.
--//如果這時在執行如下:
R:\>sqlplus scott/book@book
SQL*Plus: Release 12.1.0.1.0 Production on Mon Nov 6 12:00:05 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-28547: connection to server failed, probable Oracle Net admin error
3.重複測試看看awr報表:
--session 3:
exec dbms_workload_repository.create_snapshot();
--session 1:
select count(*) from emp,emp,emp,emp,emp,emp,emp,emp;
--session 2:
select sysdate from dual ;
--//等待結束.
--session 3:
exec dbms_workload_repository.create_snapshot();
--//檢視awr報表.實際上根本看不出問題.
SYS@book> @ &r/wait
P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATE WAIT_TIME_MICRO SECONDS_IN_WAIT
---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- ------------------- --------------- ---------------
000000004D545300 0000000000000001 00 1297371904 1 0 262 241 77 SQL*Net message to client WAITED SHORT TIME 5 31
--//這個也是共享伺服器模式的弊端.執行的語句必須很快完成,不然會影響別的會話執行sql語句.blog.itpub.net/267265/viewspace-2124172/
4.如果增加引數max_shared_servers,max_dispatchers數量:
SYS@book> alter system set max_shared_servers=6 scope=memory;
System altered.
SYS@book> alter system set max_dispatchers=10 scope=memory ;
System altered.
SYS@book> alter system set dispatchers='(PROTOCOL=TCP) (SERVICE=book,bookXDB)(dispatchers=6)' scope=memory;
System altered.
SYS@book> alter system register ;
System altered.
$ ps -lef | egrep "d00[0-9]_book|UI[D]"
F S UID PID PPID C PRI NI ADDR SZ WCHAN STIME TTY TIME CMD
0 S oracle 53333 1 0 80 0 - 61860 poll_s 14:45 ? 00:00:00 ora_d000_book
0 S oracle 53471 1 0 80 0 - 61827 poll_s 14:56 ? 00:00:00 ora_d001_book
0 S oracle 53473 1 0 80 0 - 61827 poll_s 14:56 ? 00:00:00 ora_d002_book
0 S oracle 53475 1 0 80 0 - 61827 poll_s 14:56 ? 00:00:00 ora_d003_book
0 S oracle 53477 1 0 80 0 - 61827 poll_s 14:56 ? 00:00:00 ora_d004_book
0 S oracle 53479 1 0 80 0 - 61827 poll_s 14:56 ? 00:00:00 ora_d005_book
--//依次開啟3個會話:
R:\>sqlplus scott/book@book
R:\>sqlplus scott/book@book
R:\>sqlplus scott/book@book
--//session 4:
SELECT s.sid
,s.serial#
,p.spid
,p.pid
,p.serial# p_serial#
,s.SERVER
,s.status
,s.username
,s.program
,p.program
, 'alter system kill session '''
|| s.sid
|| ','
|| s.serial#
|| ''''
|| ' immediate;'
c50
FROM v$session s, v$process p
WHERE s.paddr = p.addr and s.username='SCOTT';
SID SERIAL# SPID PID P_SERIAL# SERVER STATUS USERNAME PROGRAM PROGRAM C50
---------- ---------- ------ ------- ---------- --------- -------- -------- ------------ -------------------------- --------------------------------------------------
262 33 53471 29 4 NONE INACTIVE SCOTT sqlplus.exe oracle@xxxxxdg4 (D001) alter system kill session '262,33' immediate;
261 11 53473 30 2 NONE INACTIVE SCOTT sqlplus.exe oracle@xxxxxdg4 (D002) alter system kill session '261,11' immediate;
263 9 53479 33 1 NONE INACTIVE SCOTT sqlplus.exe oracle@xxxxxdg4 (D005) alter system kill session '263,9' immediate;
--//分別執行不同dispatchers上.這樣就不會存在阻塞.
--//session 1:
SCOTT@book> select count(*) from emp,emp,emp,emp,emp,emp,emp,emp;
...
--//session 2:
SCOTT@book> select sysdate from dual ;
SYSDATE
-------------------
2017-11-06 15:11:00
--//session 3:
SCOTT@book> select sysdate from dual ;
SYSDATE
-------------------
2017-11-06 15:11:03
--//如果要增加S00N程式數量,修改引數:
SYS@book> alter system set shared_servers=4 scope=memory ;
System altered.
$ ps -lef | egrep "s00[0-9]_book|UI[D]"
F S UID PID PPID C PRI NI ADDR SZ WCHAN STIME TTY TIME CMD
0 S oracle 53335 1 3 80 0 - 60697 poll_s 14:45 ? 00:00:55 ora_s000_book
0 S oracle 53617 1 0 80 0 - 60565 poll_s 15:13 ? 00:00:00 ora_s001_book
0 S oracle 53619 1 0 80 0 - 60565 poll_s 15:13 ? 00:00:00 ora_s002_book
0 S oracle 53621 1 0 80 0 - 60565 poll_s 15:13 ? 00:00:00 ora_s003_book
5.總結:
1.講了這麼多,回到前面遇到的問題,可以發現如果client配置時使用資料庫預設設定,在共享伺服器存在的情況下,會優先使用.這樣
如果應用配置存在問題,特別是2層應用模式,全部使用共享伺服器模式連線資料庫,這樣如果某個sql執行很慢,就有可能阻塞業務的
正常執行,即使你配置足夠的dispatchers.實際上如果你使用ezconnect連線也是共享模式.
參考連結: http://blog.itpub.net/267265/viewspace-2130292/=>[20161212]ezconnect與共享服務模式.txt
2.從上面的情況,說明在配置client時,需要選擇正確的連線型別,而不是選擇"資料庫預設設定",以免造成不必要麻煩.
3.如果這樣只能建立新的服務名,指派服務名使用共享伺服器模式.
SYS@book> show parameter service
NAME TYPE VALUE
------------- ------ ---------------
service_names string BOOK, BOOKSHARE
SYS@book> alter system set dispatchers='(PROTOCOL=TCP) (SERVICE=bookshare,bookXDB)(dispatchers=6)' scope=memory;
System altered.
--//修改連線串如下:
BOOKS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.78)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = bookshare)
)
)
R:\>sqlplus scott/book@books
SCOTT@books> @ spid
SID SERIAL# SPID PID P_SERIAL# C50
---------- ---------- ------ ------- ---------- --------------------------------------------------
171 7 53621 37 1 alter system kill session '171,7' immediate;
--//session 4:
SELECT s.sid
,s.serial#
,p.spid
,p.pid
,p.serial# p_serial#
,s.SERVER
,s.status
,s.username
,s.program
,s.SERVICE_NAME
,p.program
, 'alter system kill session '''
|| s.sid
|| ','
|| s.serial#
|| ''''
|| ' immediate;'
c50
FROM v$session s, v$process p
WHERE s.paddr = p.addr and s.username='SCOTT';
SID SERIAL# SPID PID P_SERIAL# SERVER STATUS USERNAME PROGRAM SERVICE_NAME PROGRAM C50
--- ---------- ------ ------- ---------- --------- -------- -------- ----------- ------------ ---------------------- --------------------------------------------------
171 7 53727 32 6 NONE INACTIVE SCOTT sqlplus.exe BOOKSHARE oracle@xxxxxdg4 (D004) alter system kill session '171,7' immediate;
--//退出重新登入:
R:\>sqlplus scott/book@book
SCOTT@book> @ spid
SID SERIAL# SPID PID P_SERIAL# C50
---------- ---------- ------ ------- ---------- --------------------------------------------------
184 19 53781 38 8 alter system kill session '184,19' immediate;
--//session 4:
SYS@book> /
SID SERIAL# SPID PID P_SERIAL# SERVER STATUS USERNAME PROGRAM SERVICE_NAME PROGRAM C50
--- ---------- ------ ------- ---------- --------- -------- -------- ----------- ------------ --------------- --------------------------------------------------
184 19 53781 38 8 DEDICATED INACTIVE SCOTT sqlplus.exe book oracle@gxqyydg4 alter system kill session '184,19' immediate;
--//這樣連線模式就是專用伺服器模式.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2146886/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- impala客戶端連線客戶端
- Redis客戶端連線Redis客戶端
- 配置ORACLE 客戶端連線到資料庫Oracle客戶端資料庫
- 配置TimesTen客戶端連線[TimesTen運維]客戶端運維
- PL/SQL developer 遠端連線資料庫 客戶端配置SQLDeveloper資料庫客戶端
- mysql、redis 客戶端連線池MySqlRedis客戶端
- vncserver建立與客戶端連線VNCServer客戶端
- 配置SQLNET.ORA檔案跟蹤客戶端連線SQL客戶端
- Redis客戶端連線數DevOpsRedis客戶端dev
- 從客戶端連線ASM例項客戶端ASM
- RAC 11.2.0.3 客戶端軟體Toad連線配置 -- Mac版本客戶端Mac
- 如何配置oracle客戶端連線10g rac 資料庫Oracle客戶端資料庫
- Java 客戶端 Jedis和JedisPool 連線池Java客戶端
- Oracle客戶端連線伺服器教程Oracle客戶端伺服器
- 客戶端連線資料庫的方法客戶端資料庫
- 客戶端連線故障檢查流程手段客戶端
- Oracle 19c中連線RMAN客戶端的連線方法Oracle客戶端
- 使用 WebSocket 客戶端連線 MQTT 伺服器Web客戶端MQQT伺服器
- 客戶端怎麼連線到伺服器？客戶端伺服器
- MySQL client客戶端的四種連線方式MySqlclient客戶端
- linux之客戶端連線數統計Linux客戶端
- 如何配置WSUS客戶端客戶端
- HTTP客戶端連線，選擇HttpClient還是OkHttp？HTTP客戶端client
- ESP作為單連線中的TCP客戶端TCP客戶端
- 客戶端連線RAC報ora-12545客戶端
- 8i客戶端不能連線9i?客戶端
- Presto學習筆記——Go客戶端連線PrestoREST筆記Go客戶端
- 物理DataGuard客戶端無縫切換--客戶端TAF 配置客戶端
- 在Linux 上安裝DB2 8.1 並配置windows 客戶端連線LinuxDB2Windows客戶端
- php連結nsq客戶端PHP客戶端
- 客戶端段建立到伺服器端的連線過程客戶端伺服器
- oracle客戶端連線server 端, tnsnames的三種設定方式Oracle客戶端Server
- 【DATAGUARD】物理dg配置客戶端無縫切換 (八.3)--客戶端TAF 配置客戶端
- 如何使用Redshift客戶端工具SQL Workbench來連線Redshift客戶端SQL
- gRPC學習記錄(六)--客戶端連線池RPC客戶端
- 第三方客戶端連線網易郵箱客戶端
- 4.2.14 啟用客戶端快速連線故障轉移客戶端
- 使用 Java 客戶端透過 HTTPS 連線到 EasysearchJava客戶端HTTP