在一次生成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.
其實這幾個Host對於我來說，非常熟悉，一個是數據庫遷移前的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.
