[ORACLE] TNSPING은 되지만 연결 안됨

---

문제 환경

• DB 서버

DBMS : ORACLE 19.25 RAC
OS : OEL 7.9

• Client 서버

OS : Window 11
DBMS: ORACLE 19.0.0 Single

문제 상황

테스트 연결을 위해 TNS를 작성하고 TNSPING을 날려 정상적으로 연결이 되는지 확인하였지만, 실제 연결시 ORA-12514가 발생하며 연결이 되지 않음.

• DB listener

[oracle@oel19c1 oracle]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 02-JUL-2025 10:01:45

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                30-JUN-2025 17:37:08
Uptime                    1 days 16 hr. 24 min. 37 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/grid/product/19c/network/admin/listener.ora
Listener Log File         /u01/app/grid/base/diag/tnslsnr/oel19c1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.50.210)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.50.212)(PORT=1521)))
Services Summary...

....

Service "ORADB" has 1 instance(s).
  Instance "ORADB1", status READY, has 1 handler(s) for this service...
Service "ORADBXDB" has 1 instance(s).
  Instance "ORADB1", status READY, has 1 handler(s) for this service...
The command completed successfully

• Client에 작성한 tnsnames.ora

# tnsnames.ora Network Configuration File: C:\apps\ora19c\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.


LIVE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oel19c1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORADB)
    )
  )


ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

• Client 에서 TNSPING

C:\Users\user>tnsping LIVE

TNS Ping Utility for 64-bit Windows: Version 19.0.0.0.0 - Production on 02-7월 -2025 10:13:50

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

사용된 매개변수 파일:
C:\apps\ora19c\network\admin\sqlnet.ora


별칭 분석을 위해 TNSNAMES 어댑터 사용
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oel19c1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORADB1)))에 접속하려고 시도하는 중
확인(30밀리초)

• Client에서 DB로 TNS를 이용한 연결 시도

C:\Users\user>sqlplus live/live@LIVE

SQL*Plus: Release 19.0.0.0.0 - Production on 수 7월 2 10:14:58 2025
Version 19.3.0.0.0

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

ERROR:
ORA-12514: TNS:리스너가 현재 접속 기술자에 요청된 서비스를 알지 못함

문제 분석

리스너 로그 분석

2025-07-02T10:24:31.809838+09:00
02-JUL-2025 10:24:31 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORADB)(CID=(PROGRAM=C:\apps\ora19c\bin\sqlplus.exe)(HOST=DESKTOP-I9LUES9)(USER=user))) * (ADDRESS=(PROTOCOL=tcp)(HOST=125.132.140.179)(PORT=53725)) * establish * ORADB * 12518
TNS-12518: TNS:listener could not hand off client connection
 TNS-12547: TNS:lost contact
  TNS-12560: TNS:protocol adapter error
   TNS-00517: Lost contact
    Linux Error: 32: Broken pipe

MOS 내용 참고
https://support.oracle.com/knowledge/Oracle%20Database%20Products/1069517_1.html

문제 해결

$ORACLE_HOME/bin 경로의 oracle에 잘못된 권한이 부여되어 있었음

[oracle@oel19c1 bin]$ ls -altr oracle
-rwxr-s--x 1 oracle dba 460666960 Jan 27 00:16 oracle
[oracle@oel19c1 bin]$ chmod u+s oracle
[oracle@oel19c1 bin]$ ls -altr oracle
-rwsr-s--x 1 oracle dba 460666960 Jan 27 00:16 oracle

rwx ==> rws로 변경해야 했음. 이후 연결 잘 되는 것 확인 완료