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

Checking backups

Generation of a listing with all the filesystems that forms an instance. It's useful to send to the backup administrator, for instance.

We configure BACKUPS.BAT to connect to all the instances we administer. It uses backups.sql that groups and order the directories, and generates an ordered listing of instances with the directories that contains some datafiles or configurations files.

-- BACKUPS.BAT
@echo off
sqlplus -S system/****@APP05 @c:\backups.sql >>c:\backups.out
sqlplus -S system/****@APP01-10 @c:\backups.sql >>c:\backups.out
....

-- backups.sql

set pagesize 0

col instancia format a10

set serveroutput on feed off
declare
ins varchar2(20);
ver varchar2(120);
begin
select UPPER(instance) into ins from v$thread where rownum=1;
select banner into ver from gv$version where banner like '%Edition%' and rownum=1; dbms_output.put_line(rpad(ins,8,' ')' - 'ver);
end;
/

select HOST_NAME HOST from v$instance;
select distinct substr(file_name, 1, instr(file_name,'/',-1)) from dba_data_files
union
select distinct substr(name, 1, instr(name,'/',-1)) from v$controlfile
union
select distinct substr(member, 1, instr(member,'/',-1)) from v$logfile
union
select distinct substr(file_name, 1, instr(file_name,'/',-1)) from dba_temp_files union
select distinct substr(value, 1, instr(value,'/',-1)) from v$parameterwhere name like 'log_archive_dest%' and value is not null and value <>'enable'order by 1;
select rpad('-',80,'-') from dual;
exit



-- Backups.out

APP05 - Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
hostname1
/global/aecobde5/u01/oradata/ae06/
/global/aecobde5/u02/oradata/ae06/
/global/aecobde5/u03/oradata/ae06/
/global/aecobde5/u04/oradata/ae06/
/global/aecobde5/u05/oradata/ae06/arch/
/global/aecobde5/u06/oradata/ae06/
/global/aecobde5/u07/oradata/ae06/
/global/aecobde5/u08/oradata/ae06/
/global/aecobde5/u09/oradata/ae06/
/global/aecobde5/u11/oradata/ae06/
--------------------------------------------------------------------------------
APP011 - Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
hostname2
+DATOS/app01/controlfile/
+DATOS/app01/datafile/
+DATOS/app01/onlinelog/
+DATOS/app01/tempfile/
+FLASHDB/app01/controlfile/
+FLASHDB/app01/onlinelog/
--------------------------------------------------------------------------------