OCM 11g Preparation - Configure the database instance to support shared server connections

It's hard to find servers that use shared server option. Usually in my customers, the database is connected by a middleware that manage the client connections and only make a few connections to the database.

Anyway, for OCM you may need to think in a DB that is accessed by 4k clients connections and only have 2G memory space. How to resolve it? Shared server!

What do you need to know:

  • How to enable/disable it (not only using GUI).
  • Configure TNS for it.
  • Manage all parameters involved:
    • SHARED_SERVERS
    • MAX_SHARED_SERVERS
    • SHARED_SERVER_SESSIONS
    • DISPATCHERS
    • CIRCUITS
  • Understand and change number of dispatchers / circuits / shared server processes / etc.
  • Test and check usage/performance using dictionary views.

Path to Documentation:

Administrator’s Guide -> 5 Managing Processes -> Configuring Oracle Database for Shared Server


Click here to go back to the Main OCM 11g Preparation page.

Have you enjoyed? Please leave a comment or give a 👍!

1 comments

    • Radek on April 14, 2016 at 16:22
    • Reply

    Play with multiple shared server connections and see the difference when you set the MAX_SHARED_SERVERS parameter (connections are waiting to be freed)
    While connecting/disconnecting shared server sessions observe the following dictionary views:
    - V$SHARED_SERVER (how many servers get created? What happens when you disconnect one session and immediately start another session? Does the new shared server get created or the disconnected one is reused ? )
    - V$QUEUE (what is the queue and response status for the dispatcher and common types? What does it show when you have sessions waiting to be connected?)
    - V$DISPATCHER (messages and bytes processed)
    - V$SHARED_SERVER_MONITOR
    - V$DISPATCHER_CONFIG

    Example of the shared server configuration:
    -------------------------------------------

    13:19:58 SQL> sho parameter dispat

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    dispatchers string (PROTOCOL=TCP)(SERVICE=baza2_shared)

    13:20:02 SQL> sho parameter shared

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    shared_servers integer 2

    tnsnames.ora:

    BAZA2_SHARED =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dziunia)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVER = SHARED)
    (SERVICE_NAME = baza2_shared)
    )
    )

    Connect to shared service:

    oracle@dziunia[baza2]:/home/oracle> sqlplus sys/sys@baza2_shared as sysdba

    oracle@dziunia[baza2]:/u01/app/oracle/product/11.2.0/db_1/network/admin> lsnrctl ser

    LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 13-APR-2016 13:23:28

    Copyright (c) 1991, 2013, Oracle. All rights reserved.

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dziunia.localdomain)(PORT=1521)))
    Services Summary...
    Service "baza2" has 1 instance(s).
    Instance "baza2", status READY, has 1 handler(s) for this service...
    Handler(s):
    "DEDICATED" established:0 refused:0 state:ready
    LOCAL SERVER
    Service "baza2_shared" has 1 instance(s).
    Instance "baza2", status READY, has 2 handler(s) for this service...
    Handler(s):
    "D000" established:3 refused:0 current:2 max:1022 state:ready
    DISPATCHER
    (ADDRESS=(PROTOCOL=tcp)(HOST=dziunia.localdomain)(PORT=52862))
    "DEDICATED" established:0 refused:0 state:ready
    LOCAL SERVER
    The command completed successfully

Leave a Reply

Your email address will not be published.