First, let's check how we can generate AWR reports. To be able to get AWR reports in plain text, we can use:
Syntax (for Oracle 10.2)
DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT(
l_dbid IN NUMBER,
l_inst_num IN NUMBER,
l_bid IN NUMBER,
l_eid IN NUMBER,
l_options IN NUMBER DEFAULT 0)
RETURN awrrpt_text_type_table PIPELINED;
If we want the report in HTML, we can use:
DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
l_dbid IN NUMBER,
l_inst_num IN NUMBER,
l_bid IN NUMBER,
l_eid IN NUMBER,
l_options IN NUMBER DEFAULT 0)
RETURN awrrpt_text_type_table PIPELINED;
As you see both functions accept same arguments, only difference is the format of the report. Arguments of these functions:
l_dbid: The database identifier
l_insT_num: The instance number
l_bid: The 'Begin Snapshot' ID
l_eid: The 'End Snapshot' ID
l_options: A flag to specify to control the output of the report. Default vaule is 0, if we set it to 8, report will include the ADDM specific sections include the Buffer Pool Advice, Shared Pool Advice, and PGA Target Advice.
One of the important points is, we need to determine the database ID, instance number and snapshots dynamically to be able to define it as a job in OEM Grid Control. I query DBA_HIST_SNAPSHOT to find the beginning and end snapshot IDs according to variables starttime and endtime. I read the database ID and instance number from GV$DATABASE.
Here's the simple PL/SQL block to create AWR report in HTML and mail it:
DECLARE
dbid NUMBER;
inst_id NUMBER;
bid NUMBER;
eid NUMBER;
db_unique_name VARCHAR2(30);
host_name VARCHAR2(64);
starttime CHAR(5);
endtime CHAR(5);
v_from VARCHAR2(80);
v_recipient VARCHAR2(80) := 'someguy@myemailaddress.com';
v_mail_host VARCHAR2(30) := 'somesmtpserver';
v_mail_conn UTL_SMTP.connection;
BEGIN
starttime:= '06:00';
endtime:= '10:00';
SELECT MIN(snap_id), MAX(snap_id) INTO bid, eid
FROM dba_hist_snapshot
WHERE TO_CHAR(begin_interval_time, 'hh24:mi') >= starttime
AND TO_CHAR(end_interval_time, 'hh24:mi') <= endtime
AND TRUNC(begin_interval_time) = TRUNC (SYSDATE)
AND TRUNC(end_interval_time) = TRUNC (SYSDATE);
SELECT dbid, inst_id, db_unique_name INTO dbid, inst_id, db_unique_name
FROM gv$database;
SELECT host_name INTO host_name
FROM v$instance;
v_from := db_unique_name || '@' || host_name;
v_mail_conn := UTL_SMTP.OPEN_CONNECTION(v_mail_host, 25);
UTL_SMTP.HELO(v_mail_conn, v_mail_host);
UTL_SMTP.MAIL(v_mail_conn, v_from);
UTL_SMTP.RCPT(v_mail_conn, v_recipient);
UTL_SMTP.OPEN_DATA(v_mail_conn );
UTL_SMTP.WRITE_DATA( v_mail_conn, 'From:'|| v_from || UTL_TCP.CRLF );
UTL_SMTP.WRITE_DATA( v_mail_conn, 'To:' || v_recipient || UTL_TCP.CRLF );
UTL_SMTP.WRITE_DATA( v_mail_conn, 'Subject:' ||'AWR Report of ' || v_from || ' ' || SYSDATE || ' ' || starttime || '-' || endtime || UTL_TCP.CRLF );
UTL_SMTP.WRITE_DATA( v_mail_conn, 'Content-Type: text/html; charset=utf8' || UTL_TCP.CRLF || UTL_TCP.CRLF);
FOR c1_rec IN (SELECT output FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(dbid,inst_id,bid, eid, 8 )))
LOOP
UTL_SMTP.WRITE_DATA(v_mail_conn, c1_rec.output || UTL_TCP.CRLF );
END LOOP;
UTL_SMTP.CLOSE_DATA(v_mail_conn);
UTL_SMTP.QUIT(v_mail_conn);
EXCEPTION
WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
RAISE_APPLICATION_ERROR(-20000, 'Unable to send mail: ' || SQLERRM);
END;
/
To be able to create ADDM Reports, we can use DBMS_ADVISOR package:
DBMS_ADVISOR.CREATE_TASK('ADDM',tid,tname,'Name of the ADDM Report');
DBMS_ADVISOR.SET_TASK_PARAMETER(tname,'START_SNAPSHOT',bid);
ADDM reports are created as jobs, so we wait until they're completed and get the report:
SELECT DBMS_ADVISOR.GET_TASK_REPORT(tname) FROM DUAL;
Here's the simple PL/SQL block to create ADDM report and mail it:
DECLARE
dbid NUMBER;
inst_id NUMBER;
bid NUMBER;
eid NUMBER;
db_unique_name VARCHAR2(30);
host_name VARCHAR2(64);
status VARCHAR2(11);
starttime CHAR(5);
endtime CHAR(5);
output VARCHAR2(32000);
v_from VARCHAR2(80);
v_recipient VARCHAR2(80) := 'someguy@myemailaddress.com';
v_mail_host VARCHAR2(30) := 'somesmtpserver';
v_mail_conn UTL_SMTP.connection;
tname VARCHAR2(50);
tid number;
BEGIN
starttime := '01:00';
endtime := '12:00';
SELECT MIN(snap_id),MAX(snap_id) INTO bid, eid
FROM dba_hist_snapshot
WHERE TO_CHAR(begin_interval_time,'hh24:mi') >= starttime
AND TO_CHAR(end_interval_time, 'hh24:mi') <= endtime
AND TRUNC(begin_interval_time)= TRUNC (SYSDATE)
AND TRUNC(end_interval_time) = TRUNC (SYSDATE);
SELECT dbid, inst_id, db_unique_name INTO dbid, inst_id, db_unique_name
FROM gv$database;
SELECT host_name INTO host_name
FROM v$instance;
DBMS_ADVISOR.CREATE_TASK('ADDM',tid,tname,'ADDM Report( ' || bid || ',' || eid || ' )');
DBMS_ADVISOR.SET_TASK_PARAMETER(tname,'START_SNAPSHOT',bid);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname,'END_SNAPSHOT',eid);
DBMS_ADVISOR.EXECUTE_TASK(tname);
status := 0;
while status <> 'COMPLETED' loop
select status into status from dba_advisor_tasks where task_id = tid;
dbms_lock.sleep(5);
end loop;
v_from := db_unique_name || '@' || host_name;
v_mail_conn := UTL_SMTP.OPEN_CONNECTION (v_mail_host, 25);
UTL_SMTP.HELO(v_mail_conn, v_mail_host);
UTL_SMTP.MAIL(v_mail_conn, v_from);
UTL_SMTP.RCPT(v_mail_conn, v_recipient);
UTL_SMTP.OPEN_DATA(v_mail_conn);
UTL_SMTP.WRITE_DATA(v_mail_conn, 'From:' || v_from || UTL_TCP.CRLF );
UTL_SMTP.WRITE_DATA(v_mail_conn, 'To:' || v_recipient || UTL_TCP.CRLF );
UTL_SMTP.WRITE_DATA(v_mail_conn, 'Subject: ' || 'ADDM Report of ' || v_from || '' || SYSDATE || ' ' || starttime || '-' || endtime || UTL_TCP.CRLF || UTL_TCP.CRLF );
SELECT DBMS_ADVISOR.GET_TASK_REPORT(tname) INTO output FROM DUAL;
UTL_SMTP.WRITE_DATA(v_mail_conn, output);
UTL_SMTP.CLOSE_DATA(v_mail_conn);
UTL_SMTP.QUIT(v_mail_conn);
EXCEPTION
WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
RAISE_APPLICATION_ERROR(-20000, 'Unable to send mail: ' || SQLERRM);
END;
/
I have tested these scripts on both 10.2 and 11.2, and they worked fine. By the way, don't forget to set ACL if you'll run them in 11g+
BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl(acl => 'smtpserver.xml',
description => 'Connection to SMTP',
principal => 'GOKHAN',
is_grant => TRUE,
privilege => 'connect',
start_date => SYSTIMESTAMP,
end_date => NULL);
COMMIT;
END;
/
BEGIN
DBMS_NETWORK_ACL_ADMIN.assign_acl(acl => 'smtpserver.xml',
host => 'oursmtpserver',
lower_port => 25,
upper_port => 25 );
COMMIT;
END;
/
Now we can create the job in Grid Control. Login to OEM Grid Control, click the Jobs tab then choose SQL Script as job type and click Go. Give the new job a name and description, then click Add to add the target databases. Click to "Parameters" tab, and copy paste one of the above scripts into the textbox labeled "SQL Script". Set the credentials and schedule as you wish and click submit when all done.
Syntax (for Oracle 10.2)
DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT(
l_dbid IN NUMBER,
l_inst_num IN NUMBER,
l_bid IN NUMBER,
l_eid IN NUMBER,
l_options IN NUMBER DEFAULT 0)
RETURN awrrpt_text_type_table PIPELINED;
If we want the report in HTML, we can use:
DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
l_dbid IN NUMBER,
l_inst_num IN NUMBER,
l_bid IN NUMBER,
l_eid IN NUMBER,
l_options IN NUMBER DEFAULT 0)
RETURN awrrpt_text_type_table PIPELINED;
As you see both functions accept same arguments, only difference is the format of the report. Arguments of these functions:
l_dbid: The database identifier
l_insT_num: The instance number
l_bid: The 'Begin Snapshot' ID
l_eid: The 'End Snapshot' ID
l_options: A flag to specify to control the output of the report. Default vaule is 0, if we set it to 8, report will include the ADDM specific sections include the Buffer Pool Advice, Shared Pool Advice, and PGA Target Advice.
One of the important points is, we need to determine the database ID, instance number and snapshots dynamically to be able to define it as a job in OEM Grid Control. I query DBA_HIST_SNAPSHOT to find the beginning and end snapshot IDs according to variables starttime and endtime. I read the database ID and instance number from GV$DATABASE.
Here's the simple PL/SQL block to create AWR report in HTML and mail it:
DECLARE
dbid NUMBER;
inst_id NUMBER;
bid NUMBER;
eid NUMBER;
db_unique_name VARCHAR2(30);
host_name VARCHAR2(64);
starttime CHAR(5);
endtime CHAR(5);
v_from VARCHAR2(80);
v_recipient VARCHAR2(80) := 'someguy@myemailaddress.com';
v_mail_host VARCHAR2(30) := 'somesmtpserver';
v_mail_conn UTL_SMTP.connection;
BEGIN
starttime:= '06:00';
endtime:= '10:00';
SELECT MIN(snap_id), MAX(snap_id) INTO bid, eid
FROM dba_hist_snapshot
WHERE TO_CHAR(begin_interval_time, 'hh24:mi') >= starttime
AND TO_CHAR(end_interval_time, 'hh24:mi') <= endtime
AND TRUNC(begin_interval_time) = TRUNC (SYSDATE)
AND TRUNC(end_interval_time) = TRUNC (SYSDATE);
SELECT dbid, inst_id, db_unique_name INTO dbid, inst_id, db_unique_name
FROM gv$database;
SELECT host_name INTO host_name
FROM v$instance;
v_from := db_unique_name || '@' || host_name;
v_mail_conn := UTL_SMTP.OPEN_CONNECTION(v_mail_host, 25);
UTL_SMTP.HELO(v_mail_conn, v_mail_host);
UTL_SMTP.MAIL(v_mail_conn, v_from);
UTL_SMTP.RCPT(v_mail_conn, v_recipient);
UTL_SMTP.OPEN_DATA(v_mail_conn );
UTL_SMTP.WRITE_DATA( v_mail_conn, 'From:'|| v_from || UTL_TCP.CRLF );
UTL_SMTP.WRITE_DATA( v_mail_conn, 'To:' || v_recipient || UTL_TCP.CRLF );
UTL_SMTP.WRITE_DATA( v_mail_conn, 'Subject:' ||'AWR Report of ' || v_from || ' ' || SYSDATE || ' ' || starttime || '-' || endtime || UTL_TCP.CRLF );
UTL_SMTP.WRITE_DATA( v_mail_conn, 'Content-Type: text/html; charset=utf8' || UTL_TCP.CRLF || UTL_TCP.CRLF);
FOR c1_rec IN (SELECT output FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(dbid,inst_id,bid, eid, 8 )))
LOOP
UTL_SMTP.WRITE_DATA(v_mail_conn, c1_rec.output || UTL_TCP.CRLF );
END LOOP;
UTL_SMTP.CLOSE_DATA(v_mail_conn);
UTL_SMTP.QUIT(v_mail_conn);
EXCEPTION
WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
RAISE_APPLICATION_ERROR(-20000, 'Unable to send mail: ' || SQLERRM);
END;
/
To be able to create ADDM Reports, we can use DBMS_ADVISOR package:
DBMS_ADVISOR.CREATE_TASK('ADDM',tid,tname,'Name of the ADDM Report');
DBMS_ADVISOR.SET_TASK_PARAMETER(tname,'START_SNAPSHOT',bid);
ADDM reports are created as jobs, so we wait until they're completed and get the report:
SELECT DBMS_ADVISOR.GET_TASK_REPORT(tname) FROM DUAL;
Here's the simple PL/SQL block to create ADDM report and mail it:
DECLARE
dbid NUMBER;
inst_id NUMBER;
bid NUMBER;
eid NUMBER;
db_unique_name VARCHAR2(30);
host_name VARCHAR2(64);
status VARCHAR2(11);
starttime CHAR(5);
endtime CHAR(5);
output VARCHAR2(32000);
v_from VARCHAR2(80);
v_recipient VARCHAR2(80) := 'someguy@myemailaddress.com';
v_mail_host VARCHAR2(30) := 'somesmtpserver';
v_mail_conn UTL_SMTP.connection;
tname VARCHAR2(50);
tid number;
BEGIN
starttime := '01:00';
endtime := '12:00';
SELECT MIN(snap_id),MAX(snap_id) INTO bid, eid
FROM dba_hist_snapshot
WHERE TO_CHAR(begin_interval_time,'hh24:mi') >= starttime
AND TO_CHAR(end_interval_time, 'hh24:mi') <= endtime
AND TRUNC(begin_interval_time)= TRUNC (SYSDATE)
AND TRUNC(end_interval_time) = TRUNC (SYSDATE);
SELECT dbid, inst_id, db_unique_name INTO dbid, inst_id, db_unique_name
FROM gv$database;
SELECT host_name INTO host_name
FROM v$instance;
DBMS_ADVISOR.CREATE_TASK('ADDM',tid,tname,'ADDM Report( ' || bid || ',' || eid || ' )');
DBMS_ADVISOR.SET_TASK_PARAMETER(tname,'START_SNAPSHOT',bid);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname,'END_SNAPSHOT',eid);
DBMS_ADVISOR.EXECUTE_TASK(tname);
status := 0;
while status <> 'COMPLETED' loop
select status into status from dba_advisor_tasks where task_id = tid;
dbms_lock.sleep(5);
end loop;
v_from := db_unique_name || '@' || host_name;
v_mail_conn := UTL_SMTP.OPEN_CONNECTION (v_mail_host, 25);
UTL_SMTP.HELO(v_mail_conn, v_mail_host);
UTL_SMTP.MAIL(v_mail_conn, v_from);
UTL_SMTP.RCPT(v_mail_conn, v_recipient);
UTL_SMTP.OPEN_DATA(v_mail_conn);
UTL_SMTP.WRITE_DATA(v_mail_conn, 'From:' || v_from || UTL_TCP.CRLF );
UTL_SMTP.WRITE_DATA(v_mail_conn, 'To:' || v_recipient || UTL_TCP.CRLF );
UTL_SMTP.WRITE_DATA(v_mail_conn, 'Subject: ' || 'ADDM Report of ' || v_from || '' || SYSDATE || ' ' || starttime || '-' || endtime || UTL_TCP.CRLF || UTL_TCP.CRLF );
SELECT DBMS_ADVISOR.GET_TASK_REPORT(tname) INTO output FROM DUAL;
UTL_SMTP.WRITE_DATA(v_mail_conn, output);
UTL_SMTP.CLOSE_DATA(v_mail_conn);
UTL_SMTP.QUIT(v_mail_conn);
EXCEPTION
WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
RAISE_APPLICATION_ERROR(-20000, 'Unable to send mail: ' || SQLERRM);
END;
/
I have tested these scripts on both 10.2 and 11.2, and they worked fine. By the way, don't forget to set ACL if you'll run them in 11g+
BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl(acl => 'smtpserver.xml',
description => 'Connection to SMTP',
principal => 'GOKHAN',
is_grant => TRUE,
privilege => 'connect',
start_date => SYSTIMESTAMP,
end_date => NULL);
COMMIT;
END;
/
BEGIN
DBMS_NETWORK_ACL_ADMIN.assign_acl(acl => 'smtpserver.xml',
host => 'oursmtpserver',
lower_port => 25,
upper_port => 25 );
COMMIT;
END;
/
Now we can create the job in Grid Control. Login to OEM Grid Control, click the Jobs tab then choose SQL Script as job type and click Go. Give the new job a name and description, then click Add to add the target databases. Click to "Parameters" tab, and copy paste one of the above scripts into the textbox labeled "SQL Script". Set the credentials and schedule as you wish and click submit when all done.
No comments:
Post a Comment