Below package used to scan backup logfile which is generated by RMAN and sends mail to the DBA or group. Three seperate blocks are created for 3 different types of backup.
1. Daily Hot backup
2. Weekly Hot Backup
3. Export/Import
All the logfile locations are mapped to a dicrectory object in the database and by using that the procedure reads the logifle and scans for the error message and executes the procedures based on the result. Please read all the procedures carefully before implementing. This has been impletemented in 10g and its sucessfully running for more than a year.
CREATE OR REPLACE PACKAGE BACKUPTEST.Pkg_Backup_status
as
procedure pr_Hotbackup_daily
(
bkpstart out varchar2,
bkpend OUT varchar2,
p_error_text out clob
);
procedure pr_Hotbackup_weekly
(
bkpstart out varchar2,
bkpend OUT varchar2,
p_error_text out clob
);
procedure pr_Export_daily
(
p_Exp_error out clob
);
procedure pr_mail
(
p_var varchar2
);
Function Templte(ip in number)
return varchar2;
end Pkg_Backup_status;
/
CREATE OR REPLACE PACKAGE BODY BACKUPTEST.Pkg_Backup_status
as
procedure pr_Hotbackup_daily
(
bkpstart out varchar2,
bkpend OUT varchar2,
p_error_text out clob
)
as
Dat1 varchar2(4000);
F UTL_FILE.FILE_TYPE;
fname varchar2(30);
check1 varchar2(4000);
check2 varchar2(4000);
err varchar2(1000);
F_exists boolean;
Flength Number;
Fblock Number;
error_text clob;
chk_status varchar2(20);
CRLF CHAR(2) := CHR(10) || CHR(13);
CRL CHAR(2) := CHR(10);
begin
select 'backup_daily_'||to_char(sysdate,'mm')||'_'||to_char(sysdate,'dd')||'_'||to_char(sysdate,'yyyy')||'.log' into fname from dual;
begin
select a,b,c into chk_status,bkpstart,bkpend from (select status a,to_char(START_TIME,'dd/mm/yyyy hh24:mi:ss') b,to_char(END_TIME,'dd/mm/yyyy hh24:mi:ss') c from sys.V_$RMAN_STATUS where operation='BACKUP' and to_char(start_time,'dd/mm/yyyy') = to_char(sysdate,'dd/mm/yyyy')
and object_type like 'DB%' order by 2 desc)where rownum=1;
exception when no_data_found then
chk_status := null;
end;
UTL_FILE.fgetattr('BACKUPTEST',fname,F_exists,Flength,Fblock);
IF F_exists THEN
F:= utl_file.FOPEN('BACKUPTEST',fname,'R');
check1 := 'RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============' ;
check2 := 'Recovery Manager complete.';
loop
utl_file.GET_LINE(F,Dat1);
if (dat1 = check1 ) then
loop
begin
utl_file.get_line(F,Err);
error_text := error_text|| CRL || err;
exception
when no_data_found then
UTL_FILE.FCLOSE(F);
goto NV;
end;
end loop;
elsif (dat1 =check2) then
error_text := 'Backup completed successfully';
goto NV;
end if;
end loop;
<>
p_error_text := error_text;
else
p_error_text := 'The Hot backup is not scheduled today';
bkpstart := 'No backup';
bkpend := 'No backup';
end if;
EXCEPTION
WHEN NO_DATA_FOUND THEN
UTL_FILE.FCLOSE(F);
WHEN utl_file.invalid_mode THEN
RAISE_APPLICATION_ERROR (-20051, 'Invalid Mode Parameter');
WHEN utl_file.invalid_path THEN
RAISE_APPLICATION_ERROR (-20052, 'Invalid File Location');
WHEN utl_file.invalid_filehandle THEN
RAISE_APPLICATION_ERROR (-20053, 'Invalid Filehandle');
WHEN utl_file.invalid_operation THEN
RAISE_APPLICATION_ERROR (-20054, 'Invalid Operation');
WHEN utl_file.read_error THEN
RAISE_APPLICATION_ERROR (-20055, 'Read Error');
WHEN utl_file.internal_error THEN
RAISE_APPLICATION_ERROR (-20057, 'Internal Error');
WHEN utl_file.file_open THEN
RAISE_APPLICATION_ERROR (-20059, 'File Already Opened');
WHEN utl_file.invalid_filename THEN
RAISE_APPLICATION_ERROR (-20061, 'Invalid File Name');
WHEN utl_file.access_denied THEN
RAISE_APPLICATION_ERROR (-20062, 'File Access Denied By');
WHEN others THEN
RAISE_APPLICATION_ERROR (-20099, 'Unknown UTL_FILE Error');
end;
procedure pr_Hotbackup_weekly
(
bkpstart out varchar2,
bkpend OUT varchar2,
p_error_text out clob
)
as
Dat1 varchar2(4000);
F UTL_FILE.FILE_TYPE;
fname varchar2(30);
check1 varchar2(4000);
check2 varchar2(4000);
err varchar2(1000);
F_exists boolean;
Flength Number;
Fblock Number;
error_text clob;
chk_status varchar2(20);
CRLF CHAR(2) := CHR(10) || CHR(13);
CRL CHAR(2) := CHR(10);
begin
select 'backup_complete_'||to_char(sysdate,'mm')||'_'||to_char(sysdate,'dd')||'_'||to_char(sysdate,'yyyy')||'.log' into fname from dual;
begin
select a,b,c into chk_status,bkpstart,bkpend from (select status a,to_char(START_TIME,'dd/mm/yyyy hh24:mi:ss') b,to_char(END_TIME,'dd/mm/yyyy hh24:mi:ss') c from sys.V_$RMAN_STATUS where operation='BACKUP' and to_char(start_time,'dd/mm/yyyy') = to_char(sysdate,'dd/mm/yyyy')
and object_type like 'DB%' order by 2 desc)where rownum=1;
exception when no_data_found then
chk_status := null;
end;
UTL_FILE.fgetattr('BACKUPTEST_WEEKLY',fname,F_exists,Flength,Fblock);
IF F_exists THEN
F:= utl_file.FOPEN('BACKUPTEST_WEEKLY',fname,'R');
check1 := 'RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============' ;
check2 := 'Recovery Manager complete.';
loop
utl_file.GET_LINE(F,Dat1);
if (dat1 = check1 ) then
loop
begin
utl_file.get_line(F,Err);
error_text := error_text|| CRL || err;
exception
when no_data_found then
UTL_FILE.FCLOSE(F);
goto NV;
end;
end loop;
elsif (dat1 =check2) then
error_text := 'Backup completed successfully';
goto NV;
end if;
end loop;
<>
p_error_text := error_text;
else
p_error_text := 'The Hot backup is not scheduled today';
bkpstart := 'No backup';
bkpend := 'No backup';
end if;
EXCEPTION
WHEN NO_DATA_FOUND THEN
UTL_FILE.FCLOSE(F);
WHEN utl_file.invalid_mode THEN
RAISE_APPLICATION_ERROR (-20051, 'Invalid Mode Parameter');
WHEN utl_file.invalid_path THEN
RAISE_APPLICATION_ERROR (-20052, 'Invalid File Location');
WHEN utl_file.invalid_filehandle THEN
RAISE_APPLICATION_ERROR (-20053, 'Invalid Filehandle');
WHEN utl_file.invalid_operation THEN
RAISE_APPLICATION_ERROR (-20054, 'Invalid Operation');
WHEN utl_file.read_error THEN
RAISE_APPLICATION_ERROR (-20055, 'Read Error');
WHEN utl_file.internal_error THEN
RAISE_APPLICATION_ERROR (-20057, 'Internal Error');
WHEN utl_file.file_open THEN
RAISE_APPLICATION_ERROR (-20059, 'File Already Opened');
WHEN utl_file.invalid_filename THEN
RAISE_APPLICATION_ERROR (-20061, 'Invalid File Name');
WHEN utl_file.access_denied THEN
RAISE_APPLICATION_ERROR (-20062, 'File Access Denied By');
WHEN others THEN
RAISE_APPLICATION_ERROR (-20099, 'Unknown UTL_FILE Error');
end;
procedure pr_Export_daily
(
p_Exp_error out clob
)
as
Dat2 varchar2(4000);
F1 UTL_FILE.FILE_TYPE;
F2 UTL_FILE.FILE_TYPE;
dat3 varchar2(4000);
err1 varchar2(1000);
fname varchar2(30);
mail_content clob;
F_exists boolean;
Flength Number;
Fblock Number;
CRL CHAR(2) := CHR(10);
exp_chk1 varchar2(100);
exp_chk2 varchar2(100);
Exp_error clob;
j number default 0;
n number default 0;
m number default 0;
begin
select 'backup_'||to_char(sysdate,'mm')||'_'||to_char(sysdate,'dd')||'_'||to_char(sysdate,'yyyy')||'.log' into fname from dual;
UTL_FILE.fgetattr('DTPUMP','Expdp.log',F_exists,Flength,Fblock);
IF F_exists THEN
F1:= utl_file.FOPEN('DTPUMP','Expdp.log','R');
exp_chk1 := 'EXP-00000: Export terminated unsuccessfully';
exp_chk2 := 'Export terminated successfully without warnings.';
loop
utl_file.GET_LINE(F1,Dat2);
j:=j+1;
if (dat2 = exp_chk2 ) then
Exp_error := dat2;
goto NV1;
end if;
end loop;
<>
p_Exp_error := Exp_error;
else
p_Exp_error := 'The export backup is not scheduled today';
end if;
EXCEPTION
WHEN NO_DATA_FOUND THEN
if (j < 4) then
m := j;
else
m:= 4;
end if;
F2 := utl_file.FOPEN('DTPUMP','Expdp.log','R');
loop
begin
utl_file.GET_LINE(F2,Err1);
N := N + 1;
if (N >= (j-m)) then
Exp_error := Exp_error || CRL || err1;
end if;
exception
when no_data_found then
UTL_FILE.FCLOSE(F2);
UTL_FILE.FCLOSE(F1);
p_Exp_error:= Exp_error;
exit;
end;
end loop;
WHEN utl_file.invalid_mode THEN
RAISE_APPLICATION_ERROR (-20051, 'Invalid Mode Parameter');
WHEN utl_file.invalid_path THEN
RAISE_APPLICATION_ERROR (-20052, 'Invalid File Location');
WHEN utl_file.invalid_filehandle THEN
RAISE_APPLICATION_ERROR (-20053, 'Invalid Filehandle');
WHEN utl_file.invalid_operation THEN
RAISE_APPLICATION_ERROR (-20054, 'Invalid Operation');
WHEN utl_file.read_error THEN
RAISE_APPLICATION_ERROR (-20055, 'Read Error');
WHEN utl_file.internal_error THEN
RAISE_APPLICATION_ERROR (-20057, 'Internal Error');
WHEN utl_file.file_open THEN
RAISE_APPLICATION_ERROR (-20059, 'File Already Opened');
WHEN utl_file.invalid_filename THEN
RAISE_APPLICATION_ERROR (-20061, 'Invalid File Name');
WHEN utl_file.access_denied THEN
RAISE_APPLICATION_ERROR (-20062, 'File Access Denied By');
WHEN others THEN
RAISE_APPLICATION_ERROR (-20099, 'Unknown UTL_FILE Error');
end;
procedure pr_mail
(
p_var varchar2
)
as
templt1 varchar2(200);
templt1a varchar2(200);
templt2 varchar2(200);
templt3 varchar2(200);
templt4 varchar2(200);
mail_content clob;
CRLF CHAR(2) := CHR(10) || CHR(13);
CRL CHAR(2) := CHR(10);
bkpstart varchar2(100);
bkpend varchar2(100);
error_text clob;
Exp_error clob;
F_exists boolean;
Flength Number;
Fblock Number;
begin
templt1 := Templte(23)||' DAILY BACKUP '||Templte(23);
templt1A := Templte(25)||' TIMINGS '||Templte(25);
templt2 := Templte(23)||' HOTBACKUP STATUS '||Templte(23);
templt3 := Templte(23)||' LOGICAL BACKUP STATUS '||Templte(23);
templt4 := Templte(23)||' WEEKLY BACKUP '||Templte(23);
if ( p_var='D') then
pr_Hotbackup_daily(bkpstart,bkpend,error_text);
elsif (p_var='W') then
pr_Hotbackup_weekly(bkpstart,bkpend,error_text);
end if;
pr_Export_daily (Exp_error);
if ( p_var='D') then
mail_content := 'Dear DBAs,'||CRLF||templt1||CRLF||templt1a||CRLF||' Hot Backup start_time:'||bkpstart||CRL||'Hot Backup End_time :'||bkpend||CRLF||''||templt2||''||CRLF||''|| Error_text ||''||CRLF||templt3||CRLF||''|| Exp_error ||''||CRLF||'Thank you,';
utl_mail.SEND('Databasename ','mailid@gmail.com',null,null,'DB BackupStatus',mail_content,null,null);
elsif (p_var='W') then
mail_content := 'Dear DBAs,'||CRLF||templt4||CRLF||templt1a||CRLF||' Hot Backup start_time:'||bkpstart||CRL||'Hot Backup End_time :'||bkpend||CRLF||''||templt2||''||CRLF||''|| Error_text ||''||CRLF||templt3||CRLF||''|| Exp_error ||''||CRLF||'Thank you,';
utl_mail.SEND('Databasename ','mailid@gmail.com',null,null,'DB BackupStatus',mail_content,null,null);
end if;
UTL_FILE.fgetattr('DTPUMP','Expdp.log',F_exists,Flength,Fblock);
IF F_exists THEN
utl_file.fremove('DTPUMP','Expdp.log');
end if;
end;
Function Templte
(ip in number)
return varchar2
as
Lne varchar2(30);
i number;
equls varchar2(30);
begin
for i in 1..ip
loop
Lne:=trim(Lne)||trim('=');
end loop;
return Lne;
end;
end Pkg_Backup_status;
/
1. Daily Hot backup
2. Weekly Hot Backup
3. Export/Import
All the logfile locations are mapped to a dicrectory object in the database and by using that the procedure reads the logifle and scans for the error message and executes the procedures based on the result. Please read all the procedures carefully before implementing. This has been impletemented in 10g and its sucessfully running for more than a year.
CREATE OR REPLACE PACKAGE BACKUPTEST.Pkg_Backup_status
as
procedure pr_Hotbackup_daily
(
bkpstart out varchar2,
bkpend OUT varchar2,
p_error_text out clob
);
procedure pr_Hotbackup_weekly
(
bkpstart out varchar2,
bkpend OUT varchar2,
p_error_text out clob
);
procedure pr_Export_daily
(
p_Exp_error out clob
);
procedure pr_mail
(
p_var varchar2
);
Function Templte(ip in number)
return varchar2;
end Pkg_Backup_status;
/
CREATE OR REPLACE PACKAGE BODY BACKUPTEST.Pkg_Backup_status
as
procedure pr_Hotbackup_daily
(
bkpstart out varchar2,
bkpend OUT varchar2,
p_error_text out clob
)
as
Dat1 varchar2(4000);
F UTL_FILE.FILE_TYPE;
fname varchar2(30);
check1 varchar2(4000);
check2 varchar2(4000);
err varchar2(1000);
F_exists boolean;
Flength Number;
Fblock Number;
error_text clob;
chk_status varchar2(20);
CRLF CHAR(2) := CHR(10) || CHR(13);
CRL CHAR(2) := CHR(10);
begin
select 'backup_daily_'||to_char(sysdate,'mm')||'_'||to_char(sysdate,'dd')||'_'||to_char(sysdate,'yyyy')||'.log' into fname from dual;
begin
select a,b,c into chk_status,bkpstart,bkpend from (select status a,to_char(START_TIME,'dd/mm/yyyy hh24:mi:ss') b,to_char(END_TIME,'dd/mm/yyyy hh24:mi:ss') c from sys.V_$RMAN_STATUS where operation='BACKUP' and to_char(start_time,'dd/mm/yyyy') = to_char(sysdate,'dd/mm/yyyy')
and object_type like 'DB%' order by 2 desc)where rownum=1;
exception when no_data_found then
chk_status := null;
end;
UTL_FILE.fgetattr('BACKUPTEST',fname,F_exists,Flength,Fblock);
IF F_exists THEN
F:= utl_file.FOPEN('BACKUPTEST',fname,'R');
check1 := 'RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============' ;
check2 := 'Recovery Manager complete.';
loop
utl_file.GET_LINE(F,Dat1);
if (dat1 = check1 ) then
loop
begin
utl_file.get_line(F,Err);
error_text := error_text|| CRL || err;
exception
when no_data_found then
UTL_FILE.FCLOSE(F);
goto NV;
end;
end loop;
elsif (dat1 =check2) then
error_text := 'Backup completed successfully';
goto NV;
end if;
end loop;
<
p_error_text := error_text;
else
p_error_text := 'The Hot backup is not scheduled today';
bkpstart := 'No backup';
bkpend := 'No backup';
end if;
EXCEPTION
WHEN NO_DATA_FOUND THEN
UTL_FILE.FCLOSE(F);
WHEN utl_file.invalid_mode THEN
RAISE_APPLICATION_ERROR (-20051, 'Invalid Mode Parameter');
WHEN utl_file.invalid_path THEN
RAISE_APPLICATION_ERROR (-20052, 'Invalid File Location');
WHEN utl_file.invalid_filehandle THEN
RAISE_APPLICATION_ERROR (-20053, 'Invalid Filehandle');
WHEN utl_file.invalid_operation THEN
RAISE_APPLICATION_ERROR (-20054, 'Invalid Operation');
WHEN utl_file.read_error THEN
RAISE_APPLICATION_ERROR (-20055, 'Read Error');
WHEN utl_file.internal_error THEN
RAISE_APPLICATION_ERROR (-20057, 'Internal Error');
WHEN utl_file.file_open THEN
RAISE_APPLICATION_ERROR (-20059, 'File Already Opened');
WHEN utl_file.invalid_filename THEN
RAISE_APPLICATION_ERROR (-20061, 'Invalid File Name');
WHEN utl_file.access_denied THEN
RAISE_APPLICATION_ERROR (-20062, 'File Access Denied By');
WHEN others THEN
RAISE_APPLICATION_ERROR (-20099, 'Unknown UTL_FILE Error');
end;
procedure pr_Hotbackup_weekly
(
bkpstart out varchar2,
bkpend OUT varchar2,
p_error_text out clob
)
as
Dat1 varchar2(4000);
F UTL_FILE.FILE_TYPE;
fname varchar2(30);
check1 varchar2(4000);
check2 varchar2(4000);
err varchar2(1000);
F_exists boolean;
Flength Number;
Fblock Number;
error_text clob;
chk_status varchar2(20);
CRLF CHAR(2) := CHR(10) || CHR(13);
CRL CHAR(2) := CHR(10);
begin
select 'backup_complete_'||to_char(sysdate,'mm')||'_'||to_char(sysdate,'dd')||'_'||to_char(sysdate,'yyyy')||'.log' into fname from dual;
begin
select a,b,c into chk_status,bkpstart,bkpend from (select status a,to_char(START_TIME,'dd/mm/yyyy hh24:mi:ss') b,to_char(END_TIME,'dd/mm/yyyy hh24:mi:ss') c from sys.V_$RMAN_STATUS where operation='BACKUP' and to_char(start_time,'dd/mm/yyyy') = to_char(sysdate,'dd/mm/yyyy')
and object_type like 'DB%' order by 2 desc)where rownum=1;
exception when no_data_found then
chk_status := null;
end;
UTL_FILE.fgetattr('BACKUPTEST_WEEKLY',fname,F_exists,Flength,Fblock);
IF F_exists THEN
F:= utl_file.FOPEN('BACKUPTEST_WEEKLY',fname,'R');
check1 := 'RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============' ;
check2 := 'Recovery Manager complete.';
loop
utl_file.GET_LINE(F,Dat1);
if (dat1 = check1 ) then
loop
begin
utl_file.get_line(F,Err);
error_text := error_text|| CRL || err;
exception
when no_data_found then
UTL_FILE.FCLOSE(F);
goto NV;
end;
end loop;
elsif (dat1 =check2) then
error_text := 'Backup completed successfully';
goto NV;
end if;
end loop;
<
p_error_text := error_text;
else
p_error_text := 'The Hot backup is not scheduled today';
bkpstart := 'No backup';
bkpend := 'No backup';
end if;
EXCEPTION
WHEN NO_DATA_FOUND THEN
UTL_FILE.FCLOSE(F);
WHEN utl_file.invalid_mode THEN
RAISE_APPLICATION_ERROR (-20051, 'Invalid Mode Parameter');
WHEN utl_file.invalid_path THEN
RAISE_APPLICATION_ERROR (-20052, 'Invalid File Location');
WHEN utl_file.invalid_filehandle THEN
RAISE_APPLICATION_ERROR (-20053, 'Invalid Filehandle');
WHEN utl_file.invalid_operation THEN
RAISE_APPLICATION_ERROR (-20054, 'Invalid Operation');
WHEN utl_file.read_error THEN
RAISE_APPLICATION_ERROR (-20055, 'Read Error');
WHEN utl_file.internal_error THEN
RAISE_APPLICATION_ERROR (-20057, 'Internal Error');
WHEN utl_file.file_open THEN
RAISE_APPLICATION_ERROR (-20059, 'File Already Opened');
WHEN utl_file.invalid_filename THEN
RAISE_APPLICATION_ERROR (-20061, 'Invalid File Name');
WHEN utl_file.access_denied THEN
RAISE_APPLICATION_ERROR (-20062, 'File Access Denied By');
WHEN others THEN
RAISE_APPLICATION_ERROR (-20099, 'Unknown UTL_FILE Error');
end;
procedure pr_Export_daily
(
p_Exp_error out clob
)
as
Dat2 varchar2(4000);
F1 UTL_FILE.FILE_TYPE;
F2 UTL_FILE.FILE_TYPE;
dat3 varchar2(4000);
err1 varchar2(1000);
fname varchar2(30);
mail_content clob;
F_exists boolean;
Flength Number;
Fblock Number;
CRL CHAR(2) := CHR(10);
exp_chk1 varchar2(100);
exp_chk2 varchar2(100);
Exp_error clob;
j number default 0;
n number default 0;
m number default 0;
begin
select 'backup_'||to_char(sysdate,'mm')||'_'||to_char(sysdate,'dd')||'_'||to_char(sysdate,'yyyy')||'.log' into fname from dual;
UTL_FILE.fgetattr('DTPUMP','Expdp.log',F_exists,Flength,Fblock);
IF F_exists THEN
F1:= utl_file.FOPEN('DTPUMP','Expdp.log','R');
exp_chk1 := 'EXP-00000: Export terminated unsuccessfully';
exp_chk2 := 'Export terminated successfully without warnings.';
loop
utl_file.GET_LINE(F1,Dat2);
j:=j+1;
if (dat2 = exp_chk2 ) then
Exp_error := dat2;
goto NV1;
end if;
end loop;
<
p_Exp_error := Exp_error;
else
p_Exp_error := 'The export backup is not scheduled today';
end if;
EXCEPTION
WHEN NO_DATA_FOUND THEN
if (j < 4) then
m := j;
else
m:= 4;
end if;
F2 := utl_file.FOPEN('DTPUMP','Expdp.log','R');
loop
begin
utl_file.GET_LINE(F2,Err1);
N := N + 1;
if (N >= (j-m)) then
Exp_error := Exp_error || CRL || err1;
end if;
exception
when no_data_found then
UTL_FILE.FCLOSE(F2);
UTL_FILE.FCLOSE(F1);
p_Exp_error:= Exp_error;
exit;
end;
end loop;
WHEN utl_file.invalid_mode THEN
RAISE_APPLICATION_ERROR (-20051, 'Invalid Mode Parameter');
WHEN utl_file.invalid_path THEN
RAISE_APPLICATION_ERROR (-20052, 'Invalid File Location');
WHEN utl_file.invalid_filehandle THEN
RAISE_APPLICATION_ERROR (-20053, 'Invalid Filehandle');
WHEN utl_file.invalid_operation THEN
RAISE_APPLICATION_ERROR (-20054, 'Invalid Operation');
WHEN utl_file.read_error THEN
RAISE_APPLICATION_ERROR (-20055, 'Read Error');
WHEN utl_file.internal_error THEN
RAISE_APPLICATION_ERROR (-20057, 'Internal Error');
WHEN utl_file.file_open THEN
RAISE_APPLICATION_ERROR (-20059, 'File Already Opened');
WHEN utl_file.invalid_filename THEN
RAISE_APPLICATION_ERROR (-20061, 'Invalid File Name');
WHEN utl_file.access_denied THEN
RAISE_APPLICATION_ERROR (-20062, 'File Access Denied By');
WHEN others THEN
RAISE_APPLICATION_ERROR (-20099, 'Unknown UTL_FILE Error');
end;
procedure pr_mail
(
p_var varchar2
)
as
templt1 varchar2(200);
templt1a varchar2(200);
templt2 varchar2(200);
templt3 varchar2(200);
templt4 varchar2(200);
mail_content clob;
CRLF CHAR(2) := CHR(10) || CHR(13);
CRL CHAR(2) := CHR(10);
bkpstart varchar2(100);
bkpend varchar2(100);
error_text clob;
Exp_error clob;
F_exists boolean;
Flength Number;
Fblock Number;
begin
templt1 := Templte(23)||' DAILY BACKUP '||Templte(23);
templt1A := Templte(25)||' TIMINGS '||Templte(25);
templt2 := Templte(23)||' HOTBACKUP STATUS '||Templte(23);
templt3 := Templte(23)||' LOGICAL BACKUP STATUS '||Templte(23);
templt4 := Templte(23)||' WEEKLY BACKUP '||Templte(23);
if ( p_var='D') then
pr_Hotbackup_daily(bkpstart,bkpend,error_text);
elsif (p_var='W') then
pr_Hotbackup_weekly(bkpstart,bkpend,error_text);
end if;
pr_Export_daily (Exp_error);
if ( p_var='D') then
mail_content := 'Dear DBAs,'||CRLF||templt1||CRLF||templt1a||CRLF||' Hot Backup start_time:'||bkpstart||CRL||'Hot Backup End_time :'||bkpend||CRLF||''||templt2||''||CRLF||''|| Error_text ||''||CRLF||templt3||CRLF||''|| Exp_error ||''||CRLF||'Thank you,';
utl_mail.SEND('Databasename ','mailid@gmail.com',null,null,'DB BackupStatus',mail_content,null,null);
elsif (p_var='W') then
mail_content := 'Dear DBAs,'||CRLF||templt4||CRLF||templt1a||CRLF||' Hot Backup start_time:'||bkpstart||CRL||'Hot Backup End_time :'||bkpend||CRLF||''||templt2||''||CRLF||''|| Error_text ||''||CRLF||templt3||CRLF||''|| Exp_error ||''||CRLF||'Thank you,';
utl_mail.SEND('Databasename ','mailid@gmail.com',null,null,'DB BackupStatus',mail_content,null,null);
end if;
UTL_FILE.fgetattr('DTPUMP','Expdp.log',F_exists,Flength,Fblock);
IF F_exists THEN
utl_file.fremove('DTPUMP','Expdp.log');
end if;
end;
Function Templte
(ip in number)
return varchar2
as
Lne varchar2(30);
i number;
equls varchar2(30);
begin
for i in 1..ip
loop
Lne:=trim(Lne)||trim('=');
end loop;
return Lne;
end;
end Pkg_Backup_status;
/
No comments:
Post a Comment