oracle10g 重建AWR

oracle version：Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
OS：AIX 5300-11

  1. 检查当前 snapshot interval
sqlplus / as sysdba
SQL> select snap_interval from wrm$_wr_control;

SNAP_INTERVAL
---------------------------------------------------------------------------
+00000 01:00:00.0
  1. 关闭 AWR 自动创建 snapshot
SQL> execute dbms_workload_repository.modify_snapshot_settings(interval => 0);

PL/SQL procedure successfully completed.

SQL> select snap_interval from wrm$_wr_control;

SNAP_INTERVAL
---------------------------------------------------------------------------
+40150 00:00:00.0

2.清除AWR 现有表，该版本没有catnoawr.sql

#ls /oracle/HRP/102_64/rdbms/admin/catnoawr.sql
ls: 0653-341 The file /oracle/HRP/102_64/rdbms/admin/catnoawr.sql does not exist.

无奈。。手动删除

col wnm format a100

SELECT 'DROP TABLE ' || table_name || ' CASCADE CONSTRAINTS;' as wnm
FROM dba_tables where table_name like 'WRM$_%' or table_name like 'WRH$_%' or table_name like 'AWR%';

DROP TABLE WRH$_WAITSTAT_BL CASCADE CONSTRAINTS;
DROP TABLE WRH$_WAITSTAT CASCADE CONSTRAINTS;
DROP TABLE WRH$_WAITCLASSMETRIC_HISTORY CASCADE CONSTRAINTS;
DROP TABLE WRH$_UNDOSTAT CASCADE CONSTRAINTS;
DROP TABLE WRH$_THREAD CASCADE CONSTRAINTS;
DROP TABLE WRH$_TEMPSTATXS CASCADE CONSTRAINTS;
DROP TABLE WRH$_TEMPFILE CASCADE CONSTRAINTS;
DROP TABLE WRH$_TABLESPACE_STAT_BL CASCADE CONSTRAINTS;
DROP TABLE WRH$_TABLESPACE_STAT CASCADE CONSTRAINTS;
DROP TABLE WRH$_TABLESPACE_SPACE_USAGE CASCADE CONSTRAINTS;
DROP TABLE WRH$_SYS_TIME_MODEL_BL CASCADE CONSTRAINTS;
DROP TABLE WRH$_SYS_TIME_MODEL CASCADE CONSTRAINTS;
DROP TABLE WRH$_SYSTEM_EVENT_BL CASCADE CONSTRAINTS;
DROP TABLE WRH$_SYSTEM_EVENT CASCADE CONSTRAINTS;
DROP TABLE WRH$_SYSSTAT_BL CASCADE CONSTRAINTS;
DROP TABLE WRH$_SYSSTAT CASCADE CONSTRAINTS;
DROP TABLE WRH$_SYSMETRIC_SUMMARY CASCADE CONSTRAINTS;
DROP TABLE WRH$_SYSMETRIC_HISTORY CASCADE CONSTRAINTS;
DROP TABLE WRH$_STREAMS_POOL_ADVICE CASCADE CONSTRAINTS;
DROP TABLE WRH$_STREAMS_CAPTURE CASCADE CONSTRAINTS;
DROP TABLE WRH$_STREAMS_APPLY_SUM CASCADE CONSTRAINTS;
DROP TABLE WRH$_STAT_NAME CASCADE CONSTRAINTS;
DROP TABLE WRH$_SQL_WORKAREA_HISTOGRAM CASCADE CONSTRAINTS;
DROP TABLE WRH$_SQL_SUMMARY CASCADE CONSTRAINTS;
DROP TABLE WRH$_SQL_PLAN CASCADE CONSTRAINTS;
DROP TABLE WRH$_SQL_BIND_METADATA CASCADE CONSTRAINTS;
DROP TABLE WRH$_SQLTEXT CASCADE CONSTRAINTS;
DROP TABLE WRH$_SQLSTAT_BL CASCADE CONSTRAINTS;
DROP TABLE WRH$_SQLSTAT CASCADE CONSTRAINTS;
DROP TABLE WRH$_SHARED_POOL_ADVICE CASCADE CONSTRAINTS;
DROP TABLE WRH$_SGA_TARGET_ADVICE CASCADE CONSTRAINTS;
DROP TABLE WRH$_SGASTAT_BL CASCADE CONSTRAINTS;
DROP TABLE WRH$_SGASTAT CASCADE CONSTRAINTS;
DROP TABLE WRH$_SGA CASCADE CONSTRAINTS;
DROP TABLE WRH$_SESS_TIME_STATS CASCADE CONSTRAINTS;
DROP TABLE WRH$_SESSMETRIC_HISTORY CASCADE CONSTRAINTS;
DROP TABLE WRH$_SERVICE_WAIT_CLASS_BL CASCADE CONSTRAINTS;
DROP TABLE WRH$_SERVICE_WAIT_CLASS CASCADE CONSTRAINTS;
DROP TABLE WRH$_SERVICE_STAT_BL CASCADE CONSTRAINTS;
DROP TABLE WRH$_SERVICE_STAT CASCADE CONSTRAINTS;
DROP TABLE WRH$_SERVICE_NAME CASCADE CONSTRAINTS;
DROP TABLE WRH$_SEG_STAT_OBJ CASCADE CONSTRAINTS;
DROP TABLE WRH$_SEG_STAT_BL CASCADE CONSTRAINTS;
DROP TABLE WRH$_SEG_STAT CASCADE CONSTRAINTS;
DROP TABLE WRH$_RULE_SET CASCADE CONSTRAINTS;
DROP TABLE WRH$_ROWCACHE_SUMMARY_BL CASCADE CONSTRAINTS;
DROP TABLE WRH$_ROWCACHE_SUMMARY CASCADE CONSTRAINTS;
DROP TABLE WRH$_RESOURCE_LIMIT CASCADE CONSTRAINTS;
DROP TABLE WRH$_PROCESS_MEMORY_SUMMARY CASCADE CONSTRAINTS;
DROP TABLE WRH$_PGA_TARGET_ADVICE CASCADE CONSTRAINTS;
DROP TABLE WRH$_PGASTAT CASCADE CONSTRAINTS;
DROP TABLE WRH$_PARAMETER_NAME CASCADE CONSTRAINTS;
DROP TABLE WRH$_PARAMETER_BL CASCADE CONSTRAINTS;
DROP TABLE WRH$_PARAMETER CASCADE CONSTRAINTS;
DROP TABLE WRH$_OSSTAT_NAME CASCADE CONSTRAINTS;
DROP TABLE WRH$_OSSTAT_BL CASCADE CONSTRAINTS;
DROP TABLE WRH$_OSSTAT CASCADE CONSTRAINTS;
DROP TABLE WRH$_OPTIMIZER_ENV CASCADE CONSTRAINTS;
DROP TABLE WRH$_MTTR_TARGET_ADVICE CASCADE CONSTRAINTS;
DROP TABLE WRH$_METRIC_NAME CASCADE CONSTRAINTS;
DROP TABLE WRH$_LOG CASCADE CONSTRAINTS;
DROP TABLE WRH$_LIBRARYCACHE CASCADE CONSTRAINTS;
DROP TABLE WRH$_LATCH_PARENT_BL CASCADE CONSTRAINTS;
DROP TABLE WRH$_LATCH_PARENT CASCADE CONSTRAINTS;
DROP TABLE WRH$_LATCH_NAME CASCADE CONSTRAINTS;
DROP TABLE WRH$_LATCH_MISSES_SUMMARY_BL CASCADE CONSTRAINTS;
DROP TABLE WRH$_LATCH_MISSES_SUMMARY CASCADE CONSTRAINTS;
DROP TABLE WRH$_LATCH_CHILDREN_BL CASCADE CONSTRAINTS;
DROP TABLE WRH$_LATCH_CHILDREN CASCADE CONSTRAINTS;
DROP TABLE WRH$_LATCH_BL CASCADE CONSTRAINTS;
DROP TABLE WRH$_LATCH CASCADE CONSTRAINTS;
DROP TABLE WRH$_JAVA_POOL_ADVICE CASCADE CONSTRAINTS;
DROP TABLE WRH$_INST_CACHE_TRANSFER_BL CASCADE CONSTRAINTS;
DROP TABLE WRH$_INST_CACHE_TRANSFER CASCADE CONSTRAINTS;
DROP TABLE WRH$_INSTANCE_RECOVERY CASCADE CONSTRAINTS;
DROP TABLE WRH$_FILESTATXS_BL CASCADE CONSTRAINTS;
DROP TABLE WRH$_FILESTATXS CASCADE CONSTRAINTS;
DROP TABLE WRH$_FILEMETRIC_HISTORY CASCADE CONSTRAINTS;
DROP TABLE WRH$_EVENT_NAME CASCADE CONSTRAINTS;
DROP TABLE WRH$_ENQUEUE_STAT CASCADE CONSTRAINTS;
DROP TABLE WRH$_DLM_MISC_BL CASCADE CONSTRAINTS;
DROP TABLE WRH$_DLM_MISC CASCADE CONSTRAINTS;
DROP TABLE WRH$_DB_CACHE_ADVICE_BL CASCADE CONSTRAINTS;
DROP TABLE WRH$_DB_CACHE_ADVICE CASCADE CONSTRAINTS;
DROP TABLE WRH$_DATAFILE CASCADE CONSTRAINTS;
DROP TABLE WRH$_CURRENT_BLOCK_SERVER CASCADE CONSTRAINTS;
DROP TABLE WRH$_CR_BLOCK_SERVER CASCADE CONSTRAINTS;
DROP TABLE WRH$_COMP_IOSTAT CASCADE CONSTRAINTS;
DROP TABLE WRH$_BUFFER_POOL_STATISTICS CASCADE CONSTRAINTS;
DROP TABLE WRH$_BUFFERED_SUBSCRIBERS CASCADE CONSTRAINTS;
DROP TABLE WRH$_BUFFERED_QUEUES CASCADE CONSTRAINTS;
DROP TABLE WRH$_BG_EVENT_SUMMARY CASCADE CONSTRAINTS;
DROP TABLE WRH$_ACTIVE_SESSION_HISTORY_BL CASCADE CONSTRAINTS;
DROP TABLE WRH$_ACTIVE_SESSION_HISTORY CASCADE CONSTRAINTS;
DROP TABLE WRM$_WR_CONTROL CASCADE CONSTRAINTS;
DROP TABLE WRM$_SNAP_ERROR CASCADE CONSTRAINTS;
DROP TABLE WRM$_SNAPSHOT CASCADE CONSTRAINTS;
DROP TABLE WRM$_DATABASE_INSTANCE CASCADE CONSTRAINTS;
DROP TABLE WRM$_BASELINE CASCADE CONSTRAINTS;

3.运行脚本执行重建 PS： 官方推荐清空回收站，避免报错

SQL> PURGE RECYCLEBIN;

Recyclebin purged.

新建AWR
@?/rdbms/admin/catawrtb.sql
@?/rdbms/admin/utlrp.sql
  1. 修改自动创建 snapshot 的时间为 60分钟
execute dbms_workload_repository.modify_snapshot_settings(interval => 60);

查询快照控制时长，无结果
SQL> select snap_interval from wrm$_wr_control;

no rows selected
  1. 手动生成一个snapshot
exec dbms_workload_repository.create_snapshot;
遇到如下报错：

SQL> exec dbms_workload_repository.create_snapshot;
BEGIN dbms_workload_repository.create_snapshot; END;

*
ERROR at line 1:
ORA-13518: Invalid database id (2157644216)
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 10
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 33
ORA-06512: at line 1


SQL> 
SQL> select * from WRM$_DATABASE_INSTANCE ;

no rows selected

metalink 解决方案。。。要重启。。。

ORA-13518 : 'Invalid Database Id' On Call to DBMS_WORKLOAD_REPOSITORY Package [ID 984447.1]
https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=160973618424297&id=984447.1&_afrWindowMode=0&_adf.ctrl-state=nwuu2tn83_219

Changes
    Recently AWR was recreated using catnoawr.sql and catawrtb.sql. 
Cause
    When a function / procedure is called from DBMS_WORKLOAD_REPOSITORY package,  it refers table WRM$_DATABASE_INSTANCE to get dbid information.
        As the AWR repositories has been recreated , the table doesn't not have any data present in it. 
        
    SQL> select * from WRM$_DATABASE_INSTANCE ;
        no rows selected

Solution
    The WRM$_DATABASE_INSTANCE gets populated with instance information when the instance is rebooted. So, If you have recreated AWR using catnoawr.sql , catawrtb.sql , then you need to bounce the Instance . On restart , the table will be populated with the requisite information. Hence , better option for recreating AWR is to use startup restrict method. After the recreation activity is completed , shutdown the database and open it normally.

6.重启后问题解决

SQL> set line 233 pages 233
SQL> select * from WRM$_DATABASE_INSTANCE ;

    DBID INSTANCE_NUMBER STARTUP_TIME                                                                PAR VERSION           DB_NAME   INSTANCE_NAME    HOST_NAME
---------- --------------- --------------------------------------------------------------------------- --- ----------------- --------- ---------------- ----------------------------------------------------------------
LAST_ASH_SAMPLE_ID
------------------
2157644216               1 16-JAN-24 10.39.41.000 AM                                                   NO  10.2.0.2.0        HRP       HRP              hredb
                0
  1. 手动生成一个snapshot
SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL> select snap_interval from wrm$_wr_control;

SNAP_INTERVAL
---------------------------------------------------------------------------
+00000 01:00:00.0

8.生成awr报告

@$ORACLE_HOME/rdbms/admin/awrrpt.sql
posted @   Coye  阅读(26)  评论(0)  编辑  收藏  举报
相关博文：
· 闪回的开启与关闭
· oracle 的归档模式的 开启与关闭
· oracle system信息统计,​Oracle的SYSTEM和SYSAUX表空间 转载：https://blog.csdn.net/weixin_33724759/article/details/116294716
· 53.解析数据还原恢复的流程
· Oracle之ASH、AWR、ADDM区别
阅读排行：
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
· C#/.NET/.NET Core优秀项目和框架2025年2月简报
· DeepSeek在M芯片Mac上本地化部署
· 葡萄城 AI 搜索升级：DeepSeek 加持，客户体验更智能
历史上的今天：
2020-01-16 MGR监控的一些sql
点击右上角即可分享
微信分享提示