在11.2以前,对于RAC数据库只能为多个实例分别生成AWR报告,而11.2中,Oracle终于可以将所有实例AWR报告汇总到一起。
其实实现这个功能很简单,只需要调用11.2的ORACLE_HOME/rdbms/admin/awrgrpt.sql脚本就可以了:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL> select inst_id, instance_number, instance_name, status
2 from gv$instance;
INST_ID INSTANCE_NUMBER INSTANCE_NAME STATUS
---------- --------------- ---------------- ------------
1 1 xshdb1 OPEN
2 2 xshdb2 OPEN
这是一个11.2的RAC环境,下面调用awrgrpt.sql脚本:
SQL> @?/rdbms/admin/awrgrpt
Current Database
~~~~~~~~~~~~~~~~
DB Id DB Name
----------- ------------
678661037 XSHDB
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: text
Type Specified: text
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id INSTT_NUM DB Name INSTT_NAME Host
------------ ---------- ------------ ---------------- ------------
* 678661037 2 XSHDB xshdb2 xsh-server2
* 678661037 1 XSHDB xshdb1 xsh-server1
Using 678661037 for database Id
Using instances ALL (default 'ALL')
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing without
specifying a number lists all completed snapshots.
Enter value for num_days: 1
Listing the last day's Completed Snapshots
Snap
DB Name Snap Id Snap Started Level
------------ --------- ------------------ -----
XSHDB 2991 21 Mar 2011 00:00 1
2992 21 Mar 2011 01:00 1
2993 21 Mar 2011 02:00 1
2994 21 Mar 2011 03:00 1
2995 21 Mar 2011 04:00 1
2996 21 Mar 2011 05:00 1
2997 21 Mar 2011 06:00 1
2998 21 Mar 2011 07:00 1
2999 21 Mar 2011 08:00 1
3000 21 Mar 2011 09:00 1
3001 21 Mar 2011 10:00 1
3002 21 Mar 2011 11:00 1
3003 21 Mar 2011 12:00 1
3004 21 Mar 2011 13:00 1
3005 21 Mar 2011 14:00 1
3006 21 Mar 2011 15:00 1
3007 21 Mar 2011 16:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 3000
Begin Snapshot Id specified: 3000
Enter value for end_snap: 3001
End Snapshot Id specified: 3001
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_rac_3000_3001.txt. To use this name,
press to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name awrrpt_rac_3000_3001.txt
WORKLOAD REPOSITORY REPORT (RAC)
Database Summary
~~~~~~~~~~~~~~~~
Database Snapshot Ids Number of Instances Number of Hosts Report Total (minutes)
------------------------------------ ----------------- ------------------ ------------------ ------------------------
Id Name RAC Block Size Begin End In Report Total In Report Total DB time Elapsed time
----------- --------- --- ---------- -------- -------- --------- -------- --------- -------- ----------- ------------
678661037 XSHDB YES 8192 3000 3001 2 2 2 2 0.09 59.17
Database Instances Included In Report
-> Listed in order of instance number, I#
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg Active
I# Instance Host Startup Begin Snap Time End Snap Time Release Elapsed Time(min) DB time(min) Up Time(hrs) Sessions Platform
---- --------- ---------- --------------- --------------- --------------- --------------- ----------------- ------------ ------------ ---------- ---------------
1 xshdb1 xsh-server 23-Feb-11 16:43 21-Mar-11 09:00 21-Mar-11 10:00 11.2.0.2.0 59.17 0.04 617.27 0.00 Linux x86 64-bi
2 xshdb2 xsh-server 23-Feb-11 16:44 21-Mar-11 09:00 21-Mar-11 10:00 11.2.0.2.0 59.17 0.05 617.26 0.00 Linux x86 64-bi
Cache Sizes
-> All values are in Megabytes
-> Listed in order of instance number, I#
-> End values displayed only if different from Begin values
~~~~~~~~~~~
.
.
.
Interface Sent Received
------------------------------------------ ---------------------------------------------------------- ----------------------------------------------------------
Packets Buffer Carrier Packets Buffer Frame
I# NAME IP Address IP Mask MBytes Packets Errors Dropped Ovrrun Lost MBytes Packets Errors Dropped Ovrrun Errors
---- ---------- --------------- --------------- ------------- ------------ ------- ------- ------- ------- ------------- ------------ ------- ------- ------- -------
1 eth0 10.0.10.53 255.255.255.0 0.1 634 0 0 0 0 0.4 5,67 0 0 0 0
eth0:1 10.0.10.55 255.255.255.0 0.0 0 0 0 0 0 0.0 0 0 0 0
eth0:2 10.0.10.57 255.255.255.0 0.0 0 0 0 0 0 0.0 0 0 0 0
eth2 192.168.0.1 255.255.255.0 96.0 139,526 0 0 0 0 127.7 168,11 0 0 0 0
eth2:1 169.254.88.96 255.255.0.0 0.0 0 0 0 0 0 0.0 0 0 0 0
lo 127.0.0.1 255.0.0.0 32.8 25,685 0 0 0 0 32.8 25,68 0 0 0 0
2 eth0 10.0.10.54 255.255.255.0 0.1 984 0 0 0 0 0.4 5,52 0 0 0 0
eth0:1 10.0.10.56 255.255.255.0 0.0 0 0 0 0 0 0.0 0 0 0 0
eth2 192.168.0.2 255.255.255.0 123.4 161,604 0 0 0 0 91.7 133,31 0 0 0 0
eth2:1 169.254.76.153 255.255.0.0 0.0 0 0 0 0 0 0.0 0 0 0 0
lo 127.0.0.1 255.0.0.0 10.6 23,310 0 0 0 0 10.6 23,31 0 0 0 0
~~~ ~~~~~~~~~~~~~ ~~~~~~~~~~~~ ~~~~~~~ ~~~~~~~ ~~~~~~~ ~~~~~~~ ~~~~~~~~~~~~~ ~~~~~~~~~~~~ ~~~~~~~ ~~~~~~~ ~~~~~~~ ~~~~~~~
Sum 263.0 351,743 0 0 0 0 263.7 361,63 0 0 0 0
Avg 23.9 31,977 0 0 0 0 24.0 32,87 0 0 0 0
Std 44.0 59,616 0 0 0 0 44.3 59,50 0 0 0 0
--------------------------------------------------------------------------------------------------------------------
Interconnect Device Statistics (per Second)DB/Inst: XSHDB/xshdb1 Snaps: 3000-
-> Data is retrieved from underlying Operating system and may overflow on some 32-bit OSs
-> Blank means begin value > end value
.
.
.
--------------------------------------------------------------------------------------------------------------------
End of Report
Report written to awrrpt_rac_3000_3001.txt
从报告中可以看到,两个实例的信息被汇总到了一起,而不在像以前版本那样,各个实例生成自己的报告。
而且在报告中还增加了RAC环境中各个网络接口的统计,这是以前版本所没有的。