Step-by-Step Guide to Recreating the Automatic Workload Repository (AWR) in Oracle Database



The Automatic Workload Repository (AWR) captures performance statistics for Oracle databases at regular intervals. If AWR data becomes corrupt, incomplete, or needs to be reset, it may be necessary to recreate the AWR repository.

Note: In a multitenant environment (CDB/PDB), these steps should be performed at the container level.

Step 1: Check and Disable AWR Snapshot Creation

Check Current Snapshot Interval

Before making any changes, check the current snapshot interval:


sqlplus / as sysdba
SELECT snap_interval FROM wrm$_wr_control;

Disable AWR Snapshot Creation


EXEC dbms_workload_repository.modify_snapshot_settings(interval => 0);

This prevents new snapshots from being created while AWR is being dropped.

Step 2: Restart Database in Restricted Mode (Not required if heavy transaction not happening in database)

This ensures no transactions occur while dropping AWR objects.


sqlplus / as sysdba  
SHUTDOWN IMMEDIATE;  
STARTUP RESTRICT;

Step 3: Drop and Recreate AWR Objects

Run the AWR Drop Script


START $ORACLE_HOME/rdbms/admin/catnoawr.sql;

Note: In Oracle 12.2+, running this script in restricted mode might result in ORA-00600: [opiodr] errors. If this happens, proceed manually.

Verify AWR Objects Are Dropped

Run this query to check if any AWR objects still exist:


SELECT 'DROP TABLE ' || table_name || ' CASCADE CONSTRAINTS;'
FROM dba_tables 
WHERE table_name LIKE 'WRM$_%' 
   OR table_name LIKE 'WRH$_%' 
   OR table_name LIKE 'AWR%';

Manually Drop Remaining Objects (if needed)

If any objects remain after running catnoawr.sql, manually drop them:


DROP TABLE WRH$_SYSMETRIC_HISTORY_BL CASCADE CONSTRAINTS;  
-- (This remains in 19c)
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$_DYN_REMASTER_STATS;
DROP TABLE WRM$_WR_USAGE;

Step 4: Recreate AWR Objects

Purge Recycle Bin (if needed)


PURGE RECYCLEBIN;

Run AWR Installation Scripts


START $ORACLE_HOME/rdbms/admin/catawrtb.sql;
START $ORACLE_HOME/rdbms/admin/execsvrm.sql;
START $ORACLE_HOME/rdbms/admin/utlrp.sql;

If you encounter the following errors:


ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "SYS.DBMS_SWRF_INTERNAL" has been invalidated

Then proceed to Step 5.

Step 5: Check and Recompile Invalid Objects

Run the following queries to check for invalid objects:

Check for Invalid Components


SELECT comp_name, status, version  
FROM dba_registry  
ORDER BY comp_name;

Check for Invalid Objects


SELECT object_name, owner, object_type  
FROM dba_objects  
WHERE status='INVALID'  
ORDER BY owner, object_type;

Manually Compile Objects (if needed)


ALTER PACKAGE dbms_swrf_internal COMPILE;
ALTER PACKAGE dbms_swrf_internal COMPILE BODY;

If objects appear valid but errors persist, re-run:


START $ORACLE_HOME/rdbms/admin/execsvrm.sql;

Step 6: Restart Database in Normal Mode


SHUTDOWN IMMEDIATE;
STARTUP;

Step 7: Re-enable AWR Snapshot Creation

Reset the snapshot interval to its original value (example: 60 minutes):


EXEC dbms_workload_repository.modify_snapshot_settings(interval => 60);

Step 8: Verify AWR Snapshots and Generate Reports

Manually Create Snapshots


EXEC dbms_workload_repository.create_snapshot;
-- Wait for 5 minutes and create another snapshot
EXEC dbms_workload_repository.create_snapshot;

Generate an AWR Report


START $ORACLE_HOME/rdbms/admin/awrrpt.sql;

This confirms that AWR is functioning properly.

Step 9: Handling Issues During AWR Recreation

If you still experience issues, open a Service Request (SR) with Oracle Support.

Common Issue:
If WRMS$_SNAPSHOT is missing, manually create it:


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




Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment