Home > Oracle > How to check Oracle Instance is Up or Down

How to check Oracle Instance is Up or Down

Here is the simplest way how to know whether oracle instance is up or down :
1. Try to connect to oracle using sqlplus /nolog
2. And then put code conn /as sysdba
3.  Let see in v$instance using query : select status from v$instance;
If the result is OPEN, it means that the Oracle is already up but if oracle give an error mesage message
ERROR at line 1:
ORA-01034: ORACLE not available -> oracle instance is not running.
4. If oracle is down and then You want to start it, You may using command startup and wait until database mounted and opened.

About these ads
Categories: Oracle Tags: , ,
  1. 4 September 2009 at 5:59 am

    Excellent site, keep up the good work

  2. 9 October 2009 at 4:02 am

    Hi,

    Very interesting. Thanks for such great article.

    Thanks and regards,
    Gitesh
    http://www.dbametrix.com

  3. 11 January 2010 at 8:02 am

    Super-Duper site! I am loving it!! Will come back again – taking your feeds too now, Thanks.

    I’m Out! :)

  4. z666zz666z
    8 February 2011 at 5:39 pm

    Great, but i have one question:

    How can i do that on a PL/SQL script… i mean… i want to know if it is down and in such case call the startup but i can not get it to work… allways tell me the error ORA-0134 when it is down but do not run the startup…

    I am using a PL/SQL like this:

    BEGIN
    EXECUTE IMMEDIATE ‘SELECT status FROM v$instance;';
    EXCEPTION
    WHEN OTHERS
    THEN
    IF SQLCODE != -01034
    THEN
    raise_application_error(-20001,’An error was encountered – ‘||SQLCODE||’ -ERROR- ‘||SQLERRM);
    ELSE
    STARTUP;
    END IF;
    END;
    /

    What i am doing wrong?

    I need a PL/SQL that call STARTUP only in case it is not OPEN… basically because there is something wrong because if i do the STARTUP when it is OPEN it gives an error (obvious) but then the database goes down (rare).

    Thanks

    • donkey
      25 March 2011 at 1:33 pm

      I think its impossible, oracle database still not connect to pl/SQL engine on that state. cmiiw

    • 19 August 2012 at 12:44 pm

      Oracle pl/sql script will be executed once oracle instance is up.

      Thank you

  5. z666zz666z
    8 February 2011 at 5:48 pm

    De hecho falla directamente cuando la base de datos no está OPEN incluso con esto tan simple:

    SET SERVEROUTPUT ON;
    BEGIN
    DBMS_OUTPUT.PUT_LINE(‘test’);
    END;
    /

    Cómo puedo controlar / tratar el error para hacer que se ejecute el STARTUP sólo en caso de fallo?

    Gracias.

  6. RAVI
    20 March 2012 at 5:58 pm

    TELL ME ONE THING ANY OPERATING SYSTEM COMMAND TO CHECK THE INSTANCE AVAILABILITY. AND IF 40 INSTANCE IS RUNING THEN TELL ME HOW CAN WE CHECK.

  7. 21 January 2013 at 12:06 pm

    Hello all, here every person is sharing these knowledge, so it’s pleasant to read this blog, and I used to go to see this blog everyday.

  8. Ahmad
    11 October 2013 at 1:06 am

    I am a developer and have no DBA knowledge but I am wondering whether there is a simple way to check if a database is up remotely from a Windows client?

    I am asking this because recently, our database hit the max connection and did not give any services to us but we, the developers, thought the database was down!

  9. 6 April 2014 at 12:41 pm

    Piece of writing writing is also a excitement, if you be
    familiar with then you can write if not it is complicated to write.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: