How to Recreate The AWR ( AUTOMATIC WORKLOAD ) Repository ?
来源:互联网 发布:中拓互联 中文域名 编辑:程序博客网 时间:2024/05/01 16:54
APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.1.0.2 and laterInformation 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
- Or save the spfile before modifying:create pfile='/home/oracle/admin/dbs/init@.ora.20140122' from spfile;
- In 10g and 11g , 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 = 200m scope = spfile;
alter system set db_cache_size = 300m scope = spfile;
alter system set java_pool_size = 100 scope = spfile;
alter system set large_pool_size = 50 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;
-- Setting the parameter cluster_database only applicable
-- in RAC environment
--Check actual shared pool and buffer cache usage in AWR to make sure the settings are correct
alter system set cluster_database = false scope = spfile;
- In 11g, you may need to set both SGA_TARGET and MEMORY_TARGET to 0: alter system set sga_target=0 scope= spfile;
alter system set memory_target=0 scope= spfile; - otherwise database may not startup and will fail with the errors below:SQL> startup restrict
ORA-01078: failure in processing system parameters
ORA-00824: cannot set SGA_TARGET or MEMORY_TARGET due to existing internal settings
ORA-00848: STATISTICS_LEVEL cannot be set to BASIC with SGA_TARGET or MEMORY_TARGET
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 11gdrop AWR
start ?/rdbms/admin/catnoawr.sql
alter system flush shared_pool;Check to see if all the objects are dropped :
SQL> select table_name from dba_tables where table_name like 'WRM$_%' or table_name like 'WRH$_%';If there are objects after running catnoawr.sql, drop them manually:
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
Now create AWR:start ?/rdbms/admin/catawrtb.sql
start ?/rdbms/admin/utlrp.sql
--On 11g it is necessary to also run:start ?/rdbms/admin/execsvrm.sqlNote: 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 recompile the object(s).alter package dbms_swrf_internal compile;It is important to do this even if the object(s) (dbms_swrf_internal in this case) appear valid. You will then need to re-execute the "?/rdbms/admin/execsvrm.sql" script.
alter package dbms_swrf_internal compile body;
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.Also can reset the parameter from copy of the spfile:
create spfile from pfile='/home/oracle/admin/dbs/init@.ora.20140122' ;
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:
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;
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
9) If further assistance is needed or errors arise while performing recreation of AWR, please open an sr.
- How to Recreate The AWR ( AUTOMATIC WORKLOAD ) Repository ?
- AWR - Automatic Workload Repository
- AWR(Automatic Workload Repository)
- AWR (Automatic Workload Repository) Report
- Oracle AWR (Automatic Workload Repository)
- AWR (Automatic Workload Repository)
- Oracle AWR(Automatic workload repository)
- Oracle AWR(Automatic Workload Repository) 说明
- Oracle AWR(Automatic Workload Repository) 说明
- Automatic Workload Repository(自动负载信息库,AWR)
- 2.Which two statements are true about the Automatic Workload Repository (AWR)
- AWR(Automatic Workload Repository)——概述(1)!
- AWR(Automatic Workload Repository)——分析(3)!
- AWR(Automatic Workload Repository)——分析(4)!
- 自动工作负载库(Automatic Workload Repository,AWR)
- Oracle AWR(Automatic Workload Repository) 说明 --转自tianlesoftware
- Oracle AWR(Automatic Workload Repository) 简单说明
- 自动工作负载库(Automatic Workload Repository,AWR)
- 我要如此改造HR部门!你呢?
- CSS 框模型和定位
- MVC使视图不使用布局或模板页
- bug之空间不足
- js eval方法解析java后台传来的json字符串
- How to Recreate The AWR ( AUTOMATIC WORKLOAD ) Repository ?
- 刚切换就让PullToRefreshListView呈现下拉状态刷新
- TCP 的那些事儿(转载)
- c语言的多文件开发
- 安卓系统学习--获取Google安卓系统源码
- Ubuntu下创建快捷方式(以myeclipse为例)
- Android APP: BlackContact 短信拦截
- WSOP欧洲赛在德国柏林开战
- java线程第六课:同步锁Lock