Oracle : Recreate AWR for 11gR2 databases

Steps to recreate awr

For complete steps please refer to : How to Recreate The AWR ( AUTOMATIC WORKLOAD ) Repository ? (Doc ID 782974.1)

=================================================
save a copy of spfile
=================================================
db1@SEV3(!) db1 /opt/app/db1
$ cd $OH/dbs
db1@SEV3(!) db1 /opt/app/db1/oracle/product/11.2.0.4/dbs
$ sql
SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 28 12:11:57 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create pfile='spfile_beforeawr.txt' from spfile;
File created.

=================================================
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 = 5000m scope = spfile;
// Oracle recommended 200M, but my database didnt start and failed with error ORA-00093 .. so changed the size to 5000m
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 set sga_target=0 scope= spfile;
alter system set memory_target=0 scope= spfile;
alter system reset memory_max_target scope=spfile;
####alter system set statistics_level=basic scope=spfile; // Do not run this, I got below error if we change the statistics_level=basic
##SQL> startup restrict
##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

=================================================
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;

=================================================
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

=================================================
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 drop 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_NA ME;
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_SERVE R_SUMMARY;
drop table WRM$_WR_USAGE;
drop table WRM$_SNAPSHOT_DETAILS;

===============================================
Now create AWR:
===============================================
start ?/rdbms/admin/catawrtb.sql
start ?/rdbms/admin/utlrp.sql
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:
ORA04068:
existing state of packages has been discarded
ORA04061:
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 reexecute
the "?/rdbms/admin/execsvrm.sql" script.
=================================================

=================================================
Create spfile from pfile we created before
=================================================
SQL> create spfile from pfile='spfile_beforeawr.txt';

File created.

=================================================
Restart database in normal mode
=================================================
shutdown immediate
startup

=================================================
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 b ody;
alter view <schema name>.<view_name> compile;
alter trigger <schema).<trigger_name> compile ;

=================================================
To take the AWR snapshots:
=================================================
exec dbms_workload_repository.create_snapshot;
wait for 5 min
exec dbms_workload_repository.create_snapshot;

=================================================
To create AWR report run the script:
=================================================
start $ORACLE_HOME/rdbms/admin/awrrpt.sql