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.
Like this:
Like Loading...
Excellent site, keep up the good work
Hi,
Very interesting. Thanks for such great article.
Thanks and regards,
Gitesh
http://www.dbametrix.com
Super-Duper site! I am loving it!! Will come back again – taking your feeds too now, Thanks.
I’m Out!
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
I think its impossible, oracle database still not connect to pl/SQL engine on that state. cmiiw
Oracle pl/sql script will be executed once oracle instance is up.
Thank you
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.
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.
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.