REMOVE and RECREATE AWR repository from database

Recreate AWR in Oracle 11g
1: Check and make a note of the following parameter values
   
SQL> show parameter cluster_database
SQL> show parameter statistics_level
SQL> show parameter sga_target
SQL> show parameter memory_target
SQL> show parameter spfile




2: Disable AWR Snapshots by changing statistics level to ‘BASIC’


2.1: Backup the current parameter file (pfile/spfile) before modifying
--- if database is using spfile
---
SQL> create pfile='/backup/pfile_pre_awr_recreate.ora' from spfile;

--- if database is using pfile
---
SQL> create spfile='/backup/spfile_pre_awr_recreate.ora' from pfile;

--- if spfile is in diskgroup
---
SQL> create pfile='/backup/pfile_pre_awr_recreate.ora' from spfile='spfile_dg_path';




2.2: Set the following database parameters

If sga_target is not 0 (SGA tuning is enabled), then in pfile or spfile set the following memory parameters. This is to ensure that minimum required memory is available for each of the database memory  pools


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;

----- Reset the sga_target and memory_target parameters to avoid
----- ORA-00848: STATISTICS_LEVEL cannot be set to BASIC with SGA_TARGET or MEMORY_TARGET
-----
alter system reset sga_target scope= spfile;
alter system reset memory_target scope= spfile; 
alter system reset memory_max_target scope=spfile;


---- Set the statistics Level to Basic
----
alter system set statistics_level=basic scope=spfile;


-- Set the parameter cluster_database=false
-- applicable for RAC environment
--
alter system set cluster_database = false scope = spfile;





3: Shutdown and startup the database in RESTRICTED session. This is to make sure that no transactions occur against the database during the course of AWR recreation

--- In case of RAC, startup up only one instance
SQL> shut immediate

SQL> startup restrict;


4: Drop and Recreate the AWR Repository

---- make sure there are no objects from the following query after running catnoawr.sql
---- select table_name from dba_tables where table_name like 'WRM$_%' or table_name like 'WRH$_%';
---- If the query returns objects, drop them manually and then continue with recreation
----
SQL> start ?/rdbms/admin/catnoawr.sql

SQL> alter system flush shared pool;

SQL> start ?/rdbms/admin/catawrtb.sql

SQL> start ?/rdbms/admin/utlrp.sql

SQL> start ?/rdbms/admin/execsvrm.sql




5: Check for INVALID objects in the database and compile them manually
   
SQL> select owner,object_name,object_type from dba_objects where status<>’VALID’

SQL> alter object_type owner.object_name compile [body];




6: Restore the original parameter file (pfile/spfile) from the backup
   
SQL> create spfile from pfile='/backup/pfile_pre_awr_recreate.ora';




7: startup the database with the original parameter file (pfile/spfile)
--- In case of RAC, use srvctl to start all the database Instances
---
$srvctl start database -d [db_name];

or

SQL> startup




8:Take two AWR snapshots from the database

SQL> exec dbms_workload_repository.create_snapshot;

--- Wait for FIVE minutes and take another snapshot
---

SQL> exec dbms_workload_repository.create_snapshot;




9: Create a AWR report to validate the AWR functionality
SQL> start ?/rdbms/admin/awrrpt.sql