禁用與卸載Oracle AWR特性

禁用與卸載Oracle AWR特性

AWR需要禁用？這麼好的東東。預設的情況下，AWR是可以使用的，需要耗用一定的sysaux資料表空間。但涉及到有關AWR相關的調試包(需要license)會訪問AWR視圖或者awr異常又不想更新patch，甚至沒有patch可用的情況下，我們可以禁用AWR以及卸載AWR，本文示範了如果禁用AWR功能以及卸載awr相關的資料字典。

1、禁用AWR的目的
    If most of the space in the SYSAUX tablespace is consumed by information associated with the Automatic Workload Repository (AWR), the AWR can be disabled or uninstalled, releasing space in the sysaux tablespace. This is specially relevant to customers who do not have a license to use AWR.

2、如何禁用AWR(Oracle 10g and above)

    AWR is enabled by default because many database features that are not part of the Diagnostic Pack such as Automatic Segment Advisor and Undo Advisor need information captured in AWR. Use of these features, which implicitly access some AWR views, does not require Diagnostic Pack license.

    What is not permitted without the Diagnostic Pack license is direct access by customers of AWR views and reports. Oracle, therefore, recommends that all customers, with or without Diagnostic Pack license, leave AWR enabled so that they can benefit from features that do not require a license but implicitly use AWR.  (When running both AWR and Statspack collection on one database, it is advised to schedule the two types of collections at different times.  For example, if the AWR takes a snapshot every hour, on the hour, then you could schedule a Statspack snapshot every hour, at the bottom of each hour.)

    However, for those users who all the same want to disable AWR, the package DBMS_AWR described below can be instaled and used. The package gives the ability to disable and enable AWR so as not to breaching Diagnostic Pack license terms.

Affected Releases:
All Oracle Database 10g releases and onwards

禁用awr需要下載: dbmsnoawr.plb 檔案。

------------------------------------------分割線------------------------------------------

免費在 http://linux.bkjia.com/

使用者名稱與密碼都是www.bkjia.com

具體下載目錄在 /2014年資料/9月/12日/禁用與卸載Oracle AWR特性

下載方法見

------------------------------------------分割線------------------------------------------

注意這裡的禁用我們指的是完全停用。當然通過設定STATISTICS_LEVEL也可以從一定程度上實作類別似的目的。
如果將參數STATISTICS_LEVEL設定為BASIC，下列重要的統計資訊將不會被收集。
  Automatic Workload Repository (AWR) Snapshots
  Automatic Database Diagnostic Monitor (ADDM)
  All server-generated alerts
  Automatic SGA Memory Management
  Automatic optimizer statistics collection
  Object level statistics
  End to End Application Tracing (V$CLIENT_STATS)
  Database time distribution statistics (V$SESS_TIME_MODEL and V$SYS_TIME_MODEL)
  Service level statistics
  Buffer cache advisory
  MTTR advisory
  Shared pool sizing advisory
  Segment level statistics
  PGA Target advisory
  Timed statistics
  Monitoring of statistics

3、示範禁用AWR

oracle@USDB:~> export ORACLE_SID=HKBO5
oracle@USDB:~> sqlplus / as sysdba
sys@HKBO5> select * from v$version where rownum<2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production

--查看禁用前awr的使用頻率
sys@HKBO5> SELECT name,
  2            detected_usages detected,
  3        total_samples  samples,
  4            currently_used  used,
  5            to_char(last_sample_date,'MMDDYYYY:HH24:MI') last_sample,
  6            sample_interval interval
  7        FROM dba_feature_usage_statistics
  8  WHERE name = 'Automatic Workload Repository';

NAME                                                              DETECTED    SAMPLES USED  LAST_SAMPLE      INTERVAL
---------------------------------------------------------------- ---------- ---------- ----- -------------- ----------
Automatic Workload Repository                                            0        207 FALSE 09112014:00:12    604800

oracle@USDB:~> ll *awr*
-rw-r--r-- 1 oracle oinstall 2369 2014-08-21 17:26 dbmsnoawr.plb

--Author : Leshami
--Blog  :

在CentOS 6.4下安裝Oracle 11gR2(x64)

Oracle 11gR2 在VMWare虛擬機器中安裝步驟

Debian 下 安裝 Oracle 11g XE R2

Oracle AWR報告產生步驟