Oracle: Configure the database instance to support shared server connections

The shared server architecture enables a database server to allow many user processes to share very few server processes, so the number of users that can be supported is increased. With shared server, many user processes connect to a dispatcher. The dispatcher directs multiple incoming network session requests to a common queue. An idle shared server process from a shared pool of server processes picks up a request from the queue. This means a small pool of server processes can serve a large number of clients.

Shared memory resources are preconfigured to allow the enabling of shared server at run time. There is just needed to set one parameter if you are using TCP/IP as your communication protocol, and the rest of the settings are now managed internally.

Parameter connected to managing shared servers:

• shared_servers - specifies the number of server processes that you want to create when an instance is started. This is the only required parameter for using shared servers. Setting this to a non-zero value automatically specifies shared server.
• max_shared_servers - specifies the maximum number of shared server processes allowed to be running simultaneously. Setting this parameter enables you to reserve process slots for other processes, such as dedicated servers.
• shared_server_sessions - specifies the total number of shared server sessions to allow.
• dispatchers - configures dispatcher processes in the shared server architecture. One dispatcher is always configured by default for the TCP/IP protocol, even if the parameter is not explicitly specified.
• max_dispatchers - specifies the maximum number of dispatcher processes allowed to be running simultaneously
• circuits - specifies the total number of virtual circuits that are available for inbound and outbound network sessions

Configuring Dispatchers

SQL> alter system set shared_servers = 2 scope=both; System altered. SQL> alter system set dispatchers='(PROTOCOL=tcp)(DISPATCHERS=2)' scope=both; System altered. SQL> show parameter shared_servers NAME TYPE VALUE -------------------- ----------- --------- max_shared_servers integer shared_servers integer 2 SQL> show parameter dispatcher NAME TYPE VALUE -------------------- ----------- ------------------------------ dispatchers string (PROTOCOL=tcp)(DISPATCHERS=2)

tnsnames.ora

PROD database will listen on the port 51521 and the database PROD2 will listen on the port 1521.

PROD= (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=oralin1.oracle.com)(PORT=51521)) (CONNECT_DATA= (SERVICE_NAME=PROD))) PROD2= (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=oralin1.oracle.com)(PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=PROD)(SERVER=SHARED)))

Check configuration

Shared connection:

Open two consoles:
- connect to sqlplus via listener with account SYSTEM
- check the connection of the account SYSTEM

First console: [oracle@oralin1 admin]$ sqlplus system/oracle@PROD2 SQL*Plus: Release 11.1.0.7.0 - Production on Wed Apr 11 16:02:20 2012 Copyright (c) 1982, 2008, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> Second console: SQL> select username, server from v$session where username='SYSTEM'; USERNAME SERVER ------------------------------ --------- SYSTEM NONE

Dedicated connection:

Again, open two consols, one for connection and second for checking of the connection

First console: oracle@oralin1 admin]$ sqlplus system/oracle@PROD SQL*Plus: Release 11.1.0.7.0 - Production on Wed Apr 11 16:22:39 2012 Copyright (c) 1982, 2008, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> Second console: SQL> select username, server from v$session where username='SYSTEM'; USERNAME SERVER ------------------------------ --------- SYSTEM DEDICATED

Non default port

In the case, that shared servers are configured on the another port as is default port (1521), it's neccessary to set the parameter local_listener:

SYS@ORCL> show parameter local NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener string log_archive_local_first boolean TRUE SYS@ORCL> alter system set local_listener='(ADDRESS = (PROTOCOL=TCP)(HOST=oralin1.oracle.com)(PORT=1526))' scope=both; System altered. SYS@ORCL> show parameter local NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener string (ADDRESS = (PROTOCOL=TCP)(HOST =oralin1.oracle.com)(PORT=1526 )) log_archive_local_first boolean TRUE SYS@ORCL> exit