This discussion is archived
9 Replies Latest reply: Jan 19, 2013 9:51 AM by Aman.... RSS

ORA-12560 HELP!

982153 Newbie
Currently Being Moderated
Hello,

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 127.0.0.1:8080

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 11.2.0.2.0 - Production on 17-JAN-2013 23:07
:04

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.2.0 - Produ
ction
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
dmin\listener.ora
Listener Log File C:\oraclexe\app\oracle\diag\tnslsnr\Rami-PC\listener\a
lert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Rami-PC)(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

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!
    sb92075 Guru
    Currently Being Moderated
    It does not appear that any Oracle DB is currently online.

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

    sqlplus
    / as sysdba
    shutdown abort
    startup

    COPY the results from above then PASTE all back here
  • 2. Re: ORA-12560 HELP!
    Aman.... Oracle ACE
    Currently Being Moderated
    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.

    Aman....
  • 3. Re: ORA-12560 HELP!
    982153 Newbie
    Currently Being Moderated
    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 11.2.0.2.0 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
    commands.

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

    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.

    C:\Users\Rami>
  • 4. Re: ORA-12560 HELP!
    sb92075 Guru
    Currently Being Moderated
    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 11.2.0.2.0 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 11.2.0.2.0 - 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.
    SQL> 
  • 5. Re: ORA-12560 HELP!
    982153 Newbie
    Currently Being Moderated
    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 11.2.0.2.0 - 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!
    982153 Newbie
    Currently Being Moderated
    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!
    Aman.... Oracle ACE
    Currently Being Moderated
    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.

    Aman....
  • 8. Re: ORA-12560 HELP!
    982153 Newbie
    Currently Being Moderated
    Sounds like the most probable situation Aman, thank you.
  • 9. Re: ORA-12560 HELP!
    Aman.... Oracle ACE
    Currently Being Moderated
    No problem :) .

    Aman....

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points