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

11 thoughts on “How to check Oracle Instance is Up or Down

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

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

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

  4. 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!

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