Checking the status of an instance

We need to verify if, as a consequence of a hot backup, the instance remains in a "suspend" status, then put it in open (resuming it), put all the tablespaces in "end backup", and sending a mail only when it do all this. The script follows up.

###############################################
##### resucita.sh ######
##### Authors: Toni Blanco ######
##### Fecha de creacion: 200800906 ######
##### Ultima modificacion: 20080906 ######
##### Revision: v1.0 ######
###############################################
# Si la instancia es troba en estat SUSPEND li fa un RESUME i envia un correu a explo.sistemas
# Si es troba en estat OPEN no fa res, només m'envia un correu a mi.
. /export/home/oracle/.profile
date
resultado=0
# I catch the result of the invocation to sqlplus to determine the status
# of the instance, using the dbms_output API.

resultado=`sqlplus -S "/as sysdba"<< EOF
set serveroutput on;
declare
estat varchar2(20):='nada';
a integer;
b integer;
c varchar2(100);
x integer:=0;
cursor c1 is select tablespace_name
from dba_tablespaces
where status<>'ONLINE'
order by 1;
fc1 c1%rowtype;

begin

select status into estat from "v\$instance" ; -- Status of the instance

if estat='SUSPEND' then
dbms_output.put_line(1); -- resultado:=1 --> send a mail
execute immediate 'alter system RESUME'; --> resume the instance
/* Loop to take to "end backup" all the tablespaces with another status */
open c1;
loop
exit when c1%notfound;
fetch c1 into fc1;
c:='alter tablespace '||fc1.tablespace_name||' online';
a:=dbms_sql.open_cursor;
dbms_sql.parse(a,c,dbms_sql.native);
b:=dbms_sql.execute(a);
dbms_sql.close_cursor(a);
end loop;
close c1;
else dbms_output.put_line(0);
end if;
end;
/
exit
EOF`

echo $resultado
if [ $resultado -gt 0 ]; then
# If the result i other than 0, send a mail to inform and suggest to check the actual status
echo "Instance in SUSPEND state. Check its actual status, please. "|mailx -s "Oracle AE05 recovered from a SUSPEND state" explo.sistemas@empresa.com
else
echo "Instance in OPEN state. "|mailx -s "Oracle AE05 OK" toni.blanco@empresa.com
fi

exit