oracle突然变慢 awr,案例:Oracle awr 数据严重不一致 awr部分表损坏等情况 需要重建awr...

天萃荷净

Oracle数据库服务器突然断电，导致AWR部分表出现问题，记录重建awr的步骤过程

由于某种原因，比如数据异常断电，导致awr数据严重不一致，awr部分表损坏等情况，需要重建awr，可以参考如下步骤进行重建,本文主要针对目前主流的10g和11g版本数据库,12c未进行测试

1.停止Oracle 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

2.删除Oracle 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 语句删除

3.创建Oracle 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;

4.处理Oracle无效对象

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 .compile;

alter package .compile body;

alter view .compile;

alter trigger 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)

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之案例:Oracle awr 数据严重不一致 awr部分表损坏等情况 需要重建awr