3

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

inityk
  • 476
  • 1
  • 9
  • 18
  • Are you using connection type, TNS? Have you tried a direct connection? Simply supply hostname, port, and 'xe' for SID (service ID) in your connection details. – thatjeffsmith Mar 24 '18 at 22:20
  • @thatjeffsmith I've updated my question to align with you. – inityk Mar 25 '18 at 05:31

1 Answers1

0

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
inityk
  • 476
  • 1
  • 9
  • 18
  • 1
    You can use the bequeath connection type in SQLDev if this happens again, then it too can connect to the db without a listener. – thatjeffsmith Mar 25 '18 at 13:16
  • @thatjeffsmith, thanks for information about bequeath! btw I get this error on choosing local/bequeath Connection type from drop-down: `no ocijdbc12 in java.library.path` – inityk Mar 25 '18 at 16:54