ORACLE AWR报告生成过程出现多个实例记录分析

在一次生成AWR报告中，发现在“Instances in this Workload Repository schema”部分，出现了多个实例记录信息（host敏感信息被用host1,host2,host3替换）。具体信息如下截图所示：

SQL> @?/rdbms/admin/awrrpt

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 3990839260 SCM2                1 SCM2


Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: html

Type Specified:  html


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
  3990839260        1 SCM2         SCM2         host1
* 3990839260        1 SCM2         SCM2         host2
  3990839260        1 SCM2         SCM2         host3

Using 3990839260 for database Id
Using          1 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.

 

其实这几个hostname对于我来说，非常熟悉，一个是数据库迁移前的hostname，一个是hostname修改前的名称，一个是hostname修改后的名称。那么为什么出现这个情况呢？我们先从$ORACLE_HOME/rdbms/admin/awrrpt.sql脚本开始，看看这些记录是怎么获取的。

[oracle@MyOracle admin]$ more awrrpt.sql

Rem $Header: awrrpt.sql 24-oct-2003.12:04:53 pbelknap Exp $
Rem
Rem awrrpt.sql
Rem
Rem Copyright (c) 1999, 2003, Oracle Corporation.  All rights reserved.
Rem
Rem    NAME
Rem      awrrpt.sql
Rem
Rem    DESCRIPTION
Rem      This script defaults the dbid and instance number to that of the
Rem      current instance connected-to, then calls awrrpti.sql to produce
Rem      the Workload Repository report.
Rem
Rem    NOTES
Rem      Run as select_catalog privileges.
Rem      This report is based on the Statspack report.
Rem
Rem      If you want to use this script in an non-interactive fashion,
Rem      see the 'customer-customizable report settings' section in
Rem      awrrpti.sql
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    pbelknap    10/24/03 - swrfrpt to awrrpt
Rem    pbelknap    10/14/03 - moving params to rpti
Rem    pbelknap    10/02/03 - adding non-interactive mode cmnts
Rem    mlfeng      09/10/03 - heading on
Rem    aime        04/25/03 - aime_going_to_main
Rem    mlfeng      01/27/03 - mlfeng_swrf_reporting
Rem    mlfeng      01/13/03 - Update comments
Rem    mlfeng      07/08/02 - swrf flushing
Rem    mlfeng      06/12/02 - Created
Rem

--
-- Get the current database/instance information - this will be used
-- later in the report along with bid, eid to lookup snapshots

set echo off heading on underline on;
column inst_num  heading "Inst Num"  new_value inst_num  format 99999;
column inst_name heading "Instance"  new_value inst_name format a12;
column db_name   heading "DB Name"   new_value db_name   format a12;
column dbid      heading "DB Id"     new_value dbid      format 9999999999 just c;

prompt
prompt Current Instance
prompt ~~~~~~~~~~~~~~~~

select d.dbid            dbid
     , d.name            db_name
     , i.instance_number inst_num
     , i.instance_name   inst_name
  from v$database d,
       v$instance i;

@@awrrpti

undefine num_days;
undefine report_type;
undefine report_name;
undefine begin_snap;
undefine end_snap;
--
-- End of file

 

如上所示，其实awrrpt.sql里面没有多少料，主要功能还是在awrrpti.sql中实现的。在awrrpti.sql里面，看到通过下面部分信息，定位到AWR里面的信息来源于awrinput.sql

 

检查awrinput.sql脚本，发现AWR那段数据来自于下面SQL语句

--
-- Request the DB Id and Instance Number, if they are not specified

column instt_num  heading "Inst Num"  format 99999;
column instt_name heading "Instance"  format a12;
column dbb_name   heading "DB Name"   format a12;
column dbbid      heading "DB Id"     format a12 just c;
column host       heading "Host"      format a12;

prompt
prompt
prompt Instances in this Workload Repository schema
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select distinct
       (case when cd.dbid = wr.dbid and
                  cd.name = wr.db_name and
                  ci.instance_number = wr.instance_number and
                  ci.instance_name   = wr.instance_name   and
                  ci.host_name       = wr.host_name
             then '* '
             else '  '
        end) || wr.dbid   dbbid
     , wr.instance_number instt_num
     , wr.db_name         dbb_name
     , wr.instance_name   instt_name
     , wr.host_name       host
  from dba_hist_database_instance wr, v$database cd, v$instance ci;

prompt
prompt Using &&dbid for database Id
prompt Using &&inst_num for instance number

 

最后发现原因是:视图dba_hist_database_instance里面存放的历史记录，记录了host的变化，所以上面SQL，出现了三条记录。

dba_hist_database_instance视图脚本

select dbid, instance_number, startup_time, parallel, version,
       db_name, instance_name, host_name, last_ash_sample_id
from WRM$_DATABASE_INSTANCE

 

使用下面脚本找到对应的记录，然后删除那些历史数据就可以了。但是手工删除这个表的数据是否会有问题呢? 也在测试环境测试了一下，没有任何问题，和同事讨论了一下，觉得这个数据的删除，不会出现什么问题。

SQL> SELECT HOST_NAME, MAX(STARTUP_TIME) FROM dba_hist_database_instance
  2  GROUP BY HOST_NAME;


SQL> DELETE FROM dba_hist_database_instance WHERE STARTUP_TIME <=TO_DATE('2016-05-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS');
COMMIT;
266 rows deleted.

SQL>COMMIT;