How to enable OracleSharedServer

How to enable Oracle Shared Server
the
Shared servers still have unique advantages in some applications. From the official description, we can see the following advantages:
Reduce the number of instance processes
Increase the number of users of external services
Reduce the number of processes on useless or idle servers
Reduce oracle database memory usage and OS overhead
To achieve load balancing
Compared with the dedicated server mode, an important difference is that the shared server requires the use of net services to connect, even if the user is often on the same machine as the server process.
But in some cases, you must use a dedicated server: such as when using sysdba to log in for management operations.
Of course, to use these two different modes, it is important to reflect in the client’s connection string configuration after the server is configured
Dedicated server connection — (SERVER = DEDICATED)
Shared server connection — (SERVER = SHARED)
So how to configure the shared server? Two parameters are required:
DISPATCHERS www.2cto.com
SHARED_SERVERS
Optional configuration parameters:
MAX_DISPATCHERS
MAX_SHARED_SERVERS
CIRCUITS
SHARED_SERVER_SESSIONS
In most cases, we only need to modify DISPATCHERS. Let’s first look at the parameters of the default dedicated server:
[oracle@test ~]$ sqlplus “/as sysdba”
the
SQL*Plus: Release 10.2.0.5.0 – Production on Tue Aug 14 15:04:26 2012
the
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
the
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
the
sys@GT10G> select * from v$version;
the
BANNER
————————————————– ————–
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – Prod
PL/SQL Release 10.2.0.5.0 – Production
CORE 10.2.0.5.0 Production www.2cto.com
TNS for Linux: Version 10.2.0.5.0 – Production
NLSRTL Version 10.2.0.5.0 – Production
the
sys@GT10G> show parameter disp;
the
NAME TYPE VALUE
————————————————— ————————
dispatchers string (PROTOCOL=TCP) (SERVICE=gt10gXDB)
max_dispatchers integer
Shared servers and dedicated servers can also be reflected from the information of the listener. We first record the monitoring status and information of the dedicated server before modification:
[oracle@test ~]$ lsnrctl status
the
LSNRCTL for Linux: Version 10.2.0.5.0 – Production on 14-AUG-2012 15:03:32
the
Copyright (c) 1991, 2010, Oracle. All rights reserved.
the
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test)(PORT=1521)))
STATUS of the LISTENER
————————
Alias ​​LISTENER
Version TNSLSNR for Linux: Version 10.2.0.5.0 – Production
Start Date           14-AUG-2012 14:52:08
Uptime 0 days 0 hr. 11 min. 24 sec
Trace Level         off www.2cto.com  
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/oracle/10g/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/oracle/10g/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary…
Service “PLSExtProc” has 1 instance(s).
Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
Service “gt10g” has 1 instance(s).
Instance “gt10g”, status READY, has 1 handler(s) for this service…  
Service “gt10gXDB” has 1 instance(s).
Instance “gt10g”, status READY, has 1 handler(s) for this service…  
Service “gt10g_XPT” has 1 instance(s).
Instance “gt10g”, status READY, has 1 handler(s) for this service…  
The command completed successfully
the
[oracle@test ~]$ lsnrctl service
the
LSNRCTL for Linux: Version 10.2.0.5.0 – Production on 14-AUG-2012 15:03:39
the
Copyright (c) 1991, 2010, Oracle. All rights reserved.
the
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test)(PORT=1521)))
Services Summary…
Service “PLSExtProc” has 1 instance(s).
Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
  Handler(s): www.2cto.com  
“DEDICATED” established:0 refused:0
LOCAL SERVER
Service “gt10g” has 1 instance(s).
Instance “gt10g”, status READY, has 1 handler(s) for this service…  
Handler(s):
“DEDICATED” established:0 refused:0 state:ready
LOCAL SERVER
Service “gt10gXDB” has 1 instance(s).
Instance “gt10g”, status READY, has 1 handler(s) for this service…  
Handler(s):
“D002” established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=test)(PORT=47446))
Service “gt10g_XPT” has 1 instance(s).
Instance “gt10g”, status READY, has 1 handler(s) for this service…  
Handler(s):
“DEDICATED” established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
the
Now we want to set up a shared server, here we just need to set the DISPATCHERS parameter:
sys@GT10G> alter system set DISPATCHERS = “(PROTOCOL=TCP)(DISPATCHERS=3)(PROTOCOL=IPC)(DISPATCHERS=1)”;
the
System altered.
the
Let’s first look at the difference before and after from the monitoring status and information:
[oracle@test ~]$ lsnrctl status
the
LSNRCTL for Linux: Version 10.2.0.5.0 – Production on 14-AUG-2012 15:07:37
the
Copyright (c) 1991, 2010, Oracle. All rights reserved.
the
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test)(PORT=1521)))
STATUS of the LISTENER
————————
Alias ​​LISTENER
Version TNSLSNR for Linux: Version 10.2.0.5.0 – Production
Start Date           14-AUG-2012 14:52:08
Uptime 0 days 0 hr. 15 min. 28 sec
Trace Level         off www.2cto.com  
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/oracle/10g/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/oracle/10g/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary…
Service “PLSExtProc” has 1 instance(s).
Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
Service “gt10g” has 1 instance(s).
Instance “gt10g”, status READY, has 2 handler(s) for this service…  
Service “gt10gXDB” has 1 instance(s).
Instance “gt10g”, status READY, has 1 handler(s) for this service…  
Service “gt10g_XPT” has 1 instance(s).
Instance “gt10g”, status READY, has 2 handler(s) for this service…  
The command completed successfully
the
[oracle@test ~]$ lsnrctl service
the
LSNRCTL for Linux: Version 10.2.0.5.0 – Production on 14-AUG-2012 15:09:22
the
Copyright (c) 1991, 2010, Oracle. All rights reserved.
the
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test)(PORT=1521)))
Services Summary…
Service “PLSExtProc” has 1 instance(s).
Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0
      LOCAL SERVER www.2cto.com  
Service “gt10g” has 1 instance(s).
Instance “gt10g”, status READY, has 2 handler(s) for this service…  
Handler(s):
“DEDICATED” established:0 refused:0 state:ready
LOCAL SERVER
“D000” established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=ipc)(KEY=#19006.1))
Service “gt10gXDB” has 1 instance(s).
Instance “gt10g”, status READY, has 1 handler(s) for this service…  
Handler(s):
“D002” established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=test)(PORT=47446))
Service “gt10g_XPT” has 1 instance(s).
Instance “gt10g”, status READY, has 2 handler(s) for this service…  
Handler(s):
“DEDICATED” established:0 refused:0 state:ready
LOCAL SERVER
“D000” established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=ipc)(KEY=#19006.1))
The command completed successfully
the
By comparison, we can see that the biggest change is that the shared server reflects the scheduling process “D000″ in the monitoring information.
Next, we actually connect by modifying the tnsname configuration parameter, and then we can see through the view that we already have a shared mode connected to the server:
GT10G4 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.4)(PORT = 1521))
(CONNECT_DATA =
(SERVER = SHARED) #The focus is here
(SERVICE_NAME = gt10g)
) www.2cto.com
)
sys@GT10G> Select Count(*) From V$CIRCUIT;
the
COUNT(*)
———-
1
So, if we do not configure the parameters of the shared service mode, but the client is configured to use the shared mode connection, what will be the result?
alter system set DISPATCHERS=”(PROTOCOL=TCP) (SERVICE=gt10gXDB)”;
alter system reset DISPATCHERS scope=spfile sid='*';
After trying the above two statements, I found that the parameter values ​​in the memory could not be restored, so I had to restart the database, but it still didn’t work. After searching, I found that it can be cleared directly:
sys@GT10G> alter system set dispatchers='' scope=both;
the
System altered.
the
sys@GT10G> show parameter disp;
the
NAME TYPE VALUE
————————————————— ————————
dispatchers string
max_dispatchers integer
At this time, when the client continues to try to connect in shared mode, an error will be reported:
C:\Users\Administrator>sqlplus gtlions/gtlions@gt10g4
www.2cto.com
SQL*Plus: Release 10.2.0.1.0 – Production on Tue Aug 14 15:41:14 2012
the
Copyright (c) 1982, 2005, Oracle. All rights reserved.
the
ERROR:
ORA-12520: TNS:listener could not find an available handler for the requested server type
the
-The End-
the
the
Author gtlions

Below you can see that the biggest change is that the shared server reflects the scheduling process “D000″ in the monitoring information.
Next, we actually connect by modifying the tnsname configuration parameter, and then we can see through the view that we already have a shared mode connected to the server:
GT10G4 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.4)(PORT = 1521))
(CONNECT_DATA =
(SERVER = SHARED) #The focus is here
(SERVICE_NAME = gt10g)
) www.2cto.com
)
sys@GT10G> Select Count(*) From V$CIRCUIT;
the
COUNT(*)
———-
1
So, if we do not configure the parameters of the shared service mode, but the client is configured to use the shared mode connection, what will be the result?
alter system set DISPATCHERS=”(PROTOCOL=TCP) (SERVICE=gt10gXDB)”;
alter system reset DISPATCHERS scope=spfile sid='*';
After trying the above two statements, I found that the parameter values ​​in the memory could not be restored, so I had to restart the database, but it still didn’t work. After searching, I found that it can be cleared directly:
sys@GT10G> alter system set dispatchers='' scope=both;
the
System altered.
the
sys@GT10G> show parameter disp;
the
NAME TYPE VALUE
————————————————— ————————
dispatchers string
max_dispatchers integer
At this time, when the client continues to try to connect in shared mode, an error will be reported:
C:\Users\Administrator>sqlplus gtlions/gtlions@gt10g4
www.2cto.com
SQL*Plus: Release 10.2.0.1.0 – Production on Tue Aug 14 15:41:14 2012
the
Copyright (c) 1982, 2005, Oracle. All rights reserved.
the
ERROR:
ORA-12520: TNS:listener could not find an available handler for the requested server type
the
-The End-
the
the
Author gtlions