重建AWR Repository

由于某个生产库上面awr的信息没有清理掉,导致sysaux表空间占用非常多

于是有了下面这个重建AWR的操作了

1、备份数据库、备份spfile

2、修改spfile的参数,停止awr统计信息的收集

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 set sga_target=0 scope= spfile;
alter system set memory_target=0 scope= spfile;
alter system set statistics_level=basic scope=spfile;

3、关闭数据库并启动到restrict模式
sqlplus /nolog
connect / as sysdba
shutdown immediate
startup restrict

4、删除AWR组件
start ?/rdbms/admin/catnoawr.sql
完成后检查WRH$和WRM$开头的表是不是都清理掉了
如果没有的话用类似下面的SQL删掉:
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

5、重建AWR组件
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

在做execsvrm.sql的时候可能会报如下的错误:
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

需要重新编译dbms_swrf_internal这个包:

alter package dbms_swrf_internal compile;
alter package dbms_swrf_internal compile body;

6、将备份的spfile拷贝回来,然后重启数据库到normal模式
sqlplus /nolog
connect / as sysdba
shutdown immediate
startup

7、检查是否还有失效对象:

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;

8、完全删除AWR历史信息
exec DBMS_STATS.PURGE_STATS(DBMS_STATS.PURGE_ALL);

9、测试AWR是否正常
exec dbms_workload_repository.create_snapshot;
等待5分钟再次执行:
exec dbms_workload_repository.create_snapshot;

然后检查是否可以正常生产AWR报告
@?/rdbms/admin/awrrpt.sql

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>