11gR2中awr 相关脚本的说明
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: 11gR2中awr 相关脚本的说明
应群中网友要求，简单总结下11gR2环境中awr的相关脚本，我的11gR2环境里面有如下脚本:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
[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 oinstall 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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
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环境下比较有用。 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 |
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 <return> 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 <return> 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. |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
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历史数据，便于日后分析，如下例子： |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 |
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 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
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: ......省略部分信息 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 |
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 <return> 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
One Response to “11gR2中awr 相关脚本的说明”
roger牛人啊，我还不知道呢？学习了
Leave a Reply
You must be logged in to post a comment.