转 awr自动收集脚本
1. remote get awr report
#!/usr/bin/ksh
####sample: sh awr.sh 20170515 20170516 AWR
### default it will collect every 30 or every hours awr report .
####define remote connect database
conn="sqlplus -s system/oracle123@//192.168.4.128:15022/einvprod"
##############paramter######################
startdate=$1' 00:00:01'
enddate=$2' 23:59:59'
reporttype=$3
reportformat='text'
###################env######################
oraclehome=`echo $ORACLE_HOME`
#############################################
dbname=`$conn <<EOF
set head off
set echo off
set feedback off
set linesize 120 ;
set pagesize 0;
set heading off;
select distinct name from gv\\$database;
quit;
EOF
`
dbid=`$conn <<EOF
set head off
set echo off
set feedback off
set linesize 120 ;
set pagesize 0;
set heading off;
select distinct dbid from gv\\$database;
quit;
EOF
`
instid=`$conn <<EOF
set head off
set echo off
set feedback off
set linesize 120 ;
set pagesize 0;
set heading off;
select instance_number from gv\\$instance;
quit;
EOF
`
instname=`$conn <<EOF
set head off
set echo off
set feedback off
set linesize 120 ;
set pagesize 0;
set heading off;
select instance_name from gv\\$instance;
quit;
EOF
`
for i in $instid
do
instanceid=`echo $i|tr -d ' '`
echo "from database-->"$dbname" database id--->"$dbid" instance id--->"$instanceid
echo "report type--->"$reporttype"|"$reporttype" snapshot from--->"$startdate" to--->"$enddate
echo $instanceid
if [ $reporttype = 'AWR' ];
then
export NLS_LANG=american_america.AL32UTF8
echo "generate the awr report sql...."
$conn <<EOF >awrreport.sql
set head off
set echo off
set feedback off
set linesize 120 ;
set pagesize 0;
set heading off;
select * from (
select snap_id as snaped,
lag(snap_id, 1) OVER(ORDER BY snap_id) as snapst,
to_char(end_interval_time, 'yyyymmddhh24miss') as snaped1,
lag(to_char(end_interval_time, 'yyyymmddhh24miss'), 1) OVER(ORDER BY to_char(end_interval_time, 'yyyymmddhh24miss')) as snaped2
from dba_hist_snapshot a
where a.begin_interval_time >=to_date('$startdate', 'yyyy-mm-dd hh24:mi:ss')
and a.begin_interval_time <=to_date('$enddate', 'yyyy-mm-dd hh24:mi:ss')
--and rtrim(ltrim(to_char(a.begin_interval_time,'day','NLS_DATE_LANGUAGE=AMERICAN'))) not in ('saturday','sunday')
--and to_char(a.begin_interval_time,'HH24')>=0
--and to_char(a.end_interval_time,'HH24') <=23
and a.DBID='$dbid'
and a.INSTANCE_NUMBER='$instanceid'
) where snapst is not null AND substr(snaped1,1,8)=substr(snaped2,1,8)
order by snapst;
quit;
EOF
export NLS_LANG=american_america.AL32UTF8
cat awrreport.sql | while read line
do
endd=`echo $line | awk ' { print $1 } '`
startd=`echo $line | awk ' { print $2 } '`
endt=`echo $line | awk ' { print $3 } '`
startt=`echo $line | awk ' { print $4 } '`
instid=`echo $instanceid`
awrrp="awrrpt_"$startt"_"$endt"_"$instid".txt"
$conn <<EOF
@?/rdbms/admin/awrrpti.sql;
$reportformat
$dbid
$instanceid
1
$startd
$endd
$awrrp
quit;
EOF
done
echo "generate the node"$instid "report finish,please check..."
else
echo "error!!!please check the input parameters..."
fi
done
tar cvf awr.tar *.txt
rm *.txt
2. local get awr report
#!/usr/bin/ksh
##############paramter######################
startdate=$1' 00:00:01'
enddate=$2' 23:59:59'
reporttype=$3
reportformat='text'
###################env######################
oraclehome=`echo $ORACLE_HOME`
#############################################
dbname=`sqlplus -s "/ as sysdba" <<EOF
set head off
set echo off
set feedback off
set linesize 120 ;
set pagesize 0;
set heading off;
select distinct name from gv\\$database;
quit;
EOF
`
dbid=`sqlplus -s "/ as sysdba" <<EOF
set head off
set echo off
set feedback off
set linesize 120 ;
set pagesize 0;
set heading off;
select distinct dbid from gv\\$database;
quit;
EOF
`
instid=`sqlplus -s "/ as sysdba" <<EOF
set head off
set echo off
set feedback off
set linesize 120 ;
set pagesize 0;
set heading off;
select instance_number from gv\\$instance;
quit;
EOF
`
instname=`sqlplus -s "/ as sysdba" <<EOF
set head off
set echo off
set feedback off
set linesize 120 ;
set pagesize 0;
set heading off;
select instance_name from gv\\$instance;
quit;
EOF
`
for i in $instid
do
instanceid=`echo $i|tr -d ' '`
echo "from database-->"$dbname" database id--->"$dbid" instance id--->"$instanceid
echo "report type--->"$reporttype"|"$reporttype" snapshot from--->"$startdate" to--->"$enddate
echo $instanceid
if [ $reporttype = 'AWR' ];
then
export NLS_LANG=american_america.AL32UTF8
echo "generate the awr report sql...."
$ORACLE_HOME/bin/sqlplus -s "/ as sysdba" <<EOF >awrreport.sql
set head off
set echo off
set feedback off
set linesize 120 ;
set pagesize 0;
set heading off;
select * from (
select snap_id as snaped,
lag(snap_id, 1) OVER(ORDER BY snap_id) as snapst,
to_char(end_interval_time, 'yyyymmddhh24miss') as snaped1,
lag(to_char(end_interval_time, 'yyyymmddhh24miss'), 1) OVER(ORDER BY to_char(end_interval_time, 'yyyymmddhh24miss')) as snaped2
from dba_hist_snapshot a
where a.begin_interval_time >=to_date('$startdate', 'yyyy-mm-dd hh24:mi:ss')
and a.begin_interval_time <=to_date('$enddate', 'yyyy-mm-dd hh24:mi:ss')
--and rtrim(ltrim(to_char(a.begin_interval_time,'day','NLS_DATE_LANGUAGE=AMERICAN'))) not in ('saturday','sunday')
--and to_char(a.begin_interval_time,'HH24')>=0
--and to_char(a.end_interval_time,'HH24') <=23
and a.DBID='$dbid'
and a.INSTANCE_NUMBER='$instanceid'
) where snapst is not null AND substr(snaped1,1,8)=substr(snaped2,1,8)
order by snapst;
quit;
EOF
export NLS_LANG=american_america.AL32UTF8
cat awrreport.sql | while read line
do
endd=`echo $line | awk ' { print $1 } '`
startd=`echo $line | awk ' { print $2 } '`
endt=`echo $line | awk ' { print $3 } '`
startt=`echo $line | awk ' { print $4 } '`
instid=`echo $instanceid`
awrrp="awrrpt_"$startt"_"$endt"_"$instid".txt"
$ORACLE_HOME/bin/sqlplus -s "/ as sysdba" <<EOF
@?/rdbms/admin/awrrpti.sql;
$reportformat
$dbid
$instanceid
1
$startd
$endd
$awrrp
quit;
EOF
done
echo "generate the node"$instid "report finish,please check..."
else
echo "error!!!please check the input parameters..."
fi
done
tar cvf awr.tar *.txt
3.collect every day
1. remote get awr report
#!/usr/bin/ksh
####sample: sh awr.sh 20170515 20170516 AWR
### default it will collect every day report .
####define remote connect database
conn="sqlplus -s system/oracle123@//192.168.4.128:15022/einvprod"
##############paramter######################
startdate=$1' 00:00:01'
enddate=$2' 23:59:59'
reporttype=$3
reportformat='text'
###################env######################
oraclehome=`echo $ORACLE_HOME`
#############################################
dbname=`$conn <<EOF
set head off
set echo off
set feedback off
set linesize 120 ;
set pagesize 0;
set heading off;
select distinct name from gv\\$database;
quit;
EOF
`
dbid=`$conn <<EOF
set head off
set echo off
set feedback off
set linesize 120 ;
set pagesize 0;
set heading off;
select distinct dbid from gv\\$database;
quit;
EOF
`
instid=`$conn <<EOF
set head off
set echo off
set feedback off
set linesize 120 ;
set pagesize 0;
set heading off;
select instance_number from gv\\$instance;
quit;
EOF
`
instname=`$conn <<EOF
set head off
set echo off
set feedback off
set linesize 120 ;
set pagesize 0;
set heading off;
select instance_name from gv\\$instance;
quit;
EOF
`
for i in $instid
do
instanceid=`echo $i|tr -d ' '`
echo "from database-->"$dbname" database id--->"$dbid" instance id--->"$instanceid
echo "report type--->"$reporttype"|"$reporttype" snapshot from--->"$startdate" to--->"$enddate
echo $instanceid
if [ $reporttype = 'AWR' ];
then
export NLS_LANG=american_america.AL32UTF8
echo "generate the awr report sql...."
$conn <<EOF >awrreport.sql
set head off
set echo off
set feedback off
set linesize 120 ;
set pagesize 0;
set heading off;
select * from (
select snap_id as snaped,
lag(snap_id, 1) OVER(ORDER BY snap_id) as snapst,
to_char(end_interval_time, 'yyyymmddhh24miss') as snaped1,
lag(to_char(end_interval_time, 'yyyymmddhh24miss'),1) OVER(ORDER BY to_char(end_interval_time, 'yyyymmddhh24miss')) as snaped2
from dba_hist_snapshot a
where a.begin_interval_time >=to_date('2017-09-04 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and a.begin_interval_time <=to_date('2017-09-05 23:59:00', 'yyyy-mm-dd hh24:mi:ss')
and to_char(a.begin_interval_time, 'yyyymmddhh24miss') like '%0000__' --add use for check begin time
--and rtrim(ltrim(to_char(a.begin_interval_time,'day','NLS_DATE_LANGUAGE=AMERICAN'))) not in ('saturday','sunday')
--and to_char(a.begin_interval_time,'HH24')>=0
--and to_char(a.end_interval_time,'HH24') <=23
and a.DBID='$dbid'
and a.INSTANCE_NUMBER='$instanceid'
) where snapst is not null
--AND substr(snaped1,1,8)=substr(snaped2,1,8)
order by snapst;
quit;
EOF
export NLS_LANG=american_america.AL32UTF8
cat awrreport.sql | while read line
do
endd=`echo $line | awk ' { print $1 } '`
startd=`echo $line | awk ' { print $2 } '`
endt=`echo $line | awk ' { print $3 } '`
startt=`echo $line | awk ' { print $4 } '`
instid=`echo $instanceid`
awrrp="awrrpt_"$startt"_"$endt"_"$instid".txt"
$conn <<EOF
@?/rdbms/admin/awrrpti.sql;
$reportformat
$dbid
$instanceid
1
$startd
$endd
$awrrp
quit;
EOF
done
echo "generate the node"$instid "report finish,please check..."
else
echo "error!!!please check the input parameters..."
fi
done
tar cvf awr.tar *.txt
rm *.txt
2.般我们都是条用awrrpt.sql 来创建我们的AWR报告。
我们先看下这个脚本的具体内容：
[oracle@rac1 admin]$ cat awrrpt.sql | grep -v 'Rem'|grep -v '^--'
set echo off heading on underline
on;
column inst_num heading "Inst Num" new_value inst_num format 99999;
column inst_name heading
"Instance" new_value inst_name format
a12;
column db_name heading "DB Name" new_value db_name format a12;
column dbid heading "DB Id" new_value dbid format 9999999999 just
c;
prompt
prompt Current
Instance
prompt
~~~~~~~~~~~~~~~~
select d.dbid dbid
, d.name db_name
, i.instance_number
inst_num
, i.instance_name inst_name
from v$database d,
v$instance i;
@@awrrpti
undefine num_days;
undefine report_type;
undefine report_name;
undefine begin_snap;
undefine end_snap;
在以上的脚本里，我们发现它只是生成了一些变量，然后把这些变量传给了另一个脚本：awrrpti.sql。 我们看下awrrpti.sql 脚本的具体内容：
[oracle@rac1 admin]$ cat awrrpti.sql | grep -v 'Rem'|grep -v '^--'
set echo off;
set veri off;
set feedback off;
variable rpt_options
number;
define ENABLE_ADDM = 8;
begin
:rpt_options :=
&NO_OPTIONS;
end;
/
prompt
prompt Specify the Report
Type
prompt
~~~~~~~~~~~~~~~~~~~~~~~
prompt Would you like an HTML report,
or a plain text report?
prompt Enter 'html' for an HTML report,
or 'text' for plain text
prompt
Defaults to 'html'
column report_type new_value
report_type;
set heading off;
select 'Type Specified:
',lower(nvl('&&report_type','html')) report_type from
dual;
set heading on;
set termout off;
column ext new_value
ext;
select '.html' ext from dual where
lower('&&report_type') <> 'text';
select '.txt' ext from dual where
lower('&&report_type') = 'text';
set termout on;
@@awrinput.sql
-- 这个脚本主要是确定SNAP的。
@@awrinpnm.sql 'awrrpt_'
&&ext
-- 这个脚本主要是确定AWR 文件名称的
set termout off;
column fn_name new_value fn_name
noprint;
select 'awr_report_text' fn_name from
dual where lower('&report_type') = 'text';
select 'awr_report_html' fn_name from
dual where lower('&report_type') <> 'text';
column lnsz new_value lnsz
noprint;
select '80' lnsz from dual where
lower('&report_type') = 'text';
select '1500' lnsz from dual where
lower('&report_type') <> 'text';
set linesize
&lnsz;
set termout on;
spool
&report_name;
select output from
table(dbms_workload_repository.&fn_name(
:dbid,
:inst_num,
:bid,
:eid,
:rpt_options
));
spool off;
prompt Report written to
&report_name.
set termout off;
clear columns sql;
ttitle off;
btitle off;
repfooter off;
set linesize 78 termout on feedback 6
heading on;
undefine report_name
undefine report_type
undefine ext
undefine fn_name
undefine lnsz
undefine NO_OPTIONS
undefine ENABLE_ADDM
undefine top_n_events
undefine num_days
undefine top_n_sql
undefine top_pct_sql
undefine
sh_mem_threshold
undefine
top_n_segstat
whenever sqlerror
continue;
[oracle@rac1 admin]$
这个脚本才是我们真正生成AWR的脚本。
在这个脚本里面，提示我们选择AWR报告的类型。
通过上面的2个脚本，我们将AWR报告简化一下：
select output from
table(dbms_workload_repository.&fn_name(:dbid,
:inst_num,:bid, :eid,:rpt_options ));
这条语句就是整个AWR报告的核心：
（1）&fn_name ：决定AWR报告的类型，有2个值：awr_report_html和awr_report_text。
（2）dbid，inst_num,bid,eid 可以通过dba_hist_snapshot查询. bid 指的是begin snap_id, eid 指的是end snap_id.
SQL> select * from (select
snap_id,dbid,instance_number from dba_hist_snapshot order by snap_id) where
rownum<10;
SNAP_ID DBID
INSTANCE_NUMBER
---------- ----------
---------------
184
809910293
2
184
809910293
1
185
809910293
2
185
809910293
1
186
809910293
2
186
809910293
1
187
809910293
2
187
809910293
1
188
809910293
2
9 rows selected.
我这里是个RAC 环境，
通过这个可以看出在每个节点上都保存着AWR的信息。
（3）rpt_options：该参数控制是否显示ADDM的。
--
No options. Setting this will not show the ADDM
--
specific portions of the report.
-- This is the
default setting.
--
-- ENABLE_ADDM
-
--
Show the ADDM specific portions of the report.
--
These sections include the Buffer Pool Advice,
--
Shared Pool Advice, PGA Target Advice, and
--
Wait Class sections.
define NO_OPTIONS = 0;
define ENABLE_ADDM = 8;
有了上面的数据之后，我们就可以使用如下SQL直接生成AWR报告了。
SQL>select output from
table(dbms_workload_repository.awr_report_html(809910293,
2,220,230,0));
SQL>select output from
table(dbms_workload_repository.awr_report_text(809910293, 2,220,230,0));
二.
生成AWR报告 SQL脚本
以上写了这么多，就是为了一个脚本：myawrrpt.sql. 这个脚本就是自动的去收集信息。
因为如果我们是调用awrrpt.sql的话，需要输入一些参数。
我们修改一下脚本，让它根据我们的需求来收集信息，这样就不用输入参数了。
[oracle@rac1 admin]$ cat myawrrpt.sql
conn / as sysdba;
set veri
off;
set feedback
off;
set termout
on;
set heading
off;
variable rpt_options
number;
define NO_OPTIONS =
0;
define ENABLE_ADDM =
8;
-- according to your needs, the value
can be 'text' or 'html'
define
report_type='html';
begin
:rpt_options :=
&NO_OPTIONS;
end;
/
variable dbid
number;
variable inst_num
number;
variable bid
number;
variable eid
number;
begin
select max(snap_id)-48
into :bid from dba_hist_snapshot;
select max(snap_id) into :eid from
dba_hist_snapshot;
select dbid into :dbid from
v$database;
select instance_number into :inst_num
from v$instance;
end;
/
column ext new_value ext
noprint
column fn_name new_value fn_name
noprint;
column lnsz new_value lnsz
noprint;
--select 'txt' ext from dual where
lower('&report_type') = 'text';
select 'html' ext from dual where
lower('&report_type') = 'html';
--select 'awr_report_text' fn_name from
dual where lower('&report_type') = 'text';
select 'awr_report_html' fn_name from
dual where lower('&report_type') = 'html';
--select '80' lnsz from dual where
lower('&report_type') = 'text';
select '1500' lnsz from dual where
lower('&report_type') = 'html';
set linesize
&lnsz;
-- print the AWR results into the
report_name file using the spool command:
column report_name new_value
report_name noprint;
select 'awr'||'.'||'&ext'
report_name from dual;
set termout
off;
spool
&report_name;
select output from
table(dbms_workload_repository.&fn_name(:dbid, :inst_num,:bid,
:eid,:rpt_options ));
spool
off;
set termout
on;
clear columns
sql;
ttitle
off;
btitle
off;
repfooter
off;
undefine
report_name
undefine
report_type
undefine
fn_name
undefine
lnsz
undefine
NO_OPTIONS
exit
[oracle@rac1 admin]$
http://blog.itpub.net/29065182/viewspace-1126530/
这个脚本是收集过去48个小时的snap 来生成AWR。
生成的文件名称是awr .html，这个也是spool 指定的，可以生成其他名称。
