/etc/hosts와 hostname이 리스너 통신에 미치는 영향 테스트

글쓴이 /

안녕하세요 Surmin 입니다!

오늘은 /etc/hosts와 hostname 설정이 리스너에 미치는 영향을 정리하였습니다.

테스트하실 때 참고하시면 도움이 될 것입니다.

(해당 포스팅에 도움을 주신 내맘대로긍정님께 감사의 말씀을 드립니다.
https://positivemh.tistory.com/)

Test 환경

  • 방화벽은 Down 상태
  • oracle버전 19c

Test

  • /etc/hosts 파일 제거 전 SQL Developer 접속 및 설정 확인합니다.
#설정 확인 
[root@ORA19C ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.153.37   ORA19C

#리스너 상태확인
[oracle@ORA19C dbs]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 26-APR-2025 18:27:51

Copyright (c) 1991, 2024, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORA19C)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                26-APR-2025 18:26:21
Uptime                    0 days 0 hr. 1 min. 29 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/ORA19C/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ORA19C)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "ORA19C" has 1 instance(s).
  Instance "ORA19C", status READY, has 1 handler(s) for this service...
Service "ORA19CXDB" has 1 instance(s).
  Instance "ORA19C", status READY, has 1 handler(s) for this service...
The command completed successfully

SQL Developer 접속확인

/etc/hosts 파일 설정 제거 후 접속 확인

#설정 제거함
[root@ORA19C ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

#확실한 테스트를 위하여 재시작 
[oracle@ORA19C dbs]$ lsnrctl stop
[oracle@ORA19C dbs]$ lsnrctl start

#sql은 안됨
[oracle@ORA19C admin]$ sqlplus system/oracle@//ORA19C:1521/ORA19C

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Apr 26 23:59:55 2025
Version 19.26.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

⇒결과: 그래도 접속됨 (sqlplus제외)

원인

  1. Developer 툴 접속 시 IP 입력을 통하여 접속하기 때문에

⇒ 개선: sqlplus 통하여 Test

/etc/hosts 파일은 변경한 상태

#hostname 변경 
[root@ORA19C ~]#hostname localhost
  • 확인 시 host를 못찾았다고 나옴 하지만 IPC 프로토콜을 통하여 리스너가 올라옴
    • IPC: 프로세스들이 데이터를 서로 교환할 수 있는 통신 기법
#리스너 확인
[oracle@localhost ~]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 26-APR-2025 22:32:19

Copyright (c) 1991, 2024, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORA19C)(PORT=1521)))
TNS-12545: Connect failed because target host or object does not exist
 TNS-12560: TNS:protocol adapter error
  TNS-00515: Connect failed because target host or object does not exist

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                26-APR-2025 18:41:18
Uptime                    0 days 3 hr. 51 min. 1 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/ORA19C/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ORA19C)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "ORA19C" has 1 instance(s).
  Instance "ORA19C", status READY, has 1 handler(s) for this service...
Service "ORA19CXDB" has 1 instance(s).
  Instance "ORA19C", status READY, has 1 handler(s) for this service...
The command completed successfully
  • 리눅스의 sqlplus 에서 접근 가능한지 보자
    • ORA-12154 확인
[oracle@localhost ~]$ sqlplus system/oracle@//ORA19C:1521/ORA19C

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Apr 26 22:39:24 2025
Version 19.26.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
  • ping 확인
    • link local address 확인
      • 브로드 캐스트 도메인 내의 통신에 대해서만 유효한 네트워크 주소
    • 사용이유
      • 장치가 DHCP 서버를 찾지 못하여 IP 를 할당 받지 못하거나 static IP 로의 설정 또한 지정되어 있지 않을 경우, 자동적으로 자신의 IP 를 할당하기 위하여 사용함.
[oracle@ORA19C admin]$ ping ORA19C
PING ORA19C(ORA19C (fe80::20c:29ff:fe73:a43c%ens160)) 56 data bytes
64 bytes from ORA19C (fe80::20c:29ff:fe73:a43c%ens160): icmp_seq=1 ttl=64 time=0.322 ms
64 bytes from ORA19C (fe80::20c:29ff:fe73:a43c%ens160): icmp_seq=2 ttl=64 time=0.042 ms
64 bytes from ORA19C (fe80::20c:29ff:fe73:a43c%ens160): icmp_seq=3 ttl=64 time=0.041 ms
64 bytes from ORA19C (fe80::20c:29ff:fe73:a43c%ens160): icmp_seq=4 ttl=64 time=0.038 ms
64 bytes from ORA19C (fe80::20c:29ff:fe73:a43c%ens160): icmp_seq=5 ttl=64 time=0.040 ms

⇒ 당연히 Developer 는 접속 가능하다.

-/etc/hosts 파일에 내용 추가

  • 리스너 상태 확인

[oracle@localhost ~]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 26-APR-2025 22:40:32

Copyright (c) 1991, 2024, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORA19C)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                26-APR-2025 18:41:18
Uptime                    0 days 3 hr. 59 min. 14 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/ORA19C/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ORA19C)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "ORA19C" has 1 instance(s).
  Instance "ORA19C", status READY, has 1 handler(s) for this service...
Service "ORA19CXDB" has 1 instance(s).
  Instance "ORA19C", status READY, has 1 handler(s) for this service...
The command completed successfully

sqlplus 확인

[oracle@localhost ~]$ sqlplus system/oracle@//ORA19C:1521/ORA19C

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Apr 26 22:40:36 2025
Version 19.26.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

Last Successful login time: Sat Apr 26 2025 22:26:50 +09:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.26.0.0.0

ping 확인

[oracle@localhost ~]$  ping ORA19C
PING ORA19C (192.168.153.37) 56(84) bytes of data.
64 bytes from ORA19C (192.168.153.37): icmp_seq=1 ttl=64 time=0.082 ms
64 bytes from ORA19C (192.168.153.37): icmp_seq=2 ttl=64 time=0.038 ms

⇒SQL Developer 에서 이지커넥트 접속 됨

-이번에는 /etc/hosts 파일의 내용을 제거 한 상태 및 hostname도 기본인 localhost 상태에서 Test

#호스트네임 확인
[oracle@localhost admin]$ hostname
localhost

#hosts파일 확인
[oracle@localhost admin]$ cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
  • listener.ora 파일 수정
    • hostname 이라도 SID로 설정 되어있을 경우 SQL Developer 에서 이지커넥트 연결이 가능 하지만 hostname이 설정 되어 있지 않음으로, 정적 등록 진행 합니다.
[oracle@localhost admin]$ vi 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 = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
#정적등록 부분
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = ORA19C)
      (ORACLE_HOME = /app/oracle/product/19c)
    )
  )
  • tnsnames.ora 수정
    • tnsnames.ora 파일은 수정할게 없다 왜냐하면 클라이언트 측의 연결정보이기 때문이다.
[oracle@localhost admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /app/oracle/product/19c/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORA19C =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ORA19C)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORA19C)
    )
  )

LISTENER_ORA19C =
  (ADDRESS = (PROTOCOL = TCP)(HOST = ORA19C)(PORT = 1521))
  
#정적 등록확인
[oracle@localhost admin]$ lsnrctl status
...
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/localhost/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                27-APR-2025 00:51:05
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/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "ORA19C" has 1 instance(s).
  Instance "ORA19C", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
...

ping 및 sqlplus 확인

#접속확인
[oracle@localhost admin]$ sqlplus system/oracle@//localhost:1521/ORA19C

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 27 00:52:51 2025
Version 19.26.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

Last Successful login time: Sun Apr 27 2025 00:33:58 +09:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.26.0.0.0

SQL> exit

#ping
[oracle@localhost admin]$ ping ORA19C
ping: ORA19C: Name or service not known

#tnsping 
# /etc/hosts 에 SID가 없어서 ERROR 발생함 
[oracle@localhost admin]$ tnsping ORA19C

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 27-APR-2025 01:17:40

Copyright (c) 1997, 2024, Oracle.  All rights reserved.

Used parameter files:
/app/oracle/product/19c/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ORA19C)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORA19C)))
TNS-12545: Connect failed because target host or object does not exist

Developer 접속확인

-이번에는 /etc/hosts 파일의 내용을 제거 한 상태에서 hostname만 ORA19C로 변경 후 Test

[oracle@ORA19C ~]$ hostname
ORA19C

[oracle@ORA19C ~]$ cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

listener.ora 파일 설정

[oracle@ORA19C admin]$ vi 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 = ORA19C)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

tnsnames.ora 파일은 수정할게 없음

[oracle@ORA19C admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /app/oracle/product/19c/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORA19C =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ORA19C)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORA19C)
    )
  )

LISTENER_ORA19C =
  (ADDRESS = (PROTOCOL = TCP)(HOST = ORA19C)(PORT = 1521))

tnsping

[oracle@ORA19C admin]$ tnsping ORA19C

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 27-APR-2025 02:15:38

Copyright (c) 1997, 2024, Oracle.  All rights reserved.

Used parameter files:
/app/oracle/product/19c/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ORA19C)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORA19C)))
OK (90 msec)

ping

[oracle@ORA19C admin]$ ping ORA19C
PING ORA19C(ORA19C (fe80::20c:29ff:fe73:a43c%ens160)) 56 data bytes
64 bytes from ORA19C (fe80::20c:29ff:fe73:a43c%ens160): icmp_seq=1 ttl=64 time=0.055 ms
64 bytes from ORA19C (fe80::20c:29ff:fe73:a43c%ens160): icmp_seq=2 ttl=64 time=0.039 ms

sqlplus 접속확인

[oracle@ORA19C admin]$ sqlplus system/oracle@//localhost:1521/ORA19C

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 27 02:16:31 2025
Version 19.26.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

Last Successful login time: Sun Apr 27 2025 00:52:51 +09:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.26.0.0.0
SQL> exit

Developer 접속확인

결론

  • /etc/hosts 파일 그리고 hostname을 꼭 설정하지 않아도 리스너 통신이 가능합니다.
  • hostname을 SID로 할경우 통신이 되는 이유는 일반적으로 리눅스는 자신의 호스트 이름을 로컬 루프백으로 자동 해석하는 경우가 많습니다.
  • tnsnames.ora 를 통하여 클라이언트가 DB에 붙는거고, listener.ora 통하여 DB가 리스너에 자신의 위치를 알려주며, SID 를 /etc/hosts 파일 혹은 hostname 등록하지 않을 경우 SID_LIST_LISTENER 통하여 정적으로 등록을 해줘야 합니다. 그리고 리스너가 중간에서 이어주고 리스너는 12C 기준으로 LREG 가 관리하고 LREG 프로세스가 fork → exec 통하여 리스너는 생성 됩니다 12C이전은 PMON이 리스너를 관리 합니다.

위로 스크롤