Use shell to customize awr scripts

Use shell to customize awr scripts

When you diagnose performance problems, awr is an indispensable tool? /Rdbms/admin/awrrpt. SQL may be used too much, and sometimes it may be too cumbersome to input parameters. Copying a snapshot list is not convenient.
For example, if you want to view snapshots from to a day and generate an awr report, you may have to calculate how many days the snapshot takes, it is complicated to copy one by one from a lengthy snapshot list. I always felt a little inconvenient in the process of generating awr. Today I made up my mind to customize it myself.
First, solve the snapshot list problem. Generally, we need to get the following list.
Instance DB Name Snap Id Snap Started Level
--------------------------------------------------------
TEST01 TEST01 1274 19 Oct 2014 1
1275 19 Oct 2014 1
1276 20 Oct 2014 00: 00 1
1277 20 Oct 2014 0:00 1
1278 20 Oct 2014 1

I used the following script for customization. For example, I needed a snapshot from to on July 22, October 19 two days ago, then I can directly use the script to get a simple list of snapshots.
The output result is as follows. Each day is well-off for one hour. In this way, I can simply select which snapshots are needed. View it at any time.

Current Instance
~~~~~~~~~~~~~~~~

DBID DB_NAME INST_NUM INST_NAME
---------------------------------------------
3645037571 TEST01 1 TEST01

DB_NAME SNAP_ID SNAPDAT LVL
-------------------------------------------------------
TEST01 1262 19 Oct 2014 1
1263 19 Oct 2014 1
1264 19 Oct 2014 1
1265 19 Oct 2014 1
1266 19 Oct 2014 1
1267 19 Oct 2014 1
1268 19 Oct 2014 1
1269 19 Oct 2014 1

8 rows selected.

The script content is as follows:

Sqlplus-s $ DB_CONN_STR @ $ SH_DB_SID <EOF
Break on db_name
Set pages 50
Set linesize 100
Prompt
Prompt Current Instance
Prompt ~~~~~~~~~~~~~~~~
Select d. dbid
, D. name db_name
, I. instance_number inst_num
, I. instance_name inst_name
From v \ $ database d,
V \ $ instance I;

Select
Di. db_name
, S. snap_id
, To_char (s. end_interval_time, 'dd Mon YYYY HH24: mi ') snapdat
, S. snap_level lvl
From dba_hist_snapshot s
, Dba_hist_database_instance di
Where
(Di. dbid, di. instance_number) in
(Select d. dbid
, I. instance_number inst_num
From v \ $ database d,
V \ $ instance I)
And di. dbid = s. dbid
And di. instance_number = s. instance_number
And di. startup_time = s. startup_time
And to_char (END_INTERVAL_TIME, 'yyyymmdd') = '$1'
And EXTRACT (hour from END_INTERVAL_TIME) between $2-1 and $3 + 1
Order by db_name, instance_name, snap_id;
EOF

 

After obtaining the snapshot list, it is much easier to get the awr report.
You can use the following script to obtain the awr text report. You only need two snapshot numbers for the input parameter. If you want to get an html report, you 'd better set the linesize to 1500.
Set linesize 80

Print"
Whenever sqlerror exit 5
SET FEEDBACK OFF
SET HEAD OFF
Set pages 0
Connect $ {DB_CONN_STR }@$ {SH_DB_SID} \ n
Select d. dbid | ',' | I. instance_number | ',' | $1 | ',' | $2 | ', 0' text
From v \ $ database d,
V \ $ instance I;
"| Sqlplus-s/nolog> awr_inputs.lst

Sqlplus-s $ {DB_CONN_STR }@$ {SH_DB_SID} <EOF
Spool awrrpt _ $1 _ $ 2.lst
Select output from table (dbms_workload_repository.awr_report_text ('cat awr_inputs.lst '));
Spool off;

EOF

For example, if you want to get an awr report from to, you can directly enter two snapshot numbers.
Ksh genawrtext. sh 1264 1268
> Ls-lrt
-Rw-r -- 1 ora11g dba 258496 Oct 21 awrrpt_1264_1268.lst

Generate and view Oracle AWR reports

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

Steps for generating an Oracle AWR report

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian