#!/bin/bash

: ${1?"Usage: $0 DBNAME"}

sid=$1

export ORACLE_SID=ORASP1
export ORACLE_HOME=/u00/app/oracle/product/10.2.0/db_1
export PATH=$ORACLE_HOME/bin:/bin:/usr/local/bin:${PATH}

logfile=addm_rpt_${ORACLE_SID}.log

# Generate ADDM report with 24hrs

$ORACLE_HOME/bin/sqlplus /nolog << EOS > $logfile
connect /as sysdba

var end_time varchar2(23);
var start_time varchar2(23);
var inst_num number;
var dbid number;
var bid number;
var eid number;
var inst_name varchar2(23);
variable task_name varchar2(40);

begin
  select INSTANCE_NUMBER into :inst_num from gv\$instance where INSTANCE_NAME = '${sid1}';
end;
/

begin
  select DBID into :dbid from v\$database;
end;
/

alter session set nls_date_format='YYYY-MM-DD';
begin
  select to_char(sysdate)||':000000' into :end_time from dual;
end;
/
begin
  select to_char(sysdate-1)||':000000' into :start_time from dual;
end;
/

begin
        select min(snap_id) into :eid
        from dba_hist_snapshot
          where dbid              = :dbid
            and instance_number   = :inst_num
            and end_interval_time >= to_date(:end_time, 'YYYY-MM-DD:HH24MISS');
end;
/

begin
        select min(snap_id) into :bid
        from dba_hist_snapshot
          where dbid              = :dbid
            and instance_number   = :inst_num
            and end_interval_time >= to_date(:start_time, 'YYYY-MM-DD:HH24MISS');
end;
/

begin
  declare
    id number;
    name varchar2(100);
    descr varchar2(500);
    
  BEGIN
     name := '';
     descr := 'ADDM run: snapshots [' || :bid || ', '
              || :eid || '], instance ' || :inst_num
              || ', database id ' || :dbid;
     dbms_advisor.create_task('ADDM',id,name,descr,null);
     :task_name := name;
     -- set time window
     dbms_advisor.set_task_parameter(name, 'START_SNAPSHOT', 38);
     dbms_advisor.set_task_parameter(name, 'END_SNAPSHOT', 56);
     -- set instance number
     dbms_advisor.set_task_parameter(name, 'INSTANCE', :inst_num);
     -- set dbid
     dbms_advisor.set_task_parameter(name, 'DB_ID', :dbid);
     -- execute task
     dbms_advisor.execute_task(name);
  end;
end;
/


set long 1000000 pagesize 0 longchunksize 1000
column get_clob format a80

select dbms_advisor.get_task_report(:task_name, 'TEXT', 'TYPICAL')
from   sys.dual;
exit 
EOS

exit 0

