Oracle Live

11/04/2016

AWR Get Report

Filed under: Scripts — mogukiller @ 6:43 pm
Tags:

Genera informes awr de entornos RAC. Pudiendo especificar el intervalo de horas, el día o incluso las instancias de las que extraer el informe.
El formato de salida es texto y html.

/*
 Ejemplos:
    ./SCRIPT_AWR_Get_Report.sh -d 09 		#Saca todos los awr del dia 09	
    ./SCRIPT_AWR_Get_Report.sh -h 10-11		#Saca los awr entre las 10 y las 11 del dia en curso.
   ./SCRIPT_AWR_Get_Report.sh -d 01-05 -i 2	#Saca todos los awr entre los dias 01 y 05 de la instancia 2
*/

#!/bin/ksh
#set -x
# -----------------------------------------------------
# Script: SCRIPT_AWR_Get_Report.sh
#
#
# Autor: lmds
# Fecha: 05/06/2014
# -----------------------------------------------------
# Uso:
# 	-d indicas el dia o dias que quieres el awr. -d 09 o -d 09-10 o -d 09-
#	-h horas entre las que quieres el awr. -h 10-12 o -h 10-
#	-i instancia o all
#
# Ejemplos:
#		./SCRIPT_AWR_Get_Report.sh -d 09 		#Saca todos los awr del dia 09	
#		./SCRIPT_AWR_Get_Report.sh -h 10-11		#Saca los awr entre las 10 y las 11 del dia en curso.
#		./SCRIPT_AWR_Get_Report.sh -d 01-05 -i 2	#Saca todos los awr entre los dias 01 y 05 de la instancia 2
#------------------------------------------------------

cd `dirname $0`
dir_path=`pwd`

# Configuracion
ftmp=.tmp.out
list_inst_id=.list_inst_id.out

dir_export="DATA_PUMP_DIR"
dia_inicio=`date +%d`
dia_final=`date +%d`
hora_inicio="00"
hora_final="23"
inst_id="all"

until [ -z "$1" ] 
do
	case "$1" in
		'-d')
			shift
				if [ "`echo $1 | grep '-'`" = "" ]; then
					dia_inicio=$1
					dia_final=$1
				else
					valor=`echo $1 | cut -d'-' -f1`
					[ "$valor" != "" ] && dia_inicio=${valor}
					valor=`echo $1 | cut -d'-' -f2`
					[ "$valor" != "" ] && dia_final=${valor}
				fi
		;;
		'-h')
			shift
				valor=`echo $1 | cut -d'-' -f1`
				[ "$valor" != "" ] && hora_inicio=${valor}
				valor=`echo $1 | cut -d'-' -f2`
				[ "$valor" != "" ] && hora_final=${valor}			
		;;
		'-i')
			shift
				inst_id=$1
	esac
	shift
done

fecha_inicio="${dia_inicio} ${hora_inicio}"
fecha_fin="${dia_final} ${hora_final}"
if [ ${dia_inicio} -gt ${dia_final} ]; then
	sql_text="TO_DATE(TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYY/MM')||'/${fecha_inicio}','YYYY/MM/DD HH24')" 
else
	sql_text="TO_DATE(TO_CHAR(SYSDATE,'YYYY/MM')||'/${fecha_inicio}','YYYY/MM/DD HH24')"
fi
sqlplus -s "/ as sysdba" <<[EOF] >$ftmp
	SET HEADING OFF
	SELECT 'export begin_snap='||MIN(SNAP_ID) FROM DBA_HIST_SNAPSHOT WHERE TRUNC(END_INTERVAL_TIME,'HH24') BETWEEN $sql_text AND TO_DATE(TO_CHAR(SYSDATE,'YYYY/MM')||'/${fecha_fin}','YYYY/MM/DD HH24');
	SELECT 'export end_snap='||MAX(SNAP_ID) FROM DBA_HIST_SNAPSHOT WHERE TRUNC(END_INTERVAL_TIME,'HH24') BETWEEN $sql_text AND TO_DATE(TO_CHAR(SYSDATE,'YYYY/MM')||'/${fecha_fin}','YYYY/MM/DD HH24');
	SELECT 'export path_dir_export='||DIRECTORY_PATH FROM ALL_DIRECTORIES WHERE DIRECTORY_NAME='${dir_export}';
[EOF]

. ./$ftmp

if [ "${inst_id}" = "all" ]; then
	sqlplus -s "/ as sysdba" <<[EOF] >$list_inst_id
		SET HEADING OFF
		SELECT INSTANCE_NUMBER FROM GV\$INSTANCE ORDER BY 1;
	exit;
[EOF]
else
	echo $inst_id >$list_inst_id
fi

while read inst_number
do
sqlplus -s "/ as sysdba" <<[EOF] >$ftmp
DECLARE
        l_snap_start       NUMBER :=${begin_snap};
        l_snap_end         NUMBER :=${end_snap};
        l_dir              VARCHAR2(50) := '${dir_export}';
        l_last_snap        NUMBER := NULL;
        l_last_snap_time        DATE;
        l_dbid             v\$database.dbid%TYPE;
		l_db_name		   v\$database.NAME%TYPE;
        l_instance_number  NUMBER :=${inst_number};
        l_file_html            UTL_FILE.file_type;
        l_file_txt             UTL_FILE.file_type;
        l_file_name        VARCHAR(50);
 BEGIN
   SELECT dbid,NAME   INTO   l_dbid,l_db_name   FROM   v\$database;
     
   FOR cur_snap IN (SELECT snap_id, TO_DATE(TO_CHAR(end_interval_time,'YYYY/MM/DD HH24:MI:SS'),'YYYY/MM/DD HH24:MI:SS') as start_time
                    FROM   dba_hist_snapshot
                    WHERE  instance_number = l_instance_number
                    AND    snap_id BETWEEN l_snap_start AND l_snap_end
                    ORDER BY snap_id)
   LOOP
     IF l_last_snap IS NOT NULL THEN
      l_file_html := UTL_FILE.fopen(l_dir, 'awr_'||l_db_name||'_${inst_number}_'||TO_CHAR(l_last_snap_time,'YYYY_MM_DD')|| '_' ||TO_CHAR(l_last_snap_time,'HH24')||TO_CHAR(l_last_snap_time,'MI')||
          '_' ||TO_CHAR(cur_snap.start_time,'HH24')||TO_CHAR(cur_snap.start_time,'MI')||'.html', 'w', 32767);
          l_file_txt := UTL_FILE.fopen(l_dir, 'awr_'||l_db_name||'_${inst_number}_'||TO_CHAR(l_last_snap_time,'YYYY_MM_DD')|| '_' ||TO_CHAR(l_last_snap_time,'HH24')||TO_CHAR(l_last_snap_time,'MI')||
          '_' ||TO_CHAR(cur_snap.start_time,'HH24')||TO_CHAR(cur_snap.start_time,'MI')||'.txt', 'w', 32767);
       FOR cur_rep IN (SELECT output
                       FROM   TABLE(DBMS_WORKLOAD_REPOSITORY.awr_report_html(l_dbid, l_instance_number, l_last_snap, cur_snap.snap_id)))
       LOOP
         UTL_FILE.put_line(l_file_html, cur_rep.output);
       END LOOP;
       UTL_FILE.fclose(l_file_html);
           
           FOR cur_rep IN (SELECT output
                       FROM   TABLE(DBMS_WORKLOAD_REPOSITORY.awr_report_text(l_dbid, l_instance_number, l_last_snap, cur_snap.snap_id)))
       LOOP
         UTL_FILE.put_line(l_file_txt, cur_rep.output);
       END LOOP;
           UTL_FILE.fclose(l_file_txt); 
           
         END IF;
		l_last_snap := cur_snap.snap_id;
        l_last_snap_time := cur_snap.start_time;
   END LOOP;   
 EXCEPTION
   WHEN OTHERS THEN
     IF UTL_FILE.is_open(l_file_html) THEN
       UTL_FILE.fclose(l_file_html);
     END IF;
         IF UTL_FILE.is_open(l_file_txt) THEN
       UTL_FILE.fclose(l_file_txt);
     END IF;
     RAISE; 
 END;
 /
[EOF]

done <$list_inst_id

sqlplus -s "/ as sysdba" <<[EOF] >$ftmp
        SET HEADING OFF
        SELECT 'export awr_date='||TO_CHAR(sysdate-1,'YYYY_MM') FROM DUAL;
		SELECT 'export db_name='||NAME FROM V\$DATABASE;	
[EOF]

. ./$ftmp
 
cd $path_dir_export 

tar -cvf awr_${db_name}_${awr_date}_HTML.tar awr_${db_name}_*_${awr_date}*.html
[ ! -f awr_${db_name}_${awr_date}_HTML.tar.gz ] && gzip -q awr_${db_name}_${awr_date}_HTML.tar
tar -cvf awr_${db_name}_${awr_date}_TEXT.tar awr_${db_name}_*_${awr_date}*.txt
[ ! -f awr_${db_name}_${awr_date}_TEXT.tar.gz ] && gzip -q awr_${db_name}_${awr_date}_TEXT.tar
rm -f awr_${db_name}_*_${awr_date}*.html awr_${db_name}_*_${awr_date}*.txt

mv awr_${db_name}_${awr_date}_HTML.tar.gz awr_${db_name}_${awr_date}_TEXT.tar.gz $dir_path

Dejar un comentario »

Aún no hay comentarios.

RSS feed for comments on this post. TrackBack URI

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

Blog de WordPress.com.

A %d blogueros les gusta esto: