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

Question

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

Answer 1

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 = <hostname as /etc/hosts>)(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 = <hostname as /etc/hosts>)(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

Answer 2

Below process is solved my problem.

1. Connect to server using sqlplus / as sysdba command

2. check whether local listener exists or not by using SQL> show parameter local_listener command. For me local_listener value is empty

3. set the listener dynamically by using SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=LOCALHOST)(PORT=1521))' scope=both; command

4. Then do register by using alter command SQL> alter system register;

5. Now check whether listener is added or not SQL> show parameter local_listener. Now listener is added successfully with Value = (ADDRESS=(PROTOCOL=TCP)(HOST=LOCALHOST)(PORT=1521))

6. After adding local listener I am able to connect successfully.

Answer 3

Make sure your host should be 0.0.0.0 in listener.ora and tnsnames.ora.

Also you must register your SID_name in listner.ora as mentioned below. for example if your SID_NAME is ORCLCDB, listener.ora should look like this.

# listener.ora Network Configuration File: /opt/oracle/product/18c/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

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

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCLCDB)
      (ORACLE_HOME = /opt/oracle/product/18c/dbhome_1)
      (SID_NAME = ORCLCDB)
    )
  )

The tnsnames.ora file should look like this.

# tnsnames.ora Network Configuration File: /opt/oracle/product/18c/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCLCDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCLCDB)
    )
  )

LISTENER_ORCLCDB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))

Answer 4

Below worked for me:

• Checked services using services.msc
• Restarted the services OracleOraDB12Home1TNSListener and OracleServiceORCL

After that connected using SQL developer. It worked!

Answer 5

When I got this error I realized that my hostname entry was incorrect in /etc/hosts file.

Before encountering this error I had changed the hostname of the machine from, lets say my-hostname to my-hostname2. Later I changed it back to my-hostname but the in /etc/hosts the entry was still my-hostname2 and that caused the problem while trying to resolve the ip address in listener.ora and tnsnames.ora

Answer 6

datasource: url: jdbc:oracle:thin:@//localhost:1521/ORCLPDB1.localdomain

username: SYS as SYSDBA

password: Oradoc_db1