This discussion is archived
8 Replies Latest reply: Jan 30, 2013 5:14 PM by sb92075 RSS

Oracle SQL Developer - TNS

user13387916 Newbie
Currently Being Moderated
Hi.

Before installing Oracle 11g, Microsoft Loopback adapter installed. During the installation I did not have any errors, but I can not connect to database XE

tnsnames.ora:

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

ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)


sqlnet.ora:

SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)


listener.ora:

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

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

ADR_BASE_LISTENER = C:\app\ola



In Oracle SQL Developer, I have set:
Connection name: system
Role: default
Connection type: TNS
Network Alias: XE
+ OS Authentication

but I have:
Status: Failure- Test failed. The Network Adapter could not establish the connection.


Please kindly clue how to solve the problem
  • 1. Re: Oracle SQL Developer - TNS
    sb92075 Guru
    Currently Being Moderated
    open Command Window & issue following OS commands

    tnsping XE
    lsntcrl status
    lsnrctl service
    sqlplus scott/tiger@xe

    COPY the results from all the above then PASTE everything back here
  • 2. Re: Oracle SQL Developer - TNS
    jgarry Guru
    Currently Being Moderated
    In addition to showing us what sb asked for, I wonder if you have some confusion between using bequeath and tns and OS authentication. See http://www.thatjeffsmith.com/archive/2012/05/connecting-to-oracle-with-sql-developer-using-bequeath/
  • 3. Re: Oracle SQL Developer - TNS
    user13387916 Newbie
    Currently Being Moderated
    C:\Users\ola>tnsping XE

    TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 30-JAN-2
    013 13:56:49

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

    Used parameter files:
    C:\app\ola\product\11.2.0\dbhome_1\NETWORK\ADMIN\sqlnet.ora


    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.
    0.101)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = xe)))
    TNS-12535: TNS:operation timed out

    C:\Users\ola>
    ---------------------
    Microsoft Windows [Version 6.1.7601]
    Copyright (c) 2009 Microsoft Corporation. All rights reserved.

    C:\Users\ola>lsntcrl status
    'lsntcrl' is not recognized as an internal or external command,
    operable program or batch file.

    C:\Users\ola>lsntcrl service
    'lsntcrl' is not recognized as an internal or external command,
    operable program or batch file.

    C:\Users\ola>sqlplus scott/tiger@xe

    SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 30 14:01:39 2013

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

    ERROR:
    ORA-12170: TNS:Connect timeout occurred

    Enter user-name: sys as sysdba
    Enter password:

    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    ----------------------------------------------
    I added variables (Eviromment Variables ) -> System Variables:

    TNS_ADMIN
    C:\app\ola\product\11.2.0\dbhome_1\NETWORK\ADMIN

    ----------------------------------------------------------
    SHOW PARAMETER os_authent_prefix
    NAME___________TYPE_VALUE
    os_authent_prefix string ops$

    Edited by: user13387916 on 2013-01-30 14:17
  • 4. Re: Oracle SQL Developer - TNS
    sb92075 Guru
    Currently Being Moderated
    the correct commands are as below

    lsnrctl status
    lsnrctl service

    COPY the results then PASTE all back here
  • 5. Re: Oracle SQL Developer - TNS
    user13387916 Newbie
    Currently Being Moderated
    C:\Users\ola>lsnrctl status

    LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 30-JAN-2013 15:08
    :25

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

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
    TNS-12541: TNS:no listener
    TNS-12560: TNS:protocol adapter error
    TNS-00511: No listener
    32-bit Windows Error: 2: No such file or directory
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.101)(PORT=1521
    )))
    TNS-12535: TNS:operation timed out
    TNS-12560: TNS:protocol adapter error
    TNS-00505: Operation timed out
    32-bit Windows Error: 60: Unknown error

    C:\Users\ola>
    ------------------------------
    C:\Users\ola>lsnrctl service

    LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 30-JAN-2013 15:11
    :30

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

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
    TNS-12541: TNS:no listener
    TNS-12560: TNS:protocol adapter error
    TNS-00511: No listener
    32-bit Windows Error: 2: No such file or directory
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.101)(PORT=1521
    )))
    TNS-12535: TNS:operation timed out
    TNS-12560: TNS:protocol adapter error
    TNS-00505: Operation timed out
    32-bit Windows Error: 60: Unknown error
  • 6. Re: Oracle SQL Developer - TNS
    sb92075 Guru
    Currently Being Moderated
    the correct commands are as below

    lsnrctl start
    lsnrctl status

    COPY the results then PASTE all back here
  • 7. Re: Oracle SQL Developer - TNS
    user13387916 Newbie
    Currently Being Moderated
    C:\Users\ola>lsnrctl start

    LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 30-JAN-2013 15:41
    :48

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

    Starting tnslsnr: please wait...

    TNS-12560: TNS:protocol adapter error
    TNS-00530: Protocol adapter error


    C:\Users\ola>
    -------------------------------------
    C:\Users\ola>lsnrctl status

    LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 30-JAN-2013 15:61
    :48

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
    TNS-12541: TNS:no listener
    TNS-12560: TNS:protocol adapter error
    TNS-00511: No listener
    32-bit Windows Error: 2: No such file or directory
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.101)(PORT=1521
    )))
    TNS-12535: TNS:operation timed out
    TNS-12560: TNS:protocol adapter error
    TNS-00505: Operation timed out
    32-bit Windows Error: 60: Unknown error
  • 8. Re: Oracle SQL Developer - TNS
    sb92075 Guru
    Currently Being Moderated
    user13387916 wrote:
    C:\Users\ola>lsnrctl start

    LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 30-JAN-2013 15:41
    :48

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

    Starting tnslsnr: please wait...

    TNS-12560: TNS:protocol adapter error
    TNS-00530: Protocol adapter error
    The OS/Networking is mis-configured
    [oracle@localhost ~]$ oerr tns 530
    00530, 00000, "Protocol adapter error"
    // *Cause: A generic protocol adapter error occurred.
    // *Action: For further details, turn on tracing and reexecute the operation.
    [oracle@localhost ~]$ oerr ora 12560
    12560, 00000, "TNS:protocol adapter error"
    // *Cause: A generic protocol adapter error occurred.
    // *Action: Check addresses used for proper protocol specification. Before
    // reporting this error, look at the error stack and check for lower level
    // transport errors. For further details, turn on tracing and reexecute the
    // operation. Turn off tracing when the operation is complete.
    since the listener can be started when no listener.ora file exists.
    So you can try by moving, removing, renaming or deleting listener.ora before trying to

    lsnrctl start

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points