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

I have configured oracle 12c on Rhel 7. When I try to connect database using following command it gets connected with no issues:

sqlplus system/Forest123@orcl

I'm facing problem when I try to put the connection string with hostname and port as shown below:

sqlplus system/Forest123@localhost:1521/orcl

Error occurred is


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

I have gone through the questions asked before, but none of them helped me solve this problem.

Following are my tnsnames.ora, sqlnet.ora and listener.ora files.

tnsnames.ora

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle_12C.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.localdomain)
    )
  )

listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle_12C.localdomain)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

sqlnet.ora

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

hosts file

127.0.0.1 localhost
127.0.0.1 localhost.localdomain
127.0.0.1 localhost4
127.0.0.1 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
::127.0.0.1 localhost.localdomain localhost
172.31.15.86 oracle_12C.localdomain oracle_12C

Since you have the following error message that mean either hostname is not correct and/or the domain you are using.

How to know if everything is working fine or not?
You need to execute hostname command, lets say the output is oracle_12C, and execute cat /etc/resolve.con lets say the domain name mydomain.com, then you need to edit /etc/hosts like the following

127.0.0.1 localhost
127.0.0.1 localhost.localdomain
127.0.0.1 localhost4
127.0.0.1 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
::127.0.0.1 localhost.localdomain localhost
172.31.15.86 oracle_12C.mydomin.com oracle_12C

Note if you are not using domain then /etc/hosts/ should be like the following

127.0.0.1 localhost
127.0.0.1 localhost.localdomain
127.0.0.1 localhost4
127.0.0.1 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
::127.0.0.1 localhost.localdomain localhost
172.31.15.86 oracle_12C

Now lets move to tnsnames.ora, inside this file you need to search for your database section (in your question orcl) replace it like the following (note you need to take a backup from this file before doing any changes):

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
      )

Note for host if you have doming then HOST = oracle_12C.mydomain.com, else it should be HOST = oracle_12C

Now save the file and close it.

After that lets move to listener.ora, take a backup from it and edit it like the following:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

Now restart your listener by executing lsnrctl stop and lsnrctl start then try to connect to the database by using the following commands

export ORACLE_SID=orcl
sqlplus system/Forest123

StackExchange Database Administrators Q#153335, answer score: 2