Wednesday, February 27, 2013

Configure a DB Scheduler job to send e-mail notifications

Most of the time you want to configure a job to send e-mail notifications about its state. The different states that a job can raise an event and thus send an email are :


job_broken
job_chain_stalled
job_completed
job_disabled
job_failed
job_over_max_dur
job_run_completed
job_sch_lim_reached
job_started
job_stopped
job_succeeded

job_all_events --> all the above.

First of all we have to provide the DB Scheduler with the SMTP mail server to use and a default email sender to be used:


begin

  dbms_scheduler.set_scheduler_attribute('email_server','mysmtp.mail.server'); --> Using SMTP default port (25) 
--  dbms_scheduler.set_scheduler_attribute('email_server','mysmtp.mail.server:777'); --> Using SMTP 777 port 

  dbms_scheduler.set_scheduler_attribute('email_sender','myemail@myserver.com');
  commit;
end;
/


This must be configured once. 

Then assuming that a job named MYJOB has been created run this as the job owner.




begin
  dbms_scheduler.add_job_email_notification ( job_name => 'MYJOB',
 recipients => 'myrecipient1@myemail.com,myrecipient2@myemail.com',
  subject => 'Scheduler Job Notification : %job_owner%.%job_name%-%event_type%'
  , body => '%event_type% occurred at %event_timestamp%. %error_message%',
  events => 'job_all_events' );
  commit;

end;
/




The previous command will send an email for all job events to the recipients specified using the default sender. use the following to specify sender and specific job states:


begin
  dbms_scheduler.add_job_email_notification ( job_name => 'MYJOB', recipients
  => 'myrecipient1@myemail.com, myrecipient1@myemail.com', sender =>
  'db_job_notification@myemail.com', subject =>
  'Scheduler Job Notification-%job_owner%.%job_name%-%event_type%', body =>
  '%event_type% occurred at %event_timestamp%. %error_message%', events =>
  'JOB_FAILED, JOB_BROKEN, JOB_DISABLED, JOB_SCH_LIM_REACHED');
  commit;

end;
/



By using the following query you have information for which jobs email notifications have been setup.


select job_name,
  recipient,
  event
from user_scheduler_notifications;



REFERENCES
Oracle® Database Administrator's Guide 11g Release 2 (11.2)


Wednesday, February 6, 2013

Row lock contention during inserts on table with Bitmap Indexes

Recently i investigated an issue which several concurrent sessions running inserts statements were waiting on enq: TX - row lock contention. 

I run an ASH report form Oracle EM for the duration the problem observed and i found the objects where were the root cause for these waits were 2 indexes. 

I checked the table structure and the mentioned indexes and found out that they were BITMAP indexes. As mentioned in Oracle® Database Data Warehousing Guide "Bitmap indexes are primarily intended for data warehousing applications where users query the data rather than update it. They are not suitable for OLTP applications with large numbers of concurrent transactions modifying the data"  

I have seen this behavior and in other OLTP applications and its an index design problem on the table. After explain this to the DEV team i drop and recreated the indexes as normal B-Tree indexes and problem resolved.

Please check also the following Blog entries with details on that problem. 






Friday, February 1, 2013

Oracle 11gR2 : db file async I/O submit

Recently i have sheen the waits on thsi event on an Oracle 11gR2 Enterprise editon on Linux x86_64. Further invetigion in metalink did not revealed any usefull information , but i found the following post on the web:

http://andreynikolaev.wordpress.com/2010/07/29/db-file-async-io-submit-wait-event/