5 Replies Latest reply: Mar 19, 2014 10:18 AM by clcarter RSS

    New Install - Unable to Connect with SQLPlus on Server

    wmdmurray

      New install of XE on Win 2008 server.  I receive the ORA-12154 "TNS: could not resolve the connect identifier specified" when trying to login through SQLPlus (SQL command line) while logged onto the server as admin.

       

      Here's what I found:

           I am able to connect through the web interface. 

           lsnrctrl status shows xe instance status is ready.

           tnsping resolves the TNSNAMES and report a status of OK.

       

      Any ideas why the SQL command line is unable to connect?

        • 1. Re: New Install - Unable to Connect with SQLPlus on Server
          clcarter

          >> while logged onto the server as admin

           

          The same login that performed the install?

           

          >> able to connect through the web interface.

           

          If the web pages are working, the database and listener are running.

           

          >> tnsping ... OK.

           

          An OK tnsping verifies two of the three pieces of information needed for a connect string, the host and the port. A tnsping does not verify the third piece needed in a connect string, either the instance <sid> or the <service name> also have to be correct and the only way to try that is using a sqlplus (or some other client program) connection via an alias, or from a remote client.

           

          sqlplus /nolog

          conn system@<tnsalias>

          ... password ... Connected.

          select status from v$instance;

          ... OPEN ...

          • 2. Re: New Install - Unable to Connect with SQLPlus on Server
            wmdmurray

            I am using the same (admin) login that was used to install XE.  Thanks for the info about tnsping!

             

            Still having a problem logging in with SQLPlus.  I've tried system@XE (and System@xe) but still have the TNS error.  lsnrctrl status states

               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...

             

            I'm looking at my install log files, but haven't found a different instance or service name.

            • 3. Re: New Install - Unable to Connect with SQLPlus on Server
              clcarter

              The ...Service "xe" is it, that should be the name the database instance has registered with the listener for a service name.

               

              The XEXDB is for Apex web pages, not related to a sqlplus ...connect <user>@<tnsalias> connection.

               

              But on the host where the database is running, should not have to use a tnsalias to connect. Unfortunately, windows has those particulars buried in the registry, and that can make it tough to figure out the details. And if another Oracle product, e.g. client, maybe an SE or EE engine has been installed that makes it more challenging.

               

              Try getting a sysdba connection, see if that gets connected (or not), the "/as sysdba" should be all that is needed if you're in the ORA_DBA group and the registry settings are happy, and the instance is running ...

               

              sqlplus

              ...username: /as sysdba

              ...Connected.

              ... or possibly: Connected to idle instance.

               

              And that tnsping xe should show something like "...attempting to contact (DESCRIPTION=...SERVICE_NAME=XE... or it could show SID=XE if that is what has been set up in the tnsnames.ora file. Or that is what the XE installer sets up "out of the box" auto-magically.

              • 4. Re: New Install - Unable to Connect with SQLPlus on Server
                wmdmurray

                We're getting closer!

                 

                I was able to connect as sysdba and ran the select * from v$instance.  The results were:  instance = xe, status = open, logins = allowed, database status = active, edition = XE

                 

                Considering the above, does the evidence point toward a windows registry value?

                • 5. Re: New Install - Unable to Connect with SQLPlus on Server
                  clcarter

                  ... windows registry value?

                   

                  If a sqlplus connect works when not specifying a tnsalias, probably not.

                   

                  Take a look at the connect descriptor from the tnsping, its on the same line as "attempting to contact ..." and also at one of the earlier lines- when it says "Used TNSNAMES..." then its most likely something needs an update in the tnsnames.ora file. Same folder where tnsping shows "Used parameter files:", in the %ORACLE_HOME%\network\admin folder.

                   

                  A tnsalias in a tnsnames.ora file, e.g. for sqlplus <username>@xe it should be something like this:

                   

                  XE =

                    (DESCRIPTION=

                      (ADDRESS=(PROTOCOL=TCP)(HOST=<hostname>)(PORT=1521))

                      (CONNECT_DATA=

                         (SERVICE_NAME=XE)

                      )

                    )

                   

                  Case does not matter for names. If you'd like to make a tnsalias for testing copy and paste the entire stanza (paragraph) and change the alias name- e.g. change XE = into TESTXE = and use that alias in sqlplus, e.g. connect system@testxe and see what happens. Should be same result. Try going with SID=XE instead of SERVICE_NAME=XE and that might show a different result.

                   

                  And if the network client setup isn't quite happy, maybe there is a second ethernet adapter? Or a wireless NIC on the host?)