如何重建AWR

如何重建AWR
How to Recreate The AWR ( AUTOMATIC WORKLOAD ) Repository ? [ID 782974.1]
In this Document
Goal
Solution
References
Applies to:
Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 11.2.0.2 - Release: 10.1 to 11.2 Information in this document applies to any platform.
Goal
How to Recreate The AWR( AUTOMATIC WORK LOAD ) Repository
Solution
The best way to deinstall/install AWR is as follows:
1. Disable AWR statistics gathering by setting the statistics level to basic as follows:
Check settings for parameters as follows:
sqlplus /nolog
connect / as sysdba
show parameter cluster_database
show parameter statistics_level
show parameter sga_target
In 10g , if sga_target is not 0, then in pfile or spfile set the following parameters: The example below refers to spfile: alter system set shared_pool_size = scope = spfile;
alter system set db_cache_size = scope = spfile;
alter system set java_pool_size = scope = spfile;
alter system set large_pool_size = scope = spfile;
alter system set sga_target=0 scope= spfile;
alter system set statistics_level=basic scope=spfile;
-- Setting the parameter cluster_database only applicable
-- in RAC environment
alter system set cluster_database = false scope = spfile;
In 11g, please set parameters as explained in following note: Note 461100.1 STARTUP ERRORS ora-00824 cannot set sga_target with
statistics_level=BASIC (Doc ID )
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
The following scripts drop AWR tables and then recreates them.
After recreating ,utlrp is run in order to validate all views and objects dependent on the AWR tables.
-- On both 10g and 11g
start ?/rdbms/admin/catnoawr.sql
alter system flush shared_pool;
start ?/rdbms/admin/catawrtb.sql
start ?/rdbms/admin/utlrp.sql
--On 11g it is necessary to also run:
start ?/rdbms/admin/execsvrm.sql
See: Bug: 9150463 CANNOT RECREATE THE AWR ON R11.1
(closed as not a bug)
4) Reset the parameters shared_pool_size,db_cache_size, java_pool_size ,large_pool_size, sga_target
statistics_level and cluster_database to original values.
5) Restart instance in normal mode:
sqlplus /nolog
connect / as sysdba
shutdown immediate
startup
6) 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
pool 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 . compile;
alter package . compile body;
alter view . compile;
alter trigger <schema). compile;</schema).
7) To take the AWR snapshots:
exec dbms_workload_repository.create_snapshot;
--wait for 5 min
exec dbms_workload_repository.create_snapshot;
8) To create AWR report run the script:
start $ORACLE_HOME/rdbms/admin/awrrpt.sql
References
BUG:5376177 - CAN NOT FIND CATNOAWR.SQL.
BUG:9150463 - CANNOT RECREATE THE AWR ON R11.1
NOTE:243246.1 - SYSAUX New Mandatory Tablespace in Oracle 10g and higher
NOTE:748642.1 - How to Generate an AWR Report and Create Baselines
NOTE:1376357.1 - Recreate The AWR Repository Failed Due to DBMS_SWRF_INTERNAL package
相关内容
产品
Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition
关键字
AUTOMATIC WORKLOAD REPOSITORY; AWR;
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT; DROP; REBUILD;
REPOSITORY; RESTRICTED; SNAPSHOT; UTLRP
错误
ORA-824; ORA-830; ORA-2097。