Passer de Serveur Dédié à Serveur partagé (Oracle)

ORACLE SWITCHING FROM DEDICATED SERVER TO SHARED SERVER (MTS)
By Edward Stoever

The switch from dedicated server to shared server is easy. You begin by editing the init.ora file for your instance. You will need to either add in a line for dispatchers, or edit the existing line (When creating a database with DBCA on Windows, Oracle adds in a line even if you don’t request dispatchers. So, be aware of the fact that you may just be changing that line).
At minimum, you will need:
    dispatchers= »(protocol=tcp) »
A more complete configuration would be:
    dispatchers= »(protocol=tcp)(dispatchers=2)(service=mydatabase) »
The (dispatchers=2) that you see tells oracle to spawn 2 dispatchers at startup.

Configuring dispatchers can get more elaborate. Unless you have experience and documentation for setting up a more advanced configuration, it is probably better to allow Oracle to give you default values for everything else. A complete list of possible dispatcher parameters is here:

dispatch_clause::=
            (PROTOCOL = protocol) |
            (ADDRESS = address) |
            (DESCRIPTION = description )
            [options_clause]


options_clause::=
            (DISPATCHERS = integer |
             SESSIONS = integer |
             CONNECTIONS = integer |
             TICKS = seconds |
             POOL = {1 | ON | YES | TRUE | BOTH |
                ({IN | OUT} = ticks) | 0 | OFF | NO | FALSE | ticks} |
             MULTIPLEX = {1 | ON | YES | TRUE | 0 | OFF | NO | FALSE | BOTH | IN | OUT} |
             LISTENER = tnsname |
             SERVICE = service |
             INDEX = integer
            )

There are some other parameters you will likely want to set as long as you are working on dispatchers. Here is an example:

###########################################
# DISPATCHERS
###########################################
dispatchers="(protocol=tcp)(dispatchers=2)(service=test)"

## shared servers on startup
shared_servers=2

## maximum shared server sessions
shared_server_sessions=200

## Maximum Shared Servers
max_shared_servers=20

## Maximum Dispatchers
max_dispatchers=20

Once you have changed your init.ora file and bounced your database, how can you tell if connections are going through dispatchers? There are a number of dynamic performance views that can tell you this, for example v$session, v$dispatcher, v$queue. To keep things easy, I just create my own view that gives me the data I like to see:

CREATE OR REPLACE FORCE VIEW SYSTEM.CURRENT_CONNECTIONS
(SID, SERIAL#, USERNAME, OSUSER, STATUS, 
 "SCNDS NOT ACTIVE", DISPATCHER)
AS 
SELECT  /* ©2004 by Edward Stoever, edward@database-expert.com */ 
 s.SID, s.serial#, s.username, s.osuser, s.status, 
         DECODE (s.username, NULL, 0, s.last_call_et) "SCNDS NOT ACTIVE", 
         NVL (d.NAME, 'none') "DISPATCHER" 
    FROM v$session s, v$dispatcher d 
   WHERE s.paddr = d.paddr(+) 
ORDER BY status ASC, last_call_et ASC;


CREATE PUBLIC SYNONYM CURRENT_CONNECTIONS FOR SYSTEM.CURRENT_CONNECTIONS;

Sample output:

select * from system.current_connections;

   SID SERIAL# USERNAME     OSUSER       STATUS   SCNDS NOT ACTIVE DISPAT
------ ------- ------------ ------------ -------- ---------------- ------
    15     167 SYSTEM       STOEVER      ACTIVE                  0 none
    11      10 GENERAL      GURJOBS_TEST ACTIVE              12535 none
     1       1 @            ORACLE       ACTIVE                  0 none
     2       1 @            ORACLE       ACTIVE                  0 none
     3       1 @            ORACLE       ACTIVE                  0 none
     4       1 @            ORACLE       ACTIVE                  0 none
     5       1 @            ORACLE       ACTIVE                  0 none
     6       1 @            ORACLE       ACTIVE                  0 none
     7       1 @            ORACLE       ACTIVE                  0 none
     8       1 @            ORACLE       ACTIVE                  0 none
    18     176 WTAILOR      jbautista    INACTIVE               12 D000
    16     191 WTAILOR      jbautista    INACTIVE              132 D001
    10     212 WTAILOR      jbautista    INACTIVE              134 D001
    13     119 WEB_USER     SYSTEM       INACTIVE              314 D000
    14      20 SYSTEM       vlugo        INACTIVE              721 D001

Now you can see who is connecting and how, either via a dispatcher or via a dedicated connection.
Does this mean that all connections from now on will be through a dispatcher? No. There are plenty of cases in which you will want or even require a connection that is dedicated. For example, to shutdown the database, a dedicated connection is required. Also, many processes are recource intensive and will perform better with a dedicated connection. To create a dedicated connection, you will need to edit the TNSNAMES.ORA file on the machine from which the connection originates. Here is an example:

### SHARED CONNECTION TO TEST DATABASE
TEST_SHARED =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = alpha2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = SHARED)
      (SERVICE_NAME = test)
    )
  )

### DEDICATED CONNECTION TO TEST DATABASE
TEST_DEDICATED =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = alpha2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test)
    )
  )
  
  

Now, to connect via a dispatcher, try this:

SQLPLUS scott/tiger@test_shared

to connect via a dedicated server process, try this:

SQLPLUS scott/tiger@test_dedicated