1 2 Previous Next 22 Replies Latest reply: Sep 26, 2010 2:45 PM by 800703 RSS

    Oracle/ SQL Developer connection problem.

    751980
      I have installed Oracle 11 g and SQL Developer, When I open SQL Developer and try to make a new connection ( I name the connection, put a user name in, put my password in), I click on "Test" and I get this error:

      Status: Failure - Test failed: Listener refused the connection with the followong error:ORA-12505, TNS: listener does not currently know of SID given in connect descriptor The connection descroptor used by the client was:localhost:1521:ex

      Can anyone explain this to me and help me resolve this issue I am having?
      Thank you in advance for your help
      -steve
      p.s. I have also tried the user name: Scott and Password: Tiger
        • 1. Re: Oracle/ SQL Developer connection problem.
          sb92075
          Has database been opened & is online?
          12505, 00000, "TNS:listener does not currently know of SID given in connect descriptor"
          // *Cause:  The listener received a request to establish a connection to a
          // database or other service. The connect descriptor received by the listener
          // specified a SID for an instance (usually a database instance) that either
          // has not yet dynamically registered with the listener or has not been
          // statically configured for the listener. This may be a temporary condition
          // such as after the listener has started, but before the database instance
          // has registered with the listener.
          // *Action: 
          //  - Wait a moment and try to connect a second time.
          //  - Check which instances are currently known by the listener by executing:
          //    lsnrctl services <listener name>
          //  - Check that the SID parameter in the connect descriptor specifies
          //    an instance known by the listener.
          //  - Check for an event in the listener.log file.
          post content of listener.ora & tnsnames.ora

          post results of following commands

          lsnrctl status
          lsnrctl service

          Post Operating System (OS) name & version for DB server system.
          Post results of
          SELECT * from v$version

          Edited by: sb92075 on Feb 6, 2010 5:50 PM
          • 2. Re: Oracle/ SQL Developer connection problem.
            662100
            Hi,

            {The connection descroptor used by the client was: localhost:1521:ex}

            Replace localhost with the servername where the database is located, ex with the db SID.

            Ioan

            Edited by: ioan on 6.2.2010 17:50
            • 3. Re: Oracle/ SQL Developer connection problem.
              751980
              Thanks for the response, The database is not online. I'm a student who has Oracle 11g installed on my laptop and needs to make a connection through SQL Developer to complete my assignments. Do you have any other suggestions to help me resolve this issue?
              Reguards
              -steve
              • 4. Re: Oracle/ SQL Developer connection problem.
                sb92075
                which OS user owns the Oracle software tree?

                post results from following command

                SET
                • 5. Re: Oracle/ SQL Developer connection problem.
                  751980
                  ok, thank you for the insight. I am new to this and just realized I need to use the "server name", not the "SID".
                  When I use the user name "scott" and the password "tiger" I get the responce that the account is locked.
                  I have tried all on my user names and passwords and nothing works.
                  If anyone has any more suggestions that can help me it would be great.
                  Thank you for your help this far.
                  Reguards
                  -steve
                  • 6. Re: Oracle/ SQL Developer connection problem.
                    sb92075
                    open command window

                    CUT line below & PASTE into command window

                    set
                    sqlplus
                    / as sysdba
                    select sysdate from dual;
                    exit


                    CUT commands & results then PASTE back here
                    • 7. Re: Oracle/ SQL Developer connection problem.
                      751980
                      Here are the results:

                      Enviroment variable sqlplus/ as sysdba select sysdate from dual; exit not defined

                      Does this help?
                      -steve
                      • 8. Re: Oracle/ SQL Developer connection problem.
                        662100
                        Hi,

                        You need to set up your env. Connect to sqlplus as sysdba, and unlock user scott


                        cmd

                        set ORACLE_SID=<SID of your db>
                        set ORACLE_HOME= <directory where you installed oracle softare>
                        set PATH=%ORACLE_HOME%\bin;%PATH%

                        sqlplus /as sysdba

                        alter user scott account unlock;

                        exit;
                        • 9. Re: Oracle/ SQL Developer connection problem.
                          Hans Forbrich
                          user8865949 wrote:
                          Here are the results:

                          Enviroment variable sqlplus/ as sysdba select sysdate from dual; exit not defined

                          Does this help?
                          -steve
                          Not really. It will help once you tell us the operating system. (Some of the responses assume you are using Linux.)
                          • 10. Re: Oracle/ SQL Developer connection problem.
                            751980
                            Sorry about the lack of information I am giving, I am still learning
                            I am using Windows 7
                            -steve
                            • 11. Re: Oracle/ SQL Developer connection problem.
                              Hans Forbrich
                              user8865949 wrote:
                              Sorry about the lack of information I am giving, I am still learning
                              I am using Windows 7
                              -steve
                              Closer. I don't know whether you noticed there are several different colored boxes for Windows 7 ... Windows 7 is NOT an operating system. It is a family of operating systems. TO get the best help, actually include the edition information - even if it does not make any difference in the end.

                              Where is the database actually installed?

                              Are all the firewalls set to allow Oracle traffic.
                              • 12. Re: Oracle/ SQL Developer connection problem.
                                751980
                                Thank you for your patients,
                                I have Windows 7 Home Premium
                                64-bit Operating System
                                I installed Oracle 11g onto my computer, it's on my C drive .

                                -steve
                                • 13. Re: Oracle/ SQL Developer connection problem.
                                  Hans Forbrich
                                  Windows 7 Home ... will have challenges at some point, as Oracle may not be completely compatible with Windows 7.

                                  Aside from that, you may find firewall issues even if you are on the same machine, since the default connection from SQLDeveloper is to use the network (even loopback).

                                  First step is to verify that the database and listener are running and that the database is registered to the listener. TO do that, drop into command line and issue:

                                  1) Listener
                                  lsnrctl status
                                  which will give one of two results, if properly installed. First - listener is down
                                  C:\temp>lsnrctl status
                                  
                                  LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 07-FEB-2010 20:48
                                  :13
                                  
                                  Copyright (c) 1991, 2005, Oracle.  All rights reserved.
                                  
                                  Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
                                  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=FCC003L)(PORT=1521)))
                                  TNS-12541: TNS:no listener
                                   TNS-12560: TNS:protocol adapter error
                                    TNS-00511: No listener
                                     32-bit Windows Error: 61: Unknown error
                                  If the above is displayed, check the Service applet (Control Panel -> Admin Tools > Service) to ensure serivce is up, or enter "lsnrctl start", in which case the status shoud show
                                  C:\temp>lsnrctl status
                                  
                                  LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 07-FEB-2010 20:46
                                  :40
                                  
                                  Copyright (c) 1991, 2005, Oracle.  All rights reserved.
                                  
                                  Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
                                  STATUS of the LISTENER
                                  ------------------------
                                  Alias                     LISTENER
                                  Version                   TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Produ
                                  ction
                                  Start Date                05-FEB-2010 14:42:10
                                  Uptime                    2 days 6 hr. 4 min. 30 sec
                                  Trace Level               off
                                  Security                  ON: Local OS Authentication
                                  SNMP                      OFF
                                  Default Service           XE
                                  Listener Parameter File   C:\oracle\app\oracle\product\10.2.0\server\network\adm
                                  in\listener.ora
                                  Listener Log File         C:\oracle\app\oracle\product\10.2.0\server\network\log
                                  \listener.log
                                  Listening Endpoints Summary...
                                    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC_FOR_XEipc)))
                                    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=FCC003L)(PORT=1521)))
                                  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...
                                  The command completed successfully
                                  
                                  C:\temp>
                                  2) After the service is verified up, make sure the database is up
                                  sqlplus / as sysdba
                                  which should respond with something like
                                  C:\temp>sqlplus / as sysdba
                                  
                                  SQL*Plus: Release 10.2.0.1.0 - Production on Sun Feb 7 20:43:12 2010
                                  
                                  Copyright (c) 1982, 2005, Oracle.  All rights reserved.
                                  
                                  
                                  Connected to:
                                  Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
                                  
                                  SQL>
                                  if it gives something like
                                  C:\temp>sqlplus / as sysdba
                                  
                                  SQL*Plus: Release 10.2.0.1.0 - Production on Sun Feb 7 20:44:49 2010
                                  
                                  Copyright (c) 1982, 2005, Oracle.  All rights reserved.
                                  
                                  ERROR:
                                  ORA-12560: TNS:protocol adapter error
                                  the pull up the Control Panel, Admnistration Tools, Services and start the database. Then recheck the listener and it shoudl show some additional services such as
                                  Service "PLSExtProc" has 1 instance(s).
                                    Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
                                  Service "TEST" has 1 instance(s).
                                    Instance "xe", status READY, 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_XPT" 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
                                  In the above, remember the "xe" from 'Service "xe" has 1 instance(s).'. I expect to see something about that in the TNSNAMES.ORA file in your ORACLE_HOME\network\admin directory. That file should have an entry like
                                  XE =
                                    (DESCRIPTION =
                                      (ADDRESS = (PROTOCOL = TCP)(HOST = FCC003L)(PORT = 1521))
                                      (CONNECT_DATA =
                                        (SERVER = DEDICATED)
                                        (SERVICE_NAME = XE)
                                      )
                                    )
                                  and you will note the XE on the left side of the equal sign (which is a network alias), and one XE in the (SERVICE_NAME = XE) line. On YOUR system, the XE should be replaced by whatever you called your database (often 'orcl'). REMEMBER THIS ALIAS;-) and also make note of the host that your entry uses. If your host name changes because of DHCP, you're hooped (for a while).

                                  If the database is registered (shows up in the list), the next step is to make sure a userid is available. You could use 'system' (try to avoid using SYS since it is the master userid and if you mess that up, you mess up all your homework. In a production encvironment, every access to SYS will be audited.



                                  So, let's create a userid called me with (in 11g case sensitive) password 'myself'. Make sure that user is allow to log in (create session) and create a table.
                                  C:\temp>sqlplus system/oracle
                                  
                                  SQL*Plus: Release 10.2.0.1.0 - Production on Sun Feb 7 20:54:38 2010
                                  
                                  Copyright (c) 1982, 2005, Oracle.  All rights reserved.
                                  
                                  
                                  Connected to:
                                  Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
                                  
                                  SQL> create user me identified by myself;
                                  
                                  User created.
                                  
                                  SQL> rem to show you how to change password ...
                                  SQL> alter user me identified by myself account unlock;
                                  
                                  User altered.
                                  
                                  SQL> grant create session, create table to me;
                                  
                                  Grant succeeded.
                                  
                                  SQL> exit
                                  Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
                                  and now see whether we can log in using the same 'network' technique we will later use with SQLDeveloper.

                                  Do you remember the XE discussion above? Replace XE with the one on the left of the equal sign - the system looks that up to find which service it wants to access.
                                  C:\temp>sqlplus me/myself@XE
                                  
                                  SQL*Plus: Release 10.2.0.1.0 - Production on Sun Feb 7 20:58:26 2010
                                  
                                  Copyright (c) 1982, 2005, Oracle.  All rights reserved.
                                  
                                  
                                  Connected to:
                                  Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
                                  
                                  SQL> select * from dual;
                                  
                                  D
                                  -
                                  X
                                  
                                  SQL> exit
                                  Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
                                  If the above worked, you should have no problem accessing the system using SQLDeveloper. You simply use the HOST, the PORT and the SID (alias) or service from the "lsnrctl status" output in the SQLDeveloper fields.

                                  If it did not work, try dropping the "@XE" to see whether you can get in (using the bequeath connection) which is useful for troubleshooting but will not help with the traditional SQLDeveloper connection. If you get in without the @XE (or whatever your alias is) but not with it, most likely either your listener is not up or your firewall is blocking the access to the listener.
                                  • 14. Re: Oracle/ SQL Developer connection problem.
                                    751980
                                    Ok, Here is what happened when I did ( lsnrctl status ):

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

                                    C:\Windows\system32>lsnrctl status

                                    LSNRCTL for 32-bit Windows: Version 11.1.0.6.0 - Production on 08-FEB-2010 18:27
                                    :16

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

                                    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522)))
                                    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.244.201)(PORT=15
                                    22)))
                                    TNS-12535: TNS:operation timed out
                                    TNS-12560: TNS:protocol adapter error
                                    TNS-00505: Operation timed out
                                    32-bit Windows Error: 60: Unknown error

                                    Then I tried ( lsnrctl start ) and this is what I got:

                                    C:\Windows\system32>lsnrctl start

                                    LSNRCTL for 32-bit Windows: Version 11.1.0.6.0 - Production on 08-FEB-2010 18:30
                                    :06

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

                                    Starting tnslsnr: please wait...

                                    TNSLSNR for 32-bit Windows: Version 11.1.0.6.0 - Production
                                    System parameter file is C:\app\Steve\product\11.1.0\db_2\network\admin\listener
                                    .ora
                                    Log messages written to c:\app\steve\diag\tnslsnr\George\listener\alert\log.xml
                                    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1522
                                    ipc)))
                                    Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.244.201)(P
                                    ORT=1522)))
                                    TNS-12545: Connect failed because target host or object does not exist
                                    TNS-12560: TNS:protocol adapter error
                                    TNS-00515: Connect failed because target host or object does not exist
                                    32-bit Windows Error: 49: Unknown error

                                    Listener failed to start. See the error message(s) above...


                                    C:\Windows\system32>

                                    I noticed I got ( 32-bit Windows Error: *60*: Unknown error ) you had ( 32-bit Windows Error: *61*: Unknown error )
                                    Do you know what I'm missing?
                                    -steve
                                    1 2 Previous Next