In this post , we share one of the possible reasons and solution for oracle error ORA-12514 when trying to connect to database
This error is usually observed when trying to connect to a oracle instance service identifier which is not defined in the listener.ora configuration file of the server instance
After installing oracle , the default listener.ora configuration will have contents default as below. In our example ORACLE_HOME is /usr/oracle/product/19.2.0/db_1
$ cat /usr/oracle/product/19.2.0/db_1/network/admin/listener.ora
# listener.ora Network Configuration File: /opt/oracle/product/19.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = shareolite.db.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
While installing oracle , if a Service name/ ID is defined then same has to be registered in listener.ora file also . For example if SID is defined as SHAREDB , then the listener.ora should have the below entry in listener.ora
SHAREDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = shareolite.db.com)(PORT = 1521))
)
SID_LIST_SHAREDB =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /usr/oracle/product/19.2.0/db_1)
(SID_NAME = SHAREDBD)
)
)
After making above entry , restart the listener instance
$ lsnrctl stop SHAREDB
$ lsnrctl start SHAREDB
Verify the status of listener instance
$ lsnrctl status
In the output look for below lines , which indicates listener module is up and waiting for connections
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias SHAREDB
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /usr/oracle/product/19.2.0/db_1/network/admin/listener.ora
Listener Log File /usr/oracle/diag/tnslsnr/shareolite/SHAREDB/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=shareolite.db.com)(PORT=1521)))
Services Summary...
Service "SHAREDB" has 1 instance(s).
Instance "SHAREDB", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
Note the highlighted portion which indicates listener successful registration.
Now , edit the tnsnames.ora DB name entry to point to the listener with a sample configuration as below
$ cat /usr/oracle/product/19.2.0/db_1/network/admin/tnsnames.ora
DB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = shareolite.db.com )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SHAREDB)
)
)
Note the highlighted portion where the service name is defined.
After making above changes, now on connecting to database with the DB name in the connect string , connection should be successful and ORA 12514 error should not be observed.
Hope above solution may help for few facing this issue.