1

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
  • 1
    Post the output of $lsnrctl status listener. – JSapkota Oct 26 '16 at 5:53
  • 2
    Why do you want to put the hostname and port in the connection string? – Marco Oct 26 '16 at 7:53
  • Does sqlplus system/Forest123@localhost:1521/orcl.localdomain work? Your service name is usually suffixed by your domain. Your tnsnames.ora entry connects to orcl.localdomain. – jmk Jun 17 '17 at 16:57
0

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
0

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))
0

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.

0

Below worked for me:

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

After that connected using SQL developer. It worked!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.