Go back to OCM 12c Preparation Project
Special Thanks to www.dbarj.com.br
Hands On Lab – Index
[1] Creating listener.ora and tnsnames.ora manually.
[2] How to set up static listener in listener.ora
[3] Using netca / netmgr to create listener.ora and tnsnames.ora.
[3.1] netca GUI
[3.2] netmgr GUI
[4] Creating services for connection to a determined PDB.
[5] The ways local_listener parameter can be defined
(even adding an entry in tnsnames.ora and referring to it)
[5.1] : Using local_listener init.ora parameter
[5.2] How to Define PDB Listeners With Different Ports In a Oracle database Multitenant Setup
[5.3] : Another way to set local_listener is to use names in TNSNAMES.ora
[5.4] : Shared server environment LISTENER attribute of the DISPATCHERS parameter
You can run multiple listeners in the same server. Thus, it's possible to: 1. Have 1 listener pointing to multiple databases services. 2. Have multiple listeners pointing to a single service. 3. Have multiple listeners pointing to multiple services. 4. Having a listener pointing only to a single PDB. By default, when you start a listener without a listener.ora, it will listen on 2 addresses: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hostname)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Also, by default, the database will register itself to the TCP listener running on localhost and port 1521.
The listener registration process (LREG) registers information about the database instance and dispatcher processes with the Oracle Net Listener.
When an instance starts, LREG polls the listener to determine whether it is running. If the listener is running, then LREG passes it relevant parameters. If it is not running, then LREG periodically attempts to contact it.
Note: In releases before 12c, PMON performed the listener registration. The PMON is the process responsible for it and does that every 60 seconds. You can force the register running: “alter system register”.
By default, the LREG process registers service information with its local listener on the default local address of TCP/IP, port 1521. If the listener configuration is synchronized with the database configuration, then LREG can register service information with a nondefault local listener or a remote listener on another node. If you want to set up the static listener pointing to all the DBs, you must need to know how to define static entries. This is very important and needed for a lot of DB tasks, like rman duplication, data guard FSFO, etc. For this topic, what I recommend studying is: [1] Creating listener.ora and tnsnames.ora manually. === HOL : Using samples files. === $ cd $ORACLE_HOME/network/admin $ ls afiedt.buf listener.ora samples shrept.lst sqlnet19072511AM2235.bak sqlnet.ora tnsnames.ora $ rm listener.ora tnsnames.ora sqlnet $ cd samples/ $ ls listener.ora sqlnet.ora tnsnames.ora $ cp *.ora ../ $ cd .. $ ls afiedt.buf listener.ora samples shrept.lst sqlnet19072511AM2235.bak sqlnet.ora tnsnames.ora $ vi listener.ora <-----#Uncomment and modify the LISTENER Sample entries. bash-4.1$ cat listener.ora LISTENER = (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=1521)) (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))) -bash-4.1$ lsnrctl start LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 28-JUL-2019 10:47:55 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))) Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 28-JUL-2019 10:47:55 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))) The listener supports no services The command completed successfully -bash-4.1$ -bash-4.1$ ps -ef | grep lreg <--- #Monitor the listener new background process in 12c oracle 2838 1 0 10:39 ? 00:00:00 ora_lreg_orcl12c oracle 3339 2732 0 10:48 pts/0 00:00:00 grep lreg #Wait for some time to register services automatically tail -100f /u01/app/oracle/diag/tnslsnr/racnode1/listener/alert/log.xml 28-JUL-2019 10:47:55 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=racnode1)(USER=o racle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=202375680)) * st atus * 0 28-JUL-2019 10:48:35 * service_register * orcl12c * 0 <----- This is dynamic listener registration. -bash-4.1$ cat tnsnames.ora <-----#Uncomment and modify the TNSNAMES Sample entries. orcl12c = (DESCRIPTION= (ADDRESS_LIST= (ADDRESS= (PROTOCOL=tcp) (HOST=racnode1) (PORT=1521) )) (CONNECT_DATA= (SID=orcl12c) ) ) -bash-4.1$ tnsping orcl12c TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 28-JUL-2019 10:59:17 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_LIST= (ADDRESS= (PROTOCOL=tcp) (HOST=racnode1) (PORT=1521))) (CONNECT_DATA= (SID=orcl12c))) OK (10 msec) -bash-4.1$ sqlplus c##test_user1/oracle_4U@orcl12c SQL*Plus: Release 12.1.0.2.0 Production on Sun Jul 28 10:59:19 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Sat Jul 27 2019 17:05:17 +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 Reference From Documentation. ============================= Database Net Services Reference 7 Oracle Net Listener Parameters in the listener.ora File Example 7-1 listener.ora File LISTENER= (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=sale-server)(PORT=1521)) (ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))) 13 Enabling Advanced Features of Oracle Net Services Configuring Advanced Network Address and Connect Data Information Creating a List of Listener Protocol Addresses sales.us.example.com= (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=sales1-server)(PORT=1521)) (ADDRESS=(PROTOCOL=tcp)(HOST=sales2-server)(PORT=1521))) (CONNECT_DATA= (SERVICE_NAME=sales.us.example.com))) [2] How to set up static listener in listener.ora === HOL : Using samples file === $ lsnrctl stop LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 28-JUL-2019 11:17:53 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) The command completed successfully $ ps -ef | grep pmon oracle 2802 1 0 10:39 ? 00:00:00 ora_pmon_orcl12c oracle 3800 2732 0 11:18 pts/0 00:00:00 grep pmon $ ps -ef | grep lreg oracle 2838 1 0 10:39 ? 00:00:00 ora_lreg_orcl12c oracle 3802 2732 0 11:18 pts/0 00:00:00 grep lreg $ vi listener.ora <---#Uncomment and modify the sample Listener file # address value as shown below. LISTENER = (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=1521)) (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))) # SID_LIST_ # List of services the listener knows about and can connect # clients to. There is no default. See the Net8 Administrator's # Guide for more information. SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (GLOBAL_DBNAME=orcl12c.us.oracle.com) (SID_NAME=orcl12c) (ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_1) ) ) -bash-4.1$ lsnrctl start LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 28-JUL-2019 11:18:26 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 28-JUL-2019 11:18:26 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 <------- This is the statis listener registration 13 Enabling Advanced Features of Oracle Net Services Understanding Static Service Registration Configuration of static service information is necessary in the following cases: Example 13-1 Example listener.ora File LISTENER= (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521)) (ADDRESS=(PROTOCOL=ipc)(KEY=extproc) (queuesize=50)))) SID_LIST_listener= (SID_LIST= (SID_DESC= (GLOBAL_DBNAME=sales.example.com) (SID_NAME=sales) (ORACLE_HOME=/u01/app/oracle))) Note: A statically-configured global database name disables TAF. To use TAF, do not set the GLOBAL_DBNAME parameter in the SID_LIST_listener_name section of the listener.ora file. [3] Using netca / netmgr to create listener.ora and tnsnames.ora. 3.1 netca GUI $ORACLE_HOME/bin ./netca #Listener Configuration steps Step 1:- Select Listener Configuration and Click Next Step 2:- Select Add and Click Next Step 3:- Provide Listener Name and Click Next Step 4:- Choose Selected protocols and Click Next Step 5:- Use another port number and Click Next #Tnsnames.ora Configuration steps Step 1:- Select Local Net Service Name Configuration option and Click Next Step 2:- Select Add option and Click Next Step 3:- Provide the service name and click next Step 4:- Select the network protocol and Click Next Step 5:- Provide the Hostname and Port number, then Click on Next Step 6:- This is optional, if you want to test the connection click on Yes, here we want to make sure connection is working fine to connect to database Step 7:- Wizard will confirm if the connection to database using new tns name is working fine. Now Click Next Step 8:- Network Service name will be populated by default, Click Next Step 9:- By default No option is selected, in case you want to configure another listener, select yes an repeat the steps from Step 1 to Step 8 Step 10:- Click on Next Step 11:- Click on Finish 3.2 netmgr GUI $ORACLE_HOME/bin ./netmgr # We see an options tree: Profile: sqlnet.ora file configuration Service Naming: We can add and modify the entries in the tnsnames.ora file Listeners: Here we would add other LISTENER if we needed it #Tnsnames.ora Configuration steps 1. The net manager will open. Click on Service Naming then click the plus. 2. Entry the net service name you want and click next. 3. Select the communication protocol and click next. 4. Enter the host name and port and click next. 5. Enter the service name for the Pluggable database and click next. 6. Click the test button 7. The test button defaults to testing with scott/tiger we can see how long this utility has been around. Click the change login button to change the credentials. 8. Set the username and password credentials for the Pluggable database click OK. 9. Now click test and this time the connection will be successful. Click close. 10. Click finish to close the net service name wizard. 11. Under service naming you will see your service configured. If you need to add more service names just repeat the same steps above if not continue to step 14. 12. Click file>save network configuration 13. Then click file>exit to close net manager. #Listener Configuration steps 1. The net manager will open. Click on Listeners then click the plus. 2. Choose the listener name. 3. Add Address PROTOCOL : TCP/IP Hostname : racndode1 Port : 1522 4. Save configuration [4] Creating services for connection to a determined PDB. #Silent method to add network server string in tnsnames.ora file cd /u01/database/response/ cat netca.rsp | grep -v '#' | awk 'NF>0' > netca2.rsp $ vi /u01/database/response/netca2.rsp [GENERAL] RESPONSEFILE_VERSION="12.1" CREATE_TYPE="CUSTOM" [oracle.net.ca] INSTALLED_COMPONENTS={"server","net8","javavm"} INSTALL_TYPE=""custom"" #LISTENER_NUMBER=1 #LISTENER_NAMES={"LISTENER"} #LISTENER_PROTOCOLS={"TCP;1521"} #LISTENER_START=""LISTENER"" NAMING_METHODS={"TNSNAMES","ONAMES","HOSTNAME"} NSN_NUMBER=1 NSN_NAMES={"pdb1"} NSN_SERVICE={"pdb1.us.oracle.com"} NSN_PROTOCOLS={"TCP;racnode1;1521"} 1$ ./netca -silent -responsefile /u01/database/response/netca2.rsp Parsing command line arguments: Parameter "silent" = true Parameter "responsefile" = /u01/database/response/netca2.rsp Done parsing command line arguments. Oracle Net Services Configuration: Listener "LISTENER" already exists. Default local naming configuration complete. Created net service name: pdb1 Profile configuration complete. Check the trace file for details: /u01/app/oracle/cfgtoollogs/netca/trace_OraDB12Home1-19072811AM4856.log Oracle Net Services configuration failed. The exit code is 1 $ cat tnsnames.ora ORCL12C = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = tcp)(HOST = racnode1)(PORT = 1521)) ) (CONNECT_DATA = (SID = orcl12c) ) ) PDB1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = pdb1.us.oracle.com) ) ) [5] The ways local_listener parameter can be defined (even adding an entry in tnsnames.ora and referring to it) Registering Information with a Local Listener By default, the LREG process registers service information with its local listener on the default local address of TCP/IP, port 1521. If the listener configuration is synchronized with the database configuration, then LREG can register service information with a nondefault local listener or a remote listener on another node. Synchronization occurs when the protocol address of the listener is specified in the listener.ora file and the location of the listener is specified in the initialization parameter file. To have the LREG process register with a local listener that does not use TCP/IP, port 1521, configure the LOCAL_LISTENER parameter in the initialization parameter file to locate the local listener. Shared server environment You can use the LISTENER attribute of the DISPATCHERS parameter in the initialization parameter file to register the dispatchers with a nondefault local listener. Because the LOCAL_LISTENER parameter and the LISTENER attribute enable LREG to register dispatcher information with the listener, it is not necessary to specify both the parameter and the attribute if the listener values are the same. LOCAL_LISTENER is a comma-delimited list parameter. If a comma appears in the string, then the entire string must be enclosed in double quotation marks. Set the LOCAL_LISTENER parameter as follows: ALTER SYSTEM SET LOCAL_LISTENER=["]listener_address["][,...]; For shared server connections, set the LISTENER attribute as follows: ALTER SYSTEM SET DISPATCHERS="(PROTOCOL=tcp)(LISTENER=listener_address)"; Note: If you set the parameter to null using the following statement, then the default local address of TCP/IP, port 1521 is assumed: ALTER SYSTEM SET LOCAL_LISTENER='' 9 Configuring and Administering Oracle Net Listener Configuring Dynamic Service Registration Configuring a Naming Method The listener name alias specified for the LOCAL_LISTENER or REMOTE_LISTENER initialization parameters In the init.ora file: LOCAL_LISTENER = (ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1421)) LOCAL_LISTENER = listener_sales1 In the tnsnames.ora file: listener_sales1 = (ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1421)) Note: The CONNECT_DATA information should not be included in the network service entry in the tnsnames.ora file ================================================= HOL 5.1 : Using local_listener init.ora parameter ================================================== Example-1 -bash-4.1$ cat listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = tcp)(HOST = racnode1)(PORT = 1521)) (ADDRESS = (PROTOCOL = ipc)(KEY = PNPKEY)) ) LISTENER1523 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1)(PORT = 1523)) ) ) -bash-4.1$ cat tnsnames.ora ORCL12C = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = tcp)(HOST = racnode1)(PORT = 1521)) ) (CONNECT_DATA = (SID = orcl12c) ) ) PDB2 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1)(PORT = 1523)) ) (CONNECT_DATA = (SERVICE_NAME = pdb2.us.oracle.com) ) ) PDB1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = pdb1.us.oracle.com) ) ) -bash-4.1$ dbs vi initorcl12c.ora *.LOCAL_LISTENER="(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = racnode1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl12c.us.oracle.com)))", "(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1)(PORT = 1523))) (CONNECT_DATA = (SERVICE_NAME = pdb1.us.oracle.com)))" SQL> startup pfile=initorcl12c.ora ORACLE instance started. Total System Global Area 939524096 bytes Fixed Size 2931088 bytes Variable Size 348128880 bytes Database Buffers 583008256 bytes Redo Buffers 5455872 bytes Database mounted. Database opened. SQL> show parameter local NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener string (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(H OST = racnode1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl12c.us.oracle.com))), ( DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HO ST = racnode1)(PORT = 1523))) (CONNECT_DATA = (SERVICE_NAME = pdb1.us.oracle.com))) -bash-4.1$ lsnrctl status -bash-4.1$ lsnrctl status LISTENER1523 -bash-4.1$ sqlplus c##test_user1/oracle_4U@pdb1 SQL*Plus: Release 12.1.0.2.0 Production on Sun Jul 28 12:36:26 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Sun Jul 28 2019 10:59:19 +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 -bash-4.1$ sqlplus c##test_user1/oracle_4U@pdb2 SQL*Plus: Release 12.1.0.2.0 Production on Sun Jul 28 12:36:29 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Sun Jul 28 2019 12:36:26 +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 Example-2 SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=racnode1)(PORT=1521)))', '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=racnode1)(PORT=1523)))', '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=racnode1)(PORT=1524)))' scope=both; System altered. SQL> show parameter listener NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ listener_networks string local_listener string (DESCRIPTION=(ADDRESS=(PROTOCO L=TCP)(HOST=racnode1)(PORT=152 1))), (DESCRIPTION=(ADDRESS=(P ROTOCOL=TCP)(HOST=racnode1)(PO RT=1523 ))), (DESCRIPTION=(ADDRESS=(PR OTOCOL=TCP)(HOST=racnode1)(POR T=1524))) remote_listener string -bash-4.1$ lsnrctl status -bash-4.1$ lsnrctl status listener1523 -bash-4.1$ lsnrctl status pdb_listener -bash-4.1$ tnsping pdb2 -bash-4.1$ tnsping pdb1 -bash-4.1$ tnsping orcl12c -bash-4.1$ sqlplus system/oracle_4U@orcl12c SQL*Plus: Release 12.1.0.2.0 Production on Sun Jul 28 14:22:40 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Sun Jul 28 2019 13:41:59 +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 -bash-4.1$ sqlplus system/oracle_4U@pdb2 SQL*Plus: Release 12.1.0.2.0 Production on Sun Jul 28 14:22:45 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Sun Jul 28 2019 14:22:40 +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 -bash-4.1$ sqlplus system/oracle_4U@pdb1 SQL*Plus: Release 12.1.0.2.0 Production on Sun Jul 28 14:22:48 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Sun Jul 28 2019 14:22:45 +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 ======= HOL 5.2 : #How to Define PDB Listeners With Different Ports In a Oracle database Multitenant Setup ======= ========================================== Using listener_networks init.ora parameter ========================================== -bash-4.1$ sql SQL> alter system set listener_networks='((NAME=net1)(LOCAL_LISTENER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=racnode1)(PORT=1524)))))', '((NAME=net2)(LOCAL_LISTENER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=racnode1)(PORT=1523)))))', '((NAME=net3)(LOCAL_LISTENER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=racnode1)(PORT=1521)))))' scope=memory; SQL> show parameter listener show parameter listener NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ listener_networks string ((NAME=net1)(LOCAL_LISTENER=(D ESCRIPTION=(ADDRESS=(PROTOCOL= TCP)(HOST=racnode1)(PORT=1524) )))), ((NAME=net2)(LOCAL_LISTE NER=(DESCRIPTION=(ADDRESS=(PRO TOCOL=TCP)(HOST=racnode1)(PORT =1523))))), ((NAME=net3)(LOCAL _LISTENER=(DESCRIPTION=(ADDRES S=(PROTOCOL=TCP)(HOST=racnode1 )(PORT=1521))))) local_listener string remote_listener string -bash-4.1$ cat listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = tcp)(HOST = racnode1)(PORT = 1521)) (ADDRESS = (PROTOCOL = ipc)(KEY = PNPKEY)) ) LISTENER1523 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1)(PORT = 1523)) ) ) PDB_LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1)(PORT = 1524)) ) ) $ cat tnsnames.ora ORCL12C = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = tcp)(HOST = racnode1)(PORT = 1521)) ) (CONNECT_DATA = (SID = orcl12c) ) ) PDB2 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1)(PORT = 1523)) ) (CONNECT_DATA = (SERVICE_NAME = pdb2.us.oracle.com) ) ) PDB1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1)(PORT = 1524)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb1.us.oracle.com) ) ) -bash-4.1$ lsnrctl status pdb_listener -bash-4.1$ lsnrctl status LISTENER1523 -bash-4.1$ lsnrctl status -bash-4.1$ -bash-4.1$ ps -ef | grep lsnr oracle 4739 1 0 12:30 ? 00:00:00 /u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr LISTENER -inherit oracle 4933 1 0 12:37 ? 00:00:00 /u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr LISTENER1523 -inherit oracle 6488 1 0 13:34 ? 00:00:00 /u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr pdb_listener -inherit oracle 6580 3085 0 13:41 pts/1 00:00:00 grep lsnr -bash-4.1$ -bash-4.1$ ps -ef | grep lreg oracle 6104 1 0 13:32 ? 00:00:00 ora_lreg_orcl12c oracle 6582 3085 0 13:41 pts/1 00:00:00 grep lreg -bash-4.1$ -bash-4.1$ ps -ef | grep pmon oracle 6068 1 0 13:31 ? 00:00:00 ora_pmon_orcl12c oracle 6584 3085 0 13:41 pts/1 00:00:00 grep pmon -bash-4.1$ tnsping pdb1 TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 28-JUL-2019 13:42:04 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 = DEDICATED) (SERVICE_NAME = pdb1.us.oracle.com))) OK (0 msec) -bash-4.1$ -bash-4.1$ tnsping pdb2 TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 28-JUL-2019 13:42:05 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_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1)(PORT = 1523))) (CONNECT_DATA = (SERVICE_NAME = pdb2.us.oracle.com))) OK (0 msec) -bash-4.1$ -bash-4.1$ tnsping orcl12c TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 28-JUL-2019 13:42:13 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_LIST = (ADDRESS = (PROTOCOL = tcp)(HOST = racnode1)(PORT = 1521))) (CONNECT_DATA = (SID = orcl12c))) OK (0 msec) -bash-4.1$ sqlplus system/oracle_4U@pdb1 SQL*Plus: Release 12.1.0.2.0 Production on Sun Jul 28 13:41:51 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Sun Jul 28 2019 13:39:44 +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 -bash-4.1$ sqlplus system/oracle_4U@pdb2 SQL*Plus: Release 12.1.0.2.0 Production on Sun Jul 28 13:41:54 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Sun Jul 28 2019 13:41:51 +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 -bash-4.1$ sqlplus system/oracle_4U@orcl12c SQL*Plus: Release 12.1.0.2.0 Production on Sun Jul 28 13:41:59 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Sun Jul 28 2019 13:41:54 +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 ======= HOL 5.3 : Another way to set local_listener is to use names in TNSNAMES.ora ======= -bash-4.1$ sql SQL*Plus: Release 12.1.0.2.0 Production on Sun Jul 28 14:58:21 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 set local_listener=LISTENER1523; alter system set local_listener=LISTENER1523 * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-00119: invalid specification for system parameter LOCAL_LISTENER ORA-00132: syntax error or unresolved network name 'LISTENER1523' -bash-4.1$ oerr ora 00132 00132, 00000, "syntax error or unresolved network name '%s'" // *Cause: Listener address has syntax error or cannot be resolved. // *Action: If a network name is specified, check that it corresponds // to an entry in TNSNAMES.ORA or other address repository // as configured for your system. Make sure that the entry // is syntactically correct. -bash-4.1$ cat listener.ora LISTENER = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = tcp)(HOST = racnode1)(PORT = 1521)) (ADDRESS = (PROTOCOL = ipc)(KEY = PNPKEY)) ) LISTENER1523 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1)(PORT = 1523)) ) ) PDB_LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1)(PORT = 1524)) ) ) #Edit tnsnames.ora file with nondefault listener -bash-4.1$ cat tnsnames.ora ORCL12C = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = tcp)(HOST = racnode1)(PORT = 1521)) ) (CONNECT_DATA = (SID = orcl12c) ) ) PDB2 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1)(PORT = 1523)) ) (CONNECT_DATA = (SERVICE_NAME = pdb2.us.oracle.com) ) ) PDB1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1)(PORT = 1524)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb1.us.oracle.com) ) ) LISTENER1523 =(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=1523))) PDB_LISTENER =(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=1524))) -bash-4.1$ sql SQL*Plus: Release 12.1.0.2.0 Production on Sun Jul 28 15:02:09 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 set local_listener=PDB_LISTENER; System altered. SQL> alter system set local_listener=LISTENER1523; System altered. SQL> alter system set local_listener=PDB_LISTENER,LISTENER1523; System altered. SQL> show parameter local_listener NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener string PDB_LISTENER, LISTENER1523 SQL> SQL> show parameter listener NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ listener_networks string local_listener string PDB_LISTENER, LISTENER1523 remote_listener string SQL> -bash-4.1$ lsnrctl status LISTENER1523 -bash-4.1$ lsnrctl status PDB_LISTENER -bash-4.1$ sqlplus system/oracle_4U@pdb1 SQL*Plus: Release 12.1.0.2.0 Production on Sun Jul 28 15:03:30 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Sun Jul 28 2019 15:01: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 -bash-4.1$ -bash-4.1$ sqlplus system/oracle_4U@pdb2 SQL*Plus: Release 12.1.0.2.0 Production on Sun Jul 28 15:03:34 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Sun Jul 28 2019 15:03:31 +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 -bash-4.1$ -bash-4.1$ sqlplus system/oracle_4U@orcl12c SQL*Plus: Release 12.1.0.2.0 Production on Sun Jul 28 15:03:39 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Sun Jul 28 2019 15:03:34 +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 ======= HOL 5.4 : Shared server environment LISTENER attribute of the DISPATCHERS parameter ======= To register the dispatchers with a nondefault local listener. SQL> alter system set dispatchers='(ADDRESS = (PROTOCOL = TCP)(HOST = racnode1) (service=pdb1.us.oracle.com))(dispatchers=5)(listener=PDB_LISTENER)'; SQL> show parameter local NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener string PDB_LISTENER, LISTENER1523 parallel_force_local boolean FALSE SELECT count(*) FROM V$DISPATCHER; 5 $ oerr ora 12520 12520, 00000, "TNS:listener could not find available handler for requested type of server" // *Cause: None of the known and available service handlers for requested type // of server (dedicated or shared) are appropriate for the client connection. // *Action: Run "lsnrctl services" to ensure that the instance(s) have // registered with the listener and that the appropriate handlers are // accepting connections. -bash-4.1$ lsnrctl services PDB_LISTENER LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 28-JUL-2019 15:33:47 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=racnode1)(PORT=1524))) Services Summary... Service "mypdb10.us.oracle.com" has 1 instance(s). Instance "orcl12c", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER Service "mypdb4.us.oracle.com" has 1 instance(s). Instance "orcl12c", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER Service "orcl12c.us.oracle.com" has 1 instance(s). Instance "orcl12c", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER Service "pdb1.us.oracle.com" has 1 instance(s). Instance "orcl12c", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER Service "pdb2.us.oracle.com" has 1 instance(s). Instance "orcl12c", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER The command completed successfully -bash-4.1$ lsnrctl status PDB_LISTENER LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 28-JUL-2019 15:35:58 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=racnode1)(PORT=1524))) STATUS of the LISTENER ------------------------ Alias PDB_LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 28-JUL-2019 15:35:39 Uptime 0 days 0 hr. 0 min. 19 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/pdb_listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=1524))) The listener supports no services The command completed successfully -bash-4.1$ cat tnsnames.ora ORCL12C = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = tcp)(HOST = racnode1)(PORT = 1521)) ) (CONNECT_DATA = (SID = orcl12c) ) ) PDB2 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1)(PORT = 1523)) ) (CONNECT_DATA = (SERVICE_NAME = pdb2.us.oracle.com) ) ) PDB1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1)(PORT = 1524)) (CONNECT_DATA = (SERVER = SHARED) (SERVICE_NAME = pdb1.us.oracle.com) ) ) LISTENER1523 =(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=1523))) PDB_LISTENER =(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=1524))) SQL> show parameter SHARED_SERVERS NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ max_shared_servers integer shared_servers integer 0 SQL> SQL> ALTER SYSTEM SET SHARED_SERVERS = 2; System altered. SQL> SELECT count(*) FROM V$DISPATCHER; COUNT(*) ---------- 5 -bash-4.1$ sql SQL> alter system register; System altered. SQL> exit -bash-4.1$ lsnrctl status PDB_LISTENER LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 28-JUL-2019 15:36:27 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=racnode1)(PORT=1524))) STATUS of the LISTENER ------------------------ Alias PDB_LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 28-JUL-2019 15:35:39 Uptime 0 days 0 hr. 0 min. 48 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/pdb_listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=1524))) Services Summary... Service "mypdb10.us.oracle.com" has 1 instance(s). Instance "orcl12c", status READY, has 6 handler(s) for this service... Service "mypdb4.us.oracle.com" has 1 instance(s). Instance "orcl12c", status READY, has 6 handler(s) for this service... Service "orcl12c.us.oracle.com" has 1 instance(s). Instance "orcl12c", status READY, has 6 handler(s) for this service... Service "pdb1.us.oracle.com" has 1 instance(s). Instance "orcl12c", status READY, has 6 handler(s) for this service... Service "pdb2.us.oracle.com" has 1 instance(s). Instance "orcl12c", status READY, has 6 handler(s) for this service... The command completed successfully -bash-4.1$ -bash-4.1$ lsnrctl services PDB_LISTENER LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 28-JUL-2019 15:36:36 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=racnode1)(PORT=1524))) Services Summary... Service "mypdb10.us.oracle.com" has 1 instance(s). Instance "orcl12c", status READY, has 6 handler(s) for this service... Handler(s): "D004" established:0 refused:0 current:0 max:1022 state:ready DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=17945)) "D003" established:0 refused:0 current:0 max:1022 state:ready DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=13779)) "D002" established:0 refused:0 current:0 max:1022 state:ready DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=30937)) "D001" established:0 refused:0 current:0 max:1022 state:ready DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=58100)) "D000" established:0 refused:0 current:0 max:1022 state:ready DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=38861)) "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER Service "mypdb4.us.oracle.com" has 1 instance(s). Instance "orcl12c", status READY, has 6 handler(s) for this service... Handler(s): "D004" established:0 refused:0 current:0 max:1022 state:ready DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=17945)) "D003" established:0 refused:0 current:0 max:1022 state:ready DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=13779)) "D002" established:0 refused:0 current:0 max:1022 state:ready DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=30937)) "D001" established:0 refused:0 current:0 max:1022 state:ready DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=58100)) "D000" established:0 refused:0 current:0 max:1022 state:ready DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=38861)) "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER Service "orcl12c.us.oracle.com" has 1 instance(s). Instance "orcl12c", status READY, has 6 handler(s) for this service... Handler(s): "D004" established:0 refused:0 current:0 max:1022 state:ready DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=17945)) "D003" established:0 refused:0 current:0 max:1022 state:ready DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=13779)) "D002" established:0 refused:0 current:0 max:1022 state:ready DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=30937)) "D001" established:0 refused:0 current:0 max:1022 state:ready DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=58100)) "D000" established:0 refused:0 current:0 max:1022 state:ready DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=38861)) "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER Service "pdb1.us.oracle.com" has 1 instance(s). Instance "orcl12c", status READY, has 6 handler(s) for this service... Handler(s): "D004" established:0 refused:0 current:0 max:1022 state:ready DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=17945)) "D003" established:0 refused:0 current:0 max:1022 state:ready DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=13779)) "D002" established:0 refused:0 current:0 max:1022 state:ready DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=30937)) "D001" established:0 refused:0 current:0 max:1022 state:ready DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=58100)) "D000" established:0 refused:0 current:0 max:1022 state:ready DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=38861)) "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER Service "pdb2.us.oracle.com" has 1 instance(s). Instance "orcl12c", status READY, has 6 handler(s) for this service... Handler(s): "D004" established:0 refused:0 current:0 max:1022 state:ready DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=17945)) "D003" established:0 refused:0 current:0 max:1022 state:ready DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=13779)) "D002" established:0 refused:0 current:0 max:1022 state:ready DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=30937)) "D001" established:0 refused:0 current:0 max:1022 state:ready DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=58100)) "D000" established:0 refused:0 current:0 max:1022 state:ready DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=38861)) "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER The command completed successfully -bash-4.1$ -bash-4.1$ sqlplus system/oracle_4U@pdb1 SQL*Plus: Release 12.1.0.2.0 Production on Sun Jul 28 15:54:58 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Sun Jul 28 2019 15:43:04 +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> select count(*) from dba_objects a,dba_objects b; <--Running Query $ sqlplus / as sysdba SQL> select server,count(*) from v$session group by server; SERVER COUNT(*) --------- ---------- DEDICATED 44 SHARED 1 ===================== Path to Documentation: ===================== Database Administration -> Net Services Administrator's Guide -> 9 Configuring and Administering Oracle Net Listener Example 9-4 Configuring Multiple Listeners Database Net Services Reference 7 Oracle Net Listener Parameters in the listener.ora File Example 7-1 listener.ora File 13 Enabling Advanced Features of Oracle Net Services Configuring Advanced Network Address and Connect Data Information Creating a List of Listener Protocol Addresses
Thank you for visiting this blog…
