Shulou(Shulou.com)06/01 Report--

Reproduced from: http://blog.csdn.net/tianlesoftware/article/details/6319198

Oracle's AWR report provides good information about DB performance. So DBA needs to check AWR's reports regularly. For instructions on the AWR report, please refer to:

Oracle AWR introduction

Http://blog.csdn.net/tianlesoftware/archive/2009/10/17/4682300.aspx

If you manually create AWR reports every day, it is a time-consuming task. Therefore, it is best to make a script to execute it. This article is to illustrate this problem.

One issue to note is that there can be no restart of DB between the two snap reported by AWR.

Python executes system commands

Http://blog.csdn.net/tianlesoftware/archive/2011/02/17/6192202.aspx

Python send email with attachments script

Http://blog.csdn.net/tianlesoftware/archive/2011/04/12/6318199.aspx

one。 Preparatory work

Usually we use awrrpt.sql to create our AWR report. Let's take a look at the details of the script:

[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

In the above script, we found that it just generated some variables and then passed them to another script: awrrpti.sql. Let's take a look at the details of the awrrpti.sql script:

[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

This script is mainly used to identify SNAP.

@ @ awrinpnm.sql 'awrrpt_' & & ext

This script mainly determines the name of the AWR file.

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] $

This is the script from which we actually generate AWR. In this script, we are prompted to select the type of AWR report.

With the above two scripts, let's simplify the AWR report:

Select output from

Table (dbms_workload_repository.&fn_name (: dbid,: inst_num,:bid,: eid,:rpt_options)

This statement is at the heart of the entire AWR report:

(1) & fn_name: determines the type of AWR report, with two values: awr_report_html and awr_report_text.

(2) dbid,inst_num,bid,eid can be queried by dba_hist_snapshot. Bid refers to begin snap_id and eid refers to end snap_id.

SQL > select * from (select snap_id,dbid,instance_number from dba_hist_snapshot order by snap_id) where rownumselect output from table (dbms_workload_repository.awr_report_html (809910293, 2, 220, 220, 230))

SQL > select output from table (dbms_workload_repository.awr_report_text (809910293, 2pm, 220pm, 230J 0)

two。 Generate AWR report SQL script

I wrote so much above just for a script: myawrrpt.sql. This script automatically collects information. Because if we are calling awrrpt.sql, we need to enter some parameters. Let's modify the script so that it collects information according to our requirements so that we don't have to enter parameters.

[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] $

This script collects snap from the past 48 hours to generate AWR. The name of the generated file is awr. Html, which is also specified by spool, and other names can be generated.

three。 Python script for automatically uploading AWR

To do two things in this script, the first is to call the SQL script in step 2, generate the awr report, and then send the AWR to the specified mailbox.

CreateSendAWR.py

#! / usr/bin/python

# coding=gbk

# created by tianlesoftware

# 2011-4-12

Import os

Import sys

Import smtplib

Import pickle

Import mimetypes

From email.MIMEText import MIMEText

From email.MIMEImage import MIMEImage

From email.MIMEMultipart import MIMEMultipart

SMTP_SERVER='192.168.1.120'

EMAIL_USER='user'

EMAIL_PASSWD='pwd'

EMAIL_SUBJECT='192.168.88.209 AWR Report'

FROM_USER='daimingming@1876.cn'

TO_USERS= ['daimingming@1876.cn','dvd.dba@gmail.com']

Def createawr ():

Pipe = os.popen ('/ u01/app/oracle/product/10.2.0/db_1/bin/sqlplus / nolog @ awrrpt.sql')

Def mysendmail (fromaddr,toaddrs,subject):

COMMASPACE=','

Msg = MIMEMultipart ()

Msg ['From'] = fromaddr

Msg ['To'] = COMMASPACE.join (toaddrs)

Msg ['Subject'] = subject

Txt = MIMEText ("192.168.88.209 AWR Report, The report be send at 9 AM every day")

Msg.attach (txt)

FileName = rump homebank oracledebase awr.html'

Ctype, encoding = mimetypes.guess_type (fileName)

If ctype is None or encoding is not None:

Ctype = 'application/octet-stream'

Maintype, subtype = ctype.split ('/', 1)

Att = MIMEImage ((lambda f: (f.read (), f.close () (open (fileName, 'rb')) [0], _ subtype = subtype)

Att.add_header ('Content-Disposition',' attachment', filename = fileName)

Msg.attach (att)

Server=smtplib.SMTP (SMTP_SERVER)

Server.login (EMAIL_USER,EMAIL_PASSWD)

Server.sendmail (fromaddr,toaddrs,msg.as_string ())

Server.quit ()

If _ _ name__=='__main__':

Createawr ()

Mysendmail (FROM_USER, TO_USERS, EMAIL_SUBJECT)

# print 'send successful'

four。 Add Python to crontab

[oracle@qs-wg-db1 scripts] $crontab-l

40 17 * export ORACLE_HOME='/home/oracle_app' & & ORACLE_SID=XX & & cd / u01/backup/scripts & & / u01/backup/scripts/createSendAWR.py > / u01/backup/scripts/createSendAWR.log 2 > & 1

I'm here because I signed up

SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

So the variable is added.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.