Following steps can be used to setup RMAN Backup email notification,
1. Create file compressed_backup.sh
rm /home/sami/scripts/compressed_backup.zip
rm /home/sami/scripts/compressed_backup.log
rman target / log compressed_backup.log @compressed_backup.sql
zip compressed_backup.zip /home/sami/scripts/compressed_backup.log
rman target / log backup_notification.log @backup_notification.sql
2. Create file compressed_backup.sql
run
{
sql 'alter system archive log current';
allocate channel ch1 type disk format '+ASMRECO1/celldb/backup/%d_%t_%s_%c.bak';
backup database plus archivelog;
}
delete noprompt obsolete device type disk;
crosscheck backupset device type disk;
delete noprompt expired backupset device type disk;
run
{
allocate channel ch1 type disk format '/u01/backupdisk/backupset/%d_%s_%t_%c.bak';
backup backupset all;
}
list backup device type disk;
crosscheck backupset device type disk;
3. Create file backup_notification.sql {This script is calling the procedure xxrman_status_proc that will send the email notification}
sql 'begin xxrman_status_proc; end;';
exit;
4. Below is the actual procedure when executed will send the email notification but script in step 1 when added to the cronjob will call this procedure through script in step 3,
CREATE OR REPLACE procedure xxrman_status_proc as
vmailMessage varchar2(32767);
vstatus varchar2(100);
vsendto varchar2(2000):='samiora@gmail.com';
v_utl_filehandler UTL_FILE.FILE_TYPE;
v_rawfile RAW(32767);
v_size NUMBER;
v_block NUMBER;
v_boolean BOOLEAN;
v_file_dir varchar2(100);
v_file_name varchar2(100) := 'compressed_backup.zip';
begin
v_file_dir := 'MYDIR';
v_utl_filehandler := UTL_FILE.FOPEN(v_file_dir, v_file_name, 'R',32767);
UTL_FILE.fgetattr(v_file_dir, v_file_name, v_boolean, v_size, v_block);
UTL_FILE.get_raw(v_utl_filehandler, v_rawfile, v_size);
UTL_FILE.FCLOSE(v_utl_filehandler);
SELECT STATUS INTO vstatus FROM v$rman_backup_job_details WHERE start_time > sysdate-1;
for rec in (SELECT to_char(START_TIME,'DAY DD/MON/YYYY HH:MI:SS AM') START_TIME,to_char(END_TIME,'DAY DD/MON/YYYY HH:MI:SS AM') END_TIME,OUTPUT_DEVICE_TYPE,STATUS,INPUT_TYPE,
OUTPUT_BYTES_DISPLAY, TIME_TAKEN_DISPLAY
FROM v$rman_backup_job_details
WHERE start_time > sysdate-1 ORDER BY START_TIME DESC)
loop
vmailMessage := vmailMessage || '
<table border=1 bgcolor=AQUA>
<tr>
<th>START_TIME</th>
<th>END_TIME</th>
<th>DEVICE_TYPE</th>
<th>STATUS</th>
<th>INPUT_TYPE</th>
<th>BACKUP_SIZE_ON_ASM_n_DISK</th>
<th>TOTAL_TIME_TAKEN</th>
</tr>
<tr>
<td>'||rec.START_TIME||'</td>
<td>'||rec.END_TIME||'</td>
<td>'||rec.OUTPUT_DEVICE_TYPE||'</td>
<td>'||rec.STATUS||'</td>
<td>'||rec.INPUT_TYPE||'</td>
<td>'||rec.OUTPUT_BYTES_DISPLAY||'</td>
<td>'||rec.TIME_TAKEN_DISPLAY||'</td>
</tr>
</table>';
end loop;
utl_mail.send_attach_raw( sender =>'samiora@gmail.com'
, recipients => vsendto
, cc => null
, bcc => null
, subject => vstatus || ' SUCCESSFUL : RMAN BACKUP'
, message => '<b><font color=red>RMAN Full Database Backup status</font> <br><br> ' || vmailMessage
, attachment => v_rawfile
, att_inline => FALSE
, att_filename => v_file_name
, mime_type => 'text/html; charset=us-ascii'
);
end;
/
Create the below directory as used in the above procedure,
CREATE DIRECTORY MYDIR AS '/u01/backupdisk/backupset/';
GRANT READ,WRITE ON DIRECTORY MYDIR TO PUBLIC;
5. Add the cronjob to execute everyday at 7:11PM.
11 19 * * * /home/sami/scripts/compressed_backup.sh > /home/sami/scripts/compressed_backup.log 2>&1
If you have any questions on this thread then please email me on samiappsdba@gmail.com
