8 Replies Latest reply: Jan 30, 2013 7:14 PM by sb92075 RSS

    Oracle SQL Developer - TNS

    user260user
      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
          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
            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
              user260user
              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
                the correct commands are as below

                lsnrctl status
                lsnrctl service

                COPY the results then PASTE all back here
                • 5. Re: Oracle SQL Developer - TNS
                  user260user
                  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
                    the correct commands are as below

                    lsnrctl start
                    lsnrctl status

                    COPY the results then PASTE all back here
                    • 7. Re: Oracle SQL Developer - TNS
                      user260user
                      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
                        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