Oracle Database Discussions

I have been trying to develop a script for generating ADDM Reports every hour and save it in a directory on the server. I was able to develop a script to run the AWR Reports for every hour and save them in a directory, but I ran into troubled waters in the ADDM script. Could someone please help me out here?

Database Version : Oracle Database Enterprise Edition 10.2.0.3
OS : IBM AIX 5.3

I'm trying to debug the script below to generate ADDM reports on a per hour basis and save them in a folder as well as mail than to a particular entity.

########################################################################################
# Set up Oracle environment variables...
#------------------------------------------------------------------------------
export PATH=/usr/bin:/etc:/usr/sbin:/usr/ucb:/oracle/app/oracle/product/10.2.0/bin:/usr/bin/X11:/sbin:.:/oracle/app/oracle/product/10.2.0/bin
export ORACLE_HOME=/oracle/app/oracle/product/10.2.0
export ORACLE_SID=sarcotest2.ora
export ORAENV_ASK=NO
export currdate=`date +%d_%b_%Y`
export currmon=`date +%b_%Y`

#. /usr/local/bin/oraenv
unset ORAENV_ASK


if [ ! -d /prodbk/SIL_ADDM_Reports/${currmon} ]
then
mkdir -p /prodbk/SIL_ADDM_Reports/${currmon}
echo "Directory \"/prodbk/SIL_ADDM_Reports/${currmon}\" created..."
fi

if [ ! -d /prodbk/SIL_ADDM_Reports/${currmon}/${currdate} ]
then
mkdir -p /prodbk/SIL_ADDM_Reports/${currmon}/${currdate}
echo "Directory \"/prodbk/SIL_ADDM_Reports/${currmon}/${currdate}\" created..."
fi

#
#------------------------------------------------------------------------------
# Verify that the Oracle environment variables and directories are set up...
#------------------------------------------------------------------------------
if [[ "${ORACLE_HOME}" = "" ]]
then
echo "ORACLE_HOME not set; aborting..."
exit 1
fi
if [ ! -d ${ORACLE_HOME} ]
then
echo "Directory \"${ORACLE_HOME}\" not found; aborting..."
exit 1
fi
if [ ! -d ${ORACLE_HOME}/bin ]
then
echo "Directory \"${ORACLE_HOME}/bin\" not found; aborting..."
exit 1
fi
if [ ! -x ${ORACLE_HOME}/bin/sqlplus ]
then
echo "Executable \"${ORACLE_HOME}/bin/sqlplus\" not found; aborting..."
exit 1
fi
if [ ! -x ${ORACLE_HOME}/bin/tnsping ]
then
echo "Executable \"${ORACLE_HOME}/bin/tnsping\" not found; aborting..."
exit 1
fi
#
#------------------------------------------------------------------------------
# Set shell variables used by the shell script...
#------------------------------------------------------------------------------
_Pgm=run_addm
_RunAddmListFile=${HOME}/.run_addm
if [ ! -r ${_RunAddmListFile} ]
then
echo "Script configuration file \"${_RunAddmListFile}\" not found;
aborting..."
exit 1
fi
#
#------------------------------------------------------------------------------
# ...loop through the list of database instances specified in the ".run_addm"
# list file...
#
# Entries in this file have the format:
#
# dbname:rcpt-list:hrs
#
# where:
# dbname - is the TNS connect-string of the database instance
# rcpt-list - is a comma-separated list of email addresses
# hrs - is the number of hours (from the present time)
# marking the starting point of the ADDM report
#------------------------------------------------------------------------------
grep -v "^#" ${_RunAddmListFile} | awk -F: '{print $1" "$2" "$3}' | \
while read _ListDb _ListRcpts _ListHrs
do
#----------------------------------------------------------------------
# If command-line parameters were specified for this script, then they
# must be a list of databases...
#----------------------------------------------------------------------
if (( $# > 0 ))
then
#
#---------------------------------------------------------------
# If a list of databases was specified on the command-line of
# this script, then find that database's entry in the ".run_addm"
# configuration file and retrieve the list of email recipients
# as well as the #-hrs for the addm report...
#---------------------------------------------------------------
_Db=""
_Rcpts=""
_Hrs=""
for _SpecifiedDb in $*
do
#
if [[ "${_ListDb}" = "${_SpecifiedDb}" ]]
then
_Db=${_ListDb}
_Rcpts=${_ListRcpts}
_Hrs=${_ListHrs}
fi
#
done
#
#---------------------------------------------------------------
# if the listed DB is not specified on the command-line, then
# go onto the next listed DB...
#---------------------------------------------------------------
if [[ "${_Db}" = "" ]]
then
continue
fi
#---------------------------------------------------------------
else # ...else, if no command-line parameters were specified, then
# just use the information in the ".run_addm" configuration
#---------------------------------------------------------------
_Db=${_ListDb}
_Rcpts=${_ListRcpts}
_Hrs=${_ListHrs}
#
fi
#
#----------------------------------------------------------------------
# Verify that the name of the database is a valid TNS connect-string...
#----------------------------------------------------------------------
${ORACLE_HOME}/bin/tnsping ${_Db} > /dev/null 2>&1
if (( $? != 0 ))
then
echo "\"tnsping ${_Db}\" failed; aborting..."
exit 1
fi
#
#----------------------------------------------------------------------
# Create script variables for the output files...
#----------------------------------------------------------------------
_TmpSpoolFile="/tmp/${_Pgm}_${_Db}.tmp"
_AddmReportFile="${_Pgm}_${_Db}.txt"
#



#----------------------------------------------------------------------
# Call SQL*Plus, retrieve some database instance information, and then
# call the addm report as specified...
#----------------------------------------------------------------------
${ORACLE_HOME}/bin/sqlplus -s /nolog << __EOF__ > /dev/null 2>&1
set echo off feedback off timing off pagesize 0 linesize 300 trimspool on
verify off heading off
connect omni/omni@${_Db}

col dbid new_value DBID noprint
select dbid from v\$database;

col instance_number new_value inst_num noprint
select instance_number from v\$instance;

col snap_id new_value bid
select max(snap_id)-1 snap_id
from dba_hist_snapshot
where end_interval_time >= (sysdate-(${_Hrs}/24))
and startup_time <= begin_interval_time
and dbid = &&DBID
and instance_number = &&inst_num;

col snap_id new_value eid
select max(snap_id) snap_id
from dba_hist_snapshot
where dbid = &&DBID
and instance_number = &&inst_num;

spool ${_TmpSpoolFile}
select 'BEGIN='||trim(to_char(begin_interval_time, 'HH24:MI')) snap_time
from dba_hist_snapshot
where dbid = &&DBID
and instance_number = &&inst_num
and snap_id = &&bid ;
select 'END='||trim(to_char(end_interval_time, 'HH24:MI')) snap_time
from dba_hist_snapshot
where dbid = &&DBID
and instance_number = &&inst_num
and snap_id = &&eid;
spool off
*-----------------------------------------------------*
* Fine Upto here!*
* -----------------------------------------------------*


Exec dbms_advisor.delete_task('ADDM_UAT');

DECLARE
task_name VARCHAR2(30) := 'ADDM_UAT';
task_desc VARCHAR2(30) := 'ADDM UAT';
BEGIN
dbms_advisor.create_task('ADDM','ADDM_UAT','ADDM UAT', null);
dbms_advisor.set_task_parameter('ADDM_UAT', 'START_SNAPSHOT', :bid);
dbms_advisor.set_task_parameter('ADDM_UAT', 'END_SNAPSHOT', :eid);
dbms_advisor.set_task_parameter('ADDM_UAT', 'INSTANCE', :inst_num);
dbms_advisor.set_task_parameter('ADDM_UAT', 'DB_ID', :DBID );
dbms_advisor.execute_task('ADDM_UAT');
END;
/

spool /tmp/${_AddmReportFile}

set long 1000000 pagesize 0 longchunksize 1000
column get_clob format a80

SELECT DBMS_ADVISOR.GET_TASK_REPORT('ADDM_UAT','TEXT','TYPICAL') FROM dual;

spool off

exit success
__EOF__

#
#----------------------------------------------------------------------
# Determine if the "start time" and "end time" of the addm report was
# spooled out...
#----------------------------------------------------------------------
if [ -f ${_TmpSpoolFile} ]
then
_BTstamp=`grep '^BEGIN=' ${_TmpSpoolFile} | awk -F= '{print $2}'`
_ETstamp=`grep '^END=' ${_TmpSpoolFile} | awk -F= '{print $2}'`
fi
#
#----------------------------------------------------------------------
#
#----------------------------------------------------------------------
# Determine if an addm report was spooled out...
#----------------------------------------------------------------------
if [ -f /tmp/${_AddmReportFile} ]
then
#
uuencode /tmp/${_AddmReportFile} ${_AddmReportFile} | \
mailx -s "ADDM Report for ${_Db} (${_BTstamp}-${_ETstamp} GMT)" ${_Rcpts}
#
fi

bid=`awk 'NR==6 {print $6}' ${_TmpSpoolFile}`
eid=`awk 'NR==13 {print $6}' ${_TmpSpoolFile}`

cp /tmp/${_AddmReportFile} /prodbk/SIL_ADDM_Reports/${currmon}/${currdate}/ADDM_Report_${_Db}_${bid}_${eid}.txt
#
#rm -f /tmp/${_AddmReportFile} ${_TmpSpoolFile}
#
done
#
#------------------------------------------------------------------------------
# Finish up...
#------------------------------------------------------------------------------

exit 0
#################################################################################

Initially, the script did return me an ADDM report, but the problem was that it generated the report for only a set of two snapshots (eg: 410110 and 410111).
later when the snapshots advanced further, it still generated the same report for the same snapshots.

Now, it seems it have made several changes to the script in the declare and dbms_advisor section and the reports are not being generated anyway.

One problem I am facing is

ORA-13605: The specified task or object ADDM_UAT does not exist for the current user.
ORA-06512: at "SYS.PRVT_ADVISOR", line 2043
ORA-06512: at "SYS.DBMS_ADVISOR", line 560
ORA-06512: at line 1

It seems the task is not being generated at all in my user's schema.

Any help would be much appreciated.