shell脚本实现打印某一个小时的awr报告

傷城~ 2022-08-06 08:13 96阅读 0赞

用shell脚本实现打印某一个小时的快照，如下：

\#\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
\#  FileName     :               get\_awrrpt.sh  
\#\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
\#  Author       :               wangwei  
\#  CreateDate   :               2014-08-25  
\#  Description  :               this script create a snapshot of the specified   
\#                               time   
\#               
\#\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

\#!/bin/bash  
export ORACLE\_HOME=/oracle/oracle/app/oracle/product/11.2.0/dbhome\_1  
export PATH=$ORACLE\_HOME/bin:$ORACLE\_HOME/OPatch:/sbin:/bin:/usr/sbin:/usr/bin  
export ORACLE\_SID=orcl\_sid  
export LD\_LIBRARY\_PATH=$ORACLE\_HOME/lib:$ORACLE\_HOME/lib32  
export ORACLE\_BASE=/oracle/oracle  
\#export ORA\_NLS10=$ORACLE\_HOME/nls/data  
\#export NLS\_LANG="Simplified Chinese"\_China.AL32UTF8  
export NLS\_LANG=American\_America.AL32UTF8  
export LANG=C  
cd /home/oracle/awrrpt  
v\_time="$1"  
  
sqlplus -s /nolog <<EOF  
connect / as sysdba  
  
set feedback off  
set heading off  
set termout off  
set linesize 10000  
set pagesize 50000  
set trimspool on  
set verify off  
  
column dbid new\_value dbid;  
column inst\_num new\_value inst\_num;  
column bid new\_value bid;  
column eid new\_value eid;  
column file\_name new\_value file\_name;  
  
select \*  from (select a.dbid,  
               a.instance\_number inst\_num,  
               a.startup\_time,  
               a.snap\_id bid,  
               a.snap\_id + 1 eid,  
                'awrrpt\_'||a.instance\_number ||'\_' || to\_number(to\_char(a.end\_interval\_time, 'HH24')) || '\_' ||  
               to\_number(to\_char(a.end\_interval\_time, 'HH24') + 1) ||'.html' file\_name  
          from dba\_hist\_snapshot a  
         where  to\_char(a.end\_interval\_time,'YYYYMMDDHH24')=$v\_time  
         order by bid  
          )  
 where inst\_num = 1  
/  
spool &file\_name  
select output from table(dbms\_workload\_repository.awr\_report\_html(&dbid,&inst\_num,&bid,&eid,8));  
spool off  
  
exit;  
EOF

执行脚本./get\_awrrpt.sh 2015010609 打印9点到10点的快照