禁用与卸载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 文件。 注意这里的禁用我们指的是完全停用。当然通过设置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   : http://blog.csdn.net/leshami

--执行dbmsnoawr.plb,其实质是添加了一个名为dbms_awr的pkg到当前数据库
sys@HKBO5> @dbmsnoawr.plb

Package created.

Package body created.

sys@HKBO5> exec dbms_awr.disable_awr();

PL/SQL procedure successfully completed.

sys@HKBO5> desc dbms_awr
FUNCTION AWR_ENABLED RETURNS BOOLEAN
FUNCTION AWR_STATUS RETURNS VARCHAR2
PROCEDURE DISABLE_AWR
PROCEDURE ENABLE_AWR

--查看disable后awr的状态,返回值为disable
sys@HKBO5> select dbms_awr.awr_status from dual;

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

--查询awr的数据字典,发现SNAP_INTERVAL变成了0值
sys@HKBO5> 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
sys@HKBO5> exec dbms_awr.enable_awr();

PL/SQL procedure successfully completed.

--此时SNAP_INTERVAL采样恢复到了缺省值,也就是说过程DISABLE_AWR修改了SNAP_INTERVAL设置
sys@HKBO5> select * from dba_hist_wr_control;

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

4、卸载awr 卸载awr通用用于awr相关功能或特性异常的时候。通过先卸载在安装来达到使awr特性正常化。下面给出步骤,不再演示。 卸载awr脚本:$ORACLE_HOME/rdbms/admin/catnoawr.sql 安装awr脚本:$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、参考 Doc ID 1909073.1 Doc ID 787409.1

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据库新发现

Linux上配置Unix ODBC连接Oracle数据库

Oracle10g前,Oracle并不提供UNIX/Linux下的ODBC驱动,用的最为广泛的是UnixOdbc,UnixOdbc的配置不算复杂,但是如果不顺利...

1312
来自专栏Jerry的SAP技术分享

使用ABAP代码创建S/4HANA里的Sales Order

其中红色区域的值是我代码里硬编码的,而蓝色是函数SD_SALESDOCUMENT_CREATE自己创建的。

1086
来自专栏tiane12

DB2备份恢复流程

1201
来自专栏杨建荣的学习笔记

关于数据库中的一些name(r3笔记第64天)

如果接触数据库有些时间了,可能会碰到很多关于数据库相关的名字,比如ORACLE_SID,db_name,instance_name,db_unique_name...

34012
来自专栏杨建荣的学习笔记

关于exp/imp的总结学习(r4笔记第26天)

关于exp/imp,是很常用的数据导出导入工具,在10g开始推出的数据泵datapump相当于是exp/imp的补充和升级版本。在后续章节再做一个总结。 exp...

3067
来自专栏微信公众号:Java团长

Spring boot Mybatis 整合

7.项目不使用application.properties文件 而使用更加简洁的application.yml文件: 将原有的resource文件夹下的app...

1712
来自专栏乐沙弥的世界

ORA-02409:超时:分布式事务处理等待锁定ORA-02063

ORA-02409:超时:分布式事务处理等待锁定ORA-02063 一、错误现象与环境     前端应用程序运行时出现下面的错误提示: 事件添加失败:O...

1162
来自专栏张善友的专栏

非域环境下使用证书设置数据库镜像

数据库镜像是将数据库事务处理从一个SQL Server数据库移动到不同SQL Server环境中的另一个SQL Server数据库中。镜像不能直接访问;它只用在...

2115
来自专栏程序猿

Oracle_12C的新特性

这里我们来领略下Tom眼中的12个特性增强: ? #1 Even better PL/SQL from SQL, 直接在SQL中嵌入PL/SQL对象并运行,猜测...

3009
来自专栏Hadoop实操

如何使用Sqoop2

通sqoop1一样,sqoop2同样也是在Hadoop和关系型数据库之间互传数据的工具,只不过sqoop2引入sqoop server,集中化管理connect...

1.7K8

扫码关注云+社区

领取腾讯云代金券