Example of rebuilding awr for oracle 10g and 11g databases

For some reason, such as data abnormal power, resulting in serious awr data inconsistency, awr part of the table damage, and so on, need to rebuild awr, can refer to the following steps for reconstruction, this article mainly for the current mainstream 10g and 11g version of the database, 12c did not test
Stop AWR automatically gather information

Method 1: Parameter tuning

Sqlplus/nolog
Connect/as SYSDBA
Create pfile= '/tmp/pfile.xifenfei ' from 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 = 100m scope = SPFile;
alter system Set Large_pool_size = 50m scope = SPFile;

--Memory value can be adjusted according to the actual situation

Alter system reset Sga_target scope = SPFile sid= ' * ';
Alter system set STATISTICS_LEVEL=BASIC Scope=spfile;
--11g
alter system reset Memory_target scope= spfile sid= ' * ';
alter system reset Memory_max_target scope=spfile sid= ' * ';
Alter system set sga_target=0 scope= SPFile;
Alter system set memory_target=0 scope= SPFile;
--rac
alter system Set Cluster_database = False scope = SPFile;

Method 2: Use Package/Parameter

For 10g, your need to download the package Dbms_awr. Disable_awr available at 436386.1 Package
For disabling AWR without a diagnostic Pack license in Oracle to
Install, run the package as SYS from Sql*plus:
@dbmsnoawr. PLB
To execute the package and use the command:
Begin Dbms_awr.disable_awr (); End

For 11g with the parameter control_management_pack_access to disable it
alter system Set control_management_pack_access = NONE scope = both;

Method 1: Need to restart the database

If you select Method 2, ignore this step

Sqlplus/nolog
Connect/as SYSDBA
Shutdown immediate
Startup restrict
Delete Awr
Start?/rdbms/admin/catnoawr.sql
--because bug 5376177 may not have catnoawr.sql files in 10.2.0.1/2, you can copy them from the 10.2.0.3/4.

alter system flush Shared_pool;

--Verifying AWR data Data deletion

Select table_name from dba_tables where table_name like ' wrm$_% ' or table_name like ' wrh$_% ';
If a record exists, use the DROP TABLE statement to delete the

Create Awr

Start?/rdbms/admin/catawrtb.sql
Start?/rdbms/admin/utlrp.sql
--11g
Start?/rdbms/admin/execsvrm.sql
Alter package dbms_swrf_internal compile;
Alter package dbms_swrf_internal compile body;
Start?/rdbms/admin/execsvrm.sql
Restart the database
Method 1 Correspondence processing
Create SPFile from pfile= '/tmp/pfile.xifenfei ';
Shutdown immediate
Startup

Method 2 Correspondence Processing

--11g
alter system set control_management_pack_access = ' diagnostic+tuning ' scope = both;
Shutdown immediate
Startup

--10g
@dbmsnoawr. PLB
Begin Dbms_awr.enable_awr (); end;

Handling Invalid Objects

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;

Test AWR

--Collecting snapshots
exec dbms_workload_repository.create_snapshot;
--wait for 5 min
exec dbms_workload_repository.create_snapshot;

--Generate AWR report

Start $ORACLE _home/rdbms/admin/awrrpt.sql

Pay attention to Bugs

bug:17063159 Catnoawr. SQL not dropping all AWR TABLES
bug:10211252 ' DROP TABLE wrm$_wr_usage MISSING in Catnoawr. Sql
bug:9150463 cannot recreate the AWR on R11.1