Recreate the AWR Report in Oracle

Recreate or Reconfigure the AWR Report in Oracle

If AWR is not working properly and give error on fetching AWR report or while create snapshot manually then you need to reconfigure the repository of AWR by uninstalling or install again.

Deinstall the AWR reports

1. Check the following parameter.

show parameter cluster_database
show parameter statistics_level
show parameter sga_target

2. Create pfile from spfile.

create pfile='C:\initpfile.txt' from spfile;

3. Set the following parameters depends upon following conditions.

--Check SGA_TARGET
Show parameter sga_target

---IF SGA_TARGET is 0 then set following and skip all parameters set bellow (Only check RAC environment)
alter system set statistics_level = basic scope = spfile;

-- In 10g or 11g version, if sga_target is not 0, then in pfile or spfile set the following parameters:
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;

--In RAC environment
alter system set cluster_database = false scope = spfile;

4. Shutdown database and startup in restrict mode.

shutdown immediate
startup restrict

5. Drop the AWR objects.

--- Run the following Script
start ?/rdbms/admin/catnoawr.sql

--Check the object is exists
select table_name from dba_tables where table_name like 'WRM$_%' or table_name like 'WRH$_%';

--- If objects still exists then delete it manually
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

6. Purge the Recycle bin in Oracle.

PURGE RECYCLEBIN;

Install and Create the AWR objects

7. Login with Database and execute the following scripts.
Note: database is in restrict mode.

@?/rdbms/admin/catawrtb.sql
@?/rdbms/admin/utlrp.sql

--On 11g and above
@?/rdbms/admin/execsvrm.sql

--If you got error
@?/rdbms/admin/execsvrm.sql
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 valid the package manually:
alter package dbms_swrf_internal compile;
alter package dbms_swrf_internal compile body;

8. Recreate the spfile from pfile backup to change the parameter.

Create spfile from pfile='C:\initpfile.txt';

9. Start the database in read write mode.

Shutdown immediate
Startup

10. Check the AWR with snapshot command option.

-- Create snapshot
exec dbms_workload_repository.create_snapshot;
--wait for 1 min, create snapshot
exec dbms_workload_repository.create_snapshot;

--Fetch the reports
@?\rdbms\admin\awrrpt.sql;

2 thoughts on “Recreate the AWR Report in Oracle

  1. Pingback: ORA-13516 AWR Operation failed: SWRF Schema not initialized | Smart way of Technology

  2. Pingback: ORA-02291: integrity constraint(SYS.WRM$_SNAPSHOT_FK) violated | Smart way of Technology

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.