sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】Oracle 11gR2中awr 相关脚本的详细说明

时间:2016-12-12 21:42   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle数据库AWR相关脚本的详细说明。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: 11gR2中awr 相关脚本的说明

应群中网友要求,简单总结下11gR2环境中awr的相关脚本,我的11gR2环境里面有如下脚本:

[ora11g@11gR2test admin]$ ls -ltr *awr*.sql
-rw-r--r-- 1 ora11g oinstall   1999 Oct 24  2003 awrrpt.sql
-rw-r--r-- 1 ora11g oinstall  49166 Sep  1  2004 awrinfo.sql
-rw-r--r-- 1 ora11g oinstall   1469 Jan  5  2005 awrsqrpt.sql
-rw-r--r-- 1 ora11g oinstall   6676 Jan  5  2005 awrsqrpi.sql
-rw-r--r-- 1 ora11g oinstall   2462 Jan  5  2005 awrinpnm.sql
-rw-r--r-- 1 ora11g oinstall   7575 Apr 18  2005 awrrpti.sql
-rw-r--r-- 1 ora11g oinstall  20892 May 23  2005 awrddinp.sql
-rw-r--r-- 1 ora11g oinstall   2005 May 27  2005 awrddrpt.sql
-rw-r--r-- 1 ora11g oinstall   8603 Mar  3  2006 awrinput.sql
-rw-r--r-- 1 ora11g oinstall   1148 Dec  1  2006 awrblmig.sql
-rw-r--r-- 1 ora11g oinstall    853 Jan  2  2007 catawrtv.sql
-rw-r--r-- 1 ora11g oinstallhttp://www.oracleplus.net   6500 Jan  2  2007 catawrpd.sql
-rw-r--r-- 1 ora11g oinstall   2149 Mar 13  2008 catawr.sql
-rw-r--r-- 1 ora11g oinstall   6311 Mar 13  2008 awrgrpti.sql
-rw-r--r-- 1 ora11g oinstall   7440 Mar 13  2008 awrginp.sql
-rw-r--r-- 1 ora11g oinstall  16457 Mar 13  2008 awrgdinp.sql
-rw-r--r-- 1 ora11g oinstall 228808 Oct 13  2008 spawrrac.sql
-rw-r--r-- 1 ora11g oinstall  11082 Mar 24  2009 awrextr.sql
-rw-r--r-- 1 ora11g oinstall   1523 Apr 29  2009 awrgrpt.sql
-rw-r--r-- 1 ora11g oinstall   1897 Apr 29  2009 awrgdrpt.sql
-rw-r--r-- 1 ora11g oinstall   7311 May 13  2009 awrgdrpi.sql
-rw-r--r-- 1 ora11g oinstall   7368 May 13  2009 awrddrpi.sql
-rw-r--r-- 1 ora11g oinstall  10368 Jul 15  2009 awrload.sql
-rw-r--r-- 1 ora11g oinstall  50700 Jan  4  2010 dbmsawr.sql
-rw-r--r-- 1 ora11g oinstall  85202 Feb 23  2010 spawrio.sql
-rw-r--r-- 1 ora11g oinstall 131403 Apr  1  2010 catawrvw.sql
-rw-r--r-- 1 ora11g oinstall  13364 Apr 21  2010 catnoawr.sql
-rw-r--r-- 1 ora11g oinstall 139273 Apr 21  2010 catawrtb.sql
下面逐个进行描述:

1. awrrpt.sql
    --这个脚本,不用多说, 用于产生awr 报告,当然你可以选择
      是html或者txt格式。

2. awrinfo.sql
    --获取awr 元数据的脚本,通过这个脚本,我靠可以看到awr的一些配置,以及
      整个awr快照中,哪些schema占据多少空间等等之类的信息,如下:
   Warning: Non Default AWR Setting!
--------------------------------------------------------------------------------
Snapshot interval is 60 minutes and Retention is 8 days


       DB_ID DB_NAME   HOST_PLATFORM                             INST STARTUP_TIME      LAST_ASH_SID PAR
------------ --------- ---------------------------------------- ----- ----------------- ------------ ---
* 2468313792 ROGER     11gR2test - Linux IA (32-bit)                1 02:41:44 (08/08)             0 NO

########################################################
(I) AWR Snapshots Information
########################################################

*****************************************************
(1a) SYSAUX usage - Schema breakdown (dba_segments)
*****************************************************
|
| Total SYSAUX size                        520.7 MB ( 2% of 32,768.0 MB MAX with AUTOEXTEND ON )
|
| Schema  SYS          occupies            137.1 MB (  26.3% )
| Schema  XDB          occupies            125.8 MB (  24.2% )
| Schema  APEX_030200  occupies             77.4 MB (  14.9% )
| Schema  SYSMAN       occupies             70.4 MB (  13.5% )
| Schema  MDSYS        occupies             65.4 MB (  12.6% )
| Schema  SYSTEM       occupies             13.7 MB (   2.6% )
| Schema  ORDDATA      occupies             13.4 MB (   2.6% )
| Schema  OLAPSYS      occupies              5.1 MB (   1.0% )
| Schema  EXFSYS       occupies              3.6 MB (   0.7% )
| Schema  WMSYS        occupies              3.5 MB (   0.7% )
| Schema  CTXSYS       occupies              3.5 MB (   0.7% )
| Schema  DBSNMP       occupies              1.3 MB (   0.2% )
| Schema  ORDSYS       occupies              0.4 MB (   0.1% )
.....省略部分信息


3.  awrsqrpt.sql 
      --该脚本用于获取某个快照awr内,某个SQL的执行计划等信息,如下:
   获取awr 报告awrrpt_1_238_239.html中某个消耗比较高的sql的执行计划,产生的html报告如下:

4.  awrsqrpi.sql
    --该脚本功能跟awrsqrpt.sql 类似,可以指定实例,换句话说,也就是rac环境下比较有用。

5.  awrinpnm.sql

6. awrrpti.sql
    ---该脚本跟awrrpt.sql类似,不过多描述,rac环境下比较有用。
7.  awrddinp.sql

    ---用于比较awr 快照,可以比较不同实例。

SQL> @  /rdbms/admin/awrddinp.sql


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
  691339318         2 HSKJ         hskj2        hsdb2
* 691339318         1 HSKJ         hskj1        hsdb1

Database Id and Instance Number for the First Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for dbid: 691339318
Using 691339318 for Database Id for the first pair of snapshots
Enter value for inst_num: 1
Using 1 for Instance Number for the first pair of snapshots


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
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
hskj1        HSKJ              5506 08 Aug 2012 00:00      1
                               5507 08 Aug 2012 01:00      1
                               5508 08 Aug 2012 02:00      1
                               5509 08 Aug 2012 03:00      1
                               5510 08 Aug 2012 04:00      1
                               5511 08 Aug 2012 05:00      1
                               5512 08 Aug 2012 06:00      1
                               5513 08 Aug 2012 07:00      1
                               5514 08 Aug 2012 08:00      1
                               5515 08 Aug 2012 09:00      1
                               5516 08 Aug 2012 10:00      1
                               5517 08 Aug 2012 11:00      1
                               5518 08 Aug 2012 12:00      1
                               5519 08 Aug 2012 13:00      1
                               5520 08 Aug 2012 14:00      1
                               5521 08 Aug 2012 15:00      1
                               5522 08 Aug 2012 16:00      1
                               5523 08 Aug 2012 17:00      1
                               5524 08 Aug 2012 18:00      1



Specify the First Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 5521
First Begin Snapshot Id specified: 5521

Enter value for end_snap: 5522
First End   Snapshot Id specified: 5522




Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
  691339318         2 HSKJ         hskj2        hsdb2
* 691339318         1 HSKJ         hskj1        hsdb1




Database Id and Instance Number for the Second Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for dbid2: 691339318
Using 691339318 for Database Id for the second pair of snapshots
Enter value for inst_num2: 2
Using 2 for Instance Number for the second pair of snapshots


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_days2: 1

Listing the last day's Completed Snapshots

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
hskj2        HSKJ              5506 08 Aug 2012 00:00      1
                               5507 08 Aug 2012 01:00      1
                               5508 08 Aug 2012 02:00      1
                               5509 08 Aug 2012 03:00      1
                               5510 08 Aug 2012 04:00      1
                               5511 08 Aug 2012 05:00      1
                               5512 08 Aug 2012 06:00      1
                               5513 08 Aug 2012 07:00      1
                               5514 08 Aug 2012 08:00      1
                               5515 08 Aug 2012 09:00      1
                               5516 08 Aug 2012 10:00      1
                               5517 08 Aug 2012 11:00      1
                               5518 08 Aug 2012 12:00      1
                               5519 08 Aug 2012 13:00      1
                               5520 08 Aug 2012 14:00      1
                               5521 08 Aug 2012 15:00      1
                               5522 08 Aug 2012 16:00      1
                               5523 08 Aug 2012 17:00      1
                               5524 08 Aug 2012 18:00      1



Specify the Second Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap2: 5521
Second Begin Snapshot Id specified: 5521

Enter value for end_snap2: 5522
Second End   Snapshot Id specified: 5522

.....省略部分信息


8. awrddrpt.sql
    ---比较当前实例的awr 快照之间的不同,这个脚本也是非常实用的。

9. awrinput.sql

   Get inputs for AWR report

10.  awrblmig.sql

    ----awr baseline 迁移,该脚本用于11g以前版本的baseline迁移至11g,如下:

SQL> @  /rdbms/admin/awrblmig.sql

PL/SQL procedure successfully completed.


This script will migrate the Baseline data on a pre-11g database
to the 11g database.

...                                       ...
... Completed Moving the Baseline Data    ...
...                                       ...
... If there are no Move BL Data messages ...
... above, then there are no renamed      ...
... baseline tables in the system.        ...
...                                       ...
...                                       ...
... Completed the Dropping of the         ...
... Renamed Baseline Tables               ...
...                                       ...
... If there are no Drop Table messages   ...
... above, then there are no renamed      ...
... baseline tables in the system.        ...
...                                       ...

PL/SQL procedure successfully completed.
11.   catawrtv.sql

     ---该脚本用于重建awr 相关的表和试图,其中该脚本实际上是去调用另外2个脚本:
     catawrtb.sql  --创建awr快照相关的表
     catawrvw.sql  --创建awr快照相关的试图

12.  catawrpd.sql

     ---该脚本是用于创建awr 相关的一些依赖试图,比如dba_hist_baseline,
      dba_hist_baseline_details、dba_hist_sqlbind等等。

13.  catawr.sql

     ----该脚本用于重建awr,比如创建awr的一些表,试图,存储过程等等,其中该脚本实际上
      也是去调用如下的其他脚本:

      Rem The following script will create the WR table
      @@catawrtb  ---前面说过了,不多说

      Rem Create the DBMS_WORKLOAD_REPOSITORY package
      @@dbmsawr   --创建dbms_workload_repository 包

      Rem The following script will create the DBA_HIST views for the
      Rem Workload Repository
      @@catawrvw  --创建awr快照相关的试图
      @@catawrpd  --该脚本是用于创建awr 相关的一些依赖试图

      Rem Create DBMS_ASH_INTERNAL package and package body
      Rem NOTE: prvtawr uses functions in prvtash, so include prvtash first.
      @@prvsash.plb   下面这部分就是一些oracle internal的东西了。
      @@prvtash.plb

      Rem Create DBMS_WORKLOAD_REPOSITORY package body,
      Rem Create DBMS_SWRF_INTERNAL package and package body,
      Rem Create DBMS_SWRF_REPORT_INTERNAL package and package body
      Rem NOTE: prvtawr uses functions in prvtash, so include prvtash first.
      @@prvsawr.plb
      @@prvsawri.plb
      @@prvsawrs.plb
      @@prvtawr.plb
      @@prvtawri.plb
      @@prvtawrs.plb

14.  awrgrpti.sql
     ---rac global 报告,其实跟前面的awrrpt.sql差不多。官方描述是这样的:
     report on RAC-wide differences between  values recorded in two snapshots.

15.  awrginp.sql
     ---跟前面的awrinput.sql差不多,不过是用于rac环境的,用于获取一些快照信息,没太多用处。

16.   awrgdinp.sql
     --跟前面的awrddinp.sql 类似,不过是针对rac环境,没太大实际意义。

17.   spawrrac.sql
     ---用于产生针对rac环境的awr报告,这个脚本非常有用,其中在11g之前,这个脚本是不提供的,
     从11g开始,oracle自带这个脚本了,不过比较遗憾的是,只能是txt格式,要是html格式就好了。

18.  awrextr.sql
     ---该脚本用于将awr数据抽取为dump file,然后我们可以将dmp文件导入到别的数据库中。
        这有助于用来保存awr历史数据,便于日后分析,如下例子:
SQL> @  /rdbms/admin/awrextr.SQL
~~~~~~~~~~~~~
AWR EXTRACT
~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~  This script will EXTRACT the AWR DATA FOR a range OF snapshots  ~
~  INTO a dump file.  The script will prompt users FOR the         ~
~  following information:                                          ~
~     (1) DATABASE id                                              ~
~     (2) snapshot range TO EXTRACT                                ~
~     (3) name OF directory object                                 ~
~     (4) name OF dump file                                        ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


DATABASES IN this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     DB Name      Host
------------ ------------ ------------
* 2468313792 ROGER        11gR2test

The DEFAULT DATABASE id IS the LOCAL one: '2468313792'.  TO USE this
DATABASE id, press <return> TO continue, otherwise enter an alternative.

Enter VALUE FOR dbid: 2468313792

USING 2468313792 FOR DATABASE ID


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 <return> WITHOUT
specifying a NUMBER lists ALL completed snapshots.


Enter VALUE FOR num_days: 5

Listing the LAST 5 days OF Completed Snapshots

DB Name        Snap Id    Snap Started
------------ --------- ------------------
ROGER              238 06 Aug 2012 23:40
                   239 07 Aug 2012 01:00
                   240 08 Aug 2012 02:52
                   241 08 Aug 2012 04:00


Specify the BEGIN AND END Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter VALUE FOR begin_snap: 238
BEGIN Snapshot Id specified: 238

Enter VALUE FOR end_snap: 239
END   Snapshot Id specified: 239


Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~

Directory Name                 Directory Path
------------------------------ -------------------------------------------------
DATA_PUMP_DIR                  /home/ora11g/admin/roger/dpdump/
ORACLE_OCM_CONFIG_DIR          /home/ora11g/product/11.2/db/ccr/state
XMLDIR                         /home/ora11g/product/11.2/db/rdbms/xml

Choose a Directory Name FROM the above list (case-sensitive).

Enter VALUE FOR directory_name: DATA_PUMP_DIR

USING the dump directory: DATA_PUMP_DIR

Specify the Name OF the EXTRACT Dump File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The prefix FOR the DEFAULT dump file name IS awrdat_238_239.
TO USE this name, press <return> TO continue, otherwise enter
an alternative.

Enter VALUE FOR file_name:          

USING the dump file prefix: awrdat_238_239
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|  The AWR EXTRACT dump file will be located
|  IN the following directory/file:
|   /home/ora11g/admin/roger/dpdump/
|   awrdat_238_239.dmp
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|  *** AWR EXTRACT Started ...
|
|  This operation will take a few moments. The
|  progress OF the AWR EXTRACT operation can be
|  monitored IN the following directory/file:
|   /home/ora11g/admin/roger/dpdump/
|   awrdat_238_239.log
|

END OF AWR EXTRACT
19.  awrgrpt.sql
    ---该脚本用于rac环境,跟awrrpt.sql功能差不多,其本质也就是去调用awrrpti.sql。

20.  awrgdrpt.sql
    ---rac环境下,用于比较awr快照。该脚本实际上调用awrgdrpi.sql。

21.  awrgdrpi.sql

    ----Workload Repository Global Compare Periods Report(RAC Version of Compare Period Report)

22.  awrddrpi.sql
     ----用于awr 快照的比较,我们经常用到的用于比较两个awr的脚本awrddrpt.sql 其实本质就调用这个脚本。

23.  awrload.sql
     ----用于加载awr 数据dump file,例如把其他库的awr dmp可以用这个脚本来进行加载,如下:
SQL> @  /rdbms/admin/awrload.sql
~~~~~~~~~~
AWR LOAD
~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~  This script will load the AWR data from a dump file. The   ~
~  script will prompt users for the following information:    ~
~     (1) name of directory object                            ~
~     (2) name of dump file                                   ~
~     (3) staging schema name to load AWR data into           ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~

Directory Name                 Directory Path
------------------------------ -------------------------------------------------
DATA_PUMP_DIR                  /home/ora11g/admin/roger/dpdump/
ORACLE_OCM_CONFIG_DIR          /home/ora11g/product/11.2/db/ccr/state
XMLDIR                         /home/ora11g/product/11.2/db/rdbms/xml

Choose a Directory Name from the list above (case-sensitive).

Enter value for directory_name: DATA_PUMP_DIR

Using the dump directory: DATA_PUMP_DIR

Specify the Name of the Dump File to Load
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Please specify the prefix of the dump file (.dmp) to load:

Enter value for file_name:
......省略部分信息
24.   dbmsawr.sql
    ---用于创建awr相关的管理包,用的很少。

25.  spawrio.sql
    ---用于产生系统IO 使用的报告,不过格式是txt格式,这里我摘取一段:


I/O Activity

                                                                               Value
Statistic Name                                          Value                per Sec
---------------------------------------- -------------------- ----------------------
table scans (direct read)                                   4                    0.0
table scans (long tables)                                 111                    0.0
table scans (rowid ranges)                                  0                    0.0
table scans (short tables)                             24,504                    2.6

I/O Statistics Comparison

                                                  System  Stats          Segment Stats
Statistic Name                                      (thousands)            (thousands) % Captured
---------------------------------------- ---------------------- ---------------------- ----------
full scans                                                  0.6                    0.2       33.9
physical read IO requests                                   6.9                    8.4      121.3
physical read requests optimized                            0.0                    0.0
physical write IO requests                                  4.5                    1.7       36.8
                                         ---------------------- ----------------------
sum                                                        12.0                   10.3


26.   catnoawr.sql
    ---该脚本用于清除awr元数据,以及相关用户的所有信息,如下:

SQL> @  /rdbms/admin/catnoawr.sql                                           
drop table WRH$_XPL_OPERATION_NAME
           *
ERROR at line 1:
ORA-00942: table or view does not exist


drop table WRH$_XPL_OPTION_NAME
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL>  select * from dba_hist_wr_control;
select * from dba_hist_wr_control
               *
ERROR at line 1:
ORA-04063: view "SYS.DBA_HIST_WR_CONTROL" has errors

当然清理遗憾,我们可以再重建awr,如下:
SQL> @  /rdbms/admin/catawr.sql

Session altered.


Table created.

drop table   WRH$_FILESTATXS_BL
             *
ERROR at line 1:
ORA-00942: table or view does not exist



Table created.


Table altered.


Table created.


Table created.


Table created.


Table created.

....省略部分信息
Package body created.

No errors.

Package body created.

No errors.

Package body created.

No errors.
SQL>

我发现这里重建以后还是不行,我试了几次,都不行,不知道为什么,无奈我使用
catnoawr.sql 再次清空,查dba_objects发现还有wrh$相关对象,我手工清除:
drop index WRH$_PLAN_OPTION_NAME_PK       ;
drop index WRH$_DISPATCHER_PK             ;
drop index WRH$_PLAN_OPERATION_NAME_PK    ;
drop index WRH$_DYN_REMASTER_STATS_PK     ;
drop index WRH$_PERSISTENT_QMN_CACHE_PK   ;
drop index WRH$_MVPARAMETER_PK            ;
drop index WRH$_SHARED_SERVER_SUMMARY_PK  ;
drop index WRH$_MVPARAMETER_BL_PK         ;
drop index WRH$_MVPARAMETER_PK            ;
drop index WRH$_MVPARAMETER_PK            ;
drop index WRH$_MVPARAMETER_PK            ;           
drop table WRH$_DISPATCHER                ;
drop table WRH$_SHARED_SERVER_SUMMARY     ;
drop table WRH$_PLAN_OPERATION_NAME       ;
drop table WRH$_MVPARAMETER               ;
drop table WRH$_PLAN_OPTION_NAME          ;
drop table WRH$_PERSISTENT_QMN_CACHE      ;
drop table WRH$_MVPARAMETER_BL            ;
drop table WRH$_DYN_REMASTER_STATS        ;
drop table WRH$_MVPARAMETER               ;
drop table WRH$_MVPARAMETER               ;
drop table WRH$_MVPARAMETER               ;

然后再次运行 @  /rdbms/admin/catawr.sql,然后utlrp.sql,接着手工创建快照,发现报错:
SQL> exec dbms_workload_repository.create_snapshot;
BEGIN dbms_workload_repository.create_snapshot; END;

*
ERROR at line 1:
ORA-13518: Invalid database id (2468313792)
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 99
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 122
ORA-06512: at line 1

于是我尝试重启下实例,然后发现一切都正常了,如下:
SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL> @  /rdbms/admin/awrrpt.sql

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

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
2468313792 ROGER               1 roger


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:

Type Specified:  html


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 2468313792        1 ROGER        roger        11gR2test

Using 2468313792 for database Id
Using          1 for instance number


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:
不知道,这里算不算是oracle的一个bug,这里没有深入去分析,没有什么意义。
最后简单总结下,我们所常用的几个awr相关脚本:

—-awrrpt.sql
—-awrsqrpt.sql
—-awrddrpt.sql
—-awrrpti.sql
—-awrgrpt.sql
—-awrgdrpt.sql
—-awrinfo.sql
—-catnoawr.sql
—-catawr.sql
—-spawrrac.sql 该脚本在10g中是不存在的,需要单独下载。
—-spawrio.sql
—-spadvrpt.sql 这个是针对streams 性能的报告脚本

补充:oracle mos文档How to Recreate The AWR ( AUTOMATIC WORKLOAD ) Repository [ID 782974.1]
中提到的重建awr的方法,应该是针对11.2以下版本,但是看文档描述
说是10.1.0.2 to 11.2.0.2版本,其实从11.2开始,重建awr只需要执行catawr.sql就行了。
下面是该文档提到的重建方法:

— On both 10g and 11g
start /rdbms/admin/catnoawr.sql
alter system flush shared_pool;
start /rdbms/admin/catawrtb.sql
start /rdbms/admin/utlrp.sql
–On 11g it is necessary to also run:
start /rdbms/admin/execsvrm.sql

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle 11gR2中awr 相关脚本的详细说明

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1396.html

Oracle研究中心

关键词:

11gR2中awr 相关脚本的说明

Oracle AWR中脚本分析