How to block dedicated server requests in Oracle Database?

Question

I have gone through some docs but no luck. Oracle database is always enabled to allow dedicated server processes. You can't control tnsnames.ora but you need to disallow clients to connect the database service in dedicated server mode.

Is it possible to disable dedicated server connection in server side or to block out dedicated server request from some clients in a technological way?

• bootsoon: If most clients choose dedicated server mode when Oracle Database is exposed on the internet. How can I force them to use shared server mode?
• bootsoon: From docs.oracle.com/database/121/NETAG/intro.htm Web clients that do not require an application web server to access applications can access Oracle Database directly, for example, by using a Java applet. In addition to regular connections, the database can be configured to accept HTTP protocol, FTP, or WebDAV protocol connections. These protocols are used for connections to Oracle XML DB in the Oracle Database instance. I think the dedicated server will consume a lot of resource. How can I do in this situation?

Answer 1

Taken from the Oracle documentation in the article 5.1 About Dedicated and Shared Server Processes:

Your database is always enabled to allow dedicated server processes, but you must specifically configure and enable shared server by setting one or more initialization parameters.

Enabling Shared Servers is a simple as setting the parameter SHARED_SERVERS to a non-null value:

Shared server is enabled by setting the SHARED_SERVERS initialization parameter to a value greater than 0. The other shared server initialization parameters need not be set.

^{Reference: 5.4.3 Enabling Shared Server (Oracle Docs)}

So we've got the server covered, but what about clients?

There are two parameter files where a client can configure how its process will connect to an Oracle instance. These are the tnsnames.ora file and the sqlnet.ora file.

sqlnet.ora

In the sqlnet.ora file the following parameter can be set:

USE_DEDICATED_SERVER=[on|off]

If set to on, then the parameter USE_DEDICATED_SERVER automatically appends (SERVER=dedicated) to the connect data for a connect descriptor. This way connections from this client use a dedicated server process, even if shared server is configured.

Default

off

^{Reference: 5.2.82 USE_DEDICATED_SERVER (Oracle Docs)}

So setting this parameter to OFF on the client side will ensure that ON isn't set for client connections. And the explanation tells us how the settings would look like in the tnsnames.ora file.

...automatically appends (SERVER=dedicated) to the connect data for a connect descriptor

tnsnames.ora

In the tnsnames.ora file each connection (client side) can be set up to either use a shared or a dedicated connection, which in turn results in the oracle.exe spawning an individual process (dedicated) or directing the connection to a dispatcher on the database instance (shared).

dedicated to specify whether client requests be served by dedicated server.

shared to specify whether client requests be served by a dispatcher or shared server.

^{Reference: 6.9.7 SERVER (Oracle Docs)}

Setting this parameter to (SERVER=SHARED) at the client level will ensure that the client will connect to a dispatcher.

Possible Solution

1. Enable Shared Servers at the Oracle instance level by providing the SHARED_SERVERS=1 parameter in your SPFILE or PFILE.

2. Ensure that the clients don't have any of the parameters required to connect using a Dedicated Server in their configuration files (sqlnet.ora or tnsnames.ora).

3. Ensure that USE_DEDICATED_SERVER=ON is set on your Oracle Database Server in the sqlnet.ora file as RMAN and other tasks require a dedicated connection to function correctly.

Answering Your Question

Is it possible to disable dedicated server connection in server side or to block out dedicated server request from some clients in a technological way?

No, because of my first quote:

Your database is always enabled to allow dedicated server processes, but you must specifically configure and enable shared server by setting one or more initialization parameters.

However, if the clients aren't explicitly configured to connect using a dedicated connection, then enabling Shared Servers at the instance level will allow non-configured clients to at least connect using a shared connection, because the default for USE_DEDICATED_SERVER is off.

• bootsoon: Thanks for your excellent answer.
• John K. N.: You are welcome. Hope it helps. Let me know if not. I might be able to assist. We just recently converted our organization to use Shared Server connections for all our clients, which meant performing the outlined steps.

Answer 2

No, no way to block the dedicated server mode. It's an unchangeable feature and an important mechanism in Oracle Database. Actually the shared server mode could be blocked.