OS환경 : Oracle Linux 6.8 (64bit)
DB 환경 : Oracle Database 12.2.0.1
에러 : ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
listener.ora파일과 tnsnames.ora 파일을 작성한 뒤
tnsnames.ora에 있는 tns이름으로 tnsping 시 tnsping은 성공하지만
유저로 붙으면 붙지않음
tnsping 시도
1 2 3 4 5 6 7 8 9 10 11 12 13 | $ tnsping ORCL TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 18-FEB-2019 09:11:23 Copyright (c) 1997, 2016, Oracle. All rights reserved. Used parameter files: /oracle/product/12.2.0/db_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ORCL)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL))) OK (0 msec) |
sqlplus로 접속 시도
1 2 3 4 5 6 7 8 9 | $ sqlplus system/oracle@orcl SQL*Plus: Release 12.2.0.1.0 Production on Mon Feb 18 09:23:29 2019 Copyright (c) 1982, 2016, Oracle. All rights reserved. ERROR: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor |
해결 방법 : listener.ora 파일에서 SID_NAME 및 GLOBAL_DBNAME 부분의 오타를 확인
listener.ora 파일 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | $ cat listener.ora # listener.ora Network Configuration File: /oracle/product/12.2.0/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ORCL)(PORT = 1521)) ) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ORLC) (SID_NAME = ORLC) (ORACLE_HOME = /oracle/product/12.2.0/db_1) ) ) |
GLOBAL_DBNAME 부분과 SID_NAME 부분이 ORLC로 되어있음
실제 서비스 네임 확인
1 2 3 4 5 | SQL> select value from v$parameter where name='service_names'; VALUE ----------- ORCL |
실제 SID는 ORCL
현재 리스너 상태 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | $ lsnrctl status LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 18-FEB-2019 10:22:37 Copyright (c) 1991, 2016, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=ORCL)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production Start Date 18-FEB-2019 10:22:37 Uptime 0 days 0 hr. 0 min. 16 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /oracle/product/12.2.0/db_1/network/admin/listener.ora Listener Log File /oracle/product/12.2.0/db_1/network/log/listener.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ORCL)(PORT=1521))) Services Summary... Service "ORLC" has 1 instance(s). Instance "ORLC", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully |
Service 부분에 ORLC(오타)로 올라와 있는것을 확인 가능함
리스너 정지
1 2 3 4 5 6 7 | $ lsnrctl stop LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 18-FEB-2019 10:23:50 Copyright (c) 1991, 2016, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORCL)(PORT=1521))) The command completed successfully |
listener.ora 파일 수정
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | $ cat listener.ora # listener.ora Network Configuration File: /oracle/product/12.2.0/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ORCL)(PORT = 1521)) ) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ORCL) (SID_NAME = ORCL) (ORACLE_HOME = /oracle/product/12.2.0/db_1) ) ) |
GLOBAL_DBNAME 부분과 SID_NAME 부분을 ORCL로 변경
리스너 기동
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | $ lsnrctl start LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 18-FEB-2019 10:24:00 Copyright (c) 1991, 2016, Oracle. All rights reserved. Starting /oracle/product/12.2.0/db_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 12.2.0.1.0 - Production System parameter file is /oracle/product/12.2.0/db_1/network/admin/listener.ora Log messages written to /oracle/product/12.2.0/db_1/network/log/listener.log Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ORCL)(PORT=1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORCL)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production Start Date 18-FEB-2019 10:24:00 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /oracle/product/12.2.0/db_1/network/admin/listener.ora Listener Log File /oracle/product/12.2.0/db_1/network/log/listener.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ORCL)(PORT=1521))) Services Summary... Service "ORCL" has 1 instance(s). Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully |
Service 부분이 ORCL(정상)로 올라와 있는것을 확인 가능함
tnsping 재시도
1 2 3 4 5 6 7 8 9 10 11 12 13 | $ tnsping ORCL TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 18-FEB-2019 10:26:23 Copyright (c) 1997, 2016, Oracle. All rights reserved. Used parameter files: /oracle/product/12.2.0/db_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ORCL)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL))) OK (0 msec) |
sqlplus로 재접속 시도
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | $ sqlplus system/oracle@orcl SQL*Plus: Release 12.2.0.1.0 Production on Mon Feb 18 10:31:44 2019 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> select instance_name, version, status from v$instance; INSTANCE_NAME VERSION STATUS ---------------- ----------------- ------------ ORCL 12.2.0.1.0 OPEN 1 row selected. |
정상 접속됨
원인 : listener.ora파일 또는 tnsnames.ora파일의 오타
리스너 문제가 발생할 경우 sqplus로 바로 접속 시도하지 말고
그전에 tnsping을 먼저 시도해보고
이후에 sqlplus 접속 시도를 하면 빠르게 원인을 찾을 수 있음
참조 :