Friday, January 31, 2014

RMAN Backup Email notification setup

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