How to Resolve Oracle TNS Error ORA -12514 and ORA-12521

 When you are trying to connect to your database using TNS alias you may get below errors like

ORA-12514 : TNS:listener does not currently know of service requested in connect descriptor

OR

ORA-12521: TNS:listener does not currently know of instance requested in connect descriptor

For example you are trying to connect to a DB using sqlplus as below

sqlplus user1@ORCL

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 23 05:28:08 2023

Version 19.18.0.0.0

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

Enter password:

ERROR:

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

When you try tnsping your TNS alias 

Used TNSNAMES adapter to resolve the alias

# tnsping ORCL

Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=orclhost1)(PORT=1526)) (CONNECT_DATA= (SERVICE_NAME=ORCL) (INSTANCE_NAME=ORCLCDB1)))

OK (0 msec)

Solution for ORA-12514

It seems the SERVICE_NAME given in the TNS alias is not correct. Please check with your DBA and verify the listener has a service named ORCL. Find out the correct service_name and update your TNS alias in the local tnsnames.ora file.

Solution for ORA-12521

If you are getting ORA-12521: TNS:listener does not currently know of instance requested in

connect descriptor, similarly, try tnsping of the TNS alias. In this case INSTANCE_NAME in the TNS alias description is wrong. Find out the correct instance_name and update your TNS alias in the local tnsnames.ora file.