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

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.

[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

--

-- 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

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;