9 Replies Latest reply on Jan 19, 2013 5:51 PM by Aman....

    ORA-12560 HELP!


      I found my step-son playing around with keys on my laptop and later on in the day, as usual, opened SQL developer and SQL plus to practice what I am learning on CBT nuggets tutorial for fundamentals exam. All of a sudden when i type in Username (hr) and Password i get ORA-12560: TNS:protocol adapter error .

      I am currently using Oracle 11g Express edition and when i try running the shortcut 'Get started with Oracle Database 11g Express Edition' it opens a browser window and say Could not connect to

      After searching on the Oracle forums and other websites, i still cannot find a solution. I was wondering if maybe my step son accidentally blocked something to do with Oracle 11g when prompted by my anti-virus (Kaspersky) but as I was not there I do not know exactly what he did.

      SQL developer gives me the follow error "Listener refused the connection with the following error: ORA-12505, TNS: Listener does not currently know of SID (shows Document icon) given in connect descriptor"

      Upon running lsnrctl status listener in Windows Command Prompt I get the following:

      C:\Windows\system32>lsnrctl status listener

      LSNRCTL for 32-bit Windows: Version - Production on 17-JAN-2013 23:07

      Copyright © 1991, 2010, Oracle. All rights reserved.

      STATUS of the LISTENER
      Alias LISTENER
      Version TNSLSNR for 32-bit Windows: Version - Produ
      Start Date 17-JAN-2013 22:46:23
      Uptime 0 days 0 hr. 20 min. 46 sec
      Trace Level off
      Security ON: Local OS Authentication
      SNMP OFF
      Default Service XE
      Listener Parameter File C:\oraclexe\app\oracle\product\11.2.0\server\network\a
      Listener Log File C:\oraclexe\app\oracle\diag\tnslsnr\Rami-PC\listener\a
      Listening Endpoints Summary...
      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

      Any ideas on what it could be? Please i am going nuts here trying to sort it out but I am not sure how to solve this problem and not being able to access my DB means i cannot really study for my ORA Fundamentals exam in March.

      Thanks in advance
        • 1. Re: ORA-12560 HELP!
          It does not appear that any Oracle DB is currently online.

          open Command Window & do as below EXACTLY (line for line)

          / as sysdba
          shutdown abort

          COPY the results from above then PASTE all back here
          • 2. Re: ORA-12560 HELP!
            It seems that the db service is down or the db itself is down as the listener is not showing the SID of your db in the summary. Issue the commands mentioned by Sb and post the output of them . If db is not started, you would have to start it.

            • 3. Re: ORA-12560 HELP!
              Here are the results from entering what you asked in windows command prompt:

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

              C:\Users\Rami>sqlplus / as sysdba shutdown abort startup

              SQL*Plus: Release Production

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

              Use SQL*Plus to execute SQL, PL/SQL and SQL*Plus statements.

              Usage 1: sqlplus -H | -V

              -H Displays the SQL*Plus version and the
              usage help.
              -V Displays the SQL*Plus version.

              Usage 2: sqlplus [ [<option>] [{logon | /nolog}] [<start>] ]

              <option> is: [-C <version>] [-L] [-M "<options>"] [-R <level>] [-S]

              -C <version> Sets the compatibility of affected commands to the
              version specified by <version>. The version has
              the form "x.y[.z]". For example, -C 10.2.0
              -L Attempts to log on just once, instead of
              reprompting on error.
              -M "<options>" Sets automatic HTML markup of output. The options
              have the form:
              HTML ON [HEAD text] [BODY text] [TABLE text]
              [ENTMAP {ON|OFF}] [SPOOL {ON|OFF}] [PRE[FORMAT] ON]
              -R <level> Sets restricted mode to disable SQL*Plus commands
              that interact with the file system. The level can
              be 1, 2 or 3. The most restrictive is -R 3 which
              disables all user commands interacting with the
              file system.
              -S Sets silent mode which suppresses the display of
              the SQL*Plus banner, prompts, and echoing of

              <logon> is: {<username>[<password>][@<connect_identifier>] | / }
              [AS {SYSDBA | SYSOPER | SYSASM}] [EDITION=value]

              Specifies the database account username, password and connect
              identifier for the database connection. Without a connect
              identifier, SQL*Plus connects to the default database.

              The AS SYSDBA, AS SYSOPER and AS SYSASM options are database
              administration privileges.

              <connect_identifier> can be in the form of Net Service Name
              or Easy Connect.

              @[<net_service_name> | [/]Host[:Port]/<service_name>]

              <net_service_name> is a simple name for a service that resolves
              to a connect descriptor.

              Example: Connect to database using Net Service Name and the
              database net service name is ORCL.

              sqlplus myusername/mypassword@ORCL

              Host specifies the host name or IP address of the database
              server computer.

              Port specifies the listening port on the database server.

              <service_name> specifies the service name of the database you
              want to access.

              Example: Connect to database using Easy Connect and the
              Service name is ORCL.

              sqlplus myusername/mypassword@Host/ORCL

              The /NOLOG option starts SQL*Plus without connecting to a

              The EDITION specifies the value for Session Edition.

              <start> is: @<URL>|<filename>[.<ext>] [<parameter> ...]

              Runs the specified SQL*Plus script from a web server (URL) or the
              local file system (filename.ext) with specified parameters that
              will be assigned to substitution variables in the script.

              When SQL*Plus starts, and after CONNECT commands, the site profile
              (e.g. $ORACLE_HOME/sqlplus/admin/glogin.sql) and the user profile
              (e.g. login.sql in the working directory) are run. The files may
              contain SQL*Plus commands.

              Refer to the SQL*Plus User's Guide and Reference for more information.

              • 4. Re: ORA-12560 HELP!
                Vlakarmis wrote:
                Here are the results from entering what you asked in windows command prompt:
                it appears that you are incapable to follow simple & straight forward directions.
                Consider to only use MS Access which is more user friendly than Oracle.
                [oracle@localhost ~]$ sqlplus
                SQL*Plus: Release Production on Fri Jan 18 08:43:05 2013
                Copyright (c) 1982, 2010, Oracle.  All rights reserved.
                Enter user-name: / as sysdba
                Connected to:
                Oracle Database 11g Enterprise Edition Release - Production
                With the Partitioning, OLAP, Data Mining and Real Application Testing options
                SQL> shutdown abort
                ORACLE instance shut down.
                SQL> startup
                ORACLE instance started.
                Total System Global Area  456146944 bytes
                Fixed Size                  1344840 bytes
                Variable Size             348129976 bytes
                Database Buffers          100663296 bytes
                Redo Buffers                6008832 bytes
                Database mounted.
                Database opened.
                • 5. Re: ORA-12560 HELP!
                  Sorry about that, I guess I did not follow the instructions exactly as you said although mentioning I am a newbie should have triggered something in that brain of yours to tell you I really do need step by step assistance, saying command prompt instead of saying "Run SQL PLUS and type the following" is not Newbie friendly. And to suggest i go use MS Access is also an insult to me and others trying to learn Oracle and subscribing to this forum.

                  Once again, I need help trying to understand exactly what happened, why the database suddenly stopped running. Please can you help me?

                  Here are the results:

                  Enter user-name: / as sysdba

                  Connected to:
                  Oracle Database 11g Express Edition Release - Production

                  SQL> shutdown abort
                  ORACLE instance shutdown
                  SQL> startup
                  ORACLE instance started.

                  Total System Global Area 1071333376 bytes
                  Fixed Size 1388352 bytes
                  Variable Size 704643264 bytes
                  Database Buffers 360710144 bytes
                  Redo Buffers 4591616 bytes
                  Database mounted.
                  Database opened.

                  Edited by: Vlakarmis on Jan 19, 2013 5:04 AM
                  • 6. Re: ORA-12560 HELP!
                    It is working now thank you. Might i suggest you be more patient in the future or simply avoid answering newbies if it will frustrate you when they make a mistake. Carry on...
                    • 7. Re: ORA-12560 HELP!
                      I need help trying to understand exactly what happened, why the database suddenly stopped running. Please can you help me?
                      I don't use XE myself so I am not much knowledgeable about it but in general, on Windows, if the service of the database SID(database name ) is stopped for some reason, it kills the database as well and you start seeing the same protocol adaptor error. If the service was running may be your son might had issued(by using the doskey command available through the arrow buttons) the shutdown command and it got stopped. I know it's a very wild guess but I can't think of anything else happening. So for the future, if the service property is set to Automatic for the database name, you should have the database running in the next boot of the windows unless you haven't shut it down explicitly.

                      Just my 2 cents.

                      • 8. Re: ORA-12560 HELP!
                        Sounds like the most probable situation Aman, thank you.
                        • 9. Re: ORA-12560 HELP!
                          No problem :) .