[Oracle] ORA-12514, TNS: listener does not currently know of service requested in connect dscriptor

ORA-12514 Error

SQL Developer 는 눈치를 보면서 쓴다. 분명 잘 되다가도 며칠 지나고 다시 켜보면 안 되네.

오늘은 ORA-12514 Error 가 떴다.

해결 방법을 찾아보니 이렇다.

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Cause: The listener received a request to establish a connection to a database or other service. The connect descriptor received by the listener specified a service name for a service (usually a database service) that either has not yet dynamically registered with the listener or has not been statically configured for the listener. This may be a temporary condition such as after the listener has started, but before the database instance has registered with the listener.
Action:
- Wait a moment and try to connect a second time.
- Check which services are currently known by the listener by executing: lsnrctl services
- Check that the SERVICE_NAME parameter in the connect descriptor of the net service name used specifies a service known by the listener.
- If an easy connect naming connect identifier was used, check that the service name specified is a service known by the listener.
- Check for an event in the listener.log file.

일단 지시하는대로 해본다. 제일 먼저 기다렸다가 연결시도를 수행해본다. 안 된다.

Service 돌아가는지 확인

윈도우에서 작업관리자 > 서비스에서 Oracle 관련 서비스 중 Listener 가 잘 돌아가는지 확인해주자.

실행 중이라고 뜬다. 이게 문제가 아닌 것 같다.

lsnrctl services

명령어를 입력해 확인해보자.

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DESKTOP-AV7344M)(PORT=1521)))에 연결되었습니다
서비스 요약...
"CLRExtProc" 서비스는 1개의 인스턴스를 가집니다.
  "CLRExtProc" 인스턴스(UNKNOWN 상태)는 이 서비스에 대해 1 처리기를 가집니다.
    처리기:
      "DEDICATED" 설정: 0 거부: 0
         LOCAL SERVER
"XE" 서비스는 1개의 인스턴스를 가집니다.
  "xe" 인스턴스(READY 상태)는 이 서비스에 대해 2 처리기를 가집니다.
    처리기:
      "DEDICATED" 설정:1 거부:0 상태:ready
         LOCAL SERVER
      "N000:dedicated" 설정:0 거부:0 상태:ready
         CMON <machine: DESKTOP-AV7344M, pid: 23140_23844>
         (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=50867))
명령이 성공적으로 수행되었습니다

HOST 값이랑 PORT 값이 조금 다른 것 같았다.

CMD 창에 나온 값으로 tnsnames.ora 를 다시 설정하고 tnsping ORCL 을 날려봤다.

아직 안 된다. 그런데 여기서 이상한 점을 하나 찾았다.

XE 서비스는 있는데, XEPDB1 서비스 인스턴스가 없다.

tnsnames.ora 확인

C:\app\User\product\21c\dbhomeXE\network\admin\tnsnames.ora 를 확인하라고 한다. 애초에 tnsnames.ora 파일이 없다. 뭐야.

그래서 일단 하나 만들어줬다.

그리고 CMD에 tnsping ORCL 실행해봤다.

C:\Users\wch18>tnsping ORCL

TNS Ping Utility for 64-bit Windows: Version 21.0.0.0.0 - Production on 14-11월-2021 23:28:52

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

사용된 매개변수 파일:
C:\app\wch18\product\21c\homes\OraDB21Home1\network\admin\sqlnet.ora

TNS-03505: 이름 처리에 실패했습니다

잘 보니까 사용된 매개 변수 파일 위치가 다른 블로그 경로들과 조금 다르다.

아마 버전이 달라서 그런 것 같다.

사용된 매개변수 파일로 들어가보니 tnsnames.ora 와 listner.ora 둘 다 잘 있다.

# tnsnames.ora

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = DESKTOP-AV7344M)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

LISTENER_XE =
  (ADDRESS = (PROTOCOL = TCP)(HOST = DESKTOP-AV7344M)(PORT = 1521))


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

# listener.ora

DEFAULT_SERVICE_LISTENER = XE

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\wch18\product\21c\dbhomeXE)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\wch18\product\21c\dbhomeXE\bin\oraclr.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = DESKTOP-AV7344M)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

서비스 이름이 다른 것 같아 CMD에 lsnrctl status 쳐서 확인해보니 XE와 정확하게 매칭되어 있다.

서비스 요약...
"CLRExtProc" 서비스는 1개의 인스턴스를 가집니다.
  "CLRExtProc" 인스턴스(UNKNOWN 상태)는 이 서비스에 대해 1 처리기를 가집니다.
"XE" 서비스는 1개의 인스턴스를 가집니다.
  "xe" 인스턴스(READY 상태)는 이 서비스에 대해 2 처리기를 가집니다.
명령이 성공적으로 수행되었습니다

XEPDB1 서비스가 없다.

제대로 된 PC 호스트명, IP 를 입력해도 안 되니 이유는 이것 뿐인 것 같다.

SQL> SELECT INSTANCE_NAME, STATUS FROM V$INSTANCE;

INSTANCE_NAME                    STATUS
-------------------------------- ------------------------
xe                               MOUNTED

확인해보니 인스턴스가 없다.

지난 번에 SQL Developer 폴더를 전부 삭제해서 이렇게 된 건가하는 생각이 든다.

그 외 TROUBLE SHOOTING

• Instance 를 생성해보려 했다 : Oracle 데이터베이스가 개방되지 않습니다 (ORA-01157)
• shutdown immediate –> startup –> recovery 시도 : ORA-01157, ORA-01110

  ORA-01516: 현재 컨테이너에 존재하지 않는 로그 파일, 데이터 파일 또는 임시 파일
  "C:\TABLESPACES\PDB1\DATA_FILE.DBF"이(가) 있습니다.
  

어딘가 파일이 손상된 것 같다.

가장 확실한 방법은 Oracle 한 번 더 부쉈다가 다시 설치하는 방법.

문제 생길 때마다 매번 그럴 수 없으니, 정신 가다듬고 내일 다시 도전해야겠다.