2

I am having a problem connecting sql developer to my Oracle 10g database. sqlplus is able to connect fine, but when I try to open the connection in sql developer, I get 'ORA-12514 TNS does not know of service' error. I have tried modifying my tnsnames.ora file. It has the following:

COTT3 = 
 (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
  (CONNECT_DATA = 
   (SERVER = DEDICATED)
   (SERVER_NAME = COTT3)
  )
)

Can anyone assist with any other files I may need to modify or let me know where im going wrong?

Thanks

UPDATE Output of lsnrctl status

 [oracle@VM-COT-DEV1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 02-FEB-2012 13:57:20

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                02-FEB-2012 13:01:25
Uptime                    0 days 0 hr. 55 min. 54 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /opt/oracle/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

enter code here
  • What is the JDBC url you use in SQL Developer? – a_horse_with_no_name Feb 2 '12 at 12:40
  • SYS@//localhost:1521/COTT3 – BON Feb 2 '12 at 14:06
  • That's not a JDBC url (they start with jdbc:) – a_horse_with_no_name Feb 2 '12 at 14:07
3

Try this:

COTT3 = 
 (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
  (CONNECT_DATA = 
   (SERVER = DEDICATED)
   (SERVICE_NAME = COTT3)
  )
)

If that doesn't work, replace "SERVICE_NAME" with "SID".

Put this in $ORACLE_HOME/network/admin/listener.ora:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      )
    )
  )

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = COTT3)
          (ORACLE_HOME = /opt/oracle/oracle/product/10.2.0/db_1 )
    )
  )

Then restart the listener with:

lsnrctl reload

Are you 100% sure that the database has started up?

Does:

ps auxww | grep pmon | grep -i cott3

... produce any output?

0

here is my listener.ora:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = XE)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
    )
  )

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

DEFAULT_SERVICE_LISTENER = (XE)

After adding

    (SID_DESC =
      (SID_NAME = XE)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
    )

to SID_LIST_LISTENER and restart "OracleXETNSListener" windows service, I was able to connect in SQLDeveloper using service_name=XE

  • 1
    This appears to basically be saying, "I followed the advice in the other answer, and it worked." If this is intended to be a separate answer, you should clarify what the difference between your answer and the accepted one is. If this intended as a "Hey, it actually worked" comment, then it should be just that - a comment. As you develop your reputation on this site, you'll gain the ability to comment on others' posts. – RDFozz Sep 21 '17 at 22:03

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.