[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连接也是共享模式.   参考链接:  >[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; --//这样连接模式就是专用服务器模式.