由于某种原因,比如数据异常断电,导致awr数据严重不一致,awr部分表损坏等情况,需要重建awr,可以参考如下步骤进行重建,本文主要针对目前主流的10g和11g版本数据库,12c未进行测试停止awr自动收集信息 方法1:参数调整
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;
--内存值可以根据实际情况调整
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;
方法2:使用包/参数
For
10g, you need
to
download the package DBMS_AWR.DISABLE_AWR available
at
Note 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, use the command:
begin
dbms_awr.disable_awr();
end
;
For
11g, use the parameter control_management_pack_access
to
disable it
alter
system
set
control_management_pack_access = NONE scope = both;
方法1:需要重启数据库 如果选择方法2,忽略此步骤
sqlplus /nolog
connect
/
as
sysdba
shutdown immediate
startup
restrict
删除AWR
start ?/rdbms/admin/catnoawr.sql
--由于Bug 5376177在10.2.0.1/2中可能没有catnoawr.sql文件,可以从10.2.0.3/4中拷贝过来
alter
system flush shared_pool;
--验证awr数据数据删除情况
select
table_name
from
dba_tables
where
table_name
like
'WRM$_%'
or
table_name
like
'WRH$_%'
;
如果有记录存在,使用
drop
table
语句删除
创建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
重启数据库 方法1对应处理
create
spfile
from
pfile=
'/tmp/pfile.xifenfei'
;
shutdown immediate
startup
方法2对应处理
--11g
alter
system
set
control_management_pack_access =
'DIAGNOSTIC+TUNING'
scope = both;
shutdown immediate
startup
--10g
@dbmsnoawr.plb
begin
dbms_awr.enable_awr();
end
;
处理无效对象
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;
测试AWR
--收集快照
exec
dbms_workload_repository.create_snapshot;
--wait for 5 min
exec
dbms_workload_repository.create_snapshot;
--生成awr报告
start $ORACLE_HOME/rdbms/admin/awrrpt.sql
注意BUG 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参考文档 How to Recreate The AWR ( AUTOMATIC WORKLOAD ) Repository ? (Doc ID 782974.1) How to Recreate Tables in the SYSAUX Tablespace (Doc ID 333665.1)