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)
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)
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.sq l
@$ORACLE_HOME/rdbms/admin/
@$ORACLE_HOME/rdbms/admin/
From OEM: Jobs> Ccreate Job>SQL Script.
Then, create the script to execute above scripts and schedule it.
ASKER
I want a proven/tested example
that utilizes dbms_scheduler.create_prog ram to do this
that utilizes dbms_scheduler.create_prog
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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_prog ram(
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.A WR_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;
/
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_prog
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.A
<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;
/
ASKER
If anyone can check this as propose a working solution in this direction
sql> begin
dbms_scheduler.create_prog ram(
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.A WR_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;
/
sql> begin
dbms_scheduler.create_prog
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.A
<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;
/
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
<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;
/
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;
/
ASKER
When I run the code
I get the error
DBMS_SCHEDULER.create_prog ram
*
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
I get the error
DBMS_SCHEDULER.create_prog
*
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
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;
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;
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;