7 Replies Latest reply: Jan 12, 2013 7:01 PM by 984497 RSS

    ORA-12154:  TNS:could not resolve the connect identifer specified

    984497
      My system:

      Windows 7
      64-bit

      Downloaded Oracle Database Express Edition 11g Release 2 for Windows x32 at the following link: http://www.oracle.com/technetwork/products/express-edition/downloads/index.html

      Download went good. Download successful. I follow the Oracle Database Express Edition: Getting Started Guide.

      All is good until I get to the "Creating a Database User" section to connect to SYSTEM user....

      I open the Run SQL Command Line and the following is exactly what I see:

      SQL> connect
      Enter user-name: system
      Enter password: <type my password here>
      ERROR:
      ORA-12154: TNS:could not resolve the connect identifer specified


      I've looked for the location of my tnsnames.ora, listener, and sqlnet files and they are located in C:\OracleExpressEdition\app\oracle\product\11.2.0\server\network\ADMIN

      Furthermore, tnsnames reads (I've changed my PC name to hostname for this example, but it actually reads my PC name correctly):

      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 = EXTPROC1))
      )
      (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
      )
      )

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

      I've even tried to set the path in the system environment variables sections in control panel.

      Any suggestions....???
        • 1. Re: ORA-12154:  TNS:could not resolve the connect identifer specified
          sb92075
          981494 wrote:
          My system:

          Windows 7
          64-bit

          Downloaded Oracle Database Express Edition 11g Release 2 for Windows x32 at the following link: http://www.oracle.com/technetwork/products/express-edition/downloads/index.html

          Download went good. Download successful. I follow the Oracle Database Express Edition: Getting Started Guide.

          All is good until I get to the "Creating a Database User" section to connect to SYSTEM user....

          I open the Run SQL Command Line and the following is exactly what I see:

          SQL> connect
          Enter user-name: system
          Enter password: <type my password here>
          ERROR:
          ORA-12154: TNS:could not resolve the connect identifer specified

          Error above is a "SQL*Net" related error BUT what you did, did not involve SQL*Net.
          This means the root cause & fix are external to Oracle & exist at the OS level.

          Are both OS Services for the Listener & DB started & running?
          Open Command Window & issue the following OS commands

          lsnrctl status
          lsnrctl service

          COPY the results from above then PASTE all back here.

          >
          Furthermore, tnsnames reads (I've changed my PC name to hostname for this example, but it actually reads my PC name correctly):
          obfuscating your hostname is useless, silly, & counterproductive since hostname could be part of the problem
          • 2. Re: ORA-12154:  TNS:could not resolve the connect identifer specified
            984497
            Thank you so much for your time.

            Here is the complete tnsnames.ora without "obfuscating" hostname:


            XE =
            (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = Samantha-PC)(PORT = 1521))
            (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = XE)
            )
            )

            EXTPROC_CONNECTION_DATA =
            (DESCRIPTION =
            (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
            )
            (CONNECT_DATA =
            (SID = PLSExtProc)
            (PRESENTATION = RO)
            )
            )

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




            Microsoft Windows [Version 6.1.7600]
            Copyright (c) 2009 Microsoft Corporation. All rights reserved.

            C:\Users\Samantha>lsnrctl status

            LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 12-JAN-2013 11:19
            :58

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

            Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
            STATUS of the LISTENER
            ------------------------
            Alias LISTENER
            Version TNSLSNR for 32-bit Windows: Version 11.2.0.2.0 - Produ
            ction
            Start Date 12-JAN-2013 08:33:56
            Uptime 0 days 2 hr. 46 min. 2 sec
            Trace Level off
            Security ON: Local OS Authentication
            SNMP OFF
            Default Service XE
            Listener Parameter File C:\OracleExpressEdition\app\oracle\product\11.2.0\serv
            er\network\admin\listener.ora
            Listener Log File C:\OracleExpressEdition\app\oracle\diag\tnslsnr\Samant
            ha-PC\listener\alert\log.xml
            Listening Endpoints Summary...
            (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
            (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Samantha-PC)(PORT=1521)))
            (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Samantha-PC)(PORT=8080))(Presentatio
            n=HTTP)(Session=RAW))
            Services Summary...
            Service "CLRExtProc" has 1 instance(s).
            Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
            Service "PLSExtProc" has 1 instance(s).
            Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
            Service "XEXDB" has 1 instance(s).
            Instance "xe", status READY, has 1 handler(s) for this service...
            Service "xe" has 1 instance(s).
            Instance "xe", status READY, has 1 handler(s) for this service...
            The command completed successfully




            Microsoft Windows [Version 6.1.7600]
            Copyright (c) 2009 Microsoft Corporation. All rights reserved.

            C:\Users\Samantha>lsnrctl service

            LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 12-JAN-2013 11:27
            :49

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

            Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
            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
            Service "PLSExtProc" has 1 instance(s).
            Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
            Handler(s):
            "DEDICATED" established:0 refused:0
            LOCAL SERVER
            Service "XEXDB" has 1 instance(s).
            Instance "xe", status READY, has 1 handler(s) for this service...
            Handler(s):
            "D000" established:19 refused:0 current:0 max:1022 state:ready
            DISPATCHER <machine: SAMANTHA-PC, pid: 2892>
            (ADDRESS=(PROTOCOL=tcp)(HOST=Samantha-PC)(PORT=53738))
            Service "xe" has 1 instance(s).
            Instance "xe", status READY, has 1 handler(s) for this service...
            Handler(s):
            "DEDICATED" established:0 refused:0 state:ready
            LOCAL SERVER
            The command completed successfully

            C:\Users\Samantha>

            Edited by: 981494 on Jan 12, 2013 11:32 AM
            • 3. Re: ORA-12154:  TNS:could not resolve the connect identifer specified
              sb92075
              posted results show that both DB & Listener are up & online.

              you need to use OS File Search facility to locate file called "hosts" & then post the content of it here.

              open Command Window & issue the following OS commands

              SET
              HOSTNAME
              PING Samantha-PC

              COPY the results from above & PASTE all back here
              • 4. Re: ORA-12154:  TNS:could not resolve the connect identifer specified
                984497
                Microsoft Windows [Version 6.1.7600]
                Copyright (c) 2009 Microsoft Corporation. All rights reserved.

                C:\Users\Samantha>SET
                ALLUSERSPROFILE=C:\ProgramData
                APPDATA=C:\Users\Samantha\AppData\Roaming
                CommonProgramFiles=C:\Program Files\Common Files
                CommonProgramFiles(x86)=C:\Program Files (x86)\Common Files
                CommonProgramW6432=C:\Program Files\Common Files
                COMPUTERNAME=SAMANTHA-PC
                ComSpec=C:\Windows\system32\cmd.exe
                FP_NO_HOST_CHECK=NO
                HOMEDRIVE=C:
                HOMEPATH=\Users\Samantha
                LOCALAPPDATA=C:\Users\Samantha\AppData\Local
                LOGONSERVER=\\SAMANTHA-PC
                NUMBER_OF_PROCESSORS=2
                OS=Windows_NT
                Path=C:\OracleExpressEdition\app\oracle\product\11.2.0\server\bin;;C:\Windows\sy
                stem32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell
                \v1.0\;TNS_ADMIN/tnsnames.ora
                PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH;.MSC
                PROCESSOR_ARCHITECTURE=AMD64
                PROCESSOR_IDENTIFIER=Intel64 Family 6 Model 23 Stepping 10, GenuineIntel
                PROCESSOR_LEVEL=6
                PROCESSOR_REVISION=170a
                ProgramData=C:\ProgramData
                ProgramFiles=C:\Program Files
                ProgramFiles(x86)=C:\Program Files (x86)
                ProgramW6432=C:\Program Files
                PROMPT=$P$G
                PSModulePath=C:\Windows\system32\WindowsPowerShell\v1.0\Modules\
                PUBLIC=C:\Users\Public
                SESSIONNAME=Console
                SystemDrive=C:
                SystemRoot=C:\Windows
                TEMP=C:\Users\Samantha\AppData\Local\Temp
                TMP=C:\Users\Samantha\AppData\Local\Temp
                USERDOMAIN=Samantha-PC
                USERNAME=Samantha
                USERPROFILE=C:\Users\Samantha
                windir=C:\Windows

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

                C:\Users\Samantha>PING Samantha-PC

                Pinging Samantha-PC [fe80::ad2b:9138:48a7:ef4e%12] with 32 bytes of data:
                Reply from fe80::ad2b:9138:48a7:ef4e%12: time<1ms
                Reply from fe80::ad2b:9138:48a7:ef4e%12: time<1ms
                Reply from fe80::ad2b:9138:48a7:ef4e%12: time<1ms
                Reply from fe80::ad2b:9138:48a7:ef4e%12: time<1ms

                Ping statistics for fe80::ad2b:9138:48a7:ef4e%12:
                Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
                Approximate round trip times in milli-seconds:
                Minimum = 0ms, Maximum = 0ms, Average = 0ms

                C:\Users\Samantha>
                • 5. Re: ORA-12154:  TNS:could not resolve the connect identifer specified
                  984497
                  any other suggestions out there? Wondering if it would be helpful to uninstall SQL Developer and reinstall.

                  Edited by: 981494 on Jan 12, 2013 2:07 PM
                  • 6. Re: ORA-12154:  TNS:could not resolve the connect identifer specified
                    orafad
                    981494 wrote:

                    SQL> connect
                    Enter user-name: system
                    Enter password: <type my password here>
                    ERROR:
                    ORA-12154: TNS:could not resolve the connect identifer specified
                    Does that password contain the @ character?

                    Change password or enclose the password in double quotes.
                    • 7. Re: ORA-12154:  TNS:could not resolve the connect identifer specified
                      984497
                      This was the answer to my three day journey! I changed my password removing the @ character from it. Thank you so much.....