Sqldeveloper tools is not connecting

974366

    Dear All,

     

    In the cmd prompt sqlplus is connected successfuly and can able to see the tables, but when i tried to connect the database through sqldeveloper tool throw the error like Listener refused the connection. pls help me.

     

     

    C:\Users\admin>tnsping orcl

    TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 08-MAR-2017 21:27:21

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

    Used TNSNAMES adapter to resolve the alias

    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

    (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))

    OK (0 msec)

     

      • 1. Re: Sqldeveloper tools is not connecting
        Gbenga Ajakaye

        Do you have a tnsnames.ora entry for ORCL?

        • 2. Re: Sqldeveloper tools is not connecting
          thatJeffSmith-Oracle

          how exactly are you connecting in SQL*Plus

           

          your listener is working it appears, it just has no idea what 'orcl' service is.

          • 3. Re: Sqldeveloper tools is not connecting
            974366

            Yes pls check the above ping status in cmd prompt is connecting

             

            C:\Users\admin>sqlplus

             

            SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 8 21:47:21 2017

             

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

             

            Enter user-name: hr

            Enter password:

             

            Connected to:

            Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

            With the Partitioning, OLAP, Data Mining and Real Application Testing options

             

            SQL> select * from dual;

             

            D

            -

            X

             

            SQL>

            • 4. Re: Sqldeveloper tools is not connecting
              974366

              Llistener is working fine only.

               

              C:\Users\admin>lsnrctl status

               

              LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 08-MAR-2017 21:50:00

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

              Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

              STATUS of the LISTENER

              ------------------------

              Alias                     LISTENER

              Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production

              Start Date                08-MAR-2017 07:00:56

              Uptime                    0 days 14 hr. 49 min. 4 sec

              Trace Level               off

              Security                  ON: Local OS Authentication

              SNMP                      OFF

              Listener Parameter File   C:\app\admin\product\11.2.0\dbhome_1\network\admin\listener.ora

              Listener Log File         c:\app\admin\diag\tnslsnr\admin-PC\listener\alert\log.xml

              Listening Endpoints Summary...

                (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))

                (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))

              Services Summary...

              Service "CLRExtProc" has 1 instance(s).

                Instance "CLRExtProc", status UNKNOWN, has 3 handler(s) for this service...

              The command completed successfully

               

              C:\Users\admin>

              • 5. Re: Sqldeveloper tools is not connecting
                Gbenga Ajakaye

                Umm. Can you try the query below and post back the result.

                sqlplus  hr/hr@orcl;

                1 位用户发现它有用
                • 6. Re: Sqldeveloper tools is not connecting
                  974366

                  Yes not able to connect now Thank you for help. pls help me to make it connect ..

                   

                  C:\Users\admin>sqlplus  hr/hr@orcl;

                   

                  SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 8 22:00:11 2017

                   

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

                   

                  ERROR:

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

                   

                   

                  Enter user-name:

                  • 7. Re: Sqldeveloper tools is not connecting
                    974366

                    how to find out current database name like orcl.

                    • 8. Re: Sqldeveloper tools is not connecting
                      Gbenga Ajakaye

                      That what I thought. You need to have the content below in your TNSNAMES.ORA file. Be sure to change HOST = yourhostname to your hostname.

                       

                      ORCL =

                        (DESCRIPTION =

                          (ADDRESS_LIST =

                            (ADDRESS = (PROTOCOL = TCP)(HOST = yourhostname)(PORT = 1521))

                          )

                          (CONNECT_DATA =

                            (SERVER = DEDICATED)

                            (SERVICE_NAME =orcl)

                          )

                        )

                      Once done, then try to reconnect by running

                      sqlplus  hr/hr@orcl;

                       

                      • 9. Re: Sqldeveloper tools is not connecting
                        Gbenga Ajakaye

                        Are you telling me you don't know if you have ORCL and yet you're trying to connect to it? You can run this query.

                        SQL> select username from DBA_USERS where username like 'OR%';

                        • 10. Re: Sqldeveloper tools is not connecting
                          974366

                          The same has been checked, the above is already there.

                           

                          ORCL =

                            (DESCRIPTION =

                              (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

                              (CONNECT_DATA =

                                (SERVER = DEDICATED)

                                (SERVICE_NAME = orcl)

                              )

                            )

                          • 11. Re: Sqldeveloper tools is not connecting
                            EdStevens

                            Gbenga Ajakaye wrote:

                             

                            Do you have a tnsnames.ora entry for ORCL?

                            There you go again . . . .

                             

                            The OP shows that he configured SQL Dev to use the 'basic' connection type.  That doesn't use tnsnames.ora

                             

                            In addition, the reported error message CLEARLY shows that the request made it TO the listener.  It was the listener that rejected the request.  If the were no tnsnames entry (assuming tnsnames was in play, which it is not) the request would not have gotten far enough to get the reported error, and he would have gotten ORA-12154: TNS:could not resolve the connect identifier specified

                            • 12. Re: Sqldeveloper tools is not connecting
                              Gary Graham-Oracle

                              In your initial post, the image shows a SID of xe (radio button not selected) and a Service name of orcl (radio button selected).  Why not check what Windows Task Manager says about which database service is running.  In Task Manager's Services tab do you see any names beginning with "OracleService", perhaps OracleServiceXE?

                               

                              If so, you would change the SQL Devleoper connection details to make xe the Service name and leave the SID blank. (not relevant until listener knows about which DB services are running).

                              • 13. Re: Sqldeveloper tools is not connecting
                                thatJeffSmith-Oracle

                                their listener log doesn't show a service for ORCL, or any other database for that matter, that seems to be the problem from what I'm seeing

                                • 14. Re: Sqldeveloper tools is not connecting
                                  Gary Graham-Oracle

                                  Yes, things are definitely not going well in the OP's environment...

                                   

                                  I figured since sqlplus can connect to hr, Task Manager could at least give a clue as to what Oracle service might be running, then we can go from there on getting it registered with the listener, fixing the tnsnames.ora and whatnot.  

                                  1 2 上一个 下一个