Error:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
tnsping is working
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
tnsping is working
tnsping igt
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 16-AUG-2023 12:46:17
Copyright (c) 1997, 2020, Oracle. All rights reserved.
Used parameter files:
/software/oracle/1910/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.90.98)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = igt)))
OK (0 msec)
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 16-AUG-2023 12:46:17
Copyright (c) 1997, 2020, Oracle. All rights reserved.
Used parameter files:
/software/oracle/1910/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.90.98)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = igt)))
OK (0 msec)
but sqlplus gives error:
oracle@qarec-1-aps01:/software/oracle/1910>% sqlplus a/a@igt
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Aug 16 12:46:33 2023
Version 19.10.2.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
What to check
/software/oracle/122/network/admin/listener.ora
oracle@qarec-1-aps01:/software/oracle/1910>% sqlplus a/a@igt
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Aug 16 12:46:33 2023
Version 19.10.2.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
What to check
/software/oracle/122/network/admin/listener.ora
/software/oracle/122/network/admin/tnsnames.ora
/etc/hosts
/etc/hosts
lsnrctl status
Many things can be defined wrongly...
hostname can be configured to a wrong IP, port 1521 can be closed, and more...
hostname can be configured to a wrong IP, port 1521 can be closed, and more...
telnet qarec-1-aps01 1521
Trying 10.10.90.88...
telnet: connect to address 10.10.90.88: No route to host
lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 16-AUG-2023 12:47:58
Copyright (c) 1991, 2020, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-12543: TNS:destination host unreachable
TNS-12560: TNS:protocol adapter error
TNS-00513: Destination host unreachable
Linux Error: 113: No route to host
In this case
Copyright (c) 1991, 2020, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-12543: TNS:destination host unreachable
TNS-12560: TNS:protocol adapter error
TNS-00513: Destination host unreachable
Linux Error: 113: No route to host
In this case
/etc/hosts had a wrong IP for server qarec-1-aps01
listener.ora was configured with HOST = qarec-1-aps01, but tnsnames.ora with HOST = 10.10.90.98.
tnsping worked, it was using tnsnames.ora with a correct IP
tnsping worked, it was using tnsnames.ora with a correct IP
sqlplus failed, it was using listener.ora with HOST = qarec-1-aps01, which got translated to a wrong IP address in /etc/hosts
No comments:
Post a Comment