Disabling and uninstalling the Oracle AWR feature

Does awr need to be disabled? Such a nice stuff. By default, AWR is available and requires a certain amount of sysaux table space to be consumed. But when it comes to the AWR-related debug package (which requires license) to access the AWR view or AWR exception and not to update patches, we can disable AWR and uninstall the AWR, even without patches available. This article demonstrates disabling the AWR feature and uninstalling the AWR-related data dictionary.

1. Purpose of disabling AWR
If most of the space in the Sysaux tablespace are 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 does not has a license to use AWR.

2. How to disable AWR (Oracle 10g and above)

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

    What's not permitted without the Diagnostic Pack license is direct access by customers of the AWR views and reports. Oracle, therefore, recommends-customers, with or without Diagnostic Pack license, leave AWR-enabled so, they Can benefit from features it does not require a license if implicitly use awr.   (when running both AWR and Sta Tspack collection on one database, it's advised to schedule the other types of collections at different times.  for ex Ample, if the AWR takes a snapshot every hour, on the hour, then you could schedule a statspack snapshot every hour, at th E bottom of each hour.)

However, for those users all the the same want to disable AWR, the package Dbms_awr described below can be instaled and US Ed. 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

disable awr requires download: DBMSNOAWR.PLB file.
Notice the disabling here we mean full deactivation. Of course, you can achieve similar goals by setting Statistics_level to a certain extent.
If you set the parameter Statistics_level to basic, the following important statistics will not be collected.
   automatic Workload Repository (AWR) Snapshots
   Automatic Database Diagnostic Monitor (ADDM)
   all server-generated alerts
   Automatic SGA Memory Management
 & nbsp 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. Demo Disable AWR

[email protected]: ~> export ORACLE_SID = HKBO5
[email protected]: ~> sqlplus / as sysdba
[email protected]> select * from v $ version where rownum <2;

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

-See how often awr was used before it was disabled
[email protected]> 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

[email protected]: ~> ll * awr *
-rw-r--r-- 1 oracle oinstall 2369 2014-08-21 17:26 dbmsnoawr.plb

--Author: Leshami
--Blog: http://blog.csdn.net/leshami

--Execute dbmsnoawr.plb, its essence is to add a pkg named dbms_awr to the current database
[email protected]> @ dbmsnoawr.plb

Package created.

Package body created.

[email protected]> exec dbms_awr.disable_awr ();

PL / SQL procedure successfully completed.

[email protected]> desc dbms_awr
FUNCTION AWR_ENABLED RETURNS BOOLEAN
FUNCTION AWR_STATUS RETURNS VARCHAR2
PROCEDURE DISABLE_AWR
PROCEDURE ENABLE_AWR

--Check the status of awr after disable, the return value is disable
[email protected]> select dbms_awr.awr_status from dual;

AWR_STATUS
-------------------------------------------------- -----------
DISABLED

--Query the awr data dictionary and find that SNAP_INTERVAL becomes 0
[email protected]> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- ---------------------------------------- ---------------------------------------- ----------
 733951103 +40150 00: 00: 00.0 +00007 00: 00: 00.0 DEFAULT

-Enable awr again
[email protected]> exec dbms_awr.enable_awr ();

PL / SQL procedure successfully completed.

--At this time, the SNAP_INTERVAL sampling is restored to the default value, that is, the process DISABLE_AWR has modified the SNAP_INTERVAL setting
[email protected]> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- ---------------------------------------- ---------------------------------------- ----------
 733951103 +00000 01: 00: 00.0 +00007 00: 00: 00.0 DEFAULT 

4. Uninstalling AWR
When uninstalling the AWR generic for an AWR-related feature or feature exception. Normalize the AWR feature by first uninstalling the installation to achieve it. The steps below are given and no longer demonstrated.
Uninstalling the Awr script: $ORACLE _home/rdbms/admin/catnoawr.sql
Installing the Awr script: $ORACLE _home/rdbms/admin/catawr.sql

Sqlplus/nolog
Connect/as SYSDBA
Show Parameters Statistics_level
Alter system set STATISTICS_LEVEL=BASIC Scope=spfile;
Shutdown immediate
Startup restrict
$ORACLE _home/rdbms/admin/catnoawr
Shutdown immediate
Startup

5. Reference
Doc ID 1909073.1
Doc ID 787409.1

Disabling and uninstalling the Oracle AWR feature