Configure the network environment to allow connections to multiple databases

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…