Oracle Awr report run for Last 24 Hr .. on unix prompt

snap id info 

alter session set "_push_join_predicate" = FALSE ;   If awr running slow !!!!

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

@?/rdbms/admin/awrrpt.sql      --> basic AWR report

@?/rdbms/admin/awrsqrpt.sql    --> Standard SQL statement Report

@?/rdbms/admin/awrddrpt.sql    --> Period diff on current instance

@?/rdbms/admin/awrrpti.sql     --> Workload Repository Report Instance (RAC)

@?/rdbms/admin/awrgrpt.sql     --> AWR Global Report (RAC)

@?/rdbms/admin/awrgdrpt.sql    --> AWR Global Diff Report (RAC)

@?/rdbms/admin/awrinfo.sql     --> Script to output general AWR information

ADDM report !!!

SET LONG 500000 PAGESIZE 0

SELECT DBMS_ADDM.GET_REPORT('ADDM:1825264339_20529') FROM DUAL;

col snap_id new_value last_snap

col BEGIN_INTERVAL_TIME for a25 

col END_INTERVAL_TIME   for a25 

select snap_id,CON_ID,begin_interval_time,end_interval_time from dba_hist_snapshot 

order by begin_interval_time desc 

fetch first 3 row only ;

column BEGIN_INTERVAL_TIME format a25

column END_INTERVAL_TIME format a25

column STARTUP_TIME format a25

set lines 1000

select instance_number, snap_id , BEGIN_INTERVAL_TIME, END_INTERVAL_TIME,

STARTUP_TIME from dba_hist_snapshot where dbid=(select dbid from v$database) and BEGIN_INTERVAL_TIME 

between to_date('12-sep-2021','dd-mon-yyyy') and to_date('13-sep-2021','dd-mon-yyyy') 

and instance_number in (select instance_number from v$instance)

order by BEGIN_INTERVAL_TIME asc;

select snap_id,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from dba_hist_snapshot where BEGIN_INTERVAL_TIME > systimestamp -1 order by BEGIN_INTERVAL_TIME desc;

define p_inst=1

define p_days=2

column dt heading 'Date/Hour' format a11

set linesize 500

set pages 9999  

select * from (

select min(snap_id) as snap_id,  

     to_char(start_time,'dd-MM-YY') as dt, to_char(start_time,'HH24') as hr

from (

select snap_id, s.instance_number, begin_interval_time start_time, 

   end_interval_time end_time, snap_level, flush_elapsed,

   lag(s.startup_time) over (partition by s.dbid, s.instance_number   order by s.snap_id) prev_startup_time,

   s.startup_time

from  dba_hist_snapshot s, gv$instance i

where begin_interval_time between trunc(sysdate)-&p_days and sysdate 

and   s.instance_number = i.instance_number

and   s.instance_number = &p_inst

order by snap_id

)

group by to_char(start_time,'dd-MM-YY') , to_char(start_time,'HH24') 

order by snap_id, start_time )

pivot (sum(snap_id)  for hr in ('00','01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16','17','18','19','20','21','22','23'))

order by dt

;

define num_days=2

SET LINESIZE 200  PAGESIZE 200

--UNDEF num_days

COL startup_time FOR a30

COL db_name FOR a10

COL snap_start FOR 9999999

COL snap_end FOR 9999999

COL start_interval FOR a25

COL end_interval FOR a25

COL range_interval FOR a40

COL qtd_snaps FOR 999

 

SELECT

    s.startup_time,

di.dbid,

    di.instance_name,

    MIN(snap_id) snap_start,

    MAX(snap_id) snap_end,

    MIN(end_interval_time) start_interval,

    MAX(end_interval_time) end_interval,

    EXTRACT(DAY FROM(MAX(end_interval_time) ) - MIN(end_interval_time) )

    || ' Days(s) '

    || EXTRACT(HOUR FROM(MAX(end_interval_time) ) - MIN(end_interval_time) )

    || ' Hour(s) '

    || EXTRACT(MINUTE FROM(MAX(end_interval_time) ) - MIN(end_interval_time) )

    || ' Minute(s) ' range_interval,

    MAX(snap_id) - MIN(snap_id) qtd_snaps

FROM

    dba_hist_snapshot s,

    dba_hist_database_instance di

WHERE

    di.dbid = s.dbid

    AND   di.instance_number = s.instance_number

    AND   end_interval_time > DECODE(&&num_days,0,TO_DATE('31-JAN-9999','DD-MON-YYYY'),3.14,s.end_interval_time,TO_DATE(SYSDATE,'dd/mm/yyyy') - (&num_days - 1) )

GROUP BY

    s.startup_time,

di.dbid,

    di.instance_name

ORDER BY     startup_time ASC;

#!/bin/bash
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=/opt/oracle/product/10.2
export PATH=$ORACLE_HOME/bin:.:$PATH
export ORACLE_SID=orcl
sqlplus -s /nolog <connect / as sysdba
set head off
set pages 0
set lines 132
set echo off
set feedback off
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
exec select max(snap_id) -24 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
exec select DBID into :DID from v\$database;
exec select INSTANCE_NUMBER into :INST_NUMBER from v\$instance ;
select 'spool awr_'||:BgnSnap||'_'||:EndSnap||'.txt' from dual ;
-- exec select 'spool awr_'||:BgnSnap||:EndSnap||'.txt' from dual ;
SELECT output FROM TABLE (dbms_workload_repository.awr_report_text (:DID,:INST_NUMBER,:BgnSnap,:EndSnap ) );
spool off
exit
EOF
=================


For Awr Report different spool file name




#!/bin/bash
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=/opt/oracle/product/10.2
export PATH=$ORACLE_HOME/bin:.:$PATH
export ORACLE_SID=orcl
TODAY=$(date)
DATE=`date +%d%m%Y:%H:%M:%S`
# DATE=`date +%d%m%Y`
l_awr_log_file="Awrrpt_$DATE.log"
#echo $l_awr_log_file
DATE=`date +%d%m%Y:%H:%M:%S`
sqlplus -s /nolog <connect / as sysdba
set head off
set pages 0
set lines 132
set echo off
set feedback off
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
VARIABLE x VARCHAR2(30)
exec select max(snap_id) -24 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
exec select DBID into :DID from v\$database;
exec select INSTANCE_NUMBER into :INST_NUMBER from v\$instance ;
--spool select ' awr_'||:BgnSnap||'_'||:EndSnap||'.txt' from dual ;
-- spool $DATE
spool $l_awr_log_file
-- spool print x
-- exec select 'spool awr_'||:BgnSnap||:EndSnap||'.txt' from dual ;
SELECT output FROM TABLE (dbms_workload_repository.awr_report_text (:DID,:INST_NUMBER,:BgnSnap,:EndSnap ) );
spool off
exit
EOF

=========================


set long 1000000
set pagesize 50000
column get_clob format a80
select dbms_advisor.get_task_report(task_name, 'TEXT', 'ALL') as ADDM_report
from dba_advisor_tasks where task_id=( select max(t.task_id)
from dba_advisor_tasks t, dba_advisor_log l
where t.task_id = l.task_id
and t.advisor_name='ADDM'
and l.status= 'COMPLETED');




select output
from table(
dbms_workload_repository.ash_report_text(
(select dbid from v$database),
1, -- instance id
sysdate - 2/24, -- startdate
sysdate - 1/24, -- enddate
0)
) ;




--- global Report
set head off  pages 0  lines 300 echo off  feedback off
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
VARIABLE x VARCHAR2(30)
exec select max(snap_id) -1 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
exec select DBID into :DID from v$database;
exec select INSTANCE_NUMBER into :INST_NUMBER from v$instance ;
SELECT output FROM TABLE (dbms_workload_repository.awr_global_report_text (:DID,'',:BgnSnap,:EndSnap,0 ) );

--- With Spool file

set head off  pages 0  lines 300 echo off  feedback off

VARIABLE BgnSnap NUMBER

VARIABLE EndSnap NUMBER

VARIABLE DID NUMBER

VARIABLE INST_NUMBER number

VARIABLE x VARCHAR2(30)

exec select max(snap_id) -1 into :BgnSnap from dba_hist_snapshot ;

exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;

exec select DBID into :DID from v$database;

exec select INSTANCE_NUMBER into :INST_NUMBER from v$instance ;

column awr new_val X

select to_char(:BgnSnap||'_'||:EndSnap) awr from dual;

spool awr_&X._file.txt

SELECT output FROM TABLE (dbms_workload_repository.awr_global_report_text (:DID,'',:BgnSnap,:EndSnap,0 ) );

spool off 

============

from 

https://blog.yannickjaquier.com/oracle/script-generate-series-awr-reports.html

for last 4 awr text report  .. 

 cat awr.sh

#!/bin/ksh

LOGFILE=/tmp/awr.log

sqlplus -s / as sysdba << EOF > $LOGFILE

set lines 130

set feedback off

set pages 0

select

  'export dbid=' || d.dbid as dbid

from

  v\$database d;

select

  'export db_name=' || d.name as db_name

from

  v\$database d;

select

  'export inst_num=' || i.instance_number as inst_num

from

  v\$instance i;

select

  'export inst_name=' || i.instance_name as  inst_name

from

  v\$instance i;

select 'export ininum=' ||(max(snap_id) -4) as ininum from dba_hist_snapshot ;

select 'export endnum=' ||max(snap_id)  as endnum from dba_hist_snapshot ;

EOF

while read line

do

  eval $line

done < $LOGFILE

sqlplus -s / as sysdba << EOF

set lines 130

set pages 1000

select

  snap_id,

  to_char(end_interval_time,'dd-Mon-YYYY hh24:mi') as snapdat

from dba_hist_snapshot

order by snap_id;

EOF

echo ininum = $ininum

echo endnum = $endnum

# ininum=39050

# endnum=39052

while [ $ininum -lt $endnum ];

do

  nxtnum=`expr $ininum + 1`

  repnam='awrrpt_'$inst_num'_'$ininum'_'$nxtnum'.txt'

  sqlplus -s / as sysdba << EOF

    define inst_num     = $inst_num;

    define num_days     = 100;

    define inst_name    = $inst_name;

    define db_name      = $db_name;

    define dbid         = $dbid;

    define report_type  = 'text';

    define begin_snap   = $ininum;

    define end_snap     = $nxtnum;

    define report_name  = $repnam;

    @@?/rdbms/admin/awrrpti

EOF

  ininum=$nxtnum

done

=======================

for last 4 awr html report  .. 

 cat awr.sh

#!/bin/ksh

LOGFILE=/tmp/awr.log

sqlplus -s / as sysdba << EOF > $LOGFILE

set lines 130

set feedback off

set pages 0

select

  'export dbid=' || d.dbid as dbid

from

  v\$database d;

select

  'export db_name=' || d.name as db_name

from

  v\$database d;

select

  'export inst_num=' || i.instance_number as inst_num

from

  v\$instance i;

select

  'export inst_name=' || i.instance_name as  inst_name

from

  v\$instance i;

select 'export ininum=' ||(max(snap_id) -4) as ininum from dba_hist_snapshot ;

select 'export endnum=' ||max(snap_id)  as endnum from dba_hist_snapshot ;

EOF

while read line

do

  eval $line

done < $LOGFILE

sqlplus -s / as sysdba << EOF

set lines 130

set pages 1000

select

  snap_id,

  to_char(end_interval_time,'dd-Mon-YYYY hh24:mi') as snapdat

from dba_hist_snapshot

order by snap_id;

EOF

echo ininum = $ininum

echo endnum = $endnum

# ininum=39050

# endnum=39052

while [ $ininum -lt $endnum ];

do

  nxtnum=`expr $ininum + 1`

  repnam='awrrpt_'$inst_num'_'$ininum'_'$nxtnum'.html'

  sqlplus -s / as sysdba << EOF

    define inst_num     = $inst_num;

    define num_days     = 100;

    define inst_name    = $inst_name;

    define db_name      = $db_name;

    define dbid         = $dbid;

    define report_type  = 'html';

    define begin_snap   = $ininum;

    define end_snap     = $nxtnum;

    define report_name  = $repnam;

    @@?/rdbms/admin/awrrpti

EOF

  ininum=$nxtnum

done

============

set linesize 300 pagesize 300

select snap_id,instance_number ,con_id,snap_id, end_interval_time from dba_hist_snapshot 

--WHERE begin_interval_time > TO_DATE('2011-06-07 07:00:00', 'YYYY-MM-DD HH24:MI:SS') 

WHERE end_interval_time > SYSDATE - 1

and instance_number=1 

order by 1

;

 VAR dbid NUMBER

 var bid NUMBER

 var eid NUMBER

 

exec :bid := '34710'

exec :eid := '34711'

BEGIN

  SELECT dbid INTO :dbid FROM v$database;

END;

/

/

SET TERMOUT OFF PAGESIZE 0 HEADING OFF LINESIZE 1000 TRIMSPOOL ON TRIMOUT ON TAB OFF

--*** Node 1

-- HTML

SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(:dbid, 1, :bid, :eid));

-- Text 

SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_text(:dbid, 1, :bid, :eid));

================

--*** Node 2

-- HTML

SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(:dbid, 2, :bid, :eid));

-- Text 

SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_text(:dbid, 2, :bid, :eid));

*** Global 

-- SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_GLOBAL_REPORT_HTML(:dbid, CAST(null AS VARCHAR2(10)), &bid, &eid));

SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_GLOBAL_REPORT_text(:dbid, CAST(null AS VARCHAR2(10)), :bid, :eid));

====

col OUTPUT for A150

select * from table(SYS.DBMS_WORKLOAD_REPOSITORY.awr_report_text(  (select dbid from v$database), 1,  (select max(snap_id) from dba_hist_snapshot) - 1,   (select max(snap_id) from dba_hist_snapshot)));

===========

VARIABLE BgnSnap NUMBER

VARIABLE EndSnap NUMBER

VARIABLE DID NUMBER

VARIABLE INST_NUMBER number

exec select max(snap_id) -2 into :BgnSnap from dba_hist_snapshot ;

exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;

exec select DBID into :DID from v$database;

exec select INSTANCE_NUMBER into :INST_NUMBER from v$instance ;

select 'spool awr_'||:BgnSnap||'_'||:EndSnap||'.txt' from dual ;

-- exec select 'spool awr_'||:BgnSnap||:EndSnap||'.txt' from dual ;

SELECT output FROM TABLE (dbms_workload_repository.awr_report_text (:DID,:INST_NUMBER,:BgnSnap,:EndSnap ) );

******

if below error 

SELECT output FROM TABLE (dbms_workload_repository.awr_report_text (:DID,:INST_NUMBER,:BgnSnap,:EndSnap ) )

                          *

ERROR at line 1:

ORA-20020: Database/Instance/Snapshot mismatch

ORA-06512: at "SYS.DBMS_SWRF_REPORT_INTERNAL", line 16546

ORA-06512: at "SYS.DBMS_SWRF_REPORT_INTERNAL", line 2660

ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 1189

ORA-06512: at line 1

use below code !!!!

VARIABLE BgnSnap NUMBER

VARIABLE EndSnap NUMBER

VARIABLE DID NUMBER

VARIABLE INST_NUMBER number

exec select max(snap_id) -2 into :BgnSnap from dba_hist_snapshot where DBID= (select  DBID from v$database);

exec select max(snap_id) into :EndSnap from dba_hist_snapshot where DBID= (select  DBID from v$database);

exec select DBID into :DID from v$database;

exec select INSTANCE_NUMBER into :INST_NUMBER from v$instance ;

select 'spool awr_'||:BgnSnap||'_'||:EndSnap||'.txt' from dual ;

-- exec select 'spool awr_'||:BgnSnap||:EndSnap||'.txt' from dual ;

SELECT output FROM TABLE (dbms_workload_repository.awr_report_text (:DID,:INST_NUMBER,:BgnSnap,:EndSnap ) );

====

set serveroutput on

declare

cursor c is

select to_char(s.startup_time,'dd Mon "at" HH24:mi:ss') instart_fmt

, di.instance_name inst_name

, di.instance_number instance_number

, di.db_name db_name

, di.dbid dbid

, lag (s.snap_id,1,0) over (partition by di.instance_number order by s.snap_id) begin_snap_id

, s.snap_id end_snap_id

, to_char(s.begin_interval_time,'dd-mm-yyyy-hh24:mi') beginsnapdat

, to_char(s.end_interval_time,'dd-mm-yyyy-hh24:mi')   endsnapdat

, s.snap_level lvl

from dba_hist_snapshot s, dba_hist_database_instance di,gv$instance i,v$database d

where s.dbid = d.dbid

and di.dbid = d.dbid

and s.instance_number = i.instance_number

and di.instance_number = i.instance_number

and di.dbid = s.dbid

and di.instance_number = s.instance_number

and di.startup_time = s.startup_time

and s.begin_interval_time > trunc(sysdate -1) --<<<<<<<< last last 1 days

order by di.db_name, i.instance_name, s.snap_id

;

begin

for c1 in c

loop

if c1.begin_snap_id > 0 then

dbms_output.put_line('spool '||c1.inst_name||'_'||c1.begin_snap_id||'_'||c1.end_snap_id||'_'||c1.beginsnapdat||'_'||c1.endsnapdat||'.txt');

dbms_output.put_line('select output from table(dbms_workload_repository.awr_report_text( '||c1.dbid||','||c1.instance_number||','||c1.begin_snap_id||','||c1.end_snap_id||',0 ));');

dbms_output.put_line('spool off');

end if;

end loop;

end;

/

===============

for html 

set serveroutput on linesize 300

declare

cursor c is

select to_char(s.startup_time,'dd Mon "at" HH24:mi:ss') instart_fmt

, di.instance_name inst_name

, di.instance_number instance_number

, di.db_name db_name

, di.dbid dbid

, lag (s.snap_id,1,0) over (partition by di.instance_number order by s.snap_id) begin_snap_id

, s.snap_id end_snap_id

, to_char(s.begin_interval_time,'dd-mm-yyyy-hh24:mi') beginsnapdat

, to_char(s.end_interval_time,'dd-mm-yyyy-hh24:mi')   endsnapdat

, s.snap_level lvl

from dba_hist_snapshot s, dba_hist_database_instance di,gv$instance i,v$database d

where s.dbid = d.dbid

and di.dbid = d.dbid

and s.instance_number = i.instance_number

and di.instance_number = i.instance_number

and di.dbid = s.dbid

and di.instance_number = s.instance_number

and di.startup_time = s.startup_time

and s.begin_interval_time > trunc(sysdate -1) --<<<<<<<< last last 1 days

order by di.db_name, i.instance_name, s.snap_id

;

begin

for c1 in c

loop

if c1.begin_snap_id > 0 then

dbms_output.put_line('Set heading off  trimspool off linesize 1500 termout on feedback off');

dbms_output.put_line('spool '||c1.inst_name||'_'||c1.begin_snap_id||'_'||c1.end_snap_id||'_'||c1.beginsnapdat||'_'||c1.endsnapdat||'.html');

dbms_output.put_line('select output from table(dbms_workload_repository.awr_report_html( '||c1.dbid||','||c1.instance_number||','||c1.begin_snap_id||','||c1.end_snap_id||',0 ));');

dbms_output.put_line('spool off');

end if;

end loop;

end;

/

====

For Global (RAC all node) !!!

set serveroutput on linesize 300

declare

cursor c is

select to_char(s.startup_time,'dd Mon "at" HH24:mi:ss') instart_fmt

, di.instance_name inst_name

, di.instance_number instance_number

, di.db_name db_name

, di.dbid dbid

, lag (s.snap_id,1,0) over (partition by di.instance_number order by s.snap_id) begin_snap_id

, s.snap_id end_snap_id

, to_char(s.begin_interval_time,'dd-mm-yyyy-hh24:mi') beginsnapdat

, to_char(s.end_interval_time,'dd-mm-yyyy-hh24:mi')   endsnapdat

, s.snap_level lvl

from dba_hist_snapshot s, dba_hist_database_instance di,gv$instance i,v$database d

where s.dbid = d.dbid

and di.dbid = d.dbid

and s.instance_number = i.instance_number

and di.instance_number = i.instance_number

and di.dbid = s.dbid

and di.instance_number = s.instance_number

and di.startup_time = s.startup_time

and s.begin_interval_time > trunc(sysdate -1) --<<<<<<<< last last 1 days

order by di.db_name, i.instance_name, s.snap_id

;

begin

for c1 in c

loop

if c1.begin_snap_id > 0 then

dbms_output.put_line('Set heading off  trimspool off linesize 1500 termout on feedback off');

dbms_output.put_line('spool '||c1.inst_name||'_'||c1.begin_snap_id||'_'||c1.end_snap_id||'_'||c1.beginsnapdat||'_Global_'||c1.endsnapdat||'.html');

dbms_output.put_line('select output from table(DBMS_WORKLOAD_REPOSITORY.AWR_GLOBAL_REPORT_HTML( '||c1.dbid||','||''''''||','||c1.begin_snap_id||','||c1.end_snap_id||',0 ));');

dbms_output.put_line('spool off');

end if;

end loop;

end;

/

====

define instance_number=1

set trimspool on trimout on

set lines 1500

set echo off

set heading off

set pages 0

set feedback off

set verify off

set trimspool on trimout on

define instance_number=1

--spool generate_awr_reports1..sql

select 'set heading off'   || chr(10) ||

      'set feedback off'  || chr(10) ||

      'set linesize 5000' || chr(10) ||

      'set trimspool on trimout on' || chr(10) ||

      'spool awr_'|| to_char(instance_number) || '_' || to_char(snap_id) || '_' || to_char(snap_id+1) || '.html' || chr(10) ||

      'select output from table(dbms_workload_repository.awr_report_html(' || to_char(dbid) || ',' || to_char(instance_number) || ',' ||

to_char(snap_id) || ',' || to_char(snap_id+1) || '));' || chr(10) ||

      'spool off'

from DBA_HIST_SNAPSHOT

where 1=1

and instance_number=1

and snap_id < ( select max(snap_id) from dba_hist_snapshot where instance_number=&instance_number)

order by snap_id;

set linesize 200 pagesize 200
col snaptime for a25
select dhdi.instance_name,
dhdi.db_name,
dhdi.DBID,
dhs.snap_id,
to_char(dhs.begin_interval_time,'MM/DD/YYYY:HH24:MI') begin_snap_time,
to_char(dhs.end_interval_time,'MM/DD/YYYY:HH24:MI') end_snap_time,
decode(dhs.startup_time,dhs.begin_interval_time,'**db restart**',null) db_bounce
from dba_hist_snapshot dhs, dba_hist_database_instance dhdi
where dhdi.dbid = dhs.dbid
and dhdi.instance_number = dhs.instance_number
and dhdi.startup_time = dhs.startup_time
and dhs.end_interval_time >= sysdate -2
order by db_name, instance_name, snap_id;

define  num_days     = 2;
define  db_name      = 'RAC';
define  dbid         = 1222414252;
define  begin_snap   = 10319;
define  end_snap     = 10320;
define  report_type  = 'html';
define  instance_numbers_or_ALL = 'ALL'
define  report_name  =  awrrpt_RAC_&&begin_snap._&&end_snap..&&report_type

@?/rdbms/admin/awrgrpti

VARIABLE BgnSnap NUMBER

VARIABLE EndSnap NUMBER

VARIABLE DID NUMBER

VARIABLE INST_NUMBER number

exec select max(snap_id) -24 into :BgnSnap from dba_hist_snapshot ;

exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;

exec select DBID into :DID from v$database;

define begin_snap=:BgnSnap

define EndSnap=:EndSnap

define  num_days     = 3;

define  db_name      = 'Database';

define  dbid         = :DID;

define  begin_snap   = :BgnSnap;

define  end_snap     = :EndSnap;

define  report_type  = 'html';

define  instance_numbers_or_ALL = 'ALL'

define  report_name  =  awrrpt_RAC_&begin_snap._&end_snap

@?/rdbms/admin/awrgrpti

=====

Awr dump !!!!

define 3="TIMESTAMP'2023-01-31 19:00:00'" 

define 4="TIMESTAMP'2023-02-01 19:00:00'"

define DB_DIR='DATA_PUMP_DIR'

set serveroutput on linesize 300

declare

cursor c is

select to_char(s.startup_time,'dd Mon "at" HH24:mi:ss') instart_fmt

, di.instance_name inst_name

, di.instance_number instance_number

, di.db_name db_name

, di.dbid dbid

, lag (s.snap_id,1,0) over (partition by di.instance_number order by s.snap_id) begin_snap_id

, s.snap_id end_snap_id

, to_char(s.begin_interval_time,'dd-mm-yyyy-hh24:mi') beginsnapdat

, to_char(s.end_interval_time,'dd-mm-yyyy-hh24:mi')   endsnapdat

, s.snap_level lvl

from dba_hist_snapshot s, dba_hist_database_instance di,gv$instance i,v$database d

where s.dbid = d.dbid

and di.dbid = d.dbid

and s.instance_number = i.instance_number

and di.instance_number = i.instance_number

and di.dbid = s.dbid

and di.instance_number = s.instance_number

and di.startup_time = s.startup_time

--and s.begin_interval_time > trunc(sysdate -1) --<<<<<<<< last last 1 days

AND begin_interval_time BETWEEN &3 AND &4

order by di.db_name, i.instance_name, s.snap_id

;

begin

for c1 in c

loop

if c1.begin_snap_id > 0 then

--dbms_output.put_line('Set heading off  trimspool off linesize 1500 termout on feedback off');

--dbms_output.put_line('spool '||c1.inst_name||'_'||c1.begin_snap_id||'_'||c1.end_snap_id||'_'||c1.beginsnapdat||'_'||c1.endsnapdat||'.html');

dbms_output.put_line('begin dbms_swrf_internal.awr_extract

( '||'DMPFILE   =>' ||'''awr_data'||c1.begin_snap_id||''''||','||'dmpdir   => '|| '''&DB_DIR'''||',' ||' bid =>'||c1.begin_snap_id||','||'eid      => '||c1.end_snap_id||','||'dbid     =>'||c1.dbid||'); ' );

dbms_output.put_line('dbms_swrf_internal.clear_awr_dbid;');

dbms_output.put_line('end; ');

dbms_output.put_line('/');

end if;

end loop;

end;

/

=================================!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Gobal report

set head off  pages 0  lines 132  echo off  feedback off

VARIABLE BgnSnap NUMBER

VARIABLE EndSnap NUMBER

VARIABLE DID NUMBER

VARIABLE INST_NUMBERS varchar2(20);

exec select max(snap_id) -1 into :BgnSnap from dba_hist_snapshot ;

exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;

exec select DBID into :DID from v$database;

select listagg (INSTANCE_NUMBER,',') within group (order by INSTANCE_NAME) into :INST_NUMBERS from gv$instance;

-- awr text.sql

select output from table(dbms_workload_repository.awr_global_report_text(:DID,:INST_NUMBERS,:BgnSnap,:EndSnap))

/

-- awr html.sql

select output from table(DBMS_WORKLOAD_REPOSITORY.AWR_GLOBAL_REPORT_HTML(:DID,:INST_NUMBERS,:BgnSnap,:EndSnap))

/

ADDM

VARIABLE BgnSnap NUMBER

VARIABLE EndSnap NUMBER

VARIABLE DID NUMBER

VARIABLE INST_NUMBER number

exec select max(snap_id) -2 into :BgnSnap from dba_hist_snapshot where DBID= (select  DBID from v$database);

exec select max(snap_id) into :EndSnap from dba_hist_snapshot where DBID= (select  DBID from v$database);

exec select DBID into :DID from v$database;

exec select INSTANCE_NUMBER into :INST_NUMBER from v$instance ;

DECLARE

  task_name VARCHAR2(30) := 'SYSTEM_ADDM';

  task_desc VARCHAR2(30) := 'ADDM Feature Test';

  task_id   NUMBER;

BEGIN

  select count(*)

    into task_id

    from dba_advisor_tasks

   where task_name = 'SYSTEM_ADDM';

  if task_id = 0 then

    dbms_advisor.create_task('ADDM', task_id, task_name, task_desc, null);

  else

    dbms_advisor.reset_task(task_name => 'SCOTT_ADDM');

  end if;

  dbms_advisor.set_task_parameter('SYSTEM_ADDM', 'START_SNAPSHOT', :BgnSnap);

  dbms_advisor.set_task_parameter('SYSTEM_ADDM', 'END_SNAPSHOT', :EndSnap);

  dbms_advisor.set_task_parameter('SYSTEM_ADDM', 'INSTANCE', :INST_NUMBER);

  dbms_advisor.set_task_parameter('SYSTEM_ADDM', 'DB_ID', :DID);

  dbms_advisor.execute_task('SYSTEM_ADDM');

END;

/

select dbms_advisor.get_task_report('SYSTEM_ADDM', 'TEXT', 'ALL') from dual;

=====================

mini Awr report 

http://anuj-singh.blogspot.com/2023/              

Oracle Mini Awr report ....