Disabling Oracle Performance Pack


   Disabling Performance Tuning & Diagnostic pack:

Method 1:  Revoking Management Pack Access through OEM
Home Page>setup>Management Pack Access <Remove Access> <Apply>



Method 2:
ü  Step 1



Disable Automatic/manual AWR snapshots                       

SQL> @dbmsnoawr.plb  <This Package is downloaded from My Support Article ID 436386.1>

SQL> begin dbms_awr.disable_awr(); end;

Impact:

·         Stops Automatic AWR snapshots
·         Does not allow taking manual snapshot.
·         All  <report>.sql files will work for old snaps

ü  Step 2
To Disable Automatic ADDM
ALTER SYSTEM set "_addm_auto_enable"=false;
(Dynamic parameter)

Impact:
·         Disables automatic ADDM which gets run automatically after every Automatic AWR snapshots


ü  Step 3

To disable complete Management Access Pack         

                                                                                                                                                                                                                   
SQL> drop user sysman cascade;

SQL> drop role MGMT_USER;
SQL> drop user MGMT_VIEW cascade;
SQL> drop public synonym MGMT_TARGET_BLACKOUTS;
SQL> drop public synonym SETEMVIEWUSERCONTEXT;

Impact:
·         Disables complete management access pack along with dropping OEM Repository                                                                                                                                                                                                                                                   

Method 3: (Applicable for Oracle 11g)
Set parameter
Alter system set CONTROL_MANAGEMENT_PACK_ACCESS=None;

@?/rdbms/admin/catnoawr.sql (optional)
CONTROL_MANAGEMENT_PACK_ACCESS specifies which of the Server Manageability Packs should be active. The following packs are available:
The DIAGNOSTIC pack includes AWR, ADDM etc.
  • The TUNING pack includes SQL Tuning Advisor, SQL Access Advisor etc.
A license for DIAGNOSTIC is required for enabling the TUNING pack.

Values:

  • NONE
Both packs are not available.

  • DIAGNOSTIC
Only the DIAGNOSTIC pack is available.

  • DIAGNOSTIC+TUNING
Both packs are available. This is the default.

Impact:

·         Disables Complete Performance & tuning pack

Running the ADDM analysis on the specified pair of snapshots ...

Will receive below error if tuning pack components are accessed

begin
*
ERROR at line 1:
ORA-13716: Diagnostic Package License is needed for using this feature.
ORA-06512: at "SYS.PRVT_SMGUTIL", line 52
ORA-06512: at "SYS.PRVT_SMGUTIL", line 35
ORA-06512: at "SYS.PRVT_ADVISOR", line 7126
ORA-06512: at "SYS.PRVT_ADVISOR", line 7170
ORA-06512: at "SYS.PRVT_ADVISOR", line 7229
ORA-06512: at "SYS.DBMS_ADVISOR", line 101
ORA-06512: at line 12


Verify:
Set line 500
Set pages 500
select NAME,DETECTED_USAGES,CURRENTLY_USED,FIRST_USAGE_DATE,LAST_USAGE_DATE from DBA_FEATURE_USAGE_STATISTICS where lower(name) like '%advisor%' or lower(name) like '%sql%' or lower(name) like '%automatic%';

1 comment: