 |
 |
**********************************************************************
* Description: Knowledge of Oracle listener dedicated and shared mode
* Date: 01:54 PM EST, 06/21/2017
**********************************************************************
<1> Oracle listener:
|
|__ o. The listener is a separate process receives incoming client connection requests and manages the traffic of these requests to the database server.
|
|__ o. To connect to a database service, clients first contact a listener process that typically resides on the database server.
The listener receives incoming client connection requests and hands these requests to the database server.
Once the connection is established, the client and database server communicate directly.
<2> Database service:
|
|__ o. An Oracle database is represented to clients as a service; that is, the database performs work on behalf of clients.
| A database can have one or more services associated with it.
|
|__ o. The service name is specified by the SERVICE_NAMES parameter.
The service name defaults to the global database name, a name comprising the database name (DB_NAME parameter) and domain name (DB_DOMAIN parameter):
SQL> show parameter DB_NAME;
NAME TYPE VALUE
----------------------- ----------- --------------------
db_name string wiki
SQL> show parameter DB_DOMAIN;
NAME TYPE VALUE
----------------------- ----------- --------------------
db_domain string emeralit.com
SQL> show parameter SERVICE_NAMES;
NAME TYPE VALUE
----------------------- ----------- --------------------
service_names string wiki.emeralit.com
<3> Client connect descriptor [TNS entries]:
|
|__ o. Oracle client uses a connect descriptor to connect to database remotely.
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=wiki.emeralit.com)(PORT=1521)) ........... Named as "Protocal Address"
(CONNECT_DATA=
(SERVICE_NAME=wiki.emeralit.com)))
<4> Types of Oracle database associated process:
|
|__ o. Background process:
| |
| |__ o. Such as SMON/PMON/DBWn/CKPT/LGWR. Using following SQL to query running backgroup processes:
| |
| |__ SQL> SELECT PNAME FROM V$PROCESS WHERE PNAME IS NOT NULL ORDER BY PNAME;
|
| CJQ0
| MMNL
| MMON
| SMON
| VKTM
|
|__ o. Server process:
| |
| |__ o. When listener receives request from client, it will ask database to create a server process to communicate with client to perform task according to the request.
| |
| |__ o. Server processes created on behalf of a database application can perform one or more of the following tasks:
| [a] Parse and run SQL statements issued through the application, including creating and executing the query plan.
| [b] Execute PL/SQL code.
| [c] Read data blocks from data files into the database buffer cache (the DBWn background process has the task of writing modified blocks back to disk).
| [d] Return results in such a way that the application can process the information.
|
|__ o. User process:
|
|__ o. Commonly work outside the database server itself to run the application that accesses the database, such as logging into database from a remote server via SQL*PLUS.
Then, sqlplus.exe is a user process.
<5> Server process type, known as "Service handlers":
|
|__ o. Shared server process[Dispatcher]:
| |
| |__ o. Client applications connect over a network to a dispatcher process, not a server process.
| The dispatcher process receives requests from connected clients and puts them into a request queue in the large pool.
| The first available shared server process takes the request from the queue and processes it.
| Afterward, the shared server place the result into the dispatcher response queue.
| The dispatcher process monitors this queue and transmits the result to the client.
|
|
|__ o. Dedicated server process:
| |
| |__ o. A server process that is dedicated to one client connection.
|
|
|__ o. CAUTION:
|
|__ o. Even parameter "share_parameter" is set more than 0, which means some database sessions may still be connected as dedicated.
|
|
|__ o. The shared/dedicated server process can be mixed created on a database server, when sessions got initilized.
| |
| |__ o. For example, as indicated within tnsnames.ora, all the sessions through this TNS entry will created as dedicated:
|
| EMERALIT =
| (DESCRIPTION =
| (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.34.12)(PORT = 1521))
| (CONNECT_DATA =
| (SERVER = DEDICATED)
| (SERVICE_NAME = EMERALIT)
| )
| )
|
|
|__ SQL> select username,server,module from v$session where type='USER'; --[Check a session is shared or dedicated]
|
|
| USERNAME SERVER MODULE
| ------------------------------ --------- --------------------------
| LDPPS DEDICATED w3wp.exe
| TTAXS DEDICATED FF_Credit_Parser.exe
| LDPPS DEDICATED APS.exe
| WEBSERVER_USER DEDICATED php.exe
| TTAXS DEDICATED FFACTS.exe
| TTAXS DEDICATED FFACTS.exe
|
|
|__ o. Example of one tnsnames.ora file contains different TNS entries in both dedicated and shared method, but connecting to the same database via different services.
|
|__ o. File tnsnames.ora:
|
| EMERALIT_DEDICATED =
| (DESCRIPTION =
| (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.34.12)(PORT = 1521))
| (CONNECT_DATA =
| (SERVER = DEDICATED)
| (SERVICE_NAME = orcl.emeralit.com)
| )
| )
|
|
| EMERALIT_SHARED =
| (DESCRIPTION =
| (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.34.12)(PORT = 1521))
| (CONNECT_DATA =
| (SERVER = SHARED)
| (SERVICE_NAME = emeralitXDB.emeralit.com)
| )
| )
|
|
|__ o. SQL> show paramter service_names:
|
| NAME TYPE VALUE
| ------------------------ ----------- -------------------------
| service_names string orcl.nebf.com
|
|
|__ o. SQL> show parameter dispatchers:
|
| NAME TYPE VALUE
| ------------------------ ----------- -------------------------------------
| dispatchers string PROTOCOL=TCP) (SERVICE=emeralitXDB)
|
|
|__ o. CMD> lsnrctl status
|
| LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 15-AUG-2017 14:47:35
|
| Copyright (c) 1991, 2009, Oracle. All rights reserved.
|
| Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
| STATUS of the LISTENER
| ------------------------
| Alias LISTENER
| Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
| Start Date 13-JUN-2017 10:55:35
| Uptime 63 days 3 hr. 51 min. 59 sec
| Trace Level off
| Security ON: Local OS Authentication
| SNMP OFF
| Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_2/network/admin/listener.ora
| Listener Log File /u01/app/oracle/diag/tnslsnr/nebf1s2k242/listener/alert/log.xml
| Listening Endpoints Summary...
| (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
| (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=nebftst)(PORT=1521)))
| Services Summary...
| Service "emeralitXDB.emeralit.com - Usually [DB_NAME]XDB is for shared server process" has 1 instance(s).
| Instance "nebftst2", status READY, has 1 handler(s) for this service...
| Service "orcl.emeralit.com" has 1 instance(s).
| Instance "nebftst2", status READY, has 1 handler(s) for this service...
| The command completed successfully
|
|
|__ o. CMD> sqlplus emeralit/pwd@EMERALIT_SHARED ==> TNS entries EMERALIT_SHARED ==> Service "emeralitXDB.emeralit.com" ==> Dispatcher shared service "emeralitXDB"
|
|
|__ o. SQL> select username,server,module from v$session where type='USER';
|
|
| USERNAME SERVER MODULE
| ---------------- --------- -----------
| EMERALIT SHARED SQL*Plus
|
|
|__ o. CMD> sqlplus emeralit/pwd@EMERALIT_DEDICATED ==> TNS entries EMERALIT_DEDICATED ==> Service "orcl.emeralit.com"
|
|
|__ o. SQL> select username,server,module from v$session where type='USER';
USERNAME SERVER MODULE
---------------- ------------ -----------
EMERALIT DEDICATED SQL*Plus
<6> Verify if server process is shared or dedicated:
|
|__ o. SQL> show parameter shared_server;
|
| NAME TYPE VALUE
| --------------------- ----------- --------- "0" ................... means shared server not on.
| shared_servers integer 1 "1" ................... if you are using shared server architecture or if the "DISPATCHERS" parameter is set,
| which means that the total number of dispatchers is more than 0, then the default value is 1.
| "shared_servers" ...... specifies the number of server processes that you want to create when an instance is started.
| If system load decreases, then this minimum number of servers is maintained.
| Therefore, you should take care not to set SHARED_SERVERS too high at system startup.
|
|__ o. SQL> show parameter dispatchers;
NAME TYPE VALUE
--------------------- ----------- -----------------------------------
dispatchers string (PROTOCOL=TCP) (SERVICE=nebfprdXDB)
<7> Database registration with listener:
|
|__ o. Statisctic:
| |
| |__ o. In Oracle 9i version, the database needs to be registered within $ORACLE_HOME/network/admin/listener.ora as a descriptor.
|
|__ o. Dynamic:
|
|__ o. After 9i, the database can be registered with listener via following command dynamicly:
|
|__ SQL> alter system register;
alter system set local_listener="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=STAR)(PORT=1522)))";
<8> Reference:
|
|__ o. https://docs.oracle.com/cd/B19306_01/network.102/b14212/concepts.htm
Your Comments