Oracle PL/SQL send mail with attached File using UTL_SMTP

CREATE OR REPLACE PROCEDURE send_mail_with_attach_file

          (p_from        IN VARCHAR2,

           p_to          IN VARCHAR2,           

           p_subject     IN VARCHAR2,

           p_text_msg    IN VARCHAR2 DEFAULT NULL,

           p_attach_name IN VARCHAR2 DEFAULT NULL,

           p_attach_mime IN VARCHAR2 DEFAULT NULL,

           p_attach_blob IN BLOB DEFAULT NULL,

           p_directory IN VARCHAR2,

           p_file_name      IN VARCHAR2           

           )

AS

  v_mail_conn         UTL_SMTP.connection;

  v_boundary          VARCHAR2(50) := '----=abc1234321cba=';

  v_step              PLS_INTEGER  := 57;

  -------------------------------------

  --Put here real IP!

  -------------------------------------

  v_smtp_server       CONSTANT VARCHAR2(30) := '10.20.30.40'; 

  v_smtp_server_port  CONSTANT INTEGER := 25;

  

  --File Reading  

  c_max_line_width CONSTANT PLS_INTEGER DEFAULT 54;

  v_amt            BINARY_INTEGER := 672 * 3; /* ensures proper format; 2016 */

  v_bfile          BFILE;

  v_file_length    PLS_INTEGER;

  v_buf            RAW(2100);

  v_modulo         PLS_INTEGER;

  v_pieces         PLS_INTEGER;

  v_file_pos       pls_integer := 1;

  v_to_list        VARCHAR2(1000);

  v_to             VARCHAR2(1000);

  

BEGIN

  v_mail_conn := UTL_SMTP.open_connection(v_smtp_server, v_smtp_server_port);

  UTL_SMTP.helo(v_mail_conn, v_smtp_server);

  UTL_SMTP.mail(v_mail_conn, p_from);

  

  v_to_list := p_to;

  WHILE (INSTR(v_to_list, ',') > 0) LOOP    

    --get first from list

    v_to := SUBSTR(v_to_list, 1, INSTR(v_to_list,',')-1);

    --get remaining of list

    v_to_list := SUBSTR(v_to_list, INSTR(v_to_list, ',')+1);

    --call rcpt for mail

    UTL_SMTP.rcpt(v_mail_conn, v_to);    

  END LOOP;

  --last element does not have trailing ','

  UTL_SMTP.rcpt(v_mail_conn, v_to_list);

  

  UTL_SMTP.open_data(v_mail_conn);

  UTL_SMTP.write_data(v_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);

  UTL_SMTP.write_data(v_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);

  UTL_SMTP.write_data(v_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);

  UTL_SMTP.write_data(v_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);

  UTL_SMTP.write_data(v_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf);

  UTL_SMTP.write_data(v_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);

  UTL_SMTP.write_data(v_mail_conn, 'Content-Type: multipart/mixed; boundary="' || v_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

  IF p_text_msg IS NOT NULL THEN

    UTL_SMTP.write_data(v_mail_conn, '--' || v_boundary || UTL_TCP.crlf);

    UTL_SMTP.write_data(v_mail_conn, 'Content-Type: text/plain; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);

    UTL_SMTP.write_data(v_mail_conn, p_text_msg);

    UTL_SMTP.write_data(v_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);

  END IF;

  IF p_attach_name IS NOT NULL THEN

    UTL_SMTP.write_data(v_mail_conn, '--' || v_boundary || UTL_TCP.crlf);

    UTL_SMTP.write_data(v_mail_conn, 'Content-Type: ' || p_attach_mime || '; name="' || p_attach_name || '"' || UTL_TCP.crlf);

    UTL_SMTP.write_data(v_mail_conn, 'Content-Transfer-Encoding: base64' || UTL_TCP.crlf);

    UTL_SMTP.write_data(v_mail_conn, 'Content-Disposition: attachment; filename="' || p_attach_name || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

    --Read blob

    IF p_attach_blob IS NOT NULL THEN

      FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_attach_blob) - 1 )/v_step) LOOP

        UTL_SMTP.write_data(v_mail_conn, UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(p_attach_blob, v_step, i * v_step + 1))) || UTL_TCP.crlf);

      END LOOP;

    ELSIF p_directory IS NOT NULL AND p_file_name IS NOT NULL THEN

      --Read File  

      v_bfile := BFILENAME(p_directory, p_file_name);

      -- Get the size of the file to be attached

      v_file_length := DBMS_LOB.GETLENGTH(v_bfile);

      -- Calculate the number of pieces the file will be split up into

      v_pieces := TRUNC(v_file_length / v_amt);

      -- Calculate the remainder after dividing the file into v_amt chunks

      v_modulo := MOD(v_file_length, v_amt);

      IF (v_modulo <> 0) THEN

        v_pieces := v_pieces + 1;

      END IF;

      DBMS_LOB.FILEOPEN(v_bfile, DBMS_LOB.FILE_READONLY);

      --FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_attach_blob) - 1 )/v_step) LOOP

      FOR i IN 1 .. v_pieces LOOP

        v_buf := NULL;

        DBMS_LOB.READ(v_bfile, v_amt, v_file_pos, v_buf);

        v_file_pos := I * v_amt + 1;

        UTL_SMTP.write_data(v_mail_conn, UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(v_buf))|| UTL_TCP.crlf);

        --Exxample

        --UTL_SMTP.write_data(v_mail_conn, UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(p_attach_blob, v_step, i * v_step + 1))) || UTL_TCP.crlf);

      END LOOP;

      

      DBMS_LOB.FILECLOSE(v_bfile);

      

    END IF;

  

    UTL_SMTP.write_data(v_mail_conn, UTL_TCP.crlf);

  END IF;

  UTL_SMTP.write_data(v_mail_conn, '--' || v_boundary || '--' || UTL_TCP.crlf);

  UTL_SMTP.close_data(v_mail_conn);

  UTL_SMTP.quit(v_mail_conn);

END send_mail_with_attach_file;

/

SYSAUX tablespace is full with Autostats Advisor related objects

==========

Issue:
==========

SYSAUX tablespace is full with Autostats Advisor related objects

==========

Solution:

==========

Clean up old tasks

Check Current Status
col TASK_NAME format a25
col parameter_name format a35
col parameter_value format a20
set lines 120
SELECT task_name,

       parameter_name, 

       parameter_value 

 FROM DBA_ADVISOR_PARAMETERS 

WHERE task_name='AUTO_STATS_ADVISOR_TASK' 

  AND parameter_name='EXECUTION_DAYS_TO_EXPIRE';

TASK_NAME                 PARAMETER_NAME           PARAMETER_VALUE
------------------------- ------------------------ ------------------
AUTO_STATS_ADVISOR_TASK   EXECUTION_DAYS_TO_EXPIRE UNLIMITED

Set limit for old tasks
BEGIN
  DBMS_ADVISOR.SET_TASK_PARAMETER(

     task_name=> 'AUTO_STATS_ADVISOR_TASK',                                parameter=> 'EXECUTION_DAYS_TO_EXPIRE', 
     value => 30);
END;
/

Check Status again

SELECT task_name,

       parameter_name, 

       parameter_value 

 FROM DBA_ADVISOR_PARAMETERS 

WHERE task_name='AUTO_STATS_ADVISOR_TASK' 

  AND parameter_name='EXECUTION_DAYS_TO_EXPIRE';

TASK_NAME                 PARAMETER_NAME           PARAMETER_VALUE
------------------------- ------------------------ ------------------
AUTO_STATS_ADVISOR_TASK   EXECUTION_DAYS_TO_EXPIRE 30

Check oldest task date

SELECT MIN(execution_start) 

FROM DBA_ADVISOR_EXECUTIONS WHERE task_name='AUTO_STATS_ADVISOR_TASK';

MIN(EXECUTION_STAR
------------------
27-AUG-19

Purge the expired tasks. 

This step might take time

BEGIN
  PRVT_ADVISOR.delete_expired_tasks;
END;
/

Check status in DBA_ADVISOR_EXECUTIONS 

SELECT task_id, task_name, execution_name, execution_start

  FROM DBA_ADVISOR_EXECUTIONS

 WHERE task_name='AUTO_STATS_ADVISOR_TASK'

 ORDER BY execution_start;

scp backup files to backup mng server

General

On oracle server
RMAN script running at 03:00

expdp script running at 02:00

scp scripts running at 05:00 and 05:30

On backup server
Clean old backups script running at 01:00

scripts on oracle server

00 02 * * * /etc/sh/backup/export_all_instances.sh
00 03 * * * /etc/sh/backup/jobs/run_rman_backup_cron.sh igt
00 05 * * * /home/user/scripts/send_to_mng.sh
30 05 * * * /home/user/scripts/send_to_mng_exp.sh

/home/user/scripts/send_to_mng.sh

#!/bin/bash

RUN_DATE=`date +"%Y%m%d"`

DIR_PREFIX=$RUN_DATE

REMOTE_USER=user

REMOTE_SERVER=mng_server-01

REMOTE_PATH=/backup/ipn_backup/ora_online

LOCAL_DIR=/backup/ora_online/for_backup

KEEP_DAYS=10

is_active_server=`ps -ef | grep ora_pmon_igt | grep -v grep | wc -l`

if [[ $is_active_server -eq 0 ]]; then

 echo "Oracle is not running on this server" 

 exit 0

fi

cd ${LOCAL_DIR}

last_backup_dir=`ls -ltr | grep ${DIR_PREFIX} | tail -1 | awk '{print $9}'`

if [[ -d $last_backup_dir ]]; then

 echo "tar -cvf ${last_backup_dir}.tar ${last_backup_dir}"

 tar -cvf ${last_backup_dir}.tar ${last_backup_dir}

 echo "scp -p  ${LOCAL_DIR}/${last_backup_dir}.tar ${REMOTE_USER}@${REMOTE_SERVER}:${REMOTE_PATH}/${last_backup_dir}.tar"

 scp -p  ${LOCAL_DIR}/${last_backup_dir}.tar ${REMOTE_USER}@${REMOTE_SERVER}:${REMOTE_PATH}/${last_backup_dir}.tar

 rm -f ${LOCAL_DIR}/${last_backup_dir}.tar

else

 echo "No Archive Directory to Send to mng"

fi

/home/user/scripts/send_to_mng_exp.sh

#!/bin/bash

RUN_DATE=`date +"%Y%m%d"`

FILE_PREFIX=export_igt

FILE_SUFFIX=dmp

FILE_SUFFIX_LOG=log

REMOTE_USER=user

REMOTE_SERVER=mng_server-01

REMOTE_PATH=/backup/ipn_backup/exp_backup

LOCAL_DIR=/backup/ora_exp/for_backup

LOCAL_LOG_DIR=/backup/ora_exp/for_backup/old_log

cd ${LOCAL_DIR}

last_backup_file=`ls -ltr | grep ${FILE_PREFIX} | grep ${FILE_SUFFIX} | tail -1 | awk '{print $9}'`

if [[ -f $last_backup_file ]]; then 

 #echo "scp ${REMOTE_USER}@${REMOTE_SERVER}:${REMOTE_PATH}/${last_backup_file} ${LOCAL_DIR}/${last_backup_file}"

 scp ${LOCAL_DIR}/${last_backup_file} ${REMOTE_USER}@${REMOTE_SERVER}:${REMOTE_PATH}/${last_backup_file} 

else

 echo "No export dmp file to send to mng"

fi

log_files=`ls -1 ${LOCAL_DIR}/${FILE_PREFIX}*${FILE_SUFFIX_LOG} 2>/dev/null | wc -l`

if [[ $log_files -gt 0 ]]; then

 mv -f ${LOCAL_DIR}/${FILE_PREFIX}*${FILE_SUFFIX_LOG} ${LOCAL_LOG_DIR}

fi

KEEP_BACKUPS=1

cd ${LOCAL_DIR}

#echo "expdp dmp files: ls -1 ${FILE_PREFIX}*${FILE_SUFFIX} | wc -l"

backups_number=`ls -1 ${FILE_PREFIX}*${FILE_SUFFIX} | wc -l`

#echo "backups_number = $backups_number"

while [[ $backups_number -gt $KEEP_BACKUPS ]]; do

 backup_name=`ls -ltr ${FILE_PREFIX}*${FILE_SUFFIX} | head -1 | awk '{print $9}'`

 #echo "rm -f $backup_name"

 rm -f $backup_name

 backups_number=`ls -1 ${FILE_PREFIX}*${FILE_SUFFIX} | wc -l`

done

scripts on mng server

00 01 * * * /home/user/scripts/del_old_backups.sh

/home/user/scripts/del_old_backups.sh

#!/bin/bash

LOCAL_DIR=/backup/ipn_backup/ora_online

RUN_DATE=`date +"%Y%m%d"`

BACKUP_SUFFIX=tar

KEEP_BACKUPS=4

cd ${LOCAL_DIR}

backups_number=`ls -1 *${BACKUP_SUFFIX} | wc -l`

while [[ $backups_number -gt $KEEP_BACKUPS ]]; do

 backup_name=`ls -ltr | grep ${BACKUP_SUFFIX} | head -1 | awk '{print $9}'`

 rm -f $backup_name

 backups_number=`ls -1 *${BACKUP_SUFFIX} | wc -l`

done

LOCAL_DIR=/backup/ipn_backup/exp_backup

FILE_PREFIX=export_igt

BACKUP_SUFFIX=dmp

KEEP_BACKUPS=2

cd ${LOCAL_DIR}

backups_number=`ls -1 ${FILE_PREFIX}*${BACKUP_SUFFIX} | wc -l`

while [[ $backups_number -gt $KEEP_BACKUPS ]]; do

 backup_name=`ls -ltr | grep ${FILE_PREFIX} | grep ${BACKUP_SUFFIX} | head -1 | awk '{print $9}'`

 rm -f $backup_name

 backups_number=`ls -1 ${FILE_PREFIX}*${BACKUP_SUFFIX} | wc -l`

done