Database/Oracle
56 - [Oracle 19C] The default listener - non-default port
unsungIT
2023. 11. 30. 01:43
Non-default port - to change PORT as 1529
[oracle@test admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = test.com)(PORT = 1529))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
port is 1529
[oracle@test ~]$ lsnrctl start
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 29-NOV-2023 15:46:48
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Starting /u01/app/oracle/product/19/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/test/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test.com)(PORT=1529)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test.com)(PORT=1529)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 29-NOV-2023 15:46:48
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/19/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/test/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test.com)(PORT=1529)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
[oracle@test ~]$
60s 지났지만 서비스는 아직도 동작하지 않는다.
[oracle@test ~]$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 29-NOV-2023 15:47:54
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test.com)(PORT=1529)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 29-NOV-2023 15:46:48
Uptime 0 days 0 hr. 1 min. 6 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/test/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test.com)(PORT=1529)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
[oracle@test ~]$
To need to do more.
To change the port number as 1529 on the tnsnames.ora
[oracle@test admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = test.com)(PORT = 1529))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORS.com)
)
)
LISTENER_ORS =
(ADDRESS = (PROTOCOL = TCP)(HOST = test.com)(PORT = 1529))
ORCLPDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = test.com)(PORT = 1529))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclpdb.com)
)
)
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = test.com)(PORT = 1529))
PDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = test.com)(PORT = 1529))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb1.com)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = test.com)(PORT = 1529))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.com)
)
)
[oracle@test admin]$
local_listener 값을 refresh 해야 한다.
SQL>
SQL> show parameter local
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string LISTENER_ORCL
parallel_force_local boolean FALSE
SQL>
SQL> alter system set local_listener=LISTENER_ORCL;
System altered.
SQL>
[oracle@test admin]$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 29-NOV-2023 15:55:01
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test.com)(PORT=1529)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 29-NOV-2023 15:46:48
Uptime 0 days 0 hr. 8 min. 13 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/test/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test.com)(PORT=1529)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=test.com)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/orcl/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "09d7302c9140311ce0639802a8c09c66.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "0ae8ae6e8e43ba20e063e102a8c0700c.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "0b23a05d4693eaa3e063e102a8c05ada.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "86b637b62fdf7a65e053f706e80a27ca.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcl.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclpdb.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "pdb1.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "pdbtest1.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@test admin]$
- In the case of two instances, there are sample listener.ora and tnsnames.ora
[oracle@test admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = test.com)(PORT = 1529))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
HONG =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = test.com)(PORT = 1530))
)
)
[oracle@test admin]$
-------------------------------------------------------------------------------------
[oracle@test admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCLPDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = test.com)(PORT = 1529))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclpdb.com)
)
)
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = test.com)(PORT = 1529))
LISTENER_HONG =
(ADDRESS_LIST =
#(ADDRESS = (PROTOCOL = TCP)(HOST = test.com)(PORT = 1529))
(ADDRESS = (PROTOCOL = TCP)(HOST = test.com)(PORT = 1530))
)
PDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = test.com)(PORT = 1529))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb1.com)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = test.com)(PORT = 1529))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.com)
)
)
HONG =
(DESCRIPTION =
#(ADDRESS = (PROTOCOL = TCP)(HOST = test.com)(PORT = 1529))
(ADDRESS = (PROTOCOL = TCP)(HOST = test.com)(PORT = 1530))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = hong.com)
)
)
[oracle@test admin]$
- Checking List for Listener and demons
# checking demons
[oracle@test admin]$ ps -ef | grep tns
root 6 2 0 11:03 ? 00:00:00 [netns]
oracle 20688 2305 0 22:24 ? 00:00:00 /u01/app/oracle/product/19/db_1/bin/tnslsnr HONG -inherit
oracle 23410 1 0 22:48 ? 00:00:00 /u01/app/oracle/product/19/db_1/bin/tnslsnr LISTENER -inherit
oracle 23607 17293 0 23:03 pts/0 00:00:00 grep --color=auto tns
# stop the default listener
[oracle@test admin]$ lsnrctl stop
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 29-NOV-2023 23:03:30
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test.com)(PORT=1529)))
The command completed successfully
# but listener HONG is still running
[oracle@test admin]$ ps -ef | grep tns
root 6 2 0 11:03 ? 00:00:00 [netns]
oracle 20688 2305 0 22:24 ? 00:00:00 /u01/app/oracle/product/19/db_1/bin/tnslsnr HONG -inherit
oracle 23613 17293 0 23:03 pts/0 00:00:00 grep --color=auto tns
# stop the listener HONG
[oracle@test admin]$ lsnrctl stop hong
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 29-NOV-2023 23:03:52
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test.com)(PORT=1530)))
The command completed successfully
# all demons are down
[oracle@test admin]$ ps -ef | grep tns
root 6 2 0 11:03 ? 00:00:00 [netns]
oracle 23624 17293 0 23:03 pts/0 00:00:00 grep --color=auto tns
# starting default listener
[oracle@test admin]$ lsnrctl start
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 29-NOV-2023 23:04:20
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Starting /u01/app/oracle/product/19/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/test/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test.com)(PORT=1529)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test.com)(PORT=1529)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 29-NOV-2023 23:04:20
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/19/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/test/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test.com)(PORT=1529)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
[oracle@test admin]$ ps -ef | grep tns
root 6 2 0 11:03 ? 00:00:00 [netns]
oracle 23628 1 0 23:04 ? 00:00:00 /u01/app/oracle/product/19/db_1/bin/tnslsnr LISTENER -inherit
oracle 23631 17293 0 23:04 pts/0 00:00:00 grep --color=auto tns
# starting listener HONG
[oracle@test admin]$ lsnrctl start HONG
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 29-NOV-2023 23:04:42
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Starting /u01/app/oracle/product/19/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/test/hong/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test.com)(PORT=1530)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test.com)(PORT=1530)))
STATUS of the LISTENER
------------------------
Alias HONG
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 29-NOV-2023 23:04:43
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/19/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/test/hong/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test.com)(PORT=1530)))
The listener supports no services
The command completed successfully
# all listeners are running
[oracle@test admin]$ ps -ef | grep tns
root 6 2 0 11:03 ? 00:00:00 [netns]
oracle 23628 1 0 23:04 ? 00:00:00 /u01/app/oracle/product/19/db_1/bin/tnslsnr LISTENER -inherit
oracle 23634 1 0 23:04 ? 00:00:00 /u01/app/oracle/product/19/db_1/bin/tnslsnr HONG -inherit
oracle 23637 17293 0 23:04 pts/0 00:00:00 grep --color=auto tns
[oracle@test admin]$
# default listener status
[oracle@test admin]$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 29-NOV-2023 23:09:16
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test.com)(PORT=1529)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 29-NOV-2023 23:04:20
Uptime 0 days 0 hr. 4 min. 55 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/test/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test.com)(PORT=1529)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=test.com)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/orcl/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "09d7302c9140311ce0639802a8c09c66.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "0ae8ae6e8e43ba20e063e102a8c0700c.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "0b23a05d4693eaa3e063e102a8c05ada.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "86b637b62fdf7a65e053f706e80a27ca.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcl.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclpdb.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "pdb1.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "pdbtest1.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
# listener HONG status
[oracle@test admin]$ lsnrctl status HONG
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 29-NOV-2023 23:09:25
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test.com)(PORT=1530)))
STATUS of the LISTENER
------------------------
Alias HONG
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 29-NOV-2023 23:04:43
Uptime 0 days 0 hr. 4 min. 42 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/test/hong/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test.com)(PORT=1530)))
Services Summary...
Service "0b5331dc15e059d6e063e102a8c0e71c.com" has 1 instance(s).
Instance "hong", status READY, has 1 handler(s) for this service...
Service "86b637b62fdf7a65e053f706e80a27ca.com" has 1 instance(s).
Instance "hong", status READY, has 1 handler(s) for this service...
Service "hong.com" has 1 instance(s).
Instance "hong", status READY, has 1 handler(s) for this service...
Service "hongXDB.com" has 1 instance(s).
Instance "hong", status READY, has 1 handler(s) for this service...
Service "hongpdb.com" has 1 instance(s).
Instance "hong", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@test admin]$
Testing connection
-------------------------------- conn @orcl --------------------------------
[oracle@test ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Nov 29 23:11:17 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
4 PDB1 READ WRITE NO
5 PDBTEST1 READ WRITE NO
SQL> show parameter local
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string LISTENER_ORCL
parallel_force_local boolean FALSE
SQL>
-------------------------------- conn @hong --------------------------------
SQL> conn sys/Manager123#@hong as sysdba
Connected.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 HONGPDB READ WRITE NO
SQL> show parameter local
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string LISTENER_HONG
parallel_force_local boolean FALSE
SQL>
- Success case
- Error case
