Configure shared server

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.
Advertisements