@@header

/*
*
*  Author     : Vishal Gupta
*  Purpose    : Generate AWR Report for RAC
*  Parameters : 1 - From Time ( YYYY-MM-DD HH24:MI:SS format) (Default = sysdate-1)
*               2 - To Time ( YYYY-MM-DD HH24:MI:SS format) (Default = sysdate)
*               3 - Instance ID List separated by comma( default value - All instances) 
*               4 - Report Type ( text/html, default value - html) 
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  17-Feb-14  Vishal Gupta  Changed order of input parameters
*  18-Oct-12  Vishal Gupta  Created
*  
*/



/************************************
*  INPUT PARAMETERS
************************************/
UNDEFINE FROM_TIME                   
UNDEFINE TO_TIME
UNDEFINE REPORT_TYPE
UNDEFINE INST_ID_LIST
UNDEFINE begin_snap_id
UNDEFINE end_snap_id
--UNDEFINE DBID

DEFINE FROM_TIME="&&1"
DEFINE TO_TIME="&&2"
DEFINE INST_ID_LIST="&&3"
DEFINE REPORT_TYPE="&&4"
DEFINE begin_snap_id=""
DEFINE end_snap_id=""
--DEFINE DBID=""

COLUMN  _FROM_TIME      NEW_VALUE FROM_TIME     NOPRINT
COLUMN  _TO_TIME        NEW_VALUE TO_TIME       NOPRINT
COLUMN  _INST_ID_LIST   NEW_VALUE INST_ID_LIST  NOPRINT
COLUMN  _REPORT_TYPE    NEW_VALUE REPORT_TYPE   NOPRINT

set term off
SELECT DECODE('&&FROM_TIME','',to_char(sysdate - 1 - (1/1440),'YYYY-MM-DD HH24:MI:SS'),'&&FROM_TIME')  "_FROM_TIME"
     , DECODE('&&TO_TIME','',to_char(sysdate + (1/1440),'YYYY-MM-DD HH24:MI:SS'),'&&TO_TIME')  "_TO_TIME"
     , DECODE('&&INST_ID_LIST','',NULL,'&&INST_ID_LIST')  "_INST_ID_LIST"
     , DECODE('&&REPORT_TYPE','','html','&&REPORT_TYPE')  "_REPORT_TYPE"
FROM DUAL;
set term on


PROMPT
PROMPT ***********************************************************************
PROMPT *  A W R    R E P O R T    ( R A C )
PROMPT *
PROMPT *  Input Parameters 
PROMPT *  - From Timestamp = '&&FROM_TIME' ( YYYY-MM-DD HH24:MI:SS format)
PROMPT *  - To Timestamp   = '&&TO_TIME' ( YYYY-MM-DD HH24:MI:SS format)
PROMPT *  - INST_ID_LIST   = '&&INST_ID_LIST'
PROMPT *  - Report Type    = '&&REPORT_TYPE'
PROMPT ***********************************************************************


COLUMN  _begin_snap_id      NEW_VALUE begin_snap_id     NOPRINT
COLUMN  _end_snap_id        NEW_VALUE end_snap_id       NOPRINT
COLUMN  _dbid               NEW_VALUE dbid              NOPRINT

set term off
SELECT min(s.snap_id)  "_begin_snap_id"
     , max(s.snap_id)  "_end_snap_id"
     , d.dbid          "_dbid"
  FROM dba_hist_snapshot s, v$database d
 WHERE d.dbid = s.dbid
   AND s.end_interval_time   BETWEEN TO_TIMESTAMP('&&FROM_TIME','YYYY-MM-DD HH24:MI:SS') 
                                 AND TO_TIMESTAMP('&&to_time','YYYY-MM-DD HH24:MI:SS')
GROUP BY d.dbid                                 
;
set term on


set pages 0
set lines 1500
PROMPT spooling to file &&_CONNECT_IDENTIFIER._awr_report.html
set term off
spool &&_CONNECT_IDENTIFIER._awr_report.html
SELECT * from table(dbms_workload_repository.awr_global_report_html
                       (  l_dbid       => &&DBID
                        , l_inst_num   => '&&INST_ID_LIST'
                        , l_bid        => &&begin_snap_id
                        , l_eid        => &&end_snap_id
                        , l_options    => 1  -- Currently not used used (as of v11.2.0.3)
                       )
                   );
spool off
set term on
PROMPT report generated as &&_CONNECT_IDENTIFIER._awr_report.html file.                   
host &&_CONNECT_IDENTIFIER._awr_report.html
                   
set pages 5000
                   
@@footer