Configure listener for dedicated with shared servers

I did a little research yesterday and I am writing this post to document what I learned so I can remember it and since I could not easily find this information. I have a database that uses shared servers and want to connect to it using a dedicated server connection. Everything I found said to add (SERVER = DEDICATED) on your client but I was not able to do that. A coworker of mine said that I should be able to add a service that only connects as a dedicated server process so I tried to figure out how to do that. I found a way to configure a service in the listener.ora file on the database server so that anyone connecting with that service name would only get a dedicated server connection. I tested this on Oracle 11.2.0.4 on Linux

I setup a test database for shared servers with these parameters:

alter system set max_shared_servers = 2 scope=both;

alter system set shared_servers=2 scope=both;
alter system set dispatchers='(PROTOCOL=TCP)(DISPATCHERS=2)' scope=both;

I set the service_name and domain to be like production:

SQL> show parameter service

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      ORCL

SQL> show parameter domain

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_domain                            string      WORLD

I set the listener.ora similar to production:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = ora1124)(PORT = 1521))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL.WORLD)
      (SID_NAME = ORCL)
      (ORACLE_HOME = /oracle/product/db/11.2.0.4)
    )
  )

I stopped the listener and database and then started the listener and database. At this point if I connected using (SERVICE_NAME = ORCL.WORLD) or (SID = ORCL) I got a shared server process. For example:

ORCL1124.world =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(Host = 127.0.0.1)(Port = 61020))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL.WORLD)
    )
  )

ORCL1124.world =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(Host = 127.0.0.1)(Port = 61020))
    )
    (CONNECT_DATA =
      (SID = ORCL)
    )
  )

To this point all I was doing was matching a production database of a different name’s configuration. What I wanted to do was do something to the listener.ora to add a new service name that only connects dedicated. I changed the SID_LIST_LISTENER part of the listener.ora to this:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL.WORLD)
      (SID_NAME = ORCL)
      (ORACLE_HOME = /oracle/product/db/11.2.0.4)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ORCLDED)
      (SID_NAME = ORCL)
      (ORACLE_HOME = /oracle/product/db/11.2.0.4)
    )
  )

This added the service name ORCLDED that only connects dedicated. I used a tns entry like this:

ORCL1124.world =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(Host = 127.0.0.1)(Port = 61020))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCLDED)
    )
  )

I verified this by running this query with the connection:

select username,server,count(*)
from v$session
group by username,server
order by username,server;

It would show my user’s session as either SHARED or DEDICATED.

Also, I could tell from the listener status:

[oracle@ora1124 admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 13-JAN-2021 14:46:26

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora1124)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                13-JAN-2021 14:45:51
Uptime                    0 days 0 hr. 0 min. 36 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/product/db/11.2.0.4/network/admin/listener.ora
Listener Log File         /oracle/product/diag/tnslsnr/ora1124/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora1124)(PORT=1521)))
Services Summary...
Service "ORCL.WORLD" has 2 instance(s).
  Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
  Instance "ORCL", status READY, has 3 handler(s) for this service...
Service "ORCLDED" has 1 instance(s).
  Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Notice that service ORCLDED only has 1 handler which is the one to spawn dedicated server processes. ORCL.WORLD has 3 handlers which includes 2 for the 2 dispatchers.

I also experimented with adding the setting SERVICE= to the dispatchers parameter but it did not meet my needs in this case. Here is the format of the parameter as I tested it:

alter system set dispatchers='(PROTOCOL=TCP)(DISPATCHERS=2)(SERVICE=SS)' scope=both;

With this setting the listener status had this entry for service SS:

Service "SS" has 1 instance(s).
  Instance "ORCL", status READY, has 2 handler(s) for this service...

So, it looks like setting the service in the dispatchers parameter would pull out the shared server process connections on to that service but that was not what I needed in this case.

Anyway, I messed with this yesterday and thought I would put it out there for my own memory and in case others need it.

Bobby