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 NO_OPTIONS   = 0;

 

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 -

 

--    

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 echo 

off;

 

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 指定的，可以生成其他名称。