listener.ora/tnsnames.ora 수정 시 접속이 될까?{2023년05월30일}

lsnrctl status로 항상 리스너의 상태를 확인해줘야 한다.

 

우선 기존 서버에 등록되어 있는 listener.ora의 아이피를 변경해준다면 어떤 문제가 발생될까?

listener.ora - ADDRESS - HOST의 IP를 localhost과 서버에 고정으로 등록된 IP의 경우는 lsnrctl를 실행시

# IP = localhost
[oracle@localhost ~]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 30-MAY-2023 10:39:53

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

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                30-MAY-2023 10:39:36
Uptime                    0 days 0 hr. 0 min. 17 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /ora19/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File         /ora19/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

# IP = 10.31.14.153

[oracle@localhost ~]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 30-MAY-2023 10:46:59

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.31.14.153)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                30-MAY-2023 10:46:25
Uptime                    0 days 0 hr. 0 min. 33 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /ora19/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File         /ora19/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.31.14.153)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

# 참고로 lnsrctl이 start 되어 있는 상황에서 listener.ora의 파일을 수정 후 reload를 하여도 이미 기존에 잡혀있던 IP로 연결되어 있기 때문에 변경된 IP를 적용하고자 한다면, stop 후 start를 진행해줘야지만 ip가 적용 된다.

 

ip를 사용하지 않는 것으로 변경 후 lnsrctl start를 실행하면 host를 찾을 수 없다는 문구와 함께 연결이 안되게 된다.

[oracle@localhost ~]$ lsnrctl start

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 30-MAY-2023 10:35:59

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

Starting /ora19/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /ora19/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Log messages written to /ora19/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.31.14.150)(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
   Linux Error: 99: Cannot assign requested address

Listener failed to start. See the error message(s) above...

tnsnames.ora - ADDRESS - HOST의 IP를 localhost를 변경하면? 딱히 변경을 해도 상관없이 접속에는 무리가 되지 않는다. 이는 tnsnames.ora라는 파일 자체는 정보를 저장하는 공간이기 때문에 접속하는데에 큰 지장은 없다.