addm report - run in a script

I have this that runs the awr report - how do I convert it to run the addm report?

SELECT output
   FROM (SELECT MIN(snap_id) minid, MAX(snap_id) maxid
           FROM dba_hist_snapshot
          WHERE begin_interval_time >= TRUNC(SYSDATE) - 1 AND end_interval_time < TRUNC(SYSDATE)),
        TABLE(
            DBMS_WORKLOAD_REPOSITORY.awr_report_text(
                (SELECT dbid
                   FROM v$database),
                (SELECT instance_number
                   FROM v$instance),
                minid,
                maxid
            )
        );
LVL 1
bkreynolds48Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
0
sdstuberCommented:
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;
0
bkreynolds48Author Commented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sdstuberCommented:
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;

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
0
bkreynolds48Author Commented:
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
0
awking00Commented:
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
0
bkreynolds48Author Commented:
I get an error on line
SELECT DBMS.get_report

ora 6550
pls 103
0
slightwv (䄆 Netminder) Commented:
dbms.get_report doesn't exist...

Do you mean dbms_addm.get_report?
0
bkreynolds48Author Commented:
yes sorry - type

this line from the script above

SELECT DBMS_ADDM.get_report(USER || '_' || TO_CHAR(SYSDATE - 1, 'yyyy-mm-dd')) FROM DUAL;
0
sdstuberCommented:
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
0
slightwv (䄆 Netminder) Commented:
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.
0
bkreynolds48Author Commented:
I ran as sqlplus / as sysdba
0
bkreynolds48Author Commented:
let me check for more on the error
0
bkreynolds48Author Commented:
error at line 20:
ORA-06550: line 20, column 1;
PLS-00103: Encountered the symbol "SELECT"
0
bkreynolds48Author Commented:
if I take that line out - it runs without error
0
slightwv (䄆 Netminder) Commented:
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;
/
0
bkreynolds48Author Commented:
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?
0
slightwv (䄆 Netminder) Commented:
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...
0
sdstuberCommented:
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.
0
bkreynolds48Author Commented:
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.
0
sdstuberCommented:
you had previously created the task, without deleting it.

run the delete by itself.
0
bkreynolds48Author Commented:
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.
0
bkreynolds48Author Commented:
I have tried running the delete statement several times but still get the above error
0
slightwv (䄆 Netminder) Commented:
>>PLS-00103: Encountered the symbol "FROM" when expecting one of the following:

Look at the original code that sdstuber posted.  There is no "FROM DUAL" on the delete...
0
bkreynolds48Author Commented:
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
0
sdstuberCommented:
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;
/

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

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
0
bkreynolds48Author Commented:
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
0
sdstuberCommented:
something else to consider,  the output of the query is a CLOB.

You may need to set LONG to something large,  I reran my test above with LONG set higher and I did get results, the previous output was simply truncated.

So maybe try something like this to be more reliable


set long 10000000
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;
/

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bkreynolds48Author Commented:
that seems to have worked
LAST QUESTION
is there a way to run this on more than one database?
0
sdstuberCommented:
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
0
bkreynolds48Author Commented:
Thanks for all your help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.