Configure the Multiple Listener Port in Oracle Database
Posted by Mir Sayeed Hassan on October 2nd, 2017
Configure the Multiple Listener Port in Oracle DB & Tested in our Environment
Configure Port 1521 – Default port
Configure Port 1522 – Port Added by request from client
$cd $ORACLE_HOME/network/admin/vi listener.ora LISTENER1 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = *****) (PORT = 1522) ) ) ) ) SID_LIST_LISTENER1 = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = prim) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) (SID_NAME = prim) ) ) LISTENER2 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = *****) (PORT = 1521) ) ) ) ) SID_LIST_LISTENER2 = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = prim) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) (SID_NAME = prim) ) ) ADR_BASE_LISTENER = /u01/app/oracle ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON
$cd $ORACLE_HOME/network/admin/vi tnsnames.ora prim = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = *****) (PORT = 1522) ) ) (CONNECT_DATA = (SERVER = DEDICATED) (SID = prim) ) ) prim (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = *****) (PORT = 1521) ) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prim) (UR = A) ) )
After this set the local listener into the database by using the below command
SQL> alter system set LOCAL_LISTENER="(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=*****)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=*****)(PORT=1522)))" scope=BOTH; System altered.
Verify the Above Set parameter:
SQL> show parameter LOCAL_LISTENER NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener string (ADDRESS_LIST=(ADDRESS=(PROTOC OL=TCP)(HOST=*****)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=*****)(PORT=1522))) Verify Listener1 with port 1522
LSNRCTL> status listener1 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.20.0.139)(PORT=1522))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 26-JUN-2016 05:21:51 Uptime 196 days 22 hr. 58 min. 6 sec Trace Level off Security ON: Local OS Authentication SNMP ON Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=***** )(PORT=1522))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522))) Services Summary... Service "prim" has 1 instance(s). Instance "prim", status READY, has 1 handler(s) for this service... Service "primXDB" has 1 instance(s). Instance "prim", status READY, has 1 handler(s) for this service... The command completed successfully
LSNRCTL> service listener1 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.20.0.139)(PORT=1522))) Services Summary... Service "prim" has 1 instance(s). Instance "prim", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:428 refused:0 state:ready LOCAL SERVER Service "primXDB" has 1 instance(s). Instance "prim", status READY, has 1 handler(s) for this service... Handler(s): "D000" established:0 refused:0 current:0 max:1022 state:ready DISPATCHER <machine: localhost.localdomain, pid: 4389> (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=47710))
The command completed successfully
Verify Listener1 with Port 1521
LSNRCTL> status listener2 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.20.0.139)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 07-JAN-2017 14:07:12 Uptime 1 days 21 hr. 44 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP ON Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))) Services Summary... Service "prim" has 1 instance(s). Instance "prim", status READY, has 1 handler(s) for this service... Service "primXDB" has 1 instance(s). Instance "prim", status READY, has 1 handler(s) for this service... The command completed successfully LSNRCTL> service listener2 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=*****)(PORT=1521))) Services Summary... Service "prim" has 1 instance(s). Instance "prim", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:10 refused:0 state:ready LOCAL SERVER Service "primXDB" has 1 instance(s). Instance "prim", status READY, has 1 handler(s) for this service... Handler(s): "D000" established:0 refused:0 current:0 max:1022 state:ready DISPATCHER <machine: localhost.localdomain, pid: 4389> (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=47710))