[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;

--//这样连接模式就是专用服务器模式.