[SOLVED] Unable to connect to Database using SQL Developer (not picking up from tnsnames.ora)

Issue

I have installed Oracle 11g & SQL Developer on my Linux Mint OS.

After setting ORACLE_PATH and relevant variable in .bashrc file, I am able to connect using sqlplus.

Snapshot of my tnsnames.ora

  XE =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
          (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = XE)
          )
        )

 EXTPROC_CONNECTION_DATA =
            (DESCRIPTION =
              (ADDRESS_LIST =
                (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
              )
              (CONNECT_DATA =
                (SID = PLSExtProc)
                (PRESENTATION = RO)
              )   
    )

had tried with (HOST = localhost) too.

I've my localhost and IP address is mentioned in /etc/hosts file.

I've configured TNSnames directory to sql developer following below command:

tools->Preferences->Database->advanced->Tnsnames Directory (where tnsnames.ora is stored)

Set the ORACLE_HOME as :/u01/app/oracle/product/11.2.0/xe/

(Not able to connect to SQL Developer even with SYS/SYSDBA credentials)

Error I receive:

listener refused the connection ora-12514 listener does not currently know of service requested

I've tried almost every single solution by following posts of:

• Google
• StackOverFlow
• Blogs
• Other solutions

But couldn't solve my issue.

Please don't mark this question as duplicate. I'm badly stuck at this.

EDIT: I'm trying to connect using :

1) Connection Type: Basic ; Role: Default

Hostname: Localhost; Port: 1521; SERVICE_NAME: xe

Error:

listener refused the connection ora-12514 listener does not currently know of service requested

2) Connection Type: TNS ; Role: Default

Specifying Connect Identifier throws:

no ocijdbc12 in java.library.path

Specifying Network Alias 'XE' (choosing from drop-down menu) throws:

The network Adapter could not establish the connection

Yet I'm able to access using SYS credentials & custom user credentials from terminal- sqlplus

Solution

Finally! I am able to connect to database using SQL Developer.

(I had verified that port 1521 is opened and was able to ping the localhost with port 1521 and It was not blocked by Firewall )

Reason I was unable to connect was No TNS listener (This I got while trying connect from SQL Command Line)

One thing I understand that running SQL*Plus from terminal does not require TNS Listener to connect.

In order to resolve, I had to replace HOST in Listener.ORA file.

From

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

To

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

And then I restart the listener,

lsnrctl stop

lsnrctl start

Answered By - inityk

Answer Checked By - Clifford M. (WPSolving Volunteer)