addm report - run in a script

Maybe dbms_addm.get_report?

http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_addm.htm#ARPLS65059

If you're just trying to dump the reports that are already available..

something like this?

SELECT DBMS_ADVISOR.get_task_report(
           task_name,
           'TEXT',
           'TYPICAL',
           'ALL',
           owner
       )
           AS report
  FROM dba_addm_tasks;

what I am trying to do is get yesterday's addm report - so need to get the start/end snap_id and then run the addm report - like the above for the awr report

ADDM isn't likely to already have a report for the previous day.  Instead, it will have many small-interval reports.

To get a full-day report, you'll have to request it and then retrieve it.

Something like this should do it

DECLARE
    v_task_name VARCHAR2(40) := USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd');
    v_min_id    NUMBER;
    v_max_id    NUMBER;
    v_dbid      NUMBER;
BEGIN
    SELECT MIN(snap_id), MAX(snap_id), MIN(dbid)
      INTO v_min_id, v_max_id, v_dbid
      FROM dba_hist_snapshot
     WHERE begin_interval_time >= TRUNC(SYSDATE) - 1 AND end_interval_time < TRUNC(SYSDATE);

    DBMS_ADDM.analyze_db(
        task_name        => v_task_name,
        begin_snapshot   => v_min_id,
        end_snapshot     => v_max_id,
        db_id            => v_dbid
    );
END;

SELECT DBMS_ADDM.get_report(USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd')) FROM DUAL;


BEGIN
    DBMS_ADDM.delete(USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd'));
END;

Select all Open in new window

Do note though, just because there are snapshots for the beginning and ending of a day doesn't mean ADDM has the necessary information to generate the report.  You may need to narrow the range

thanks - I will give this a try - but given this is Friday it may take me a couple of days to get back to this

Thanks so much for your help

You might consider including the l_options parameter set to 8, which will display the ADDM specific portions of the report.
DBMS_WORKLOAD_REPOSITORY.awr_report_text(
                 (SELECT dbid
                    FROM v$database),
                 (SELECT instance_number
                    FROM v$instance),
                 minid,
                 maxid,
                  8
             )

You may also consider the dbms_advisor package (see the following link) to create the addm report.
http://oracle-base.com/articles/10g/automatic-database-diagnostic-monitor-10g.php

I get an error on line
SELECT DBMS.get_report

ora 6550
pls 103

dbms.get_report doesn't exist...

Do you mean dbms_addm.get_report?

yes sorry - type

this line from the script above

SELECT DBMS_ADDM.get_report(USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd')) FROM DUAL;

which did you try?


dbms.get_report --- this should fail

or

dbms_addm.get_report -- this worked for me.


if you get  "does not exist"  it may be because you don't have access, not because it doesn't exist

Make sure you have permission to execute DBMS_ADDM.

Try from sqlplus and post the entire stack trace.  There should be more lines to go with the ora-06550.

I ran as sqlplus / as sysdba

let me check for more on the error

error at line 20:
ORA-06550: line 20, column 1;
PLS-00103: Encountered the symbol "SELECT"

if I take that line out - it runs without error

If you just copied and pasted the script as-is, you need a '/' after the END; in a pl/sql block.

For example:

...
END;
/

SELECT
...
END;
/

yes I figured that out
can you tell me what this  does? - little scared about deletes in production

BEGIN
    DBMS_ADDM.delete(USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd'));
END

also I need to spool this to a file so I can email the output to myself

one more thing I will need to try to add is that I have 3 databases on this server and would like this to run for all three once this is working
Is that Possible?

The online docs tell you:
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_addm.htm#ARPLS65049

Look at the code above:
v_task_name VARCHAR2(40) := USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd');

Then it calls DBMS_ADDM.analyze_db with that task_name.

The delete is just cleaning up after itself...

It deletes the ADDM task the previous pl/sql block created.

The 3 steps above do this...

You create a task named for yourself
Generate a report for your own task
Then delete your own task.

I just logged on from where the script is running
here is what I ran

SQL> DECLARE
    v_task_name VARCHAR2(40) := USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd');
    v_min_id    NUMBER;
    v_max_id    NUMBER;
    v_dbid      NUMBER;
BEGIN
    SELECT MIN(snap_id), MAX(snap_id), MIN(dbid)
      INTO v_min_id, v_max_id, v_dbid
      FROM dba_hist_snapshot
     WHERE begin_interval_time >= TRUNC(SYSDATE) - 1 AND end_interval_time < TRUNC(SYSDATE);

    DBMS_ADDM.analyze_db(
        task_name        => v_task_name,
        begin_snapshot   => v_min_id,
        end_snapshot     => v_max_id,
        db_id            => v_dbid
    );
END;
/

SELECT DBMS_ADDM.get_report(USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd')) FROM DUAL;

BEGIN
DBMS_ADDM.delete(USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd')) FROM DUAL;
END;
/


here is the output

ERROR at line 1:
ORA-13627: Setting of parameter START_SNAPSHOT is disallowed until the task is
reset.
ORA-06512: at "SYS.PRVT_ADVISOR", line 4082
ORA-06512: at "SYS.PRVT_ADVISOR", line 4094
ORA-06512: at "SYS.DBMS_ADVISOR", line 363
ORA-06512: at "SYS.DBMS_ADDM", line 65
ORA-06512: at line 12


SQL> SQL>
DBMS_ADDM.GET_REPORT(USER||'_'||TO_CHAR(SYSDATE-1,'YYYY-MM-DD'))
--------------------------------------------------------------------------------
          ADDM Report for Task 'BEV_2015-06-07'
          -------------------


SQL> SQL>   2    3    4  DBMS_ADDM.delete(USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd')) FROM DUAL;
                                                                    *
ERROR at line 2:
ORA-06550: line 2, column 69:
PLS-00103: Encountered the symbol "FROM" when expecting one of the following:
:= . ( % ;
The symbol ":= was inserted before "FROM" to continue.

you had previously created the task, without deleting it.

run the delete by itself.

SQL> BEGIN
DBMS_ADDM.delete(USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd')) FROM DUAL;
END;
/  2    3    4  
DBMS_ADDM.delete(USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd')) FROM DUAL;
                                                                    *
ERROR at line 2:
ORA-06550: line 2, column 69:
PLS-00103: Encountered the symbol "FROM" when expecting one of the following:
:= . ( % ;
The symbol ":= was inserted before "FROM" to continue.

I have tried running the delete statement several times but still get the above error

thanks - it runs with no errors - sorry about the pilot error
I don't get any output
can I spool to a file?  I tried but nothing was in it so must be missing something besides just
spool filename

no, the filename is pretty much it,
here's an example

spool testfile.txt
DECLARE
    v_task_name VARCHAR2(40) := USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd');
    v_min_id    NUMBER;
    v_max_id    NUMBER;
    v_dbid      NUMBER;
BEGIN
    SELECT MIN(snap_id), MAX(snap_id), MIN(dbid)
      INTO v_min_id, v_max_id, v_dbid
      FROM dba_hist_snapshot
     WHERE begin_interval_time >= TRUNC(SYSDATE) - 1 AND end_interval_time < TRUNC(SYSDATE);

    DBMS_ADDM.analyze_db(
        task_name        => v_task_name,
        begin_snapshot   => v_min_id,
        end_snapshot     => v_max_id,
        db_id            => v_dbid
    );
END;
/
SELECT DBMS_ADDM.get_report(USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd')) FROM DUAL;
BEGIN
    DBMS_ADDM.delete(USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd'));
END;
/

Select all Open in new window

the script above produced this for me...

SQL> DECLARE
  2      v_task_name VARCHAR2(40) := USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd');
  3      v_min_id    NUMBER;
  4      v_max_id    NUMBER;
  5      v_dbid      NUMBER;
  6  BEGIN
  7      SELECT MIN(snap_id), MAX(snap_id), MIN(dbid)
  8        INTO v_min_id, v_max_id, v_dbid
  9        FROM dba_hist_snapshot
 10       WHERE begin_interval_time >= TRUNC(SYSDATE) - 1 AND end_interval_time < TRUNC(SYSDATE);
 11  
 12      DBMS_ADDM.analyze_db(
 13          task_name        => v_task_name,
 14          begin_snapshot   => v_min_id,
 15          end_snapshot     => v_max_id,
 16          db_id            => v_dbid
 17      );
 18  END;
 19  /

PL/SQL procedure successfully completed.

SQL> SELECT DBMS_ADDM.get_report(USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd')) FROM DUAL;

DBMS_ADDM.GET_REPORT(USER||'_'||TO_CHAR(SYSDATE-1,'YYYY-MM-DD'))                
--------------------------------------------------------------------------------
          ADDM Report for Task 'SYSTEM_2015-06-07'                              
          -------------------                                                   
                                                                                

SQL> BEGIN
  2      DBMS_ADDM.delete(USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd'));
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> exit

Select all Open in new window

note, there isn't anything useful in it, because their isn't any ADDM data for that time period on the database I used.
So, the script ran, but that doesn't guarantee useful output

That's what I was trying to warn about in my second post

I have Cygwin on my laptop and connected to the same database that I was running on the AIX system.  Ran these same commands and data was returned.
weird
oh - Cygwin runs a Linux env on my laptop

that seems to have worked
LAST QUESTION
is there a way to run this on more than one database?

simply connect to each database in turn and run the script again



if you have database links then you could invoke the procedures and queries across the links
same code just add @your_link

Thanks for all your help