The application was down, and a quick SQL*Plus connection was supposed to confirm the database status. Instead of a clean sqlplus login prompt, the screen flashed an error:

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

While investigation we found that The database was up. The listener was also up and running. Everything looked fine at first glance — but still the application refused to connect to the Database.

This blog walks through how I diagnosed the issue and resolved it step by step in an Oracle E-Business Suite 12.2 CDB/PDB environment, restoring the missing services and bringing connectivity back to life.

ISSUE:

When trying to connect application using sqlplus it shows:

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

CAUSE:

A quick lsnrctl status revealed the real problem: critical EBS services were missing from the listener.

On checking listener services, it shows that:

Service “7fa5615751261fc6e053fd0113ac890d” has 1 instance(s).
Instance “TESTCDB”, status READY, has 1 handler(s) for this service…
Service “TESTCDB” has 1 instance(s).
Instance “TESTCDB”, status READY, has 1 handler(s) for this service…
Service “TESTCDBXDB” has 1 instance(s).
Instance “TESTCDB”, status READY, has 1 handler(s) for this service…
Service “testpdb” has 1 instance(s).
Instance “TESTCDB”, status READY, has 1 handler(s) for this service…

Here, 2 of the critical services of listener “TESTPDB_ebs_patch” and “ebs_TESTPDB” were missing.

SOLUTION:

Bounce listener services and then bounce db services ( In my case the application was already down if your application is up then before shutting down your db, stop application services )

1. lsnrctl status TESTCDB
   
   Service “7fa5615751261fc6e053fd0113ac890d” has 1 instance(s).
   Instance “TESTCDB”, status READY, has 1 handler(s) for this service…
   Service “TESTCDB” has 1 instance(s).
   Instance “TESTCDB”, status READY, has 1 handler(s) for this service…
   Service “TESTCDBXDB” has 1 instance(s).
   Instance “TESTCDB”, status READY, has 1 handler(s) for this service…
   Service “testpdb” has 1 instance(s).
   Instance “TESTCDB”, status READY, has 1 handler(s) for this service…
   
2. lsnrctl stop TESTCDB
   
3. lsnrctl start TESTCDB ( The services which we are trying to start will not start at this step although check its status )
   
   Services Summary…
   Service “7fa5615751261fc6e053fd0113ac890d” has 1 instance(s).
   Instance “TESTCDB”, status READY, has 1 handler(s) for this service…
   Service “TESTCDB” has 1 instance(s).
   Instance “TESTCDB”, status READY, has 1 handler(s) for this service…
   Service “TESTCDBXDB” has 1 instance(s).
   Instance “TESTCDB”, status READY, has 1 handler(s) for this service…
   Service “testpdb” has 1 instance(s).
   Instance “TESTCDB”, status READY, has 1 handler(s) for this service…
   The command completed successfully
   
4. Bouncing DB:
   
   Connect:
   sqlplus / as sysdba
   shutdown immediate
   startup
   
5. Now, check the listener services:
   
   lsnrctl status TESTCDB

Services Summary…
Service “7fa5615751261fc6e053fd0113ac890d” has 1 instance(s).
Instance “TESTCDB”, status READY, has 1 handler(s) for this service…
Service “TESTCDB” has 1 instance(s).
Instance “TESTCDB”, status READY, has 1 handler(s) for this service…
Service “TESTCDBXDB” has 1 instance(s).
Instance “TESTCDB”, status READY, has 1 handler(s) for this service…
Service “TESTPDB_ebs_patch” has 1 instance(s).
Instance “TESTCDB”, status READY, has 1 handler(s) for this service…
Service “ebs_TESTPDB” has 1 instance(s).
Instance “TESTCDB”, status READY, has 1 handler(s) for this service…
Service “testpdb” has 1 instance(s).
Instance “TESTCDB”, status READY, has 1 handler(s) for this service…
The command completed successfully

NOW, CONNECT THE APPLICATION USING SQLPLUS.