1. Check current snap_interval and disable snapshot creation, see below.
Check current AWR snapshot interval as follows:
sqlplus /nolog
connect / as sysdba
select snap_interval from wrm$_wr_control;
Disable AWR snapshot creation as follows:
execute dbms_workload_repository.modify_snapshot_settings(interval => 0);
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
Execute script for deleting AWR.
— Run Script
start $ORACLE_HOME/rdbms/admin/catnoawr.sql
Check to see if all the objects are dropped :
SQL> 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%’;
If there are objects after running catnoawr.sql or an error occurs while running script, please drop objects manually examples below:
drop table WRH$_SYSMETRIC_HISTORY_BL CASCADE CONSTRAINTS; –> note this table will remain 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$_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;
4. Now create AWR
Please check recycle object details before doing this task, In some cases ,You may need to purge the recycle bin to avoid any error.
PURGE RECYCLEBIN;
start $ORACLE_HOME/rdbms/admin/catawrtb.sql
–On 11g and higher, it is necessary to also run:
start $ORACLE_HOME/rdbms/admin/execsvrm.sql
start $ORACLE_HOME/rdbms/admin/utlrp.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:
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 below.
5. 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.
Check for invalids with below SQL:
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 ;
Recompile manually if needed please:
alter package dbms_swrf_internal compile;
alter package dbms_swrf_internal compile body;
6. Restart instance in normal mode
sqlplus /nolog
connect / as sysdba
shutdown immediate
startup
7. Re-enable auto AWR snapshot creation with the interval you had before, example below is every 60 minutes.
execute dbms_workload_repository.modify_snapshot_settings(interval => 60);
8. Verify you can AWR snapshots and you can generate an AWR report
Generate manual snapshot and AWR report:
–generate AWR snaphot
exec dbms_workload_repository.create_snapshot;
–wait for 5 min and generate another
exec dbms_workload_repository.create_snapshot;
–generate test AWR report
start $ORACLE_HOME/rdbms/admin/awrrpt.sql
9. If further assistance is needed or errors arise while performing recreation of AWR.
sqlplus / as sysdba
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
);
