Процедура для сбора статистики с лог файлов по расписанию

Создаем таблицу. Создаем директорию с которой будут загружаться лог файлы. Если существует лог файл с текущей датой, то загружаем.
Парсим регулярками. Если слово ERROR иначе все ОК. Вытаскиваем дату начала и завершения резевного копирования. Вычисляем интервалы между этими датами.
Получаем время выполнения скрипта. Добавляем запись в таблицу. Удаляем старые лог файлы и записи с таблицы созданные позднеее 30 дней. А дальше с помощью DATABASE LINK собираем со всех серверов бд в центральную базу сбора данных.

-- Create table
create table ORACLEDBA.RMAN_LOGS
(
id NUMBER(15),
file_name VARCHAR2(1000),
file_clob CLOB,
timestamp DATE,
server_sid VARCHAR2(100),
rman_error VARCHAR2(100),
backup_start DATE,
backup_end DATE,
elapsed_time INTERVAL DAY(2) TO SECOND(6),
sqlcode VARCHAR2(1000)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);

--DIRECTORY
declare
sDIR varchar2(1000);
begin
begin
SELECT name||'\RMAN' INTO sDIR FROM V$RECOVERY_FILE_DEST;
exception when others then
sDIR:='';
end;
execute immediate 'CREATE OR REPLACE DIRECTORY RMAN_LOB_DIR AS '''||sDIR||'''';
end;
/

--Grants
GRANT SELECT ON sys.v_$instance TO oracledba;
GRANT READ, WRITE ON DIRECTORY RMAN_LOB_DIR TO sys;
GRANT READ, WRITE ON DIRECTORY RMAN_LOB_DIR TO oracledba;
--Procedure
create or replace procedure ORACLEDBA.P_R_RMAN_LOG as
cCLOB CLOB;
bBLOB BLOB;
SRC_LOC BFILE;
sFILE ORACLEDBA.RMAN_LOGS.FILE_NAME%TYPE;
sSID ORACLEDBA.RMAN_LOGS.SERVER_SID%TYPE;
sMESSAGE ORACLEDBA.RMAN_LOGS.RMAN_ERROR%TYPE;
dBACKUP_START ORACLEDBA.RMAN_LOGS.BACKUP_START%TYPE;
dBACKUP_END ORACLEDBA.RMAN_LOGS.BACKUP_END%TYPE;
nELAPSED_TIME ORACLEDBA.RMAN_LOGS.ELAPSED_TIME%TYPE;
nLID NUMBER(4);
sSQLCODE varchar2(1000);
sTEMP1 varchar2(100);
sTEMP2 varchar2(100);

/* Конвертация блоб в клоб */
FUNCTION blob_to_clob(plob IN BLOB) RETURN CLOB IS
lclob_Result CLOB := 'X';
l_dest_offsset INTEGER := 1;
l_src_offsset INTEGER := 1;
l_lang_context INTEGER := dbms_lob.default_lang_ctx;
l_warning INTEGER;
BEGIN
IF plob IS NOT NULL AND LENGTH(plob) > 0 THEN
dbms_lob.converttoclob(dest_lob => lclob_Result,
src_blob => plob,
amount => dbms_lob.lobmaxsize,
dest_offset => l_dest_offsset,
src_offset => l_src_offsset,
blob_csid => dbms_lob.default_csid,
lang_context => l_lang_context,
warning => l_warning);
IF l_warning != 0 THEN
sSQLCODE:='Function blob_to_clob warning:' ||l_warning;
RETURN NULL;
END IF;
RETURN(lclob_Result);
ELSE
RETURN NULL;
END IF;
EXCEPTION
WHEN OTHERS THEN
sSQLCODE:='Function blob_to_clob error:' || SQLCODE;
RETURN NULL;
END blob_to_clob;

/* Запись статистики в таблицу */
procedure P_R_SET_RMAN_LOGS(sFILENAME in varchar2,
sERROR in varchar2,
cTXT in clob,
dSTART in date,
dEND in date,
iTIME interval day to second) as
begin
insert into ORACLEDBA.RMAN_LOGS (id, file_name, file_clob, timestamp, server_sid, rman_error, backup_start, backup_end, elapsed_time, sqlcode)
values (nLID, sFILENAME, cTXT, sysdate, sSID, sERROR, dSTART, dEND, iTIME, sSQLCODE);
commit;
end P_R_SET_RMAN_LOGS;

/* Получение SID */
procedure P_R_GET_SID as
begin
begin
select t.INSTANCE_NAME into sSID from sys.v_$instance t where rownum = 1;
exception
when others then
sSID := null;
end;
end P_R_GET_SID;

/* Получение LID */
procedure P_R_GET_LID as
begin
begin
select t.lid into nLID from TUNE t where rownum = 1;
exception
when others then
nLID := null;
end;
end P_R_GET_LID;

/* Удаление старых записей из таблицы */
procedure P_R_CLEAR_LOG(nDAYS in number) as
begin
delete from ORACLEDBA.RMAN_LOGS where timestamp<sysdate-nDAYS;
commit;
end;

/* Удаление старых лог файлов */
procedure P_R_DELETE_LOG_FILES(nDAYS in number) as
begin
/* цикл по датам */
for cur in ( select trunc(sysdate-60-nDAYS+level) daten from dual
connect by level <= (sysdate-(sysdate-60))) loop /* sql выводит даты в колчисетве 60шт с задним числом на nDAYS*/ sFILE := 'backup_' || to_char(cur.daten, 'DD.MM.YYYY') || '.log'; if (DBMS_LOB.FILEEXISTS(BFILENAME('RMAN_LOB_DIR', sFILE)) = 1) then begin UTL_FILE.FREMOVE('RMAN_LOB_DIR', sFILE); exception when others then /* если отказано в доступе на удаление файла, то пропускаем*/ null; end; end if; end loop; end; begin sFILE := 'backup_' || to_char(sysdate, 'DD.MM.YYYY') || '.log'; P_R_GET_SID; P_R_GET_LID; src_loc := BFILENAME('RMAN_LOB_DIR', sFILE); if (DBMS_LOB.FILEEXISTS(src_loc) = 1) then DBMS_LOB.CREATETEMPORARY(bBLOB, TRUE, DBMS_LOB.SESSION); DBMS_LOB.OPEN(src_loc, DBMS_LOB.LOB_READONLY); DBMS_LOB.OPEN(bBLOB, DBMS_LOB.LOB_READWRITE); DBMS_LOB.LOADFROMFILE(dest_lob => bBLOB,
src_lob => src_loc,
amount => DBMS_LOB.getLength(src_loc));

DBMS_LOB.CLOSE(bBLOB);
DBMS_LOB.CLOSE(src_loc);

cCLOB := blob_to_clob(bBLOB);
if cCLOB is not null then
/* Если в лог файле строка с ошибкой */
if (regexp_like(cCLOB, 'ERROR')) then
sMESSAGE := 'ERROR MESSAGE';
else
sMESSAGE := 'OK';
end if;
/* RMIAS_BACKUP_START:30.04.2013 0:00:00 */
sTEMP1 := substr(regexp_substr(cCLOB,'RMIAS_BACKUP_START:\d{2}.\d{2}.\d{4}\s{1, 2}\d{1,2}:\d{2}:\d{2}'), 20);
/* RMIAS_BACKUP_END:30.04.2013 0:03:44 */
sTEMP2 := substr(regexp_substr(cCLOB,'RMIAS_BACKUP_END:\d{2}.\d{2}.\d{4}\s{1, 2}\d{1,2}:\d{2}:\d{2}'), 18);
/* Начало выполнения резевного копирования */
dBACKUP_START := to_date(sTEMP1, 'DD.MM.YYYY HH24:MI:SS');
/* Завершение резевного копирования */
dBACKUP_END := to_date(sTEMP2, 'DD.MM.YYYY HH24:MI:SS');
/* Время выполнения резевного копирования*/
nELAPSED_TIME := (dBACKUP_END - dBACKUP_START) DAY TO SECOND;
end if;
else
sMESSAGE := 'Log file not found';
end if;

P_R_SET_RMAN_LOGS(sFILE, sMESSAGE, cCLOB, dBACKUP_START, dBACKUP_END, nELAPSED_TIME);
P_R_CLEAR_LOG(30);
P_R_DELETE_LOG_FILES(30);

END P_R_RMAN_LOG;
/