How to enable Oracle Shared Server

How to enable the Oracle Shared Server has unique advantages in some applications, as shown in the official description: reduce the number of instance processes, increase the number of external service users, reduce the number of useless or idle server processes, reduce the oracle database's memory usage and OS overhead, achieve Load Balancing compared to the VPC mode, one important difference is that the shared server requires the use of net services for connection, even if the user and the server process are often on the same machine. However, in some cases, you must use a dedicated server, for example, when using sysdba for login management. Of course, to use these two different modes, it is important that after the SERVER is configured, the client connection string configuration also reflects the dedicated server connection --- (SERVER = DEDICATED) shared server connection --- (SERVER = SHARED) how to configure the shared server? Two parameters are required: DISPATCHERS www.2cto.com SHARED_SERVERS can be configured with the parameter: In most cases, we only need to modify DISPATCHERS. Let's first look at the default dedicated server parameters: [oracle @ test ~] $ Sqlplus "/as sysdba" SQL * Plus: Release 10.2.0.5.0-Production on Tue Aug 14 15:04:26 2012 Copyright (c) 1982,201 0, Oracle. all Rights Reserved. connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options sys @ GT10G> select * from v $ version; BANNER Release -------------------------------------------------- -------------- Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-ProdPL/SQL Release 10.2.0.5.0-ProductionCORE 10.2.0.5.0 Production functions TNS for Linux: Version 10.2.0.5.0-ProductionNLSRTL Version 10.2.0.5.0-Production sys @ GT10G> show parameter disp; name type value ------------------------------------ -------- -------------------------------- dispatchers string (PROTOCOL = TCP) (S ERVICE = gt10gXDB) max_dispatchers integer Shared Server and dedicated server can also be reflected from the listener information. We first record and modify the listening status and information of the previous dedicated server: [oracle @ test ~] $ Lsnrctl status LSNRCTL for Linux: Version 10.2.0.5.0-Production on 14-AUG-2012 15:03:32 Copyright (c) 1991,201 0, Oracle. all rights reserved. connecting to (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = test) (PORT = 1521) STATUS of the LISTENER ------------------------ Alias LISTENERVersion TNSLSNR for Linux: version 10.2.0.5.0-ProductionStart Date 14-AUG-2012 14: 52: 08 Uptime 0 days 0 hr. 11 min. 24 SecTrace Level off www.2cto.com Security ON: Local OS AuthenticationSNMP OFFListener Parameter File/u01/oracle/10g/product/10.2.0/db_1/network/admin/listener. oraListener Log File/u01/oracle/10g/product/10.2.0/db_1/network/log/listener. logListening 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, ha S 1 handler (s) for this service... The command completed successfully [oracle @ test ~] $ Lsnrctl service LSNRCTL for Linux: Version 10.2.0.5.0-Production on 14-AUG-2012 15:03:39 Copyright (c) 1991,201 0, Oracle. all rights reserved. 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" establi Shed: 0 refused: 0 LOCAL SERVERService "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 SERVERService "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 <ma Chine: test, pid: 18635> (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 SERVERThe command completed successfully now we need to set the sharing server. Here we just need to set the DISPATCHERS parameter: sys @ GT10G> alter system set DISPATCHERS = "(PROTOCOL = TCP) (DISPATCHERS = 3) (PRO TOCOL = IPC) (DISPATCHERS = 1) "; System altered. First, let's look at the differences between the listener status and information: [oracle @ test ~] $ Lsnrctl status LSNRCTL for Linux: Version 10.2.0.5.0-Production on 14-AUG-2012 15:07:37 Copyright (c) 1991,201 0, Oracle. all rights reserved. connecting to (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = test) (PORT = 1521) STATUS of the LISTENER ------------------------ Alias LISTENERVersion TNSLSNR for Linux: version 10.2.0.5.0-ProductionStart Date 14-AUG-2012 14: 52: 08 Uptime 0 days 0 hr. 15 min. 28 SecTrace Level off www.2cto.com Security ON: Local OS AuthenticationSNMP OFFListener Parameter File/u01/oracle/10g/product/10.2.0/db_1/network/admin/listener. oraListener Log File/u01/oracle/10g/product/10.2.0/db_1/network/log/listener. logListening 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, ha S 2 handler (s) for this service... The command completed successfully [oracle @ test ~] $ Lsnrctl service LSNRCTL for Linux: Version 10.2.0.5.0-Production on 14-AUG-2012 15:09:22 Copyright (c) 1991,201 0, Oracle. all rights reserved. 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 refuse D: 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 <machine: test, pid: 19006> (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 <machine: test, pid: 18635> (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 <machine: test, pid: 19006> (ADDRESS = (PROTOCOL = ipc) (KEY = #19006.1 )) the command completed successfully compared to show that The biggest change is that The Shared Server shows The scheduling process "D000" in The listener information ". Next, we can modify the tnsname configuration parameters to connect to the server. Then, we can see from the view that we have connected to the server in the sharing mode: GT10G4 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.4) (PORT = 1521) (CONNECT_DATA = (SERVER = SHARED) # Here (SERVICE_NAME = gt10g) www.2cto.com) sys @ GT10G> Select Count (*) from V $ CIRCUIT; COUNT (*) ---------- 1. What is the result if the shared service mode parameter is not configured but the client is configured to connect in the shared service mode? Alter system set DISPATCHERS = "(PROTOCOL = TCP) (SERVICE = gt10gXDB)"; alter system reset DISPATCHERS scope = spfile sid = '*'; after trying the above two statements to find that the parameter values in the memory still cannot be restored, You have to restart the database and find that the database still does not work. Then you can search and find it and clear it directly: sys @ GT10G> alter system set dispatchers = ''scope = both; System altered. sys @ GT10G> show parameter disp; name type value =-------- -------------------------------- dispatchers stringmax_dispatchers integer when the client continues to try to connect in shared mode, an error is reported: C: \ Users \ Administrator> sqlplus gtlions/gtlions @ gt10g4 www.2cto.com SQL * Plus: Release 10.2.0.1.0-Production on Tuesday August 14 15:41:14 2012 Copyright (c) 1982,200 5, Oracle. all rights reserved. ERROR: ORA-12520: TNS: Listeners cannot find available handlers for The requested server type-The End-author gtlions