오라클 19c 리스너 상태 READY와 UNKNOWN 차이

OS환경 : Oracle Linux 7.5 (64bit)

DB 환경 : Oracle Database 19.3.0.0

방법 : 오라클 19c 리스너 상태 READY와 UNKNOWN 차이

lsnrctl status 명령으로 리스너 상태를 조회했을 때 나오는 READY와 UNKNOWN 에 대해 설명함

현재 리스너 상태 확인

$ lsnrctl status
 
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 15-SEP-2020 15:16:43
 
Copyright (c) 1991, 2019, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle19)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                15-SEP-2020 15:16:35
Uptime                    0 days 0 hr. 0 min. 7 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /app/oracle/product/19c/network/admin/listener.ora
Listener Log File         /app/oracle/diag/tnslsnr/oracle19/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle19)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "orcl19" has 2 instance(s).
  Instance "orcl19", status UNKNOWN, has 1 handler(s) for this service...
  Instance "orcl19", status READY, has 1 handler(s) for this service...
Service "orcl19XDB" has 1 instance(s).
  Instance "orcl19", status READY, has 1 handler(s) for this service...
The command completed successfully

orcl19XDB는 제외하고 설명함

orcl19에 대해 READY와 UNKNOWN이 존재함

READY 로 표시된 서비스는 동적으로 등록된 리스너임

KNOWN 로 표시된 서비스는 정적으로 등록도니 리스너임

정적등록 VS 동적 등록

정적 등록과 동적 등록의 가장 큰 차이는 Oracle Instance가 리스너를 자동으로 인식 하느냐 못하느냐의 차이임

정적 등록은 listener.ora파일에 직접 서비스를 등록해주는 것임(아래 listener.ora의 SID_LIST_LISTENER)

동적 등록은 PMON 프로세스가 LREG 프로세스를 통해 리스너에 등록 가능한 서비스를 자동으로 등록해주는 것임

PMON(Process Monitor)

오라클에 서버에서 사용되는 각 프로세스들을 감시하는 프로세스

LREG (Listener Registration)

리스너에게 인스턴스, 서비스, 핸들러 및 엔드 포인트에 대해 알리는 프로세스

(12c 이전에는 PMON 프로세스가 이 역할을 함)

현재 리스너 서비스 확인

$ lsnrctl service
 
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 15-SEP-2020 15:36:03
 
Copyright (c) 1991, 2019, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle19)(PORT=1521)))
Services Summary...
Service "orcl19" has 2 instance(s).
  Instance "orcl19", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
  Instance "orcl19", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:420 refused:0 state:ready
         LOCAL SERVER
Service "orcl19XDB" has 1 instance(s).
  Instance "orcl19", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: oracle19, pid: 9856>
         (ADDRESS=(PROTOCOL=tcp)(HOST=oracle19)(PORT=10309))
The command completed successfully

현재 listener.ora 파일 확인

$ cat $ORACLE_HOME/network/admin/listener.ora
# listener.ora Network Configuration File: /app/oracle/product/19c/network/admin/listener.ora
# Generated by Oracle configuration tools.
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle19)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
 
SID_LIST_LISTENER =
 (SID_LIST =
  (SID_DESC =
   (ORACLE_HOME = /app/oracle/product/19c)
   (SID_NAME = orcl19)
  )
 )

listener.ora 파일에 SID_LIST_LISTENER 로 등록하면 KNOWN으로 표시됨

이는 정적으로 등록된 리스너이고 KNOWN이 작동이 안하는것은 아님, 단지 구분을 위한 표시임

하지만 제대로 작동하는지 상태는 확인할 수 없음

테스트

listener1 => 동적 리스너로 등록(포트 1521)

listener2 => 정적 리스너로 등록(포트 1522)

기존 리스너 정지

$ lsnrctl stop
 
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 15-SEP-2020 17:27:29
 
Copyright (c) 1991, 2019, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle19)(PORT=1521)))
The command completed successfully

listener.ora 파일수정

기존 리스너 삭제 후 listener1 리스너와 listener2 리스너 생성

아래와 같이 수정

$ cat $ORACLE_HOME/network/admin/listener.ora
# listener.ora Network Configuration File: /app/oracle/product/19c/network/admin/listener.ora
# Generated by Oracle configuration tools.
 
LISTENER1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle19)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
 
LISTENER2 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle19)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    )
  )
 
SID_LIST_LISTENER2 =
 (SID_LIST =
  (SID_DESC =
   (ORACLE_HOME = /app/oracle/product/19c)
   (SID_NAME = orcl19)
  )
 )

listener1, listener2 리스너 모두 기동

listener1 기동
$ lsnrctl start listener1
 
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 15-SEP-2020 17:31:45
 
Copyright (c) 1991, 2019, Oracle.  All rights reserved.
 
Starting /app/oracle/product/19c/bin/tnslsnr: please wait...
 
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /app/oracle/product/19c/network/admin/listener.ora
Log messages written to /app/oracle/diag/tnslsnr/oracle19/listener1/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle19)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle19)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     listener1
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                15-SEP-2020 17:31:45
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /app/oracle/product/19c/network/admin/listener.ora
Listener Log File         /app/oracle/diag/tnslsnr/oracle19/listener1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle19)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
 
listener2 기동
$ lsnrctl start listener2
 
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 15-SEP-2020 17:33:54
 
Copyright (c) 1991, 2019, Oracle.  All rights reserved.
 
Starting /app/oracle/product/19c/bin/tnslsnr: please wait...
 
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /app/oracle/product/19c/network/admin/listener.ora
Log messages written to /app/oracle/diag/tnslsnr/oracle19/listener2/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle19)(PORT=1522)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle19)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     listener2
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                15-SEP-2020 17:33:54
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /app/oracle/product/19c/network/admin/listener.ora
Listener Log File         /app/oracle/diag/tnslsnr/oracle19/listener2/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle19)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
Services Summary...
Service "orcl19" has 1 instance(s).
  Instance "orcl19", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

1521 포트로 리스너를 기동 후 LREG 프로세스가 1분이내에 INSTANCE를 발견해 리스너에 등록함

더 빠르게 등록해주기 위해 수동으로 아래 명령 실행

SQL> alter system register;
 
System altered.

listener1, listener2 리스너 상태 확인

listener1 상태 확인
$ lsnrctl status listener1
 
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 15-SEP-2020 17:35:01
 
Copyright (c) 1991, 2019, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle19)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     listener1
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                15-SEP-2020 17:31:45
Uptime                    0 days 0 hr. 3 min. 16 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /app/oracle/product/19c/network/admin/listener.ora
Listener Log File         /app/oracle/diag/tnslsnr/oracle19/listener1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle19)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "orcl19" has 1 instance(s).
  Instance "orcl19", status READY, has 1 handler(s) for this service...
Service "orcl19XDB" has 1 instance(s).
  Instance "orcl19", status READY, has 1 handler(s) for this service...
The command completed successfully
 
listener2 상태 확인
$ lsnrctl status listener2
 
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 15-SEP-2020 17:35:21
 
Copyright (c) 1991, 2019, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle19)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     listener2
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                15-SEP-2020 17:33:54
Uptime                    0 days 0 hr. 1 min. 27 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /app/oracle/product/19c/network/admin/listener.ora
Listener Log File         /app/oracle/diag/tnslsnr/oracle19/listener2/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle19)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
Services Summary...
Service "orcl19" has 1 instance(s).
  Instance "orcl19", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

listener1, listener2 리스너 서비스 확인

listener1 서비스 확인
$ lsnrctl service listener1
 
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 15-SEP-2020 17:40:08
 
Copyright (c) 1991, 2019, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle19)(PORT=1521)))
Services Summary...
Service "orcl19" has 1 instance(s).
  Instance "orcl19", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:161 refused:0 state:ready
         LOCAL SERVER
Service "orcl19XDB" has 1 instance(s).
  Instance "orcl19", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: oracle19, pid: 9856>
         (ADDRESS=(PROTOCOL=tcp)(HOST=oracle19)(PORT=10309))
The command completed successfully
 
listener2 서비스 확인
$ lsnrctl service listener2
 
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 15-SEP-2020 17:40:06
 
Copyright (c) 1991, 2019, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle19)(PORT=1522)))
Services Summary...
Service "orcl19" has 1 instance(s).
  Instance "orcl19", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully

listener1은 READY 상태에 established가 161임

listener2는 UNKNOWN 상태에 established가 0임

tnsnames.ora 파일 확인

아래와 같이 수정

$ cat $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /app/oracle/product/19c/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
 
ORCL19_L1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle19)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl19)
    )
  )
 
ORCL19_L2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle19)(PORT = 1522)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl19)
    )
  )

listener2 종료 후 listener1을 통해 tnsping 및 sqlplus 접속 테스트

listener2 리스너 정지
$ lsnrctl stop listener2
 
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 15-SEP-2020 19:44:19
 
Copyright (c) 1991, 2019, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle19)(PORT=1522)))
The command completed successfully
 
orcl19_l1로 tnsping
$ tnsping orcl19_l1
 
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 15-SEP-2020 19:45:14
 
Copyright (c) 1997, 2019, Oracle.  All rights reserved.
 
Used parameter files:
 
 
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle19)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl19)))
OK (0 msec)
 
orcl19_l1로 sqlplus 접속 테스트
$ sqlplus system/oracle@orcl19_l1
 
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 15 19:45:23 2020
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
Last Successful login time: Tue Sep 15 2020 19:45:22 +09:00
 
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
 
SQL> select host_name from v$instance; HOST_NAME-----------------oracle19

정상적으로 연결됨(listener1 리스너로 접속)

listener2 접속확인

orcl19_l2로 tnsping
$ tnsping orcl19_l2
 
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 15-SEP-2020 19:47:02
 
Copyright (c) 1997, 2019, Oracle.  All rights reserved.
 
Used parameter files:
 
 
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle19)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl19)))
TNS-12541: TNS:no listener
 
orcl19_l2로 sqlplus 접속 테스트
$ sqlplus system/oracle@orcl19_l2
 
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 15 19:47:18 2020
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
ERROR:
ORA-12541: TNS:no listener
 
 
Enter user-name:

listener2 리스너가 내려가 있기때문에 tnsping, sqlplus 연결 모두 실패함

반대로 listener2 기동 후 

listener1 종료 후 listener2을 통해 tnsping 및 sqlplus 접속 테스트

listener2 리스너 기동
$ lsnrctl start listener2
 
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 15-SEP-2020 19:49:11
 
Copyright (c) 1991, 2019, Oracle.  All rights reserved.
 
Starting /app/oracle/product/19c/bin/tnslsnr: please wait...
 
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /app/oracle/product/19c/network/admin/listener.ora
Log messages written to /app/oracle/diag/tnslsnr/oracle19/listener2/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle19)(PORT=1522)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle19)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     listener2
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                15-SEP-2020 19:49:11
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /app/oracle/product/19c/network/admin/listener.ora
Listener Log File         /app/oracle/diag/tnslsnr/oracle19/listener2/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle19)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
Services Summary...
Service "orcl19" has 1 instance(s).
  Instance "orcl19", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
 
listener1 리스너 정지
$ lsnrctl stop listener1
 
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 15-SEP-2020 19:49:29
 
Copyright (c) 1991, 2019, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle19)(PORT=1521)))
The command completed successfully
 
orcl19_l2로 tnsping
$ tnsping orcl19_l2
 
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 15-SEP-2020 19:49:35
 
Copyright (c) 1997, 2019, Oracle.  All rights reserved.
 
Used parameter files:
 
 
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle19)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl19)))
OK (0 msec)
 
orcl19_l2로 sqlplus 접속 테스트
$ sqlplus system/oracle@orcl19_l2
 
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 15 19:49:41 2020
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
Last Successful login time: Tue Sep 15 2020 19:49:24 +09:00
 
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
 
SQL> select host_name from v$instance;
 
HOST_NAME
-----------------
oracle19

정상적으로 연결됨(listener2 리스너로 접속)

listener2의 status가 UNKNOWN이지만 접속이 가능함을 확인할 수 있음

UNKNOWN으로 표시되어 있던 listener2의 established도 tns를 통한 sqlplus 접속시마다 1씩 올라감

$ lsnrctl service listener2
 
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 15-SEP-2020 20:54:06
 
Copyright (c) 1991, 2019, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle19)(PORT=1522)))
Services Summary...
Service "orcl19" has 1 instance(s).
  Instance "orcl19", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:1 refused:0
         LOCAL SERVER
The command completed successfully

테스트2

리스너 2개 모두 기본포트(1521)이 아닌 다른 포트로 생성 후 테스트

listener3 => 동적 리스너로 등록(포트 1523)

listener4 => 정적 리스너로 등록(포트 1524)

기존리스너 모두 정지

$ lsnrctl stop listener1
$ lsnrctl stop listener2

listener.ora 파일수정

기존 리스너 삭제 후 listener3 리스너와 listener4 리스너 생성

아래와 같이 수정

$ cat $ORACLE_HOME/network/admin/listener.ora
# listener.ora Network Configuration File: /app/oracle/product/19c/network/admin/listener.ora
# Generated by Oracle configuration tools.
 
LISTENER3 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle19)(PORT = 1523))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1523))
    )
  )
 
LISTENER4 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle19)(PORT = 1524))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1524))
    )
  )
 
SID_LIST_LISTENER4 =
 (SID_LIST =
  (SID_DESC =
   (ORACLE_HOME = /app/oracle/product/19c)
   (SID_NAME = orcl19)
  )
 )

listener3, listener4 리스너 모두 기동

$ lsnrctl start LISTENER3
$ lsnrctl start LISTENER4
(로그 생략)

1521 포트로 리스너를 기동 후 LREG 프로세스가 수초이내에 INSTANCE를 발견해 리스너에 등록함

더 빠르게 등록해주기 위해 수동으로 아래 명령 실행

기본 포트가 아닌 1523, 1524 포트로 리스너를 기동했기 때문에 아래 명령을 실행해도 변함없음

SQL> alter system register;
 
System altered.

listener3, listener4 리스너 상태 확인

listener3 상태 확인
$ lsnrctl status listener3
 
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 16-SEP-2020 21:38:49
 
Copyright (c) 1991, 2019, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle19)(PORT=1523)))
STATUS of the LISTENER
------------------------
Alias                     listener3
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                16-SEP-2020 21:35:16
Uptime                    0 days 0 hr. 3 min. 33 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /app/oracle/product/19c/network/admin/listener.ora
Listener Log File         /app/oracle/diag/tnslsnr/oracle19/listener3/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle19)(PORT=1523)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1523)))
The listener supports no services
The command completed successfully
 
listener4 상태 확인
$ lsnrctl status listener4
 
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 16-SEP-2020 21:38:56
 
Copyright (c) 1991, 2019, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle19)(PORT=1524)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER4
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                16-SEP-2020 21:26:28
Uptime                    0 days 0 hr. 12 min. 27 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /app/oracle/product/19c/network/admin/listener.ora
Listener Log File         /app/oracle/diag/tnslsnr/oracle19/listener4/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle19)(PORT=1524)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1524)))
Services Summary...
Service "orcl19" has 1 instance(s).
  Instance "orcl19", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

listener3 리스너의 경우 아무런 서비스도 올라와있지 않음

listener4 리스너의 경우 UNKNOWN 으로 서비스가 올라와있음

listener3, listener4 리스너 서비스 확인

listener3 서비스 확인
$ lsnrctl service listener3
 
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 16-SEP-2020 21:40:21
 
Copyright (c) 1991, 2019, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle19)(PORT=1523)))
The listener supports no services
The command completed successfully
You have mail in /var/spool/mail/oracle
 
listener4 서비스 확인
$ lsnrctl service listener4
 
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 16-SEP-2020 21:40:25
 
Copyright (c) 1991, 2019, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle19)(PORT=1524)))
Services Summary...
Service "orcl19" has 1 instance(s).
  Instance "orcl19", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully

listener3 리스너의 경우 아무런 서비스도 올라와있지 않음

listener4 리스너의 경우 UNKNOWN 으로 서비스가 올라와있음

tnsnames.ora 파일 확인

아래와 같이 수정

$ cat $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /app/oracle/product/19c/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
 
ORCL19_L3 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle19)(PORT = 1523))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl19)
    )
  )
 
ORCL19_L4 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle19)(PORT = 1524))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl19)
    )
  )

listener3 종료 후 listener4을 통해 tnsping 및 sqlplus 접속 테스트

listener3 리스너 정지
$ lsnrctl stop listener3
 
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 16-SEP-2020 21:50:15
 
Copyright (c) 1991, 2019, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle19)(PORT=1523)))
The command completed successfully
 
orcl19_l4로 tnsping
$ tnsping orcl19_l4
 
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 16-SEP-2020 21:50:27
 
Copyright (c) 1997, 2019, Oracle.  All rights reserved.
 
Used parameter files:
 
 
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle19)(PORT = 1524)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl19)))
OK (0 msec)
 
orcl19_l4로 sqlplus 접속 테스트
$ sqlplus system/oracle@orcl19_l4
 
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 16 21:50:37 2020
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
Last Successful login time: Wed Sep 16 2020 21:48:57 +09:00
 
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
 
SQL> select host_name from v$instance;
 
HOST_NAME
---------
oracle19

정상적으로 연결됨(listener4 리스너로 접속)

listener4의 status가 UNKNOWN이지만 접속이 가능함을 확인할 수 있음

listener3 접속확인

orcl19_l3로 tnsping
$ tnsping orcl19_l3
 
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 16-SEP-2020 22:00:17
 
Copyright (c) 1997, 2019, Oracle.  All rights reserved.
 
Used parameter files:
 
 
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle19)(PORT = 1523)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl19)))
TNS-12541: TNS:no listener
 
orcl19_l3로 sqlplus 접속 테스트
$ sqlplus system/oracle@orcl19_l3
 
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 16 22:00:24 2020
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
ERROR:
ORA-12541: TNS:no listener
 
 
Enter user-name:

listener3 리스너가 내려가 있기때문에 tnsping, sqlplus 연결 모두 실패함

반대로 listener3 기동 후

listener4 종료 후 listener3을 통해 tnsping 및 sqlplus 접속 테스트

listener3 리스너 기동
$ lsnrctl start listener3
 
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 16-SEP-2020 22:01:43
 
Copyright (c) 1991, 2019, Oracle.  All rights reserved.
 
Starting /app/oracle/product/19c/bin/tnslsnr: please wait...
 
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /app/oracle/product/19c/network/admin/listener.ora
Log messages written to /app/oracle/diag/tnslsnr/oracle19/listener3/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle19)(PORT=1523)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1523)))
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle19)(PORT=1523)))
STATUS of the LISTENER
------------------------
Alias                     listener3
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                16-SEP-2020 22:01:43
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /app/oracle/product/19c/network/admin/listener.ora
Listener Log File         /app/oracle/diag/tnslsnr/oracle19/listener3/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle19)(PORT=1523)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1523)))
The listener supports no services
The command completed successfully
You have mail in /var/spool/mail/oracle
 
listener4 리스너 정지
$ lsnrctl stop listener4
 
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 16-SEP-2020 22:02:19
 
Copyright (c) 1991, 2019, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle19)(PORT=1524)))
The command completed successfully
 
orcl19_l3로 tnsping
$ tnsping orcl19_l3
 
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 16-SEP-2020 22:02:23
 
Copyright (c) 1997, 2019, Oracle.  All rights reserved.
 
Used parameter files:
 
 
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle19)(PORT = 1523)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl19)))
OK (0 msec)
 
orcl19_l3로 sqlplus 접속 테스트
$ sqlplus system/oracle@orcl19_l3
 
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 16 22:02:27 2020
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
 
 
Enter user-name:
 

tnsping은 OK로 나오지만 실제 sqlplus 접속은 불가함

listener3 리스너에 orcl19 서비스가 올라가있지 않기때문임

리스너 기본포트(1521)이 아닌 포트를 동적 리스너로 사용할 때에는

local_listener 파라미터에 등록을 해줘야 해당 리스너에 서비스가 올라감

local_listener 파라미터 설정(tnsnames.ora에 설정한 이름을 등록)

파라미터 확인

SQL> show parameter local_listener
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
local_listener                 string

파라미터 변경 후 확인

SQL> alter system set local_listener='ORCL19_L3';
 
System altered.
 
SQL> show parameter local_listener
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
local_listener                 string     ORCL19_L3

정상적으로 파라미터값이 변경됨

listener3 리스너 상태 및 서비스 확인

listener3 리스너 서비스 확인
$ lsnrctl status listener3
 
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 16-SEP-2020 22:08:30
 
Copyright (c) 1991, 2019, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle19)(PORT=1523)))
STATUS of the LISTENER
------------------------
Alias                     listener3
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                16-SEP-2020 22:01:43
Uptime                    0 days 0 hr. 6 min. 46 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /app/oracle/product/19c/network/admin/listener.ora
Listener Log File         /app/oracle/diag/tnslsnr/oracle19/listener3/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle19)(PORT=1523)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1523)))
Services Summary...
Service "orcl19" has 1 instance(s).
  Instance "orcl19", status READY, has 1 handler(s) for this service...
Service "orcl19XDB" has 1 instance(s).
  Instance "orcl19", status READY, has 1 handler(s) for this service...
The command completed successfully
 
listener3 리스너 서비스 확인
$ lsnrctl service listener3
 
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 16-SEP-2020 22:08:40
 
Copyright (c) 1991, 2019, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle19)(PORT=1523)))
Services Summary...
Service "orcl19" has 1 instance(s).
  Instance "orcl19", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "orcl19XDB" has 1 instance(s).
  Instance "orcl19", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: oracle19, pid: 9856>
         (ADDRESS=(PROTOCOL=tcp)(HOST=oracle19)(PORT=10309))
The command completed successfully

listener3 리스너에 정상적으로 서비스가 등록됨

sqlplus 접속 테스트

$ sqlplus system/oracle@orcl19_l3
 
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 16 22:09:49 2020
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
Last Successful login time: Wed Sep 16 2020 21:50:37 +09:00
 
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
 
SQL> select host_name from v$instance;
 
HOST_NAME
---------
oracle19

정상적으로 접속됨

참조 : 

https://docs.oracle.com/en/database/oracle/oracle-database/19/netag/configuring-and-administering-oracle-net-listener.html#GUID-9CDAFB4E-A675-4DF6-90A2-09DF94B80648

https://myjamong.tistory.com/215

https://docs.oracle.com/en/database/oracle/oracle-database/18/refrn/background-processes.html#GUID-86184690-5531-405F-AA05-BB935F57B76D

https://oracledbwr.com/oracle-12c-background-process-lreg-listener-registration/

http://www.gurubee.net/lecture/2811

Doc. 787055.1