9 Replies Latest reply: May 27, 2014 3:05 PM by user13168582 RSS

    XE 11G Fresh install SQL Plus ORA-12154 : TNS : Could not resolve the connect identifier

    user13168582

      Hi,

       

      Just done a fresh install on a "vrigin" Windows 2008 (32 bit) server of 11G XE.  All appears to work fine can access via the web front end, connect to Apex etc.

      I want to upgrade the Apex to latest version so need to run some scripts from SQLPlus .. can't connect, get the error message in the title.

       

      Have done exactly the same install and procedure on Windows 7 laptop and Windows 2008 (64 bit server) with no issue ... any ideas why this install would be different ??

        • 1. Re: XE 11G Fresh install SQL Plus ORA-12154 : TNS : Could not resolve the connect identifier
          Paul M.
          need to run some scripts from SQLPlus .. can't connect, get the error message in the title.

          Please post the exact syntax you used to connect.

          Have done exactly the same install and procedure on Windows 7 laptop and Windows 2008 (64 bit server) with no issue

          You did install XE 11g on Windows 64 bit with no problems ? Strange, since Oracle says it does not work : Oracle Database Express Edition 11g Release 2 Downloads

          • 2. Re: XE 11G Fresh install SQL Plus ORA-12154 : TNS : Could not resolve the connect identifier
            user13168582

            Run SQLPLUS.

            SQL> connect sys as sysdba

            SQL> enter password (so I entered password).

             

            Yes it installs on  Windows 64 bit, you need to rename one file mid install and then the install works fine and it runs with no issues, presumably using the WOW64 engine that is in 64 bit Windows.

            • 3. Re: XE 11G Fresh install SQL Plus ORA-12154 : TNS : Could not resolve the connect identifier
              Paul M.

              Run SQLPLUS.

              SQL> connect sys as sysdba

              SQL> enter password (so I entered password).

              Check if LOCAL environment variable is set (with a wrong value). See this example :

              C:\>sqlplus sys as sysdba

               

              SQL*Plus: Release 11.2.0.2.0 Production on Mer Mag 21 16:40:34 2014

               

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

               

              Enter password:

               

              Connected to:

              Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

               

              SQL> exit

              Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

               

              C:\>set LOCAL=xe

               

              C:\>sqlplus sys as sysdba

               

              SQL*Plus: Release 11.2.0.2.0 Production on Mer Mag 21 16:40:52 2014

               

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

               

              Enter password:

               

              Connected to:

              Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

               

              SQL> exit

              Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

               

              C:\>set LOCAL=foo

               

              C:\>sqlplus sys as sysdba

               

              SQL*Plus: Release 11.2.0.2.0 Production on Mer Mag 21 16:41:07 2014

               

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

               

              Enter password:

              ERROR:

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

               

               

              Enter user-name:

              • 4. Re: XE 11G Fresh install SQL Plus ORA-12154 : TNS : Could not resolve the connect identifier
                user13168582

                LOCAL wasn't set ... setting it to xe made no difference - same error.

                I've never set LOCAL on any other installation before :-(

                • 5. Re: XE 11G Fresh install SQL Plus ORA-12154 : TNS : Could not resolve the connect identifier
                  user468360

                  Try including your service name in your connect string:

                       connect sys@XE as sysdba

                  If that doesn't work, issue the following from a command prompt and verify all of the parameters are correct or post the output if you need help:

                       tnsping XE

                  • 6. Re: XE 11G Fresh install SQL Plus ORA-12154 : TNS : Could not resolve the connect identifier
                    Paul M.

                    Try including your service name in your connect string:

                         connect sys@XE as sysdba

                    Isn't that the same as

                    C:\>set LOCAL=XE

                     

                    SQL> connect sys as sysdba

                    ?

                    • 7. Re: XE 11G Fresh install SQL Plus ORA-12154 : TNS : Could not resolve the connect identifier
                      user13168582

                      Exactly the same .. TNSPING comes back with exactly what I would expect it to for parameters and connects OK with a 20 msec turn around ..

                      • 8. Re: XE 11G Fresh install SQL Plus ORA-12154 : TNS : Could not resolve the connect identifier
                        Paul M.

                        Exactly the same .. TNSPING comes back with exactly what I would expect it to for parameters and connects OK with a 20 msec turn around ..

                        And you get ORA-12154 error when connecting without a connection string ? If the error is not misleading (which I don't think), then the only way I know to get that error is connecting "over the network", and if you don't use a connection string, the only way to do that is using LOCAL environment variable.

                         

                        Where did you look for it, registry, environment variables, where ?

                         

                        Do you get the same error if you try the Easy Connect Naming method ? for example

                        C:\>sqlplus system/manager@localhost/xe

                        Configuring Naming Methods

                         

                        This is what the error means :

                        $ oerr ora 12154

                        12154, 00000, "TNS:could not resolve the connect identifier specified"

                        // *Cause:  A connection to a database or other service was requested using

                        // a connect identifier, and the connect identifier specified could not

                        // be resolved into a connect descriptor using one of the naming methods

                        // configured. For example, if the type of connect identifier used was a

                        // net service name then the net service name could not be found in a

                        // naming method repository, or the repository could not be

                        // located or reached.

                        // *Action:

                        //   - If you are using local naming (TNSNAMES.ORA file):

                        //      - Make sure that "TNSNAMES" is listed as one of the values of the

                        //        NAMES.DIRECTORY_PATH parameter in the Oracle Net profile

                        //        (SQLNET.ORA)

                        //      - Verify that a TNSNAMES.ORA file exists and is in the proper

                        //        directory and is accessible.

                        //      - Check that the net service name used as the connect identifier

                        //        exists in the TNSNAMES.ORA file.

                        //      - Make sure there are no syntax errors anywhere in the TNSNAMES.ORA

                        //        file.  Look for unmatched parentheses or stray characters. Errors

                        //        in a TNSNAMES.ORA file may make it unusable.

                        //   - If you are using directory naming:

                        //      - Verify that "LDAP" is listed as one of the values of the

                        //        NAMES.DIRETORY_PATH parameter in the Oracle Net profile

                        //        (SQLNET.ORA).

                        //      - Verify that the LDAP directory server is up and that it is

                        //        accessible.

                        //      - Verify that the net service name or database name used as the

                        //        connect identifier is configured in the directory.

                        //      - Verify that the default context being used is correct by

                        //        specifying a fully qualified net service name or a full LDAP DN

                        //        as the connect identifier

                        //   - If you are using easy connect naming:

                        //      - Verify that "EZCONNECT" is listed as one of the values of the

                        //        NAMES.DIRETORY_PATH parameter in the Oracle Net profile

                        //        (SQLNET.ORA).

                        //      - Make sure the host, port and service name specified

                        //        are correct.

                        //      - Try enclosing the connect identifier in quote marks.

                        //

                        //   See the Oracle Net Services Administrators Guide or the Oracle

                        //   operating system specific guide for more information on naming.

                        $

                         

                        Which edition is your OS ?

                        • 9. Re: XE 11G Fresh install SQL Plus ORA-12154 : TNS : Could not resolve the connect identifier
                          user13168582

                          OK resolved this ... Windows issue but I don't know what.  Automatic updates were disables so decided to let the server patch itself up to date while looking into this ... one of the patches installed seemed to fix the issue, which one I do not know though.