ADDM REPORT HUNG - Recreate the Automatic Workload Repository (AWR)

ADDM REPORT HUNG - Recreate the Automatic Workload Repository (AWR)

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

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

-- Run Script
start ?/rdbms/admin/catnoawr.sql
-- Flush Shared Pool
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 and 12c release 1, it is necessary to also run:

start ?/rdbms/admin/execsvrm.sql

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

Reference Link of Oracle Doc : Doc ID 782974.1