Go back to OCM 12c Preparation Project
Special Thanks to www.dbarj.com.br
Hands On Lab – Index
[1] How Shared Server works ?
[2] Configure the Shared Server
[2.1] Configure DISPATCHER with cdb
[2.1] Configure DISPATCHER with PDB on non default listener port
[2.1] Stop and Start DISPATCHER and test the connection.
- The objective of SHARED SERVERS configure connections to reduce resource consumption of the database. - The example that is put in the documentation is a certain type of DB where orders are taken. We assume we have 1,000 telephone operators taking orders over the phone and recording them in the database. With dedicated connections could have 1,000 sessions in the database, one for each operator. But when you consider that most of the time, talking to the operator (90% time) client instead of putting the data in the database, we can conclude that we need only 10% of the sessions. We could configure shared connections 100 (instead of 1000) and thus releasing the memory 900 unused sessions. This free memory may be useful to us in other areas of the server (more CACHE, another instance of DB, DB outsiders running on the same machine, etc … processes). - We review the documentation specific example, where memory compared various settings: Dedicate, SHARED and DRCP (this will be discussed later). In the example taken into account we have 5000 connections and memory required for each session is 400KB and 4MB per server process. - POOL size for DRCP is 100 and we have 100 SHARED SERVERS. Dedicate SERVER => 5000 CONN X (400KB / SESS + 4 MB / SERVPROC) = 22GB SHARED SERVER => (5000 CONN X 400KB / SESS) + (100 SHAREDSERVERS X 4 MB / SERVPROC) = 2.5GB DRCP => 100 POOLSERVERS X (400KB / SESS + 4 MB / SERVPROC) + (5,000 x CONN 35KB / CONN) = 615MB [1] How Shared Server works ? 1. client user processes connect to a dispatcher Note: The dispatcher can support multiple client connections concurrently. Each client connection is bound to a virtual circuit, which is a piece of shared memory used by the dispatcher for client database connection requests and replies. 2. The dispatcher places a virtual circuit on a common queue when a request arrives. 3. An idle shared server process picks up the virtual circuit from the common queue, 4. Shared server process - services the request 5. Relinquishes the virtual circuit in Response Queue. 6. Dispatcher process send an replies back to the client from response Queue. Note: Shared server architecture requires Oracle Net Services. User processes targeting the shared server must connect through Oracle Net Services, even if they are on the same system as the Oracle Database instance. [2] Configure the Shared Server 5.3 Configuring Oracle Database for Shared Server 5.3.1 Initialization Parameters for Shared Server SHARED_SERVERS - This is the only required parameter for using shared servers. - Specifies the initial number of shared servers to start and the minimum number of shared servers to keep MAX_SHARED_SERVERS - Specifies the maximum number of shared servers that can run simultaneously. SHARED_SERVER_SESSIONS - Specifies the total number of shared server user sessions that can run simultaneously. - Setting this parameter enables you to reserve user sessions for dedicated servers. DISPATCHERS - Configures dispatcher processes in the shared server architecture. MAX_DISPATCHERS - Specifies the maximum number of dispatcher processes that can run simultaneously. CIRCUITS - Specifies the total number of virtual circuits that are available for inbound and outbound network sessions. 5.3.2 Memory Management for Shared Server - Shared server requires some user global area (UGA) in either the shared pool or large pool. 5.3.3 Enabling Shared Server 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. [1] Set shared server dynamically by setting the SHARED_SERVERS initialization parameter to a nonzero value with the ALTER SYSTEM statement. [2] Set the SHARED_SERVERS initialization parameter to a nonzero value at database startup by including it in the initialization parameter file. In typical systems, the number of shared servers stabilizes at a ratio of one shared server for every ten connections. The PMON (process monitor) background process cannot terminate shared servers below the value specified by SHARED_SERVERS. Notes: [1]If SHARED_SERVERS is not included in the initialization parameter file at database startup, but DISPATCHERS is included and it specifies at least one dispatcher, shared server is enabled. In this case, the default for SHARED_SERVERS is 1. [2] If neither SHARED_SERVERS nor DISPATCHERS is included in the initialization file, you cannot start shared server after the instance is brought up by just altering the DISPATCHERS parameter. You must specifically alter SHARED_SERVERS to a nonzero value to start shared server. [3] If you create your Oracle database with Database Configuration Assistant (DBCA), DBCA configures a dispatcher for Oracle XML DB (XDB). This is because XDB protocols like HTTP and FTP require shared server. This results in a SHARED_SERVER value of 1. Although shared server is enabled, this configuration permits only sessions that connect to the XDB service to use shared server. To enable shared server for regular database sessions (for submitting SQL statements),you must add an additional dispatcher configuration, or replace the existing configuration with one that is not specific to XDB. - First let’s review the default settings that have to be installed with the DB DBCA. show parameter Dispatchers show parameter shared_servers Note: We see that we have a dispatcher configured for OCMXDB service (XML DB) The shared_servers parameter takes the default value (1), because we defined the parameter Dispatchers show spparameter Dispatchers show spparameter shared_servers 5.3.4 Configuring Dispatchers The DISPATCHERS initialization parameter configures dispatcher processes in the shared server architecture. At least one dispatcher process is required for shared server to work. If you do not specify a dispatcher, but you enable shared server by setting SHARED_SERVER to a nonzero value, then by default Oracle Database creates one dispatcher for the TCP protocol. The equivalent DISPATCHERS explicit setting of the initialization parameter for this configuration is: dispatchers="(PROTOCOL=tcp)" To calculate the initial number of dispatchers to create during instance startup, use the following formula: Number of dispatchers = CEIL ( max. concurrent sessions / connections for each dispatcher ) For example, assume a system that can support 970 connections for each process, and that has: A maximum of 4000 sessions concurrently connected through TCP/IP and A maximum of 2,500 sessions concurrently connected through TCP/IP with SSL The DISPATCHERS attribute for TCP/IP should be set to a minimum of five dispatchers (4000 / 970), and for TCP/IP with SSL three dispatchers (2500 / 970). DISPATCHERS='(PROT=tcp)(DISP=5)', '(PROT=tcps)(DISP=3)' Example: Typical This is a typical example of setting the DISPATCHERS initialization parameter. DISPATCHERS="(PROTOCOL=TCP)(DISPATCHERS=2)" Example: Forcing the IP Address Used for Dispatchers DISPATCHERS="(ADDRESS=(PROTOCOL=TCP)(HOST=144.25.16.201))(DISPATCHERS=2)" Example: Forcing the Port Used by Dispatchers DISPATCHERS="(ADDRESS=(PROTOCOL=TCP)(PORT=5000))" DISPATCHERS="(ADDRESS=(PROTOCOL=TCP)(PORT=5001))" For example, suppose the instance was started with this DISPATCHERS setting in the initialization parameter file: DISPATCHERS='(PROT=tcp)(DISP=2)', '(PROT=tcps)(DISP=2)' To increase the number of dispatchers for the TCP/IP protocol from 2 to 3, and decrease the number of dispatchers for the TCP/IP with SSL protocol from 2 to 1, you can issue the following statement: ALTER SYSTEM SET DISPATCHERS = '(INDEX=0)(DISP=3)', '(INDEX=1)(DISP=1)'; or ALTER SYSTEM SET DISPATCHERS = '(PROT=tcp)(DISP=3)', '(PROT=tcps)(DISP=1)'; 5.3.4.5 Shutting Down Specific Dispatcher Processes SELECT NAME, NETWORK FROM V$DISPATCHER; ALTER SYSTEM SHUTDOWN IMMEDIATE 'D002'; 5.3.5 Disabling Shared Server Set the SHARED_SERVERS initialization parameter to 0. To terminate dispatchers once all shared server clients disconnect, enter this statement: ALTER SYSTEM SET DISPATCHERS = ''; Database Net Services Administrator's Guide -> 11 Configuring a Shared Server Architecture -->Configuring Clients for Environments with Both Shared and Dedicated Servers sales= (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=sales.us.example.com) (SERVER=shared))) sales= (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=sales.us.example.com) (SERVER=dedicated))) You can configure the client profile file, sqlnet.ora, with USE_DEDICATED_SERVER=on. This adds (SERVER=dedicated) to the CONNECT_DATA section of the connect descriptor the client uses. HOL : Suppose you have 100 concurrent sessions (representing 100 users), and they are only working on the DB 10% of the time. Then we set up 10 server processes => shared_servers = 10. ALTER SYSTEM SET Shared_servers= 10 SCOPE = BOTH ; We reviewed the processes that have been created SELECT * FROM v$shared_server; - We set up to SHARED SERVERS (generally is limited by the PROCESSES parameter) ALTER SYSTEM SET max_shared_servers = 100 SCOPE = BOTH; We can limit the number of virtual circuits using Dispatchers - A CIRCUIT represents an area of shared memory that is used to handle requests and responses - We can limit your number with the parameter CIRCUITS; It can be used to limit the impact on the SGA - ALTER SYSTEM SET CIRCUITS=100 SCOPE = BOTH; – - Once we have defined a set of SHARED SERVERS we will set the dispatchers. An initial value can be obtained by dividing the maximum number of concurrent sessions between the maximum number of connections for each dispatcher. In our machine we have defined a limit of 1,024 connections per process (maximum number of open file descriptors => ulimit -n). If we could get to have 4,000 concurrent sessions, then we define 4000/1024 = 4 (rounding up). The calculation we have done is CEIL (USU MAX concurrents / CONN / PROCESS…) = CEIL (4000/1024) = 4 - We enable a new service dispatcher for pdb1 If you do not specify INDEX, the dispatcher with the same is amended DESCRIPTION, ADDRESS, or PROTOCOL - Use INDEX = 1 to avoid changing the service dispatcher OCMXDB - We can see that INDEX must each dispatcher at the CONF_INDX field in V$DISPATCHER_CONFIG ALTER SYSTEM SET dispatchers ='(INDEX=1)(PROTOCOL=TCP)(dispatchers=4)(SERVICE=pdb1)' SCOPE = BOTH; - We found that two processes have been set DISPATCHER: - · We will see four rows with CONF_INDX = 1 - · We also found the service dispatcher cdb1XDB (CONF_INDX = 0) SELECT * FROM V$DISPATCHER; #We will configure the connection descriptor in the file $ORACLE_HOME/network/admin/tnsnames.ora to make a test by connecting SHARED SERVERS. # Add the following entry to the file $ ORACLE_HOME / network / admin / tnsnames.ora pdb1test = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = racnode1) (PORT = 1521)) ) (CONNECT_DATA = (SERVER = SHARED) (SERVICE_NAME = pdb1) ) ) # We connect using this descriptor sqlplus system@pdb1test # Using another terminal, review the pdb1 service dispatchers and see if we have connected # In our example we have connected via D004: lsnrctl services We may find that sessions are connected via SHARED SERVERS The column SERVER V$SESSION will be NONE (inactive) or SHARED (active) for sessions using SHARED SERVERS - · If we launch this consultation session SHARED SERVERS see the SERVER = “SHARED” value - · If lazamos this query from another session will see the SERVER = “None” for the same SID SELECT SID, USERNAME, PROGRAM, SERVICE_NAME, SERVER FROM V$SESSION WHERE SERVER IN ('SHARED', 'NONE'); select server,count(*) from v$session group by server; - We can see state of SHARED SERVERS SELECT * FROM V$shared_server; #We have the possibility of eliminating the dispatcher process we want. Here’s an example: - Eliminated the dispatcher D003 ALTER SYSTEM SHUTDOWN IMMEDIATE 'D003'; - We check that is eliminated. This operation may take some time, but specify the IMMEDIATE SELECT NAME, STATUS, ACCEPT FROM V$DISPATCHER; #We reverse all changes. ALTER SYSTEM RESET dispatchers SCOPE = SPFILE; ALTER SYSTEM SET dispatchers = '(PROTOCOL = TCP) (SERVICE = OCMXDB)' SCOPE = SPFILE; ALTER SYSTEM RESET shared_servers SCOPE = SPFILE; ALTER SYSTEM RESET max_shared_servers SCOPE = SPFILE; ALTER SYSTEM RESET CIRCUITS SCOPE = SPFILE; - Restart the instance to get rid of the configuration of the second DISPATCHER SHUTDOWN IMMEDIATE STARTUP === HOL : Configure DISPATCHER with cdb === -bash-4.1$ export ORACLE_SID=cdb1 -bash-4.1$ sql SQL> STARTUP ORACLE instance started. Total System Global Area 704643072 bytes Fixed Size 2928392 bytes Variable Size 293601528 bytes Database Buffers 402653184 bytes Redo Buffers 5459968 bytes Database mounted. Database opened. SQL> SQL> show parameter shared_server NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ max_shared_servers integer shared_server_sessions integer shared_servers integer 1 SQL> SQL> show parameter dispatchers NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dispatchers string (PROTOCOL=TCP) (SERVICE=cdb1XDB) max_dispatchers integer SQL> SELECT * FROM V$SHARED_SERVER; NAME PADDR STATUS MESSAGES BYTES BREAKS ---- ---------------- ---------------- ---------- ---------- ---------- CIRCUIT IDLE BUSY IN_NET OUT_NET REQUESTS ---------------- ---------- ---------- ---------- ---------- ---------- CON_ID ---------- S000 0000000088E3A2A0 WAIT(COMMON) 0 0 0 00 46700 7 0 0 0 0 SQL> show parameter shared_server_session NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ shared_server_sessions integer SQL> show parameter circuits NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ circuits integer SQL> ALTER SYSTEM SET SHARED_SERVERS=10; System altered. SQL> show parameter shared_servers NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ max_shared_servers integer shared_servers integer 10 SQL> ALTER SYSTEM SET MAX_SHARED_SERVERS=100; System altered. SQL> show parameter max_shared_servers; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ max_shared_servers integer 100 SQL> SQL> set pagesize 1000 SQL> select * from v$shared_server; NAME PADDR STATUS MESSAGES BYTES BREAKS ---- ---------------- ---------------- ---------- ---------- ---------- CIRCUIT IDLE BUSY IN_NET OUT_NET REQUESTS ---------------- ---------- ---------- ---------- ---------- ---------- CON_ID ---------- S000 0000000088E3A2A0 WAIT(COMMON) 0 0 0 00 61103 7 0 0 0 0 S001 0000000088E45520 WAIT(COMMON) 0 0 0 00 7800 0 0 0 0 0 S002 0000000088E481C0 WAIT(COMMON) 0 0 0 00 7796 0 0 0 0 0 S003 0000000088E48CE8 WAIT(COMMON) 0 0 0 00 7795 0 0 0 0 0 S004 0000000088E49810 WAIT(COMMON) 0 0 0 00 7794 0 0 0 0 0 S005 0000000088E4A338 WAIT(COMMON) 0 0 0 00 7792 1 0 0 0 0 S006 0000000088E4AE60 WAIT(COMMON) 0 0 0 00 7791 0 0 0 0 0 S007 0000000088E4B988 WAIT(COMMON) 0 0 0 00 7790 0 0 0 0 0 S008 0000000088E4C4B0 WAIT(COMMON) 0 0 0 00 7789 0 0 0 0 0 S009 0000000088E4CFD8 WAIT(COMMON) 0 0 0 00 7787 0 0 0 0 0 10 rows selected. SQL> ALTER SYSTEM SET CIRCUITS=100; System altered. SQL> show parameter circuits NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ circuits integer 100 SQL> SQL> show parameter dispatchers NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dispatchers string (PROTOCOL=TCP) (SERVICE=cdb1XDB) max_dispatchers integer SQL> ALTER SYSTEM SET DISPATCHERS='(INDEX=1)(PROTOCOL=TCP)(PORT=5001)(HOST=192.168.56.101)(DISPATCHERS=4)(SERVICE=cdb1)'; ALTER SYSTEM SET DISPATCHERS='(INDEX=1)(PROTOCOL=TCP)(PORT=5001)(HOST=192.168.56.101)(DISPATCHERS=4)(SERVICE=cdb1)' * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-00119: invalid specification for system parameter DISPATCHERS ORA-00111: invalid attribute PORT SQL> alter system set dispatchers='(INDEX=1)(ADDRESS=(PROTOCOL=TCP)(PORT=5001)(HOST=192.168.56.101)) (DISPATCHERS=4)(SERVICE=cdb1)'; System altered. SQL> show parameter dispatchers NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dispatchers string (PROTOCOL=TCP)(SERVICE=cdb1XDB ), (ADDRESS=(PROTOCOL=TCP)(POR T=5001)(HOST=192.168.56.101))( DISPATCHERS=4)(SERVICE=cdb1) max_dispatchers integer SQL> SQL> SELECT * FROM V$DISPATCHER; NAME ---- NETWORK -------------------------------------------------------------------------------- PADDR STATUS ACC MESSAGES BYTES BREAKS ---------------- ---------------- --- ---------- ---------- ---------- OWNED CREATED IDLE BUSY LISTENER CONF_INDX CON_ID ---------- ---------- ---------- ---------- ---------- ---------- ---------- D000 (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=25234)) 0000000088E39778 WAIT YES 0 0 0 0 0 19855 0 0 0 0 D001 (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.101)(PORT=5001)) 0000000088E4CFD8 WAIT YES 0 0 0 0 0 4623 0 0 1 0 D002 (ADDRESS=(PROTOCOL=TCP)(PORT=5001)(HOST=192.168.56.101)) 0000000088E58258 WAIT NO 0 0 0 0 0 0 0 0 1 0 D003 (ADDRESS=(PROTOCOL=TCP)(PORT=5001)(HOST=192.168.56.101)) 0000000088E5BA20 WAIT NO 0 0 0 0 0 0 0 0 1 0 D004 (ADDRESS=(PROTOCOL=TCP)(PORT=5001)(HOST=192.168.56.101)) 0000000088E5C548 WAIT NO 0 0 0 0 0 0 0 0 1 0 -bash-4.1$ cat tnsnames.ora SS_CDB1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1)(PORT = 1521)) (CONNECT_DATA = (SERVER = SHARED) (SERVICE_NAME = cdb1) ) ) -bash-4.1$ tnsping ss_cdb1 TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 30-JUL-2019 16:09:21 Copyright (c) 1997, 2014, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1)(PORT = 1521)) (CONNECT_DATA = (SERVER = SHARED) (SERVICE_NAME = cdb1))) TNS-12541: TNS:no listener -bash-4.1$ lsnrctl start LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 30-JUL-2019 16:09:28 Copyright (c) 1991, 2014, Oracle. All rights reserved. Starting /u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 12.1.0.2.0 - Production System parameter file is /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora Log messages written to /u01/app/oracle/diag/tnslsnr/racnode1/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))) Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 30-JUL-2019 16:09:30 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/racnode1/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))) Services Summary... Service "orcl12c.us.oracle.com" has 1 instance(s). Instance "orcl12c", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully -bash-4.1$ -bash-4.1$ sql SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 30 16:09:41 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> alter system register; System altered. SQL> exit -bash-4.1$ lsnrctl status LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 30-JUL-2019 16:09:50 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 30-JUL-2019 16:09:30 Uptime 0 days 0 hr. 0 min. 20 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/racnode1/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))) Services Summary... Service "cdb1" has 1 instance(s). Instance "cdb1", status READY, has 2 handler(s) for this service... Service "cdb1XDB" has 1 instance(s). Instance "cdb1", status READY, has 1 handler(s) for this service... Service "orcl12c.us.oracle.com" has 1 instance(s). Instance "orcl12c", status UNKNOWN, has 1 handler(s) for this service... Service "pdb1" has 1 instance(s). Instance "cdb1", status READY, has 1 handler(s) for this service... Service "pdb2" has 1 instance(s). Instance "cdb1", status READY, has 1 handler(s) for this service... The command completed successfully -bash-4.1$ -bash-4.1$ lsnrctl services LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 30-JUL-2019 16:10:00 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=1521)) Services Summary... Service "cdb1" has 1 instance(s). Instance "cdb1", status READY, has 2 handler(s) for this service... Handler(s): "D001" established:0 refused:0 current:0 max:1022 state:ready <------- Index 1 DISPATCHER with CDB DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.101)(PORT=5001)) "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER Service "cdb1XDB" has 1 instance(s). Instance "cdb1", status READY, has 1 handler(s) for this service... Handler(s): "D000" established:0 refused:0 current:0 max:1022 state:ready DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=25234)) Service "orcl12c.us.oracle.com" has 1 instance(s). Instance "orcl12c", status UNKNOWN, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 LOCAL SERVER Service "pdb1" has 1 instance(s). Instance "cdb1", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER Service "pdb2" has 1 instance(s). Instance "cdb1", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER The command completed successfully TERMINAL-A bash-4.1$ sqlplus system/oracle_4U@ss_cdb1 SQL> set time on 16:11:23 SQL> / 16:11:24 SQL> 16:11:26 SQL> 16:11:26 SQL> 16:11:31 SQL> 16:11:32 SQL> select count(*) from dba_objects a,dba_objects; ^Cselect count(*) from dba_objects a,dba_objects * ERROR at line 1: ORA-01013: user requested cancel of current operation 16:11:55 SQL> 16:11:56 SQL> TERMINAL-B -bash-4.1$ sql SQL> select server,count(*) from v$session 2 group by server; SERVER COUNT(*) --------- ---------- DEDICATED 31 NONE 1 SQL> set time on 16:11:30 SQL> 16:11:30 SQL> 16:11:30 SQL> / SERVER COUNT(*) --------- ---------- DEDICATED 31 SHARED 1 16:11:48 SQL> / SERVER COUNT(*) --------- ---------- DEDICATED 31 SHARED 1 16:11:50 SQL> 16:11:57 SQL> / SERVER COUNT(*) --------- ---------- DEDICATED 31 NONE 1 16:11:59 SQL> 16:11:59 SQL> === HOL : Configure DISPATCHER with PDB on non default listener port === SQL> show parameter local NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener string LISTENER, LISTENER1523, PDB_LISTENER parallel_force_local boolean FALSE SQL> alter system set dispatchers='(INDEX=2)(ADDRESS=(PROTOCOL=TCP)(PORT=5002)(HOST=192.168.56.101)) (DISPATCHERS=2)(SERVICE=pdb1)(LISTENER=PDB_LISTENER)'; System altered. SQL> show parameter dispatchers NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dispatchers string (PROTOCOL=TCP)(SERVICE=cdb1XDB ), (ADDRESS=(PROTOCOL=TCP)(POR T=5001)(HOST=192.168.56.101))( DISPATCHERS=4)(SERVICE=cdb1), (ADDRESS=(PROTOCOL=TCP)(PORT=5 002)(HOST=192.168.56.101))(DIS PATCHERS=2)(SERVICE=pdb1)(LIST ENER=PDB_LISTENER) max_dispatchers integer cat tnsnames.ora SS_PDB1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1)(PORT = 1524)) (CONNECT_DATA = (SERVER = SHARED) (SERVICE_NAME = pdb1) ) ) -bash-4.1$ tnsping ss_pdb1 TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 30-JUL-2019 16:29:28 Copyright (c) 1997, 2014, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1)(PORT = 1524)) (CONNECT_DATA = (SERVER = SHARED) (SERVICE_NAME = pdb1))) OK (0 msec) -bash-4.1$ -bash-4.1$ lsnrctl services PDB_LISTENER LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 30-JUL-2019 16:29:34 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=racnode1)(PORT=1524))) Services Summary... Service "cdb1" has 1 instance(s). Instance "cdb1", status READY, has 2 handler(s) for this service... Handler(s): "D001" established:1 refused:0 current:0 max:1022 state:ready DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.101)(PORT=5001)) "DEDICATED" established:1 refused:0 state:ready LOCAL SERVER Service "cdb1XDB" has 1 instance(s). Instance "cdb1", status READY, has 1 handler(s) for this service... Handler(s): "D000" established:0 refused:0 current:0 max:1022 state:ready DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=34370)) Service "pdb1" has 1 instance(s). Instance "cdb1", status READY, has 2 handler(s) for this service... Handler(s): "DEDICATED" established:1 refused:0 state:ready LOCAL SERVER "D005" established:0 refused:0 current:0 max:1022 state:ready <------- Index 2 DISPATCHER with PDB DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.101)(PORT=5002)) Service "pdb2" has 1 instance(s). Instance "cdb1", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:1 refused:0 state:ready LOCAL SERVER The command completed successfully -bash-4.1$ TERMINAL-A -bash-4.1$ sqlplus system/oracle_4U@ss_pdb1 SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 30 16:29:49 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Tue Jul 30 2019 16:20:37 +05:30 Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> SET TIME ON 16:30:16 SQL> / 16:30:19 SQL> 16:30:20 SQL> SELECT COUNT(*) FROM DBA_OBJECTS A,DBA_OBJECTS B; ^CSELECT COUNT(*) FROM DBA_OBJECTS A,DBA_OBJECTS B * ERROR at line 1: ORA-01013: user requested cancel of current operation TERMINAL-B -bash-4.1$ sql SQL> SELECT SERVER,COUNT(*) FROM V$SESSION 2 GROUP BY SERVER; SERVER COUNT(*) --------- ---------- DEDICATED 39 NONE 1 SQL> SET TIME ON 16:30:18 SQL> 16:30:21 SQL> 16:30:21 SQL> 16:30:21 SQL> / SERVER COUNT(*) --------- ---------- DEDICATED 35 SHARED 1 16:30:43 SQL> 16:30:44 SQL> 16:30:44 SQL> 16:30:44 SQL> / SERVER COUNT(*) --------- ---------- DEDICATED 35 SHARED 1 16:30:47 SQL> 16:30:47 SQL> / SERVER COUNT(*) --------- ---------- DEDICATED 35 NONE 1 16:30:51 SQL> 16:30:51 SQL> === HOL : Stop and Start DISPATCHER and test the connection. === SQL> alter system set dispatchers='(INDEX=1)(ADDRESS=(PROTOCOL=TCP)(PORT=5001)(HOST=192.168.56.101)) (DISPATCHERS=4)(SERVICE=PDB2)(LISTENER=LISTENER1523)'; System altered. SQL> show parameter dispatchers NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dispatchers string (PROTOCOL=TCP)(SERVICE=cdb1XDB ), (ADDRESS=(PROTOCOL=TCP)(POR T=5001)(HOST=192.168.56.101))( DISPATCHERS=4)(SERVICE=PDB2)(L ISTENER=LISTENER1523) max_dispatchers integer -bash-4.1$ lsnrctl services LISTENER1523 LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 30-JUL-2019 17:48:03 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=racnode1)(PORT=1523))) Services Summary... Service "cdb1" has 1 instance(s). Instance "cdb1", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER Service "cdb1XDB" has 1 instance(s). Instance "cdb1", status READY, has 1 handler(s) for this service... Handler(s): "D000" established:0 refused:0 current:0 max:1022 state:ready DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=61088)) Service "pdb1" has 1 instance(s). Instance "cdb1", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER Service "pdb2" has 1 instance(s). Instance "cdb1", status READY, has 2 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER "D001" established:0 refused:0 current:0 max:1022 state:ready DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.101)(PORT=5001)) The command completed successfully -bash-4.1$ vi tnsnames.ora SS_PDB2 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1)(PORT = 1523)) (CONNECT_DATA = (SERVER = SHARED) (SERVICE_NAME = pdb2) ) ) -bash-4.1$ sqlplus system/oracle_4U@ss_pdb2 SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 30 17:49:26 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Tue Jul 30 2019 17:48:43 +05:30 Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> exit SQL> alter system shutdown immediate 'D001'; System altered. SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options -bash-4.1$ -bash-4.1$ sqlplus system/oracle_4U@ss_pdb2 SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 30 17:52:16 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. ERROR: ORA-12520: TNS:listener could not find available handler for requested type of server Enter user-name: ^C -bash-4.1$ -bash-4.1$ lsnrctl services LISTENER1523 LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 30-JUL-2019 17:52:30 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=racnode1)(PORT=1523))) Services Summary... Service "cdb1" has 1 instance(s). Instance "cdb1", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER Service "cdb1XDB" has 1 instance(s). Instance "cdb1", status READY, has 1 handler(s) for this service... Handler(s): "D000" established:0 refused:0 current:0 max:1022 state:ready DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=61088)) Service "pdb1" has 1 instance(s). Instance "cdb1", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER Service "pdb2" has 1 instance(s). Instance "cdb1", status READY, has 2 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER "D001" established:5 refused:0 current:0 max:1022 state:blocked DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.101)(PORT=5001)) The command completed successfully -bash-4.1$ -bash-4.1$ lsnrctl services LISTENER1523 LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 30-JUL-2019 17:52:37 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=racnode1)(PORT=1523))) Services Summary... Service "cdb1" has 1 instance(s). Instance "cdb1", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER Service "cdb1XDB" has 1 instance(s). Instance "cdb1", status READY, has 1 handler(s) for this service... Handler(s): "D000" established:0 refused:0 current:0 max:1022 state:ready DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=61088)) Service "pdb1" has 1 instance(s). Instance "cdb1", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER Service "pdb2" has 1 instance(s). Instance "cdb1", status READY, has 3 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER "D001" established:5 refused:0 current:0 max:1022 state:blocked DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.101)(PORT=5001)) "D002" established:0 refused:0 current:0 max:1022 state:ready DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.101)(PORT=5001)) The command completed successfully -bash-4.1$ sqlplus system/oracle_4U@ss_pdb2 SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 30 17:52:52 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Tue Jul 30 2019 17:50:11 +05:30 Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> exit ===================== Path to Documentation: ===================== Administrator’s Guide -> 5 Managing Processes -> Configuring Oracle Database for Shared Server Database Net Services Administrator's Guide -> 11 Configuring a Shared Server Architecture -->Configuring Clients for Environments with Both Shared and Dedicated Servers Thank you for visiting this blog.
ORACLE-LEARN 