How to Recreate the Automatic Workload Repository (AWR)? (Doc ID 782974.1)

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 or 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 = 100m scope = spfile;
alter system set large_pool_size = 50m 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;

NOTE: If SGA_TARGET is 0, then per above step can be skipped other then 'alter system set statistics_level = basic scope = spfile;'
12c STARTup issue with SGA_TARGET and STARTISTICS_LEVEL=BASIC
As per the steps we cannot set SGA_TARGET and STATISTICS_LEVEL to BASIC as it will end up with error
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

Document 461100.1 STARTUP ERRORS ora-00824 cannot set sga_target with statistics_level=BASIC

1. 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

1. 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, 11g, and 12c release 1 drop AWR

-- Run Script
start ?/rdbms/admin/catnoawr.sql
-- Flush Shared Pool
alter system flush shared_pool;

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

--- Here are the addition commands needs to be run in 12.2.0.1 database

spool drop_awr_objs.sql

SELECT 'DROP TABLE ' || table_name || ' CASCADE CONSTRAINTS;'
FROM dba_tables where table_name like 'WRM$_%' or table_name like 'WRH$_%';
spool off

Note: With 12.2, running the catnoawr.sql script with the database stated up in restricted mode fails with an ORA-00600: internal error code, arguments: [opiodr:]. But this procedure works with previous versions.

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:
check recycle object details before doing this task, In some cases ,You may need to purge the recycle bin to avoid any error.
start ?/rdbms/admin/catawrtb.sql
start ?/rdbms/admin/utlrp.sql

--On 11g and 12c, 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='/home/oracle/admin/dbs/init@.ora.20140122' 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 .<package_name> compile;
alter package .<package_name> compile body;
alter view .<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.

Note: If WRMS$SNAPSHOT Table is not created as a part of AWR recreation. please do manually create it using

sqlplus / as sysdba
CREATE TABLE "SYS"."WRMS$_SNAPSHOT"
( "SNAP_ID" NUMBER NOT NULL ENABLE,
"DBID" NUMBER NOT NULL ENABLE,
"INSTANCE_NUMBER" NUMBER NOT NULL ENABLE,
"STARTUP_TIME" TIMESTAMP (3) NOT NULL ENABLE,
"BEGIN_INTERVAL_TIME" TIMESTAMP (3) NOT NULL ENABLE,
"END_INTERVAL_TIME" TIMESTAMP (3) NOT NULL ENABLE,
"FLUSH_ELAPSED" INTERVAL DAY (5) TO SECOND (1),
"SNAP_LEVEL" NUMBER,
"STATUS" NUMBER,
"ERROR_COUNT" NUMBER,
"BL_MOVED" NUMBER,
"SNAP_FLAG" NUMBER,
"SNAP_TIMEZONE" INTERVAL DAY (0) TO SECOND (0),
"STAGE_INST_ID" NUMBER NOT NULL ENABLE,
"STAGE_ID" NUMBER NOT NULL ENABLE
);

REFERENCES
NOTE:243246.1 - This document has been replaced with more recent information on this topic. Please refer to more recent documentation.

NOTE:748642.1 - How to Generate an AWR Report and Create Baselines
NOTE:1363422.1 - Automatic Workload Repository (AWR) Reports - Main Information Sources
BUG:5376177 - CAN NOT FIND CATNOAWR.SQL.
BUG:9150463 - CANNOT RECREATE THE AWR ON R11.1