How to Recreate The AWR ( AUTOMATIC WORKLOAD ) Repository ?

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Information in this document applies to any platform.

GOAL

How to Recreate The AWR( AUTOMATIC WORK LOAD ) Repository

SOLUTION

The best way to deinstall/install AWR is as follows:

1. Disable AWR statistics gathering by setting the statistics level to basic as follows:

• Check settings for parameters as follows:
  
  sqlplus /nolog
  connect / as sysdba
  show parameter cluster_database
  show parameter statistics_level
  show parameter sga_target

 

• Or save the spfile before modifying:
  
  create pfile='/home/oracle/admin/dbs/init@.ora.20140122' from spfile;

• In 10g and 11g , if sga_target is not 0, then in pfile or spfile set the following parameters:
  The example below refers to spfile:
  
  alter system set shared_pool_size = 200m scope = spfile;
  alter system set db_cache_size = 300m scope = spfile;
  alter system set java_pool_size = 100 scope = spfile;
  alter system set large_pool_size = 50 scope = spfile;
  alter system reset sga_target scope= spfile;
  alter system reset memory_target scope= spfile; 
  alter system reset memory_max_target scope=spfile;
  alter system set statistics_level=basic scope=spfile;
  
  -- Setting the parameter cluster_database only applicable
  -- in RAC environment
  --Check actual shared pool and buffer cache usage in AWR to make sure the settings are correct
  alter system set cluster_database = false scope = spfile;

 

• In 11g, you may need to set both SGA_TARGET and MEMORY_TARGET to 0: 
  
  alter system set sga_target=0 scope= spfile;
  alter system set memory_target=0 scope= spfile;
• otherwise database may not startup and will fail with the errors below:
  
  SQL> startup restrict
  ORA-01078: failure in processing system parameters
  ORA-00824: cannot set SGA_TARGET or MEMORY_TARGET due to existing internal settings
  ORA-00848: STATISTICS_LEVEL cannot be set to BASIC with SGA_TARGET or MEMORY_TARGET

 

2. Shutdown database and startup in restrict mode so that no transactions will occur while dropping the 
AWR repository:

sqlplus /nolog
connect / as sysdba
shutdown immediate
startup restrict

3. Drop and recreate the AWR objects

• The following scripts drop AWR tables and then recreates them.
  After recreating ,utlrp is run in order to validate all views and objects dependent on the AWR tables.

  -- On both 10g and 11gdrop AWR

  start ?/rdbms/admin/catnoawr.sql
  alter system flush shared_pool;

  Check to see if all the objects are dropped :

  SQL> select table_name from dba_tables where table_name like 'WRM$_%' or table_name like 'WRH$_%';

  If there are objects after running catnoawr.sql, drop them manually:

  drop type AWR_OBJECT_INFO_TABLE_TYPE; 
  drop type AWR_OBJECT_INFO_TYPE; 
  drop table WRH$_PLAN_OPERATION_NAME; 
  drop table WRH$_PLAN_OPTION_NAME; 
  drop table WRH$_MV_PARAMETER; 
  drop table WRH$_MV_PARAMETER_BL; 
  drop table WRH$_DYN_REMASTER_STATS;
  drop table WRH$_PERSISTENT_QMN_CACHE;
  drop table WRH$_DISPATCHER;
  drop table WRH$_SHARED_SERVER_SUMMARY;
  drop table WRM$_WR_USAGE
  drop table WRM$_SNAPSHOT_DETAILS
  
  Now create AWR:
  
  start ?/rdbms/admin/catawrtb.sql
  start ?/rdbms/admin/utlrp.sql
  
  --On 11g it is necessary to also run:
  start ?/rdbms/admin/execsvrm.sql

   

  Note: If you receive the following errors when executing "?/rdbms/admin/execsvrm.sql", as follows:

  start ?/rdbms/admin/execsvrm.sql
  
  Fails with the following errors :
  
  ERROR at line 1:
  ORA-04068: existing state of packages has been discarded
  ORA-04061: existing state of package body "SYS.DBMS_SWRF_INTERNAL" has been
  invalidated

  
  then recompile the object(s). 
  

  alter package dbms_swrf_internal compile;
  alter package dbms_swrf_internal compile body;

  It is important to do this even if the object(s) (dbms_swrf_internal in this case) appear valid. You will then need to re-execute the "?/rdbms/admin/execsvrm.sql" script. 
  
  

4) Reset the parameters shared_pool_size,db_cache_size, java_pool_size ,large_pool_size, sga_target statistics_level and cluster_database to original values.Also can reset the parameter from copy of the spfile:

create spfile from pfile='/home/oracle/admin/dbs/init@.ora.20140122' ;

5) Restart instance in normal mode:

 

sqlplus /nolog
connect / as sysdba
shutdown immediate
startup

6) Check invalid objects exists are not , if exists then please compile it manually. As we have run utlrp.sql, any invalid objects should already have been reported there:

spool objects.lst
set pagesize500
set linesize 100

select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version 
from dba_registry 
order by comp_name;

select substr(object_name,1,40) object_name,substr(owner,1,15) owner,object_type 
from dba_objects 
where status='INVALID' order by owner,object_type;

select owner,object_type,count(*) 
from dba_objects 
where status='INVALID' 
group by owner,object_type order by owner,object_type ;

spool off

alter package <schema name>.<package_name> compile;
alter package <schema name>.<package_name> compile body;
alter view <schema name>.<view_name> compile;
alter trigger <schema).<trigger_name> compile;

7) To take the AWR snapshots:

exec dbms_workload_repository.create_snapshot;
--wait for 5 min
exec dbms_workload_repository.create_snapshot;

8) To create AWR report run the script:

start $ORACLE_HOME/rdbms/admin/awrrpt.sql

9) If further assistance is needed or errors arise while performing recreation of AWR, please open an sr.