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/
--------------------------------------------------------------------------------