Month: June 2019

Hey folks,
Seems you like it, so on same line that the post from last week: Let’s say you want to prevent yourself to violate Oracle licensing of your Enterprise Edition database… But as you know, Oracle is tricky sometimes. For example, all Diagnostic Pack features are enabled by default, you just need a JR DBA to go there and used for you to be stuck.

By the way, here is the first mistake: No, AWR is not part of Enterprise Edition, it also requires Diagnostic Pack!

Ok Matheus, so how to prevent it? Here it goes:

Disable AWR with Oracle provided script

Use the script dbmsnoawr.plb provided in MOS Doc ID 1909073.1 to disable AWR once the database has been created.

To install, run the package as SYS from SQL*Plus:
SQL> @dbmsnoawr.plb

To execute the package, use the command:
SQL> begin dbms_awr.disable_awr(); end;

Modify the CONTROL_MANAGEMENT_PACK_ACCESS init parameter to NONE and disable the AWR related advisors.

Disable AWR Related Options

ALTER SYSTEM SET control_management_pack_access=NONE;

SQL> select client_name, operation_name, status from dba_autotask_operation;

CLIENT_NAME                                                      OPERATION_NAME                                                   STATUS 
---------------------------------------------------------------- ---------------------------------------------------------------- --------
auto optimizer stats collection                                  auto optimizer stats job                                         ENABLED
auto space advisor                                               auto space advisor job                                           ENABLED
sql tuning advisor                                               automatic sql tuning task                                        ENABLED


BEGIN
  dbms_auto_task_admin.disable(
    client_name => 'sql tuning advisor',
    operation   => NULL,
    window_name => NULL);

  dbms_auto_task_admin.disable(
    client_name => 'auto space advisor',
    operation   => NULL,
    window_name => NULL);

END;
/

SQL> select client_name, operation_name, status from dba_autotask_operation;

CLIENT_NAME                                                      OPERATION_NAME                                                   STATUS 
---------------------------------------------------------------- ---------------------------------------------------------------- --------
auto optimizer stats collection                                  auto optimizer stats job                                         ENABLED
auto space advisor                                               auto space advisor job                                           DISABLED
sql tuning advisor                                               automatic sql tuning task                                        DISABLED

Some reference on it?
– How To Avoid the AWR Usage Without Having Diagnostic Pack License (Doc ID 2276199.1)
– Controlling Diagnostic and Tuning Pack Usage (Doc ID 436386.1)
– Disabling and Uninstalling AWR (Doc ID 1909073.1)

Hope it helps!