2

I am having all kinds of connection issues with sql developer all of a sudden. It was working till yesterday and stopped working on windows 8 machine.

i am still able to connect via command prompt.

I exclusively opened the port 1521 thats been configured for listener.

Also opened all ports for local programs.

added TNS_ADMIN to register keys .

but still no luck

Heres my listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
(GLOBAL_DBNAME=orcl)
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\rani\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\rani\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  )

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

ADR_BASE_LISTENER = C:\app\rani

  • tnsnames.ora content

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

Every things fine from ocmmand prompt - from command prompt:


    Show parameter service_name;

    NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------
service_names                        string      orcl


    C:\Users\rani>lsnrctl services LISTENER

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 11-DEC-2014 23:44
:51

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER

I think i have done everythingn i possibly can but i still not able to conenct my sqlplus developer i keep getting :Network adapter could not establish connection.

sqlnet log details:


Fatal NI connect error 12505, connecting to:
 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=C:\app\rani\product\11.2.0\dbhome_1\bin\emagent.exe)(HOST=PICKLE)(USER=SYSTEM))))

  VERSION INFORMATION:
    TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
    Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 11.2.0.1.0 - Production
  Time: 11-DEC-2014 23:09:10
  Tracing to file: C:\app\rani\product\11.2.0\dbhome_1\NETWORK\trace\sqlnet.trc
  Tns error struct:
    ns main err code: 12564
    TNS-12564: TNS:connection refused
    ns secondary err code: 0
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0

fyi: my computer name is PICKLE which should be same as localhost.


listener details:
    C:\Users\rani>lsnrctl services LISTENER

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 12-DEC-2014 00:14
:15

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully

    C:\Users\rani>lsnrctl status

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 12-DEC-2014 00:14
:39

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Produ
ction
Start Date                12-DEC-2014 00:13:54
Uptime                    0 days 0 hr. 0 min. 45 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\app\rani\product\11.2.0\dbhome_1\NETWORK\ADMIN\list
ener.ora
Listener Log File         c:\app\rani\diag\tnslsnr\pickle\listener\alert\log.xml

Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Any help , i am thinking the sql developer is not ready for windows 8 may be

1

Solved! the issue was LOCAL_LISTENER was not set to orcl so SQL> alter system register;

SQL> alter system set LOCAL_LISTENER='(ADDRESS = (PROTOCOL=TCP)(HOST=localhost)(PORT=1521))' scope=both;

did the trick !

0

I think you should have a service called 'orcl' when you do a

lsnrctl status

But at the moment you don't. Or inversely try to change your tnsname.ora (SERVICE_NAME = orcl) to 'CLRExtProc'.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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