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.

Oracle Apps - CDB / PDB how to source the environment and connect to CDB and PDB

 In Oracle E-Business suite with Multi tenant database, load the proper environment variables and connect to the database by performing the following steps:

• For the non-CDB database, source the  $ORACLE_HOME/<non-CDB SID>_<HOST>.env/cmd file. Then, run  sqlplus<user>/<password>@<non-CDB SID>

• For the CDB database, source the $ORACLE_HOME/<CDB SID>_<HOST>.env / cmd file. Then, run sqlplus<user>/<password> or connect as SYSDBA.

• For the PDB database on UNIX/Linux platforms, to connect as SYSDBA, source the $ORACLE_HOME/<CDBSID>_<HOST>.env file. Set the ORACLE_PDB_SID environment variable to <PDB SID>. Then, connect as SYSDBA.

• For the PDB database on Windows platforms, to connect as SYSDBA, source the $ORACLE_HOME/<PDBSID>_<HOST>.cmd file. Then, connect as SYSDBA.

• For the PDB database, to connect to other users, source the $ORACLE_HOME/<PDB_SID>_<HOST>.env / cmd file. Then,run sqlplus <user>/<password>@<PDB SID>

.

How to check if connected to Oracle Pluggable Database (PDB)

 To know if you are connected to a Pluggable Database ( PDB) which is part of Container Database(CDB)

Execute below SQL script.

SELECT DECODE (SYS_CONTEXT ('Userenv', 'CDB_NAME')

                                         ,null,'NO','YES'),

                                  Sys_Context('Userenv', 'CON_ID'),

                                  Sys_Context('Userenv', 'CON_NAME')

                          From dual;

If connected to PDB you will get first column value as YES , a non zero number for second column and PDB Name in third column

If connected to a normal database (non CDB/PDB) you will get first column value as NO, zero for second column and Database Name in third column.