Link to home
Start Free TrialLog in
Avatar of lhbras
lhbras

asked on

How to automate AWR reports through Oracle create program and scheduler

I want to automate the AWR report generation.

There is a high loading period every monday from 9am to 1pm and I want that the AWR reports are generated automatically every monday for the period. We collect half hourly snapshots.

I want this to be done through Oracle Scheduler (Oracle create_program and create_schedule)
Avatar of sonicefu
sonicefu
Flag of Pakistan image

You can do this using DBMS_SQLTUNE and Oracle job scheduler.
Schedule the followin scripts using OS scheduling commands (at or cron)

@$ORACLE_HOME/rdbms/admin/awrrpt.sql
@$ORACLE_HOME/rdbms/admin/awrrpti.sql

From OEM: Jobs> Ccreate Job>SQL Script.

Then, create the script to execute above scripts and schedule it.

Avatar of lhbras
lhbras

ASKER

I want a proven/tested example

that utilizes dbms_scheduler.create_program to do this
ASKER CERTIFIED SOLUTION
Avatar of sonicefu
sonicefu
Flag of Pakistan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of lhbras

ASKER

sonicefu

This is a solution which is provided on this website by expert "Dotten"

When I try this I am not able to get it working. Can you please look into it and provide me with a workable solution.

sql> begin
 dbms_scheduler.create_program(
 program_name => 'MY_PROGRAM',
 progam_action => 'DECLARE
                                 start_id NUMBER;
                                 end_id  NUMBER;
                              BEGIN
                                SELECT snap_id
                                    INTO     start_id
                                FROM    dba_hist_snapshot
                                WHERE (SELECT snap_id
                                             FROM dba_hist_snapshot
                                             ORDER BY snap_id DESC;)
                                   AND rownum = 2;
                                SELECT MAX(snap_id)
                                   INTO end_id
                                FROM dba_hist_snapshot;
                               
                           DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
                                 <your_dbid>,
                                 <your_instance_number>,
                                  start_id,
                                  end_id,
                                  DEFAULT)
                                  RETURN awrrpt_text_type_table PIPELINED;
                                 END;',
 program_type => 'PL/SQL BLOCK',
 number_of_arguments => 2,
 enabled => 'TRUE);
end;
/
Then, create your job referencing the program:
BEGIN
    DBMS_SCHEDUER.CREATE_JOB(
          job_name => 'my_job',
          program_name => 'MY_PROGRAM',
          repeat_interval => 'FREQ=DAILY;BYHOUR=12',
          comments          =>  'Daily at noon');
END;
/


Avatar of lhbras

ASKER

If anyone can check this as propose a working solution in this direction

sql> begin
 dbms_scheduler.create_program(
 program_name => 'MY_PROGRAM',
 progam_action => 'DECLARE
                                 start_id NUMBER;
                                 end_id  NUMBER;
                              BEGIN
                                SELECT snap_id
                                    INTO     start_id
                                FROM    dba_hist_snapshot
                                WHERE (SELECT snap_id
                                             FROM dba_hist_snapshot
                                             ORDER BY snap_id DESC;)
                                   AND rownum = 2;
                                SELECT MAX(snap_id)
                                   INTO end_id
                                FROM dba_hist_snapshot;
                               
                           DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
                                 <your_dbid>,
                                 <your_instance_number>,
                                  start_id,
                                  end_id,
                                  DEFAULT)
                                  RETURN awrrpt_text_type_table PIPELINED;
                                 END;',
 program_type => 'PL/SQL BLOCK',
 number_of_arguments => 2,
 enabled => 'TRUE);
end;
/
Then, create your job referencing the program:
BEGIN
    DBMS_SCHEDUER.CREATE_JOB(
          job_name => 'my_job',
          program_name => 'MY_PROGRAM',
          repeat_interval => 'FREQ=DAILY;BYHOUR=12',
          comments          =>  'Daily at noon');
END;
/
Avatar of lhbras

ASKER

If someone can correct or addup to the above so that it becomes operational
i think you are missing the following things in the code

<your_dbid>,
<your_instance_number>

you must check these values from your database and provide it in the code, you can check DBID using the following query
select DBID from v$database ;

and

find the INSTANCE_NUMBER using the following query

select  INSTANCE_NUMBER from v$instance;


and put it in the code,  instead of <your_dbid> and  <your_instance_number>

for example my dbid is 1183279397 and instance_number is 1183279397

BEGIN
   DBMS_SCHEDULER.create_program
      (program_name             => 'MY_PROGRAM',
       progam_action            => 'DECLARE
                                 start_id NUMBER;
                                 end_id  NUMBER;
                              BEGIN
                                SELECT snap_id
                                    INTO     start_id
                                FROM    dba_hist_snapshot
                                WHERE (SELECT snap_id
                                             FROM dba_hist_snapshot
                                             ORDER BY snap_id DESC;)
                                   AND rownum = 2;
                                SELECT MAX(snap_id)
                                   INTO end_id
                                FROM dba_hist_snapshot;
                               
                           DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
                                 1183279397,
                                 1,
                                  start_id,
                                  end_id,
                                  DEFAULT)
                                  RETURN awrrpt_text_type_table PIPELINED;
                                 END;',
       program_type             => 'PL/SQL BLOCK',
       number_of_arguments      => 2,
       enabled                  => 'TRUE'
      );
END;
/
 
 
--Then, create your job referencing the program:
 
 
BEGIN
    DBMS_SCHEDUER.CREATE_JOB(
          job_name => 'my_job',
          program_name => 'MY_PROGRAM',
          repeat_interval => 'FREQ=DAILY;BYHOUR=12',
          comments          =>  'Daily at noon');
END;
/

Open in new window



BEGIN
   DBMS_SCHEDULER.create_program
      (program_name             => 'MY_PROGRAM',
       progam_action            => 'DECLARE
                                 start_id NUMBER;
                                 end_id  NUMBER;
                              BEGIN
                                SELECT snap_id
                                    INTO     start_id
                                FROM    dba_hist_snapshot
                                WHERE (SELECT snap_id
                                             FROM dba_hist_snapshot
                                             ORDER BY snap_id DESC;)
                                   AND rownum = 2;
                                SELECT MAX(snap_id)
                                   INTO end_id
                                FROM dba_hist_snapshot;
                               
                           DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
                                 1183279397,
                                 1,
                                  start_id,
                                  end_id,
                                  DEFAULT)
                                  RETURN awrrpt_text_type_table PIPELINED;
                                 END;',
       program_type             => 'PL/SQL BLOCK',
       number_of_arguments      => 2,
       enabled                  => 'TRUE'
      );
END;
/
 
 
--Then, create your job referencing the program:
 
 
BEGIN
   dbms_scheduer.create_job
                     (job_name             => 'my_job',
                      program_name         => 'MY_PROGRAM',
                      repeat_interval      => 'freq=weekly; byday=mon; byhour=9;',
                      comments             => 'Weekly'
                     );
END;
/

Open in new window

Avatar of lhbras

ASKER

When I run the code
I get the error

   DBMS_SCHEDULER.create_program
   *
ERROR at line 2:
ORA-06550: line 2, column 4:
PLS-00306: wrong number or types of arguments in call to 'CREATE_PROGRAM'
ORA-06550: line 2, column 4:
PL/SQL: Statement ignored

Also the desired SQL in the PLSQL is not correct. Can you please test and provide a working solution

SELECT snap_id
                                    INTO     start_id
                                FROM    dba_hist_snapshot
                                WHERE (SELECT snap_id
                                             FROM dba_hist_snapshot
                                             ORDER BY snap_id DESC;)
                                   AND rownum = 2;
                                SELECT MAX(snap_id)
                                   INTO end_id
                                FROM dba_hist_snapshot


Avatar of lhbras

ASKER

Can someone provide me with a workable solution

If you check the SQL in above PLSQL code. This does not work.

SELECT snap_id FROM dba_hist_snapshot
WHERE (SELECT snap_id FROM dba_hist_snapshot ORDER BY snap_id DESC)AND rownum = 2;
You can try this:

SELECT sh.snap_id FROM dba_hist_snapshot sh
WHERE sh.snap_id IN (
SELECT sh1.snap_id FROM dba_hist_snapshot sh1 ORDER BY sh1.snap_id DESC) AND rownum = 2;