How to Recreate The AWR ( AUTOMATIC WORKLOAD ) Repository—重建awr

联系:手机/微信(+86 17813235971) QQ(107644445)

标题:How to Recreate The AWR ( AUTOMATIC WORKLOAD ) Repository—重建awr

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

由于某种原因,比如数据异常断电,导致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)

One thought on “How to Recreate The AWR ( AUTOMATIC WORKLOAD ) Repository—重建awr

  1. 不能sga自动管理,不然statistics_level=basic报错

    SQL&gt; alter system set statistics_level=basic;
    alter system set statistics_level=basic
    *
    ERROR at line 1:
    ORA-02097: parameter cannot be modified because specified value is invalid
    ORA-00830: cannot set statistics_level to BASIC with auto-tune SGA enabled
    

    有可能出现type没有删除被catnoawr.sql脚本删除,在后续执行catawrtb.sql报错,不过该类错误可以忽略

    create type AWRSQRPT_TEXT_TYPE
                *
    ERROR at line 1:
    ORA-00955: name is already used by an existing object
    create type AWRSQRPT_TEXT_TYPE_TABLE
                *
    ERROR at line 1:
    ORA-00955: name is already used by an existing object
    
  2. start ?/rdbms/admin/execsvrm.sql出现如下错误

    BEGIN
    *
    ERROR at line 1:
    ORA-04068: existing state of packages has been discarded
    ORA-04061: existing state of package &quot;SYS.DBMS_SWRF_INTERNAL&quot; has been
    invalidated
    ORA-04065: not executed, altered or dropped package &quot;SYS.DBMS_SWRF_INTERNAL&quot;
    ORA-06508: PL/SQL: could not find program unit being called:
    &quot;SYS.DBMS_SWRF_INTERNAL&quot;
    ORA-06512: at line 3
    

    解决方法:
    alter package dbms_swrf_internal compile;
    alter package dbms_swrf_internal compile body;
    start ?/rdbms/admin/execsvrm.sql

  3. SQL&gt; exec dbms_workload_repository.create_snapshot;
    BEGIN dbms_workload_repository.create_snapshot; END;
    *
    ERROR at line 1:
    ORA-13518: Invalid database id (3832954167)
    ORA-06512: at &quot;SYS.DBMS_WORKLOAD_REPOSITORY&quot;, line 99
    ORA-06512: at &quot;SYS.DBMS_WORKLOAD_REPOSITORY&quot;, line 122
    ORA-06512: at line 1
    

    由于start ?/rdbms/admin/execsvrm.sql执行未成功导致,需要
    alter package dbms_swrf_internal compile;
    alter package dbms_swrf_internal compile body;
    start ?/rdbms/admin/execsvrm.sql

评论已关闭。