Showing posts with label email notification. Show all posts
Showing posts with label email notification. Show all posts

Tuesday, August 7, 2012

Email Notification ddl audit trigger


-----------------------------------------------------------------------------------------------------------------------

connect sys as sysdba user and run two scripts for install and configure utl_mail package

SQL> conn sys@orcl as sysdba
Enter password: ******
Connected.
SQL> @d:\oracle\product\10.2.0\db_1\rdbms\admin\utlmail.sql

Package created.


Synonym created.

SQL> @d:\oracle\product\10.2.0\db_1\rdbms\admin\prvtmail.plb;

Package body created.

No errors.

Set SMTP_OUT_SERVER parameter for smtp_exchange_server. This parameter is not modifiable means we have to bounce our database to set this parameter

SQL> alter system set smtp_out_server = 'mail.apexsoftcell.com' scope=spfile;



SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup

Grant EXECUTE privs to user which use utl_mail package.

SQL> grant execute on utl_mail to ldbo;

Grant succeeded.

------------------------------------------------------------------------------------------

DROP TABLE ddl_events;
CREATE TABLE ddl_events
( eventId          NUMBER(10,0),
  eventDate        DATE,
  oraLoginUser     VARCHAR2(30),
  oraDictObjName   VARCHAR2(30),
  oraDictObjOwner  VARCHAR2(30),
  oraDictObjType   VARCHAR2(30),
  oraSysEvent      VARCHAR2(30),
  machine          VARCHAR2(64),
  program          VARCHAR2(64),
  osuser           VARCHAR2(30),
  ip_address       VARCHAR2(20));

DROP TABLE ddl_events_sql;
CREATE TABLE ddl_events_sql
( eventId          NUMBER(10,0),
  sqlLine          NUMBER(10,0),
  sqlText          VARCHAR2(4000) );

-----Sequence to support events id's:
DROP SEQUENCE dsq_ddlEvents;

CREATE SEQUENCE dsq_ddlEvents START WITH 1000;

-------and here is trigger code:

CREATE OR REPLACE TRIGGER dtr_ddlEvents
AFTER DDL ON DATABASE
DECLARE

  l_sqlText    ORA_NAME_LIST_T;

BEGIN
  IF ORA_DICT_OBJ_OWNER in ('LDBO')
  THEN
BEGIN
  utl_mail.send (
  sender => 'kshitij@apexsoftcell.com',
  recipients => 'kshitij@apexsoftcell.com',
  subject => 'DDL change  has been made in '||ORA_DATABASE_NAME||' database.',
  message => 'User '||ORA_LOGIN_USER||' had run '||ORA_SYSEVENT|| ' on '||ORA_DICT_OBJ_TYPE||' '||ORA_DICT_OBJ_OWNER||'.'||ORA_DICT_OBJ_NAME||' in '||ORA_DATABASE_NAME||' database.'
  );
  END;
  INSERT INTO ddl_events
  ( SELECT dsq_ddlEvents.NEXTVAL,
           SYSDATE,
           ORA_LOGIN_USER,
           ORA_DICT_OBJ_NAME,
           ORA_DICT_OBJ_OWNER,
           ORA_DICT_OBJ_TYPE,
           ORA_SYSEVENT,
           machine,
           program,
           osuser,
           SYS_CONTEXT('USERENV','IP_ADDRESS')
      FROM SYS.DUAL,
           SYS.V_$SESSION
     WHERE SYS_CONTEXT('USERENV','SESSIONID' ) = audsid(+) );

   FOR l IN 1..ORA_SQL_TXT(l_sqlText) LOOP
    INSERT INTO ddl_events_sql
    ( eventId, sqlLine, sqlText )
    VALUES
    ( dsq_ddlEvents.CURRVAL, l, l_sqlText(l) );
  END LOOP;
 END IF;
END;
/

Posted by at No comments:
Labels:

Email Notification for alter user


CREATE OR REPLACE TRIGGER dtr_userEvents
BEFORE ALTER ON SCHEMA
DECLARE
  l_sqlText    ORA_NAME_LIST_T;

BEGIN
  IF ora_dict_obj_type IN ( 'USER') and  SYS_CONTEXT ('USERENV', 'SESSION_USER') in ('SYS','LDBO')
  THEN
BEGIN
  utl_mail.send (
  sender => 'kshitij@apexsoftcell.com',
  recipients => 'kshitij@apexsoftcell.com',
  subject => 'User change  has been made in '||ORA_DATABASE_NAME||' database.',
  message => 'User '||ORA_LOGIN_USER||' had run '||ORA_SYSEVENT|| ' on '||ORA_DICT_OBJ_TYPE||' '||ORA_DICT_OBJ_NAME||' in '||ORA_DATABASE_NAME||' database.'
  );
  END;
  INSERT INTO ddl_events
  ( SELECT dsq_ddlEvents.NEXTVAL,
           SYSDATE,
           ORA_LOGIN_USER,
           ORA_DICT_OBJ_NAME,
           ORA_DICT_OBJ_OWNER,
           ORA_DICT_OBJ_TYPE,
           ORA_SYSEVENT,
           machine,
           program,
           osuser,
           SYS_CONTEXT('USERENV','IP_ADDRESS')
      FROM SYS.DUAL,
           SYS.V_$SESSION
     WHERE SYS_CONTEXT('USERENV','SESSIONID' ) = audsid(+) );

   FOR l IN 1..ORA_SQL_TXT(l_sqlText) LOOP
    INSERT INTO ddl_events_sql
    ( eventId, sqlLine, sqlText )
    VALUES
    ( dsq_ddlEvents.CURRVAL, l, l_sqlText(l) );
  END LOOP;
 END IF;
END;
/
Posted by at No comments:
Labels:

Friday, April 20, 2012

Oracle 10g Scheduler Job Email Notification Wrapped

------------------------job_email_notification.sql------------------------------
prompt Enter an outgoing e-mail SMTP server host
define email_server_host    = &1
begin
  if sys_context('USERENV','SESSION_USER') != 'SYS' then
          raise_application_error(-20000, 'This script must be run AS SYS');
  end if;
end;
/
create or replace procedure sys.email_notification_procedure wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
8d1 40e
VeYguoLO7klQMOD091RnOY7N6yYwg2NcuiCDfI4Zxz8C6i6ZWT7mVZJ8aSwlXabiX5Ax6B9/
q8zsDVn2XkSDnC21vLdn5nYOHyoGN11uu06/4ssapd0sWsqzVTCSSTTQwesP6/owIvC7agcU
QaKx0KGRPVosicCYZzwoQ6JMTUCbI1rdZz+e7o5Tyz6758ZmoqyAHew/UhAPZnrpbkUlHr6b
yjKpvAyTTWdBrHQS5inCHQhrhst1GzPBY8KTiXrpoTD9coufGCvdENtDaAw1ASMqVyL7rgc3
RssAmgZwbiznWJAsfALzEX2sVhR2djz0Cti8/ECr/4F7RK26zboDMdjEO19Qtm4fHB3wSdIy
UFqdUavFMAXAiNky1oPmoqOA25+KleDvAnkMzBkBacuY3RzJq0prDL4C7u5kaW7TQdvqYsIH
2AoJ39tlJAZf1nFWiu3Ub++/+MiDUOD3l7fU6AB0LsLLMUsFpY5Vcro290JOIaeyUZbhZ7Wc
pfkwHGLu0wpSHEbT4YsjaQpLzM+xSCS4ik/wOMe1+H276sFPTEmWFkiwrVGQ5v22VciSRsUV
AhvP3QRSvg8qqnpmLlES3YK5wD9ttRa0yQiyaz/oGRRUVlmytAIw51VpHQme9DtdatzpbJzq
JsKbRRShbsihz5DJnIkKXi+5Vv+ezx/XyYYTdg9SO6VrrPJ13gtv1qyrbYjz+6W4ABYN8ETu
fl9+AEPR5yPdojuPn994XylBo9m5i/ImF+DVTkDA6lXjHunek4LJMS2WkO5jgpLgF1tYx7xA
i/A0/W2PAxwBaQC8JZmTXFkAG92oM7i0RaHUKMQ7s+cHUaKpcBuicIj28iM30bAUnKNrlc3D
cubPD6wZt1EBDGPzkSllE4W/DZjLatiSWBqrxRx1sNzTmjurRcANj5yXuTCUa0rq9RMf7vEI
zYn8CPy/IVjBZdeZSOknt3NjWH4FTjGfJwQLD4PS9svHf3rEYZp26ULMudQjWRYwYUokOcP9
39ij+GqqlpHqWQ==

/
show errors
grant execute on sys.email_notification_procedure to public;
begin
  dbms_scheduler.drop_program (
      program_name => 'sys.email_notification_program',
      force => true);
exception when others then null;
end;
/
begin
  dbms_scheduler.create_program (
      program_name => 'sys.email_notification_program',
      program_action=> 'sys.email_notification_procedure ',
      program_type => 'STORED_PROCEDURE',
      number_of_arguments => 6,
      enabled => FALSE) ;

  dbms_scheduler.define_metadata_argument (
      program_name => 'email_notification_program',
      argument_position => 1 ,
      argument_name => 'message',
      metadata_attribute => 'EVENT_MESSAGE') ;

  dbms_scheduler.define_program_argument (
      program_name => 'email_notification_program',
      argument_position => 2,
      argument_name => 'recipient_address',
      argument_type => 'varchar2') ;

  dbms_scheduler.define_program_argument (
      program_name => 'email_notification_program',
      argument_position => 3,
      argument_name => 'email_server_host',
      argument_type => 'varchar2') ;

  dbms_scheduler.define_program_argument (
      program_name => 'email_notification_program',
      argument_position => 4,
      argument_name => 'sender_address',
      argument_type => 'varchar2') ;

  dbms_scheduler.define_program_argument (
      program_name => 'email_notification_program',
      argument_position => 5,
      argument_name => 'subject_prefix',
      argument_type => 'varchar2') ;

  dbms_scheduler.define_program_argument (
      program_name => 'email_notification_program',
      argument_position => 6,
      argument_name => 'email_server_port',
      argument_type => 'number') ;

  dbms_scheduler.enable ('email_notification_program');
end ;
/
grant execute on sys.email_notification_program to public;
create or replace procedure sys.add_job_email_notification wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
221f c06
LJgrI9/yuQ8GWaZAPQ3Rnz8kmYQwg82ruscT1JJ6Hup+gq1Eo1H3kcahHpdeNtVYNjfCqZYQ
OI4TGrlXK8X6eYKGLJognUxwH1Bs9AVOwCEc/c0//vjRQS2FY4eXFlgPDxedYhnJOgc613mf
xFSaNJi+7GQgb7+kA4SFChGJAv+J0wYHXUc4uSZqOd9+fPySgk9AoYswHIxScVsOF0uItkOM
pi/QSpAhWntTOLjRUvxFiSN3A/cptAgdMIvQ72/t6Kgi1Lneb87rX8zGAI0FZ1AoA1dS4Wd8
Vx51QBwBY3DN2PRSxFSLLCAkc3q1dbUNozS6D0PExFUKlVs176TtUUjx1wSk+B/93qLZOj15
3AYKD04e+AXPoYdiGfFpLWbTjF5QG20+2h0t3IxX3kkzO7gVxQ/595YDDx8KMrYSb3/bQURS
iwe9kGk8LeO2v0btAxZWzaTo6/MBXcaoKaS/U2Nmwya00c4TW3Jivqoc4c/4vB5GzHVc7g65
/qBzrPIQLGhqd7hp/r7ICAFuvvNN9fd1YJ78JaVHS/AEcX0d3rzgpmt88uQtKKcWAbOIAtJ6
C0os69jjZ9fLSJcnBWySDsYIF0ckHa0lfLPNMFZWR+nS5NuA2pxNBwDbMowpju3djVCNiv5p
DLiw7mhkH3IZwb86wLz/ZL2RLJOWhcWdmBsEppHLN6CH979tbEirxpBdwGDpYGuXyB16QODx
V4yp8RFxAzeeShV48/VjV7NTr6ZWKw+C6eIbywyRwin69rTgNRogCC6RuAiy2CGZTUpPogOT
RA8MTuAxTgvRvKzq2RC+qwiiWHsfiMNXHGXlbAZvmA66q9nJmhy0BzMMmKqRLHSKjISYJDDe
KEEsMXL41vH8BGaVtzdRXRpf3/vru+16q35GiLP0LJ9n4Ycy0YbieOzhWkeUu+ZzGKyCGDN4
bakfRAkUA8+LPhe29wvK7MpARiXbKORdwm06KR1GtW0Ow48e9Hm2dA6QhUSEYikMkQAoZy4Q
7u6vMOB6encMOQ+5/P1xFEYb5j5rdSTixxuEJnr0VAghFDl168r6PrItcllRFGZ67aghSY7g
B0VkA1i2xneG5oTrppcCCEvwNjRx4GJ8nQMO/PLPkxzUVuyskG5f6tG8HLdOXha6kRV2pXyk
8jVuUIT5bB5r7uaRSY6f0b61Xwtrieb4RtJppTvmCma702Hxp+n7VYBxLYqo5FDdMFkdfgpe
JOR4otFsQFEN0N0EvWojkd3iTSMEWYQAYRw1bXKZH7ZsjQ4JKJh8KqzKqaaEwrqPsyqrwNev
e40hRuINN+AmEbdxiFXjJ1IuQjpJwT/Aoy9NmyiDC4U+0NGNAGcHNFYWx9Pl7wBB5fBfMFZy
is1vvc5MVZlFleVpZre8YuBKWTmfJmJ3XEPZQk2Db5LCGjxfzRixYCShB++Gg/JsqZsv6DVp
weqZSkXa+H2heXeGvSGIER5NKjw6lzFzJXJwN56h0ZAHjbE4D+i570KIEpQoXpdU20XgNNa7
vZAYbE4xj48O/sJjOD2YLM/TpwkZhKqSJpD4+vSR1au/Ke955dsW9kfsXNyNPY5U2cLobmFA
YtERZEi0U9Fxe2r00m790V9lcqF2rViJVbsIXxSE8oP05+LzfKzunkwHoAEMYs/DLEE4Fnw8
TsRVElfNkbFOgTSYMPi6Pw3/b6oVcEfVrLN28xwce/y94WdQITv1uYssmxuIf2mlPcLbzjhu
fIICOrdYFa6n8AsURk31bjiQ0sUMBpYykh1Mkk1IL14BXl5hvDZeLm2KOiw6VXu6qpJ1txPa
4J6IqBKoAs8//F7ogbm0F1IlzeUMNx3rfi/p6Y0xDpCs4JKn6AUZJYy9LBz1CQ6J+4Bhhqs+
VfJT0U7EMItlDEzEQkZja8AjzmWv2qPkfQcfyEwA7Y4Pbev6HOU+AqGZji4WOWxD4DmwE5OP
U3fYNd04xj8nVMaQcqUCASFJVHwaxYbI0iJkBHYzP6ngyoooZfpZS9mgILj2idpXIJtvwy15
Z6l77BP0p2CYr6iPfuPmLVRbnHfFf51W8Jep2OuaQYDwEucvvC0z7Z+KXcPSfOUBxsnMn2PG
hyV6t1HQ5Dbyt5OaxNnr3jG8RbJhbzq8BEwzqeKtEQc/E56bJCZxlcewyZQRggAnj38uZUmh
dSTytvMeG97atiAA04QJ0cF4Cnpq4i2sArJrFylblAHgrM+7FY/N+w70xI5l7pWNAjLYp/NS
bWuN8yM1TGmpzrhF/s+A5RVkU6w2QLZCMluqtwZ7tHw2WEY1Q+9XaIXaJEGQpjaGYKaq4SXG
b0zEC3/HoZRueLirzXtBW+nPjphwrm4MvgCaGiGN1pKlNmThRWNzT55qkecLjskKWrh0So80
Quyttqkh/YLzln4GxFyPgsrwNLxw4u5VdyeIi4kt8ik+Bc8lBo15+WnaUS2V2rMwR8Y7p9Fb
Fdsg4XibzqHQxPDWhH85wIZaME19PcNU1SDwPw2uDs2wJUGeDRrl51arBt5y1mhA86hK/ThL
Phfwo5cI/7ZEh0CcsZx2p3AEE5Xu+70pPg/ctWYFCFKx++aHrCEo9lf2p44dtSqVuMFjKyy5
+J+dF+5Z9+4SW5f59so43A5T1O+B/QC8rQvzbnqwQwJ2GbyNTZXiWloESaVjQFNHv6QcpVg2
7lfkS1Tfj6PN0lQhhpvsRTWYbsI9EVjy16lPi9kd+QY4roTjcN5sr6Q2Dtf9Lqz/XSguNAbg
A2trU5k1pP1UYWWd3yRFjWuOOyI7RdfqEjHGQwZzwPmvtNw7It7hr5LYRZQPiY8wYExn6cwr
lRLCsFHeAZ2awf6+c9u08gegtG/wxJAQlDtJI1O5xaCjLvhAHw0fB5aHTRe6Y6x55/To6mvQ
aRtzxdHS6TwgvSRpwue8SVEAej/ScyTk+XjuSf0P2XDjAxQTGnbv2sesiWHOOHC7yL6SIYc9
ug+VOnCq//D+GQ/GOriq/5Kdps+kBcFPryv4ZDdkCxKgJAUUBiDm7lRxta77Clw2fzwhk8Bw
HJy1Ov1YNKd4

/
show errors
create or replace public synonym add_job_email_notification for
  sys.add_job_email_notification;
grant execute on sys.add_job_email_notification to public;
create or replace procedure sys.remove_job_email_notification wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
ad9 4d4
qpXz6AXyFGIr0eqn4Y84+3I044Uwg+0rBUiDZy/Ngg9/5cMxojb7zGPlRlo5lkv07W2fsWmS
4LNAw8M+WkYz9NC7is5IwAqOKpdqwoLHi8DJn3pewNvkWn+0ZKrN+diGT5ggCJKxJ8csIL8H
11WL+PxLMXn9SB2Po8sL7gJ0qZl1EichgcNkvUFIVPR2hwP3N9KWtpgfLBNzorwvWa+Gm/dJ
GuOZ5MI0XvcXFjJCL4nd9sDOeqbtdwhanmq8BLH0IV5ZR5CnjFg8dmvN8NZn5qsii1mULcOk
145P79wUm6LpjstYFS+RBGU49X5Mw4R9nxY52A6GcyKK/6rYxB5z/Eu8RFbfdp+lSxkZ4ZSk
K/4ec+77uZ8S03tz1MguUxEOf/kLb3mqBPx1HrOJOnmVoHSLX2YyA8i6aVD3QXmzo8jDvQNa
3ecWZXqPyTHonsd3jw+2Gke88i6ZTXSWqxfL1aBD1D2jqNo9bYazQrt20DIlzTKYxUuo998U
oWz0CFJVgnQlXTXliiGmW9kpqVhls0vPLhr3cL2L+xNLBJl6RfQXQTxHxDWC7u/AK3oMOZl+
al54Uxcudhh3InnILfGoPPptebsxErJtPt8cWYkH8IU53spBpfqquSOPX45OdIT6JZvB0rca
8rTqUGvdT7JFdpsMr2+zxYhLsUYTcfgaJLhNLO+JxkEswAgk5v/v3xmlKvteSXT8kDcJZ6nK
iFE1DcQZym2q9h+cZi5c63qjkyahAo7TKqwQYE9NJgSx5+JRouEPFUkvTqKu7pu5G+vo13cw
l92sE945aUcsvK8VZiicbIjshnHJhW9RWRoggIGRZhkYWOrr8cVBPZNNK9gRbHX1uqFyrxqx
lwOVvw3SC6iXDSrZkxifgwUCZElC8r1Jxg0TZqklNaI1af7q9kp7kEzgwsLYzaYuI40Sl+wZ
OqyKSP+CKw588mRvUXCkQyl9vPPZoAp+E2VjGifV0PbJqv1vG+q5QrsGUU4p9wJcBT/Dtoxm
yZx9Q0OGuWTiCZ0LE5OPxtLMLaAWtEwwu8mgs+zvrjpI2dns2Cp7b4ynJMJNp7yfleuf8vuN
rYB0zrIdpLINzEhiZEgKbU8Wxu9Xf5kAWaEQuOe04ZHZiPwSg54X8uAkAjrsY5DsE8kJm9Dm
4a78N0+Z1D9CY8U81GFnygFlHb3ekxV5D/pY8LE7b6B59xMxI2HUWnQc+Lv54eNwFT4=

/
show errors
create or replace public synonym remove_job_email_notification for
  sys.remove_job_email_notification;
grant execute on sys.remove_job_email_notification to public;


----------------------------------end job_email_notification.sql--------------------

exec add_job_email_notification(job_name => 'EXPORT_JOB', subject_prefix => 'Apex Job Notification', recipient_address => 'kshitij@apexsoftcell.com',sender_address =>'info@apexsoftcell.com', email_server_host => 'mail.apexsoftcell.com', email_server_port => 25);

exec  DBMS_SCHEDULER.run_job ('EXPORT_JOB');


---------------------------------------start remove_job_notification.sql--------------
-- Run this script as SYS for example:
--   SQL> connect sys/sys_password as sysdba
--   SQL> @remove_job_notification

-- This script removes all objects created by the accompanying
-- job_notification.sql script. It also removes any job e-mail
-- notifications that have been added.

-- this must be run as SYS (to do privilege checking) so check this
begin
  if sys_context('USERENV','SESSION_USER') != 'SYS' then
          raise_application_error(-20000, 'This script must be run AS SYS');
  end if;
end;
/

drop procedure sys.email_notification_procedure;

begin
  dbms_scheduler.drop_program (
      program_name => 'sys.email_notification_program',
      force => true);
end;
/

drop public synonym add_job_email_notification;

drop procedure sys.add_job_email_notification;

drop public synonym remove_job_email_notification;

drop procedure sys.remove_job_email_notification;

-- now remove any e-mailer jobs that have been created
-- They won't run anyway since the program they point to has been removed.
BEGIN
  FOR old_notify_job IN
  ( SELECT '"'||owner||'"."'||job_name ||'"' name FROM dba_scheduler_jobs WHERE
    job_name like '%_EMAILER%'
    and comments like 'Auto-generated job to send email alerts for job%'
    and program_owner = 'SYS'
    and program_name = 'EMAIL_NOTIFICATION_PROGRAM'
  )
  LOOP
    dbms_scheduler.drop_job(old_notify_job.name, TRUE);
  END LOOP;
END;
/


---------------------------------------end remove_job_notification.sql--------------

Posted by at No comments:
Labels:

Oracle 10g Scheduler Job Email Notification

----------------------------------------------------start job_notification.sql---------------------

-- Run this script as SYS with 2 parameters, an outgoing e-mail SMTP server
-- and port (normally 25) for example:
--   SQL> connect sys/sys_password as sysdba
--   SQL> @job_notification smtp.example.com 25
--
-- This script creates 2 procedures to let users setup e-mail notification for
-- jobs they have access to and grants execute on these to public. It also
-- creates public synonyms for these procedures.
-- add_job_email_notification
-- remove_job_email_notification
--
-- It also creates a utility program and procedure
-- sys.email_notification_program
-- sys.email_notification_procedure
--
-- WARNING If any of these objects exist, they will be replaced.
--
-- To remove the objects created by this script, run the accompanying
-- remove_job_notification.sql script. This script also removes any job
-- e-mail notifications that have been added.

-- DBA either define the below variables or pass them into the script
prompt Enter an outgoing e-mail SMTP server host
define email_server_host    = &1
prompt Enter an outgoing e-mail SMTP server port
define email_server_port    = &2

-- this must be run as SYS (to do privilege checking) so check this
begin
  if sys_context('USERENV','SESSION_USER') != 'SYS' then
          raise_application_error(-20000, 'This script must be run AS SYS');
  end if;
end;
/

-- create a utility procedure to send e-mail for a scheduler job event
create or replace procedure sys.email_notification_procedure
( message           IN sys.scheduler$_event_info,
  recipient_address IN VARCHAR2,
  email_server_host IN VARCHAR2,
  sender_address    IN VARCHAR2,
  subject_prefix    IN VARCHAR2,
  email_server_port IN PLS_INTEGER) AS
msg_text          VARCHAR2(4000);
mail_conn         utl_smtp.connection;
all_addresses     VARCHAR2(4000) := ','||recipient_address;
single_address    VARCHAR2(500);
BEGIN

  -- compose the message
  msg_text := 'Job: "'||message.object_owner||'"."'||message.object_name||'"'||
                UTL_TCP.CRLF ||
              'Event: '|| message.event_type || UTL_TCP.CRLF ||
              'Date: '|| regexp_replace(message.event_timestamp,'\.[0-9]{3,}') ||
                UTL_TCP.CRLF;

  IF message.log_id IS NOT NULL AND message.log_id != 0 THEN
    msg_text := msg_text || 'Log ID: '||message.log_id||UTL_TCP.CRLF;
  END IF;

  IF message.error_code IS NOT NULL AND message.error_code != 0 THEN
    msg_text := msg_text ||
      'Error code: ' || message.error_code|| UTL_TCP.CRLF ||
      'Error message: ' || UTL_TCP.CRLF || message.error_msg;
  END IF;


  -- now send the e-mail
  mail_conn := utl_smtp.open_connection(email_server_host,email_server_port);
  utl_smtp.helo(mail_conn, email_server_host);
  utl_smtp.mail(mail_conn, sender_address);

  single_address := regexp_substr(all_addresses,'^,[^,]*');
  all_addresses := regexp_replace(all_addresses,'^,[^,]*');

  WHILE single_address IS NOT NULL AND
    regexp_substr(single_address, '[[:graph:]]') IS NOT NULL
  LOOP
    utl_smtp.rcpt(mail_conn, replace(single_address,','));
    single_address := regexp_substr(all_addresses,'^,[^,]*');
    all_addresses := regexp_replace(all_addresses,'^,[^,]*');
  END LOOP;

  utl_smtp.open_data(mail_conn);
  utl_smtp.write_data(mail_conn, 'From: ' || sender_address ||    UTL_TCP.CRLF);
  utl_smtp.write_data(mail_conn, 'To: ' || recipient_address ||     UTL_TCP.CRLF);
  utl_smtp.write_data(mail_conn, 'Subject: ' || subject_prefix ||     ' - ' ||  message.object_owner || '.' || message.object_name ||     ' ' || message.event_type || UTL_TCP.CRLF || UTL_TCP.CRLF);
  utl_smtp.write_data(mail_conn, msg_text || UTL_TCP.CRLF);
  utl_smtp.close_data(mail_conn);
  utl_smtp.quit(mail_conn);
END;
/

show errors

grant execute on sys.email_notification_procedure to public;

-- drop any existing email_notification_program, suppressing all errors
begin
  dbms_scheduler.drop_program (
      program_name => 'sys.email_notification_program',
      force => true);
exception when others then null;
end;
/

-- create a utility program to send e-mail for a scheduler job event
begin
  dbms_scheduler.create_program (
      program_name => 'sys.email_notification_program',
      program_action=> 'sys.email_notification_procedure ',
      program_type => 'STORED_PROCEDURE',
      number_of_arguments => 6,
      enabled => FALSE) ;

  dbms_scheduler.define_metadata_argument (
      program_name => 'email_notification_program',
      argument_position => 1 ,
      argument_name => 'message',
      metadata_attribute => 'EVENT_MESSAGE') ;

  dbms_scheduler.define_program_argument (
      program_name => 'email_notification_program',
      argument_position => 2,
      argument_name => 'recipient_address',
      argument_type => 'varchar2') ;

  dbms_scheduler.define_program_argument (
      program_name => 'email_notification_program',
      argument_position => 3,
      argument_name => 'email_server_host',
      argument_type => 'varchar2') ;

  dbms_scheduler.define_program_argument (
      program_name => 'email_notification_program',
      argument_position => 4,
      argument_name => 'sender_address',
      argument_type => 'varchar2') ;

  dbms_scheduler.define_program_argument (
      program_name => 'email_notification_program',
      argument_position => 5,
      argument_name => 'subject_prefix',
      argument_type => 'varchar2') ;

  dbms_scheduler.define_program_argument (
      program_name => 'email_notification_program',
      argument_position => 6,
      argument_name => 'email_server_port',
      argument_type => 'number') ;

  dbms_scheduler.enable ('email_notification_program');
end ;
/

grant execute on sys.email_notification_program to public;

-- Sets up e-mail notification for selected job events
-- for a given job.
-- This creates an e-mailer job in the job owner's schema
-- The default events chosen for e-mail notification are:
-- JOB_FAILED,JOB_BROKEN,JOB_SCH_LIM_REACHED,JOB_CHAIN_STALLED
-- The caller must be the job owner or have alter privileges on the job.
create or replace procedure sys.add_job_email_notification
(
  job_name  IN VARCHAR2,
  recipient_address IN VARCHAR2,
  events            IN VARCHAR2 DEFAULT
     'JOB_SUCCEEDED, JOB_FAILED,JOB_BROKEN,JOB_SCH_LIM_REACHED,JOB_CHAIN_STALLED',
  sender_address    IN VARCHAR2 DEFAULT 'noreply@apexsoftcell.com',
  subject_prefix    IN VARCHAR2 DEFAULT 'Oracle Scheduler Job Notification',
  email_server_host IN VARCHAR2 DEFAULT '&email_server_host',
  email_server_port IN PLS_INTEGER DEFAULT &email_server_port
) AS
events_on         NUMBER := 0;
job_object        VARCHAR2(35);
job_owner         VARCHAR2(35);
canon_job_name    VARCHAR2(30);
canon_job_owner   VARCHAR2(30);
caller            VARCHAR2(30) := sys_context('USERENV','SESSION_USER');
notifier_job_name VARCHAR2(30);
new_raise_events  NUMBER := 0;
cur_raise_events  VARCHAR2(200);
priv_count        NUMBER;
event_condition   VARCHAR2(4000);
comments_text     VARCHAR2(100) :=
  'Auto-generated job to send email alerts for job ';
type event_name_list is table of varchar2(30);
type event_number_list is table of number;
event_names event_name_list :=  event_name_list('JOB_STARTED',
  'JOB_SUCCEEDED', 'JOB_FAILED', 'JOB_BROKEN', 'JOB_COMPLETED',
  'JOB_STOPPED', 'JOB_SCH_LIM_REACHED', 'JOB_DISABLED',
  'JOB_CHAIN_STALLED',  'JOB_OVER_MAX_DUR');
event_numbers event_number_list :=
  event_number_list(1,2,4,8,16,32,64,128,256,512);
event_found       pls_integer := 0;
event_10862       pls_integer := 0;
begin

  -- get job name and owner
  sys.dbms_isched.resolve_name(job_name, job_object, job_owner, caller);

  -- canonicalize job name and owner
  dbms_utility.canonicalize(job_object, canon_job_name, 30);
  dbms_utility.canonicalize(job_owner, canon_job_owner, 30);
  comments_text := comments_text ||'"'||canon_job_owner ||'"."'||canon_job_name||'"' ;

  -- check if the caller has privileges on the job

  -- check if the caller is the job owner or 'SYS'
  IF canon_job_owner = caller or caller = 'SYS' THEN
    goto privilege_check_passed;
  END IF;

  -- check whether the caller has been granted ALTER on the job or
  -- CREATE ANY JOB or SCHEDULER_ADMIN or DBA directly
  select count(*) into priv_count from dba_sys_privs where grantee=caller and
    privilege='CREATE ANY JOB';
  IF priv_count > 0 THEN goto privilege_check_passed; END IF;

  select count(*) into priv_count  from dba_role_privs where grantee=caller and
    granted_role='SCHEDULER_ADMIN';
  IF priv_count > 0 THEN goto privilege_check_passed; END IF;

  select count(*) into priv_count from dba_tab_privs where grantee=caller and
    owner=canon_job_owner and table_name=canon_job_name;
  IF priv_count > 0 THEN goto privilege_check_passed; END IF;

  -- recursive privileges check for CREATE ANY JOB system priv
  -- includes a recursive roles check so SCHEDULER_ADMIN will also work
  -- this is slow but all simple privilege checks have failed
  select count(*) into priv_count from (
    select grantee, granted from
    (
      /* roles granted */
      select grantee, granted_role granted from dba_role_privs
      /* system privileges granted */
      union
      select grantee, privilege granted from dba_sys_privs
    )
  start with grantee = caller connect by grantee = prior granted )
  where granted = 'CREATE ANY JOB';

  IF priv_count > 0 THEN goto privilege_check_passed; END IF;

  -- recursive privileges check whether the caller has object privileges on the job
  -- this is slow but all simple privilege checks have failed
  select count(*) into priv_count from (
  select * from
    (
    /* object privileges granted */
      select table_name g1, owner g2, grantee obj, grantee own, privilege typ
      from dba_tab_privs
    /* role privileges granted */
    union
      select granted_role  g1, granted_role  g2, grantee, grantee, null
      from dba_role_privs
    )
  start with g1 = canon_job_name and g2 = canon_Job_owner
  connect by g1 = prior obj and g2 = prior own)
  where obj=caller;

  IF priv_count > 0 THEN goto privilege_check_passed; END IF;

  -- no privileges, throw job_does_exist error
  dbms_sys_error.raise_system_error(-23308, canon_job_owner, canon_job_name,    TRUE);

<<privilege_check_passed>>

  -- retrieve current events turned on. cast NO_DATA_FOUND to job not found
  begin
  select raise_events into cur_raise_events from dba_scheduler_jobs where
  job_name=canon_job_name and owner=canon_job_owner ;
  exception when no_data_found then
    dbms_sys_error.raise_system_error(-23308, canon_job_owner, canon_job_name,
      TRUE);
  when others then raise;
  end;

  -- generate event_condition
  event_condition := 'tab.user_data.object_owner = '''||canon_job_owner||
            ''' AND tab.user_data.object_name = '''||canon_job_name|| '''';

  if instr(UPPER(events),'JOB_ALL_EVENTS')>0 then
    -- by default we have no events clause so all events will trigger an e-mail
    event_found := 1;
  else
    event_condition := event_condition ||' AND tab.user_data.event_type in (';

    for i in event_names.first..event_names.last loop
      if instr(UPPER(events),event_names(i))>0 then
        event_condition := event_condition || ''''||event_names(i)||''',';
        event_found := 1;
      end if;
    end loop;

    if instr(UPPER(events),'JOB_RUN_COMPLETED')>0 then
        event_condition := event_condition ||
          '''JOB_SUCCEEDED'',''JOB_FAILED'',''JOB_STOPPED'',';
        event_found := 1;
    end if;

    -- strip last comma and add close brace
    event_condition := regexp_replace(event_condition, ',$');
    event_condition := event_condition || ')';
  end if;

  -- if no events have been specified, throw an error
  if event_found = 0 then
    dbms_sys_error.raise_system_error(-24098, events, 'EVENTS',TRUE);
  end if;

  -- collect all events to turn on
  if cur_raise_events is null then
    cur_raise_events := UPPER(events) ;
  else
    cur_raise_events := UPPER(events) ||','||UPPER(cur_raise_events) ;
  end if;

  for i in event_names.first..event_names.last loop
    if instr(cur_raise_events,event_names(i))>0 then
      new_raise_events := new_raise_events + event_numbers(i);
    end if;
  end loop;

  if instr(cur_raise_events,'JOB_RUN_COMPLETED')>0 then
    new_raise_events := new_raise_events -
      bitand(new_raise_events,sys.dbms_scheduler.job_run_completed) +
      sys.dbms_scheduler.job_run_completed;
  end if;

  if instr(cur_raise_events,'JOB_ALL_EVENTS')>0 then
    new_raise_events := new_raise_events -
      bitand(new_raise_events,sys.dbms_scheduler.job_all_events) +
      sys.dbms_scheduler.job_all_events;
  end if;

  -- turn on events the user is interested in
  dbms_scheduler.set_attribute
    ( '"'||canon_job_owner||'"."'||canon_job_name||'"' , 'raise_events' ,
      new_raise_events);

  -- set event 10862 if not set so that we can add a subscriber
  -- this is necessary because if event 10862 is not set then AQ is in backward
  -- compatibility mode which checks the login user instead of the current user
  -- for privileges.
  dbms_system.read_ev(10862, event_10862);
  IF event_10862 = 0 THEN
    EXECUTE IMMEDIATE
      'ALTER SESSION SET EVENTS ''10862 TRACE NAME CONTEXT FOREVER, LEVEL 1''';
  END IF;

  -- add a new subscriber for this notification
  BEGIN
    dbms_aqadm.add_subscriber
     (queue_name => 'SYS.SCHEDULER$_EVENT_QUEUE',
      subscriber => sys.aq$_agent(canon_job_owner, NULL, NULL),
      rule => 'tab.user_data.object_owner = '''||canon_job_owner||'''');
  EXCEPTION WHEN others then

    -- unset event 10862, if we set it above
    IF event_10862 = 0 THEN
      EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ' ||
        '''10862 TRACE NAME CONTEXT OFF''' ;
    END IF;

    if sqlcode = -24034 then NULL;
    else raise;
    end if;
  end;

  -- unset event 10862, if we set it above
  IF event_10862 = 0 THEN
    EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ' ||
      '''10862 TRACE NAME CONTEXT OFF''' ;
  END IF;

  -- allow the job owner to access our events queue via this subscriber
  dbms_aqadm.enable_db_access(canon_job_owner,'"'||canon_job_owner||'"');

  -- if this procedure has been run already for this job, drop previously
  -- created email notification jobs. One of the parameters might have changed
  -- and existing email notification jobs might have been altered or broken.
  FOR old_notify_job IN
  ( SELECT '"'||owner||'"."'||job_name ||'"' name FROM dba_scheduler_jobs WHERE
    owner = canon_job_owner and job_name like substr(canon_job_name,1,10) || '_EMAILER%'
    and comments = comments_text and program_owner = 'SYS'
    and program_name = 'EMAIL_NOTIFICATION_PROGRAM'
  )
  LOOP
    dbms_scheduler.drop_job(old_notify_job.name);
  END LOOP;

  -- now create a notifier job which waits on job events
  BEGIN
  -- try using a simple name without an added number suffix
  notifier_job_name := substr(canon_job_name,1,10) || '_EMAILER' ;
  notifier_job_name := '"' || canon_job_owner || '"."' || notifier_job_name ||'"' ;
  dbms_scheduler.create_job(notifier_job_name,
                            program_name => 'sys.email_notification_program',
                            event_condition => event_condition,
                            queue_spec =>'sys.scheduler$_event_queue,"'
                              || canon_job_owner||'"',
                            comments => comments_text);
  EXCEPTION WHEN OTHERS THEN
  IF sqlcode != -27477 THEN RAISE; END IF;
  -- a job already exists using our simple name, add a numerical suffix
  notifier_job_name :=
  dbms_scheduler.generate_job_name( '"'||substr(canon_job_name,1,10)||'_EMAILER"');
  notifier_job_name := '"' || canon_job_owner || '"."' || notifier_job_name ||'"' ;

  dbms_scheduler.create_job(notifier_job_name,
                            program_name => 'sys.email_notification_program',
                            event_condition => event_condition,
                            queue_spec =>'sys.scheduler$_event_queue,"'
                              || canon_job_owner||'"',
                            comments => comments_text);
  END;

  dbms_scheduler.set_job_argument_value(notifier_job_name,2,
                                        recipient_address);

  dbms_scheduler.set_job_argument_value(notifier_job_name,3,
                                        email_server_host);

  dbms_scheduler.set_job_argument_value(notifier_job_name,4,
                                        sender_address);

  dbms_scheduler.set_job_argument_value(notifier_job_name,5,
                                        subject_prefix);

  dbms_scheduler.set_job_argument_value(notifier_job_name,6,
                                        to_char(email_server_port));

  dbms_scheduler.enable(notifier_job_name);

end;
/

show errors

create or replace public synonym add_job_email_notification for
  sys.add_job_email_notification;

grant execute on sys.add_job_email_notification to public;

-- Removes e-mail notification for a given job.
-- This removes the e-mailer job that was created for the job in the job
-- owner's schema.
-- It does not remove the agent created for the job owner or reset raise_events
-- for the given job.
-- The caller must be the job owner or have alter privileges on the given job.
create or replace procedure sys.remove_job_email_notification
(
  job_name  IN VARCHAR2) AS
job_object        VARCHAR2(35);
job_owner         VARCHAR2(35);
canon_job_name    VARCHAR2(30);
canon_job_owner   VARCHAR2(30);
caller            VARCHAR2(30) := sys_context('USERENV','SESSION_USER');
notifier_job_name VARCHAR2(30);
priv_count        NUMBER;
comments_text     VARCHAR2(100) :=
  'Auto-generated job to send email alerts for job ';
begin

  -- get job name and owner
  sys.dbms_isched.resolve_name(job_name, job_object, job_owner, caller);

  -- canonicalize job name and owner
  dbms_utility.canonicalize(job_object, canon_job_name, 30);
  dbms_utility.canonicalize(job_owner, canon_job_owner, 30);
  comments_text := comments_text ||'"'||canon_job_owner ||'"."'||canon_job_name||'"' ;

  -- check if the caller has privileges on the job

  -- check if the caller is the job owner or 'SYS'
  IF canon_job_owner = caller or caller = 'SYS' THEN
    goto privilege_check_passed;
  END IF;

  -- check whether the caller has been granted ALTER on the job or
  -- CREATE ANY JOB or SCHEDULER_ADMIN or DBA directly
  select count(*) into priv_count from dba_sys_privs where grantee=caller and
    privilege='CREATE ANY JOB';
  IF priv_count > 0 THEN goto privilege_check_passed; END IF;

  select count(*) into priv_count  from dba_role_privs where grantee=caller and
    granted_role='SCHEDULER_ADMIN';
  IF priv_count > 0 THEN goto privilege_check_passed; END IF;

  select count(*) into priv_count from dba_tab_privs where grantee=caller and
    owner=canon_job_owner and table_name=canon_job_name;
  IF priv_count > 0 THEN goto privilege_check_passed; END IF;

  -- recursive privileges check for CREATE ANY JOB system priv
  -- includes a recursive roles check so SCHEDULER_ADMIN will also work
  -- this is slow but all simple privilege checks have failed
  select count(*) into priv_count from (
    select grantee, granted from
    (
      /* roles granted */
      select grantee, granted_role granted from dba_role_privs
      /* system privileges granted */
      union
      select grantee, privilege granted from dba_sys_privs
    )
  start with grantee = caller connect by grantee = prior granted )
  where granted = 'CREATE ANY JOB';

  IF priv_count > 0 THEN goto privilege_check_passed; END IF;

  -- recursive privileges check whether the caller has object privileges on the job
  -- this is slow but all simple privilege checks have failed
  select count(*) into priv_count from (
  select * from
    (
    /* object privileges granted */
      select table_name g1, owner g2, grantee obj, grantee own, privilege typ
      from dba_tab_privs
    /* role privileges granted */
    union
      select granted_role  g1, granted_role  g2, grantee, grantee, null
      from dba_role_privs
    )
  start with g1 = canon_job_name and g2 = canon_Job_owner
  connect by g1 = prior obj and g2 = prior own)
  where obj=caller;

  IF priv_count > 0 THEN goto privilege_check_passed; END IF;

  -- no privileges, throw job_does_exist error
  dbms_sys_error.raise_system_error(-23308, canon_job_owner, canon_job_name,
    TRUE);

<<privilege_check_passed>>

  -- drop created email notification jobs.
  FOR old_notify_job IN
  ( SELECT '"'||owner||'"."'||job_name ||'"' name FROM dba_scheduler_jobs WHERE
    owner = canon_job_owner and job_name like substr(canon_job_name,1,10) || '_EMAILER%'
    and comments = comments_text and program_owner = 'SYS'
    and program_name = 'EMAIL_NOTIFICATION_PROGRAM'
  )
  LOOP
    dbms_scheduler.drop_job(old_notify_job.name);
  END LOOP;

end;
/

show errors

create or replace public synonym remove_job_email_notification for
  sys.remove_job_email_notification;

grant execute on sys.remove_job_email_notification to public;

----------------------------------------------------end job_notification.sql---------------------
exec add_job_email_notification(job_name => 'EXPORT_JOB', subject_prefix => 'Apex Job Notification', recipient_address => 'kshitij@apexsoftcell.com',sender_address =>'info@apexsoftcell.com', email_server_host => 'mail.apexsoftcell.com', email_server_port => 25);

exec  DBMS_SCHEDULER.run_job ('EXPORT_JOB');
----------------------------------------------------start 
remove_job_notification.sql
--------------------
-- Run this script as SYS for example:
--   SQL> connect sys/sys_password as sysdba
--   SQL> @remove_job_notification

-- This script removes all objects created by the accompanying
-- job_notification.sql script. It also removes any job e-mail
-- notifications that have been added.

-- this must be run as SYS (to do privilege checking) so check this
begin
  if sys_context('USERENV','SESSION_USER') != 'SYS' then
          raise_application_error(-20000, 'This script must be run AS SYS');
  end if;
end;
/

drop procedure sys.email_notification_procedure;

begin
  dbms_scheduler.drop_program (
      program_name => 'sys.email_notification_program',
      force => true);
end;
/

drop public synonym add_job_email_notification;

drop procedure sys.add_job_email_notification;

drop public synonym remove_job_email_notification;

drop procedure sys.remove_job_email_notification;

-- now remove any e-mailer jobs that have been created
-- They won't run anyway since the program they point to has been removed.
BEGIN
  FOR old_notify_job IN
  ( SELECT '"'||owner||'"."'||job_name ||'"' name FROM dba_scheduler_jobs WHERE
    job_name like '%_EMAILER%'
    and comments like 'Auto-generated job to send email alerts for job%'
    and program_owner = 'SYS'
    and program_name = 'EMAIL_NOTIFICATION_PROGRAM'
  )
  LOOP
    dbms_scheduler.drop_job(old_notify_job.name, TRUE);
  END LOOP;
END;
/
----------------------------------------------------end 
remove_job_notification.sql
--------------------
Posted by at No comments:
Labels:

Monday, April 16, 2012

Oracle 11g Job Email Notification


exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('email_server','mail.kshitijdomain.com:25');

----------------exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('email_sender','info@kshitijdomain.com');


BEGIN
DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
job_name => 'COMPILE',
recipients => 'kshitij@kshitijdomain.com',
sender => 'info@kshitijdomain.com',
subject => 'Job Notification-%job_owner%.%job_name%-%event_type%',
body => '%event_type% at %event_timestamp%. %error_message%',
events => 'JOB_SUCCEEDED,JOB_FAILED, JOB_BROKEN, JOB_DISABLED, JOB_SCH_LIM_REACHED,JOB_CHAIN_STALLED,JOB_OVER_MAX_DUR');
END;
/

--------------------------------------

SET serveroutput ON
declare
v_att VARCHAR2(64);
v_att2 varchar2(64);
BEGIN
DBMS_SCHEDULER.GET_SCHEDULER_ATTRIBUTE('email_server', v_att);
DBMS_SCHEDULER.GET_SCHEDULER_ATTRIBUTE('email_sender', v_att2);
dbms_output.put_line('server: ' || v_att);
dbms_output.put_line('sender: ' || v_att2);
END;
/

------------------------------------

BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'compile',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN UTL_RECOMP.recomp_serial(''DPCDSL''); END;',
start_date => '01-APR-12 11:00.00.00 PM ASIA/CALCUTTA',
repeat_interval => 'freq=DAILY',
end_date => NULL,
enabled => TRUE,
comments => 'JOB to compile invalid objects');
END;
/


exec DBMS_SCHEDULER.run_job ('compile');

------------------------------------------------------------------------------------------------------------------------

BEGIN
DBMS_SCHEDULER.REMOVE_JOB_EMAIL_NOTIFICATION ('COMPILE');
END;
/

Posted by at No comments:
Labels: ,

Saturday, March 24, 2012

Oracle 10g Scheduler Job Email Notification

1) connect sys as sysdba user and run two scripts for install and configure utl_mail package

SQL> conn sys@apx1213 as sysdba
Enter password: ******
Connected.
SQL> @d:\oracle\product\10.2.0\db_1\rdbms\admin\utlmail.sql

Package created.


Synonym created.

SQL> @d:\oracle\product\10.2.0\db_1\rdbms\admin\prvtmail.plb;

Package body created.

No errors.

2) Set SMTP_OUT_SERVER parameter for smtp_exchange_server. This parameter is not modifiable means we have to bounce our database to set this parameter

SQL> alter system set smtp_out_server = 'mail.apexsoftcell.com' scope=spfile;

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup

3) Grant EXECUTE privs to user which use utl_mail package.

SQL> grant execute on utl_mail to ldbo;

Grant succeeded.

) Create procedure for Email Notification

create or replace procedure PRC_EMAIL (pSubject IN VARCHAR2, pMessage IN VARCHAR2) is
BEGIN
utl_mail.send(sender => 'info@apexsoftcell.com', recipients => 'kshitij@apexsoftcell.com', subject => pSubject, message => pMessage);
END;
/

4) Create Scheduler Job

BEGIN
DBMS_SCHEDULER.drop_JOB (job_name => 'compile');
END;
/


BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'compile',
job_type => 'PLSQL_BLOCK',
job_action => 'DECLARE lnResult VARCHAR2(150);
BEGIN UTL_RECOMP.recomp_serial(''LDBO''); lnResult:=''SUCCESS'';
PRC_EMAIL(''Compile Notification'',lnResult);
EXCEPTION WHEN OTHERS THEN lnResult:=SUBSTR(SQLERRM,1,150);
PRC_EMAIL(''Compile Notification'',lnResult);
END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=hourly; byminute=0; bysecond=0;',
end_date => NULL,
enabled => TRUE,
comments => 'Compile job');
END;
/

5) Execute Job Manually
exec DBMS_SCHEDULER.run_job ('compile');
Posted by at No comments:
Labels: ,

Thursday, December 15, 2011

EMAIL NOTIFICATION changes in init.ora parameters


Auditing changes to init.ora parameters (via pfile or spfile) is an important DBA task. Sometimes, users which have “alter system” privilege can make unauthorized changes to the initialization parameters in the spfile on a production database. Hence, auditing changes to parameters is a critical DBA task. Fortunately, it's quite simple to audit these changes by implementing the audit_sys_operations=true.

Here is a method to track changes to the initialization parameters. In order to track all changes to parameters we can use audit for thealter system statement for any specific user

We should follow below steps to track changes to init.ora parameters:

1. ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
2. SHUTDOWN IMMEDIATE
3. STARTUP
4. CREATE USER TEST IDENTIFIED BY TEST;
5. GRANT DBA TO TEST;
6. AUDIT ALTER SYSTEM BY test;
7. CONN TEST/TEST
8. ALTER SYSTEM SET AUDIT_TRAIL=db SCOPE=SPFILE;

9. Create an alert script to notify the DBA when a parameter has changed.

Let's start by finding the action_name in the dba_audit_trail view for the alter system command:

SQL> select username, timestamp, action_name from dba_audit_trail;

USERNAME TIMESTAMP ACTION_NAME
------------------------------ ------------- ----------------------------
TEST 29-MAY-09 ALTER SYSTEM


STEP 1 - We can track changes made by SYS user by setting audit_sys_operations parameter to TRUE.

SQL> alter system set audit_sys_operations=true scope=spfile;
System altered.

STEP 2 - Next, we bounce the instance to make the change take effect:
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.

Here we see our auditing parameters:
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /home/oracle/oracle/product/10 .2.0/db_1/admin/fkhalid/adump
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string DB

SQL> alter system set audit_trail=db scope=spfile;
System altered.

STEP 3 - Here we go to the adump directory and examine the audit files:
SQL> host
[oracle@localhost bin]$ cd /home/oracle/oracle/product/10.2.0/db_1/admin/kam/adump/

[oracle@localhost adump]$ ls
ora_5449.aud ora_5476.aud ora_5477.aud ora_5548.aud ora_5575.aud ora_5576.aud

[oracle@localhost adump]$ cat ora_5576.aud
Audit file /home/oracle/oracle/product/10.2.0/db_1/admin/kam/adump/ora_5576.aud
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1/
System name: Linux
Node name: localhost.localdomain
Release: 2.6.18-92.el5
Version: #1 SMP Tue Jun 10 18:49:47 EDT 2008
Machine: i686
Instance name: kam
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 5576, image: oracle@localhost.localdomain (TNS V1-V3)
Fri May 29 02:38:30 2009
ACTION : 'alter system set audit_trail=db scope=spfile'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: oracle
CLIENT TERMINAL: pts/2
STATUS: 0

STEP 4 - Now, create a crontab job to seek new entries in the adump directory.
#******************************************************
# list the full-names of all possible adump files . . . .
#******************************************************
rm -f /tmp/audit_list.lst
find $DBA/$ORACLE_SID/adump/*.trc -mtime -1 -print >> /tmp/audit_list.lst
STEP 5 - When found, send the DBA an e-mail:
# If initialization paramneter has changed, send an e-mail
if [ -f /tmp/audit_list.lst]; then
then
# Now, be sure that we don't clog the mailbox.
# the following statement checks to look for existing mail,
# and only sends mail when mailbox is empty . . .
if [ ! -s /var/spool/mail/oramy_sid ]
then
cat /oracle/MY_SID/scripts/oracheck.log | mail oramy_sid
fi
sendmail . . .
fi

Please beware that using the auditing command imposes additional work on the production database.

Posted by at No comments:
Labels:

Thursday, August 25, 2011

Server Disk Space Notification

' Sample code for monitoring windows disk space



' Constants for drive types
Const Unknown = 0
Const Removable = 1
Const Fixed = 2
Const Remote = 3
Const CDROM = 4
Const RAMDisk = 5

' general constants – NEED TO BE MODIFIED FOR YOUR ENVIRONMENT
Const MailServer = "mail.arihantcapital.com"
Const MailServerPort = "25"

Const LOCAL_HARD_DISK = 3

' Send a mail message
Sub SendMail(Sender, Recipient, Subject, Message)
Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = Subject
objMessage.From = Sender
objMessage.To = Recipient
objMessage.TextBody = Message

objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

'Name or IP of Remote SMTP Server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = MailServer

'Server port (typically 25)
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = MailServerPort

objMessage.Configuration.Fields.Update

objMessage.Send
End Sub

' get current computer name (from system environment variables)
Function GetCurrentComputerName
set oWsh = WScript.CreateObject("WScript.Shell")
set oWshSysEnv = oWsh.Environment("PROCESS")
GetCurrentComputerName = oWshSysEnv("COMPUTERNAME")
End Function

'==================================================================
' Begin main code
'==================================================================
str = ""

'Only enumerate physical disks (Not Network Drives)
Const HARD_DISK = 3

'====================================================================
' Server Server1
'===================================================================
strComputer = "192.168.0.7"
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

Set colDisks = objWMIService.ExecQuery _
("Select * from Win32_LogicalDisk Where DriveType = " & HARD_DISK & "")

str = str & "Server: " & strComputer & vbcrlf
For Each objDisk in colDisks
str = str & "Disk: "& objDisk.DeviceID & vbTab
str = str & " Free Disk Space: "& FormatNumber(CLng(objDisk.FreeSpace / 1024 / 1024),0,,,-1) & " MB" & vbcrlf
Next

str = str & vbcrlf


'Send the email
SendMail "backoffice@arihantcapital.com", "kshitij.rakesh@arihantcapital.com", "Server" & strComputerName & ": Drive Space Report", str


Posted by at No comments:
Labels: ,

Low Disk Space Window Notification

Set wshShell = WScript.CreateObject( "WScript.Shell" )
strComputerName = wshShell.ExpandEnvironmentStrings( "%COMPUTERNAME%" )
'WScript.Echo "Computer Name: " & strComputerName
Set objMessage = CreateObject("CDO.Message")


objMessage.Subject = "Disk Space Alert: " &strComputerName
objMessage.From = "kshitij.rakesh@arihantcapital.com"
objMessage.To = "kshitij.rakesh@arihantcapital.com"

Dim objShell, space_value, Result
Set objShell = Wscript.CreateObject("WScript.Shell")

Set DiskSet = GetObject("winmgmts:{impersonationLevel=impersonate}").ExecQuery ("select * from Win32_LogicalDisk where DriveType=3")
For each Disk in DiskSet
If (Disk.Name="D:") Then
Disk.FreeSpace=Disk.FreeSpace/1024
Disk.FreeSpace=Disk.FreeSpace/1024
Disk.FreeSpace=Disk.FreeSpace/1024
Result = Disk.FreeSpace
End If
Next
space_value = Result
Wscript.echo space_value
if space_value < 150 then

objMessage.TextBody = "Server Disk Space Low, " & space_value & " GB"
objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mail.arihantcapital.com"
objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objMessage.Configuration.Fields.Update
objMessage.Send

end if


Posted by at No comments:
Labels: ,

Friday, May 6, 2011

LD Billing Porcess Email Notification

CREATE OR REPLACE TRIGGER email_nsef_bill
AFTER insert on TBLLOCKTABLE
DECLARE
b VARCHAR2(3) := UTL_TCP.CRLF;
l_subject VARCHAR2(50) := 'NSEF Bill Process Done';
l_message VARCHAR2(500);

BEGIN

if (USER in ('RITESHR')) then
l_message :=
'Date/Time: ' || TO_CHAR( SYSDATE, 'mm/dd/yyyy hh24:mi:ss' ) || b;
l_message :=
l_message || 'User: ' || SYS_CONTEXT( 'USERENV', 'CURRENT_USER' ) || b;
l_message :=
l_message || 'OS User: ' || SYS_CONTEXT( 'USERENV', 'OS_USER' ) || b;

l_message :=
l_message || 'Host: ' || SYS_CONTEXT( 'USERENV', 'HOST' ) || b;
l_message :=
l_message || 'Terminal: ' || SYS_CONTEXT( 'USERENV', 'TERMINAL' ) || b;

l_message :=
l_message || 'IP Address: ' || SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) || b;
l_message :=
l_message || 'Database Name: ' || ora_database_name || b;

BEGIN
utl_mail.send
( sender => 'kshitij.rakesh@arihantcapital.com',
recipients => 'kshitij.rakesh@arihantcapital.com',
subject => l_subject,
message => l_message );

EXCEPTION
WHEN others THEN
RAISE;

END;
end if;
END email_nsef_bill;
/
Posted by at No comments:
Labels: ,

Wednesday, May 4, 2011

LD LOCKTABLE Email Notification

Select * From Tbllocktable Where Dcashbankfinancialentry Not Like ' ';
------------


CREATE OR REPLACE TRIGGER email_lock_table
AFTER UPDATE of Dcashbankreceiptentry,Dcashbankfinancialentry,Djournalentry on TBLLOCKTABLE
DECLARE
b VARCHAR2(3) := UTL_TCP.CRLF;
l_subject VARCHAR2(40) := 'ALERT: UNLOCK Data Files FY1112';
l_message VARCHAR2(500);

BEGIN

if (SYS_CONTEXT( 'USERENV', 'CURRENT_USER' ) not in ('LDBO')) then

l_message :=
'Date/Time: ' || TO_CHAR( SYSDATE, 'mm/dd/yyyy hh24:mi:ss' ) || b;
l_message :=
l_message || 'User: ' || SYS_CONTEXT( 'USERENV', 'CURRENT_USER' ) || b;
l_message :=
l_message || 'OS User: ' || SYS_CONTEXT( 'USERENV', 'OS_USER' ) || b;

l_message :=
l_message || 'Host: ' || SYS_CONTEXT( 'USERENV', 'HOST' ) || b;
l_message :=
l_message || 'Terminal: ' || SYS_CONTEXT( 'USERENV', 'TERMINAL' ) || b;

l_message :=
l_message || 'IP Address: ' || SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) || b;
l_message :=
l_message || 'Database Name: ' || ora_database_name || b;

BEGIN
utl_mail.send
( sender => 'kshitij.rakesh@arihantcapital.com',
recipients => 'kshitij.rakesh@arihantcapital.com',
subject => l_subject,
message => l_message );

EXCEPTION
WHEN others THEN
RAISE;

END;
END IF;
END email_lock_table;
/
Posted by at No comments:
Labels: ,

Thursday, March 24, 2011

VBScript Email with authenticated SMTP User

Set wshShell = WScript.CreateObject( "WScript.Shell" )
strComputerName = wshShell.ExpandEnvironmentStrings( "%COMPUTERNAME%" )
'WScript.Echo "Computer Name: " & strComputerName

Set objMessage = CreateObject("CDO.Message")
strComputer = "." ' Name of the computer
objMessage.Subject = "Backoffice Oracle Job Status"
objMessage.From = "kshitij.rakesh@ldserver.com"
objMessage.To = "kshitij.rakesh@ldserver.com"
objMessage.TextBody = "Backoffice Oracle on " & strComputerName & " at " & FormatDateTime(Date,1) & " " & Time

'==This section provides the configuration information for the remote SMTP server.
'==Normally you will only change the server name or IP.
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

'Name or IP of Remote SMTP Server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mail.ldserver.com"

'Server port (typically 25)
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25


'Type of authentication, NONE, Basic (Base64 encoded), NTLM
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1


'Your UserID on the SMTP server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusername") = "ldsupport@ldserver.com"

'Your password on the SMTP server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "Ld@support"

objMessage.Configuration.Fields.Update

'==End remote SMTP server configuration section==

objMessage.Send

Posted by at No comments:
Labels:

Tuesday, March 15, 2011

Email Notification for LDBO login

CREATE OR REPLACE TRIGGER LDBO_logon_notifications
after logon on database
DECLARE
b VARCHAR2(3) := UTL_TCP.CRLF;
l_subject VARCHAR2(40) := 'Alert - LDBO Login';
l_message VARCHAR2(500);

BEGIN
if (USER in ('LDBO') AND SYS_CONTEXT('USERENV','Module') not IN ('vfp9.exe') AND SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) NOT IN ('192.168.0.25','192.168.1.84','192.168.1.199','192.168.1.64','192.168.1.126','192.168.1.127','192.168.1.95','192.168.1.163') ) then

l_message :=
'Date/Time: ' || TO_CHAR( SYSDATE, 'mm/dd/yyyy hh24:mi:ss' ) || b;
l_message :=
l_message || 'OS User: ' || SYS_CONTEXT( 'USERENV', 'OS_USER' ) || b;

l_message :=
l_message || 'Host: ' || SYS_CONTEXT( 'USERENV', 'HOST' ) || b;
l_message :=
l_message || 'Terminal: ' || SYS_CONTEXT( 'USERENV', 'TERMINAL' ) || b;

l_message :=
l_message || 'IP Address: ' || SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) || b;
l_message :=
l_message || 'Database Name: ' || ora_database_name || b;
l_message :=
l_message || 'Application Name: ' || SYS_CONTEXT('USERENV','Module') || b;

BEGIN
utl_mail.send
( sender => 'kshitij.rakesh@arihantcapital.com',
recipients => 'kshitij.rakesh@arihantcapital.com',
subject => l_subject,
message => l_message );

EXCEPTION
WHEN others THEN
RAISE;

END;
END IF;
END LDBO_logon_notifications;
/

Posted by at No comments:
Labels:

Wednesday, September 1, 2010

Oracle User Login / Error Email Notification

connect sys as sysdba user and run two scripts for install and configure utl_mail package

SQL> conn sys@orcl as sysdba
Enter password: ******
Connected.
SQL> @c:\oracle\product\10.2.0\db_1\rdbms\admin\utlmail.sql

Package created.


Synonym created.

SQL> @c:\oracle\product\10.2.0\db_1\rdbms\admin\prvtmail.plb;

Package body created.

No errors.

Set SMTP_OUT_SERVER parameter for smtp_exchange_server. This parameter is not modifiable means we have to bounce our database to set this parameter

SQL> alter system set smtp_out_server = 'mail.uniconindia.in' scope=spfile;



SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup

Grant EXECUTE privs to user which use utl_mail package.

SQL> grant execute on utl_mail to ldbo;

Grant succeeded.


---------------------------------Database Shutdown Notification-------------------


CREATE OR REPLACE TRIGGER LDBO_shut_notifications
before shutdown on database
DECLARE
b VARCHAR2(3) := UTL_TCP.CRLF;
l_subject VARCHAR2(40) := 'Problem Alert - LD Database is Down';
l_message VARCHAR2(500);

BEGIN
l_message :=
'Date/Time: ' || TO_CHAR( SYSDATE, 'mm/dd/yyyy hh24:mi:ssss' ) || b;
l_message :=
l_message || 'OS User: ' || SYS_CONTEXT( 'USERENV', 'OS_USER' ) || b;

l_message :=
l_message || 'Host: ' || SYS_CONTEXT( 'USERENV', 'HOST' ) || b;
l_message :=
l_message || 'Terminal: ' || SYS_CONTEXT( 'USERENV', 'TERMINAL' ) || b;

l_message :=
l_message || 'IP Address: ' || SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) || b;
l_message :=
l_message || 'Protocol: ' || SYS_CONTEXT( 'USERENV', 'NETWORK_PROTOCOL' ) || b;

l_message :=
l_message || 'Database Instance: ' || ora_instance_num || b;
l_message :=
l_message || 'Database Name: ' || ora_database_name || b;

BEGIN
utl_mail.send
( sender => 'Dbanotification@uniconindia.in',
recipients => 'dbamonitoring@uniconindia.in',
subject => l_subject,
message => l_message );

EXCEPTION
WHEN others THEN
RAISE;

END;
END LDBO_shut_notifications;
/

---------------------------------Database Startup Notification-------------------

CREATE OR REPLACE TRIGGER LDBO_start_notifications
after startup on database
DECLARE
b VARCHAR2(3) := UTL_TCP.CRLF;
l_subject VARCHAR2(40) := 'Recovery Alert - LD Database is Up';
l_message VARCHAR2(500);

BEGIN
l_message :=
'Date/Time: ' || TO_CHAR( SYSDATE, 'mm/dd/yyyy hh24:mi:ssss' ) || b;
l_message :=
l_message || 'OS User: ' || SYS_CONTEXT( 'USERENV', 'OS_USER' ) || b;

l_message :=
l_message || 'Host: ' || SYS_CONTEXT( 'USERENV', 'HOST' ) || b;
l_message :=
l_message || 'Terminal: ' || SYS_CONTEXT( 'USERENV', 'TERMINAL' ) || b;

l_message :=
l_message || 'IP Address: ' || SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) || b;
l_message :=
l_message || 'Protocol: ' || SYS_CONTEXT( 'USERENV', 'NETWORK_PROTOCOL' ) || b;

l_message :=
l_message || 'Database Instance: ' || ora_instance_num || b;
l_message :=
l_message || 'Database Name: ' || ora_database_name || b;

BEGIN
utl_mail.send
( sender => 'Dbanotification@uniconindia.in',
recipients => 'dbamonitoring@uniconindia.in',
subject => l_subject,
message => l_message );

EXCEPTION
WHEN others THEN
RAISE;

END;
END LDBO_start_notifications;
/




------------------------------------SYS User Logon-------------


CREATE OR REPLACE TRIGGER SYS_logon_notifications
after logon on database
DECLARE
b VARCHAR2(3) := UTL_TCP.CRLF;
l_subject VARCHAR2(40) := 'Alert - SYS Login';
l_message VARCHAR2(500);

BEGIN
if (USER in ('SYS')) then
l_message :=
'Date/Time: ' || TO_CHAR( SYSDATE, 'mm/dd/yyyy hh24:mi:ssss' ) || b;
l_message :=
l_message || 'OS User: ' || SYS_CONTEXT( 'USERENV', 'OS_USER' ) || b;

l_message :=
l_message || 'Host: ' || SYS_CONTEXT( 'USERENV', 'HOST' ) || b;
l_message :=
l_message || 'Terminal: ' || SYS_CONTEXT( 'USERENV', 'TERMINAL' ) || b;

l_message :=
l_message || 'IP Address: ' || SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) || b;
l_message :=
l_message || 'Protocol: ' || SYS_CONTEXT( 'USERENV', 'NETWORK_PROTOCOL' ) || b;

l_message :=
l_message || 'Database Instance: ' || ora_instance_num || b;
l_message :=
l_message || 'Database Name: ' || ora_database_name || b;

BEGIN
utl_mail.send
( sender => 'Dbanotification@uniconindia.in',
recipients => 'kgupta2@uniconindia.in',
subject => l_subject,
message => l_message );

EXCEPTION
WHEN others THEN
RAISE;

END;
END IF;
END SYS_logon_notifications;
/


------------------------------------LDBO- DBA User Logon-------------

CREATE OR REPLACE TRIGGER LDBO_logon_notifications
after logon on database
DECLARE
b VARCHAR2(3) := UTL_TCP.CRLF;
l_subject VARCHAR2(40) := 'Alert - LDBO Login';
l_message VARCHAR2(500);

BEGIN
if (USER in ('LDBO')) then

l_message :=
'Date/Time: ' || TO_CHAR( SYSDATE, 'mm/dd/yyyy hh24:mi:ss' ) || b;
l_message :=
l_message || 'OS User: ' || SYS_CONTEXT( 'USERENV', 'OS_USER' ) || b;

l_message :=
l_message || 'Host: ' || SYS_CONTEXT( 'USERENV', 'HOST' ) || b;
l_message :=
l_message || 'Terminal: ' || SYS_CONTEXT( 'USERENV', 'TERMINAL' ) || b;

l_message :=
l_message || 'IP Address: ' || SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) || b;
l_message :=
l_message || 'Protocol: ' || SYS_CONTEXT( 'USERENV', 'NETWORK_PROTOCOL' ) || b;

l_message :=
l_message || 'Database Instance: ' || ora_instance_num || b;
l_message :=
l_message || 'Database Name: ' || ora_database_name || b;

BEGIN
utl_mail.send
( sender => 'Dbanotification@uniconindia.in',
recipients => 'kgupta2@uniconindia.in',
subject => l_subject,
message => l_message );

EXCEPTION
WHEN others THEN
RAISE;

END;
END IF;
END LDBO_logon_notifications;
/

------------------------------------Failed Logon-------------

CREATE OR REPLACE TRIGGER failed_logon_notifications
AFTER SERVERERROR ON DATABASE
DECLARE
b VARCHAR2(3) := UTL_TCP.CRLF;
l_subject VARCHAR2(40) := 'Alert - Failed Login';
l_message VARCHAR2(500);

BEGIN
IF ora_is_servererror( 28000 ) THEN
l_message :=
'Date/Time: ' || TO_CHAR( SYSDATE, 'mm/dd/yyyy hh24:mi:ssss' ) || b;
l_message :=
l_message || 'OS User: ' || SYS_CONTEXT( 'USERENV', 'OS_USER' ) || b;

l_message :=
l_message || 'Host: ' || SYS_CONTEXT( 'USERENV', 'HOST' ) || b;
l_message :=
l_message || 'Terminal: ' || SYS_CONTEXT( 'USERENV', 'TERMINAL' ) || b;

l_message :=
l_message || 'IP Address: ' || SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) || b;
l_message :=
l_message || 'Protocol: ' || SYS_CONTEXT( 'USERENV', 'NETWORK_PROTOCOL' ) || b;

l_message :=
l_message || 'Database Instance: ' || ora_instance_num || b;
l_message :=
l_message || 'Database Name: ' || ora_database_name || b;

BEGIN
utl_mail.send
( sender => 'Dbanotification@uniconindia.in',
recipients => 'kgupta2@uniconindia.in',
subject => l_subject,
message => l_message );

EXCEPTION
WHEN others THEN
RAISE;

END;
END IF;
END failed_logon_notifications;




-----------------------------------Listener Error-----




CREATE OR REPLACE TRIGGER failed_listener_notifications
AFTER SERVERERROR ON DATABASE
DECLARE
b VARCHAR2(3) := UTL_TCP.CRLF;
l_subject VARCHAR2(40) := 'TNS:listener could not hand off client connection';
l_message VARCHAR2(500);

BEGIN
IF ora_is_servererror( 12518 ) THEN
l_message :=
'Date/Time: ' || TO_CHAR( SYSDATE, 'mm/dd/yyyy hh24:mi:ssss' ) || b;
l_message :=
l_message || 'OS User: ' || SYS_CONTEXT( 'USERENV', 'OS_USER' ) || b;

l_message :=
l_message || 'Host: ' || SYS_CONTEXT( 'USERENV', 'HOST' ) || b;
l_message :=
l_message || 'Terminal: ' || SYS_CONTEXT( 'USERENV', 'TERMINAL' ) || b;

l_message :=
l_message || 'IP Address: ' || SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) || b;
l_message :=
l_message || 'Protocol: ' || SYS_CONTEXT( 'USERENV', 'NETWORK_PROTOCOL' ) || b;

l_message :=
l_message || 'Database Instance: ' || ora_instance_num || b;
l_message :=
l_message || 'Database Name: ' || ora_database_name || b;

BEGIN
utl_mail.send
( sender => 'Dbanotification@uniconindia.in',
recipients => 'kgupta2@uniconindia.in',
subject => l_subject,
message => l_message );

EXCEPTION
WHEN others THEN
RAISE;

END;
END IF;
END failed_logon_notifications;





-----------------------------Trigger Insert--------------------


CREATE OR REPLACE TRIGGER insert_ecn
before insert on ldbo.TBLDIGITALSIGNEDREPORTS
DECLARE
b VARCHAR2(3) := UTL_TCP.CRLF;
l_subject VARCHAR2(40) := 'ECN Process Done Successfully';
l_message VARCHAR2(500);

BEGIN
if (USER in ('ECNUSER')) then

l_message :=
'Date/Time: ' || TO_CHAR( SYSDATE, 'mm/dd/yyyy hh24:mi:ss' ) || b;
l_message :=
l_message || 'OS User: ' || SYS_CONTEXT( 'USERENV', 'OS_USER' ) || b;

l_message :=
l_message || 'Host: ' || SYS_CONTEXT( 'USERENV', 'HOST' ) || b;
l_message :=
l_message || 'Terminal: ' || SYS_CONTEXT( 'USERENV', 'TERMINAL' ) || b;

l_message :=
l_message || 'IP Address: ' || SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) || b;
l_message :=
l_message || 'Protocol: ' || SYS_CONTEXT( 'USERENV', 'NETWORK_PROTOCOL' ) || b;

l_message :=
l_message || 'Database Instance: ' || ora_instance_num || b;
l_message :=
l_message || 'Database Name: ' || ora_database_name || b;

BEGIN
utl_mail.send
( sender => 'Dbanotification@uniconindia.in',
recipients => 'kgupta2@uniconindia.in',
subject => l_subject,
message => l_message );

EXCEPTION
WHEN others THEN
RAISE;

END;
END IF;
END insert_ecn;
/



---------------Trigger if login at unofficial time---------



CREATE OR REPLACE PROCEDURE secure_dml(dateval IN DATE)IS
BEGIN
IF TO_CHAR (dateval, 'HH24:MI') NOT BETWEEN '08:00' AND '20:00'
OR TO_CHAR (dateval, 'DY') IN ('SAT', 'SUN') THEN
RAISE_APPLICATION_ERROR (-20205, 'Changes only allowed during office hours');
END IF;
END secure_dml;
/

CREATE OR REPLACE TRIGGER secure_data
BEFORE INSERT OR UPDATE OR DELETE
ON orders
FOR EACH ROW
BEGIN
secure_dml(:NEW.datecol);
END;
/

INSERT INTO orders VALUES ('ABC', 999, SYSDATE-1);
INSERT INTO orders VALUES ('ABC', 999, SYSDATE-4/24);
INSERT INTO orders VALUES ('ABC', 999, SYSDATE+1);

------------Trigger To Disallow Entry Of Future Dates ------



CREATE OR REPLACE TRIGGER t_date
BEFORE INSERT
ON orders
FOR EACH ROW

DECLARE
bad_date EXCEPTION;
BEGIN
IF :new.datecol > SYSDATE THEN
RAISE_APPLICATION_ERROR(-20005,'Future Dates Not Allowed');
END IF;
END;
/

INSERT INTO orders VALUES ('ABC', 999, SYSDATE-1);
INSERT INTO orders VALUES ('ABC', 999, SYSDATE);
INSERT INTO orders VALUES ('ABC', 999, SYSDATE+1);


--------------------Holiday login-------

CREATE TABLE Company_holidays (Day DATE);


CREATE OR REPLACE TRIGGER Emp_permit_changes
BEFORE INSERT OR DELETE OR UPDATE ON Emp99
DECLARE
Dummy INTEGER;
Not_on_weekends EXCEPTION;
Not_on_holidays EXCEPTION;
Non_working_hours EXCEPTION;
BEGIN
/* check for weekends: */
IF (TO_CHAR(Sysdate, 'DY') = 'SAT' OR
TO_CHAR(Sysdate, 'DY') = 'SUN') THEN
RAISE Not_on_weekends;
END IF;
/* check for company holidays:*/
SELECT COUNT(*) INTO Dummy FROM Company_holidays
WHERE TRUNC(Day) = TRUNC(Sysdate);
/* TRUNC gets rid of time parts of dates: */
IF dummy > 0 THEN
RAISE Not_on_holidays;
END IF;
/* Check for work hours (8am to 6pm): */
IF (TO_CHAR(Sysdate, 'HH24') < 8 OR
TO_CHAR(Sysdate, 'HH24') > 18) THEN
RAISE Non_working_hours;
END IF;
EXCEPTION
WHEN Not_on_weekends THEN
Raise_application_error(-20324,'May not change '
||'employee table during the weekend');
WHEN Not_on_holidays THEN
Raise_application_error(-20325,'May not change '
||'employee table during a holiday');
WHEN Non_working_hours THEN
Raise_application_error(-20326,'May not change '
||'Emp_tab table during non-working hours');
END;




-----------------
Posted by at No comments:
Labels:

Thursday, May 20, 2010

Oracle Export Backup Job Email Notification

---------------------------------------------Export.bat---------------------------


@ECHO off
SETLOCAL
IF [%1]==[] goto s_start

ECHO GETDATE.cmd
ECHO Returns the date independent of regional settings
ECHO Creates the environment variables %v_year% %v_month% %v_day%
ECHO.
ECHO SYNTAX
ECHO GETDATE
ECHO.
ECHO.
GOTO :eof

:s_start

FOR /f "tokens=2-4 skip=1 delims=(-)" %%G IN ('echo.^|date') DO (
FOR /f "tokens=2 delims= " %%A IN ('date /t') DO (
SET v_first=%%G
SET v_second=%%H
SET v_third=%%I
SET v_all=%%A
)
)

SET %v_first%=%v_all:~0,2%
SET %v_second%=%v_all:~3,2%
SET %v_third%=%v_all:~8,2%

rem ECHO Today is Year: [%yy%] Month: [%mm%] Day: [%dd%]

ENDLOCAL & SET v_year=%yy%& SET v_month=%mm%& SET v_day=%dd%
ren C:\00\1.txt USED66665_%v_month%%v_day%%v_year%.txt

exp snsexport/snsexp@sns1011srv owner=(ldbo) file=G:\EXPORT\sns1011ora_%v_day%%v_month%%v_year%.DMP LOG=G:\EXPORT\sns1011ora_%v_day%%v_month%%v_year%.LOG

if exist G:\EXPORT\sns1011ora_%v_day%%v_month%%v_year%.DMP GOTO COMPLETED

if not exist G:\EXPORT\sns1011ora_%v_day%%v_month%%v_year%.DMP GOTO FAILED

:COMPLETED
Cscript.exe C:\Email_export.vbs
exit
:FAILED
Cscript.exe C:\Emailfail_export.vbs

---------------------------------------------Email_export.vbs---------------------------


Set wshShell = WScript.CreateObject( "WScript.Shell" )
strComputerName = wshShell.ExpandEnvironmentStrings( "%COMPUTERNAME%" )
'WScript.Echo "Computer Name: " & strComputerName
Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "LD Oracle Export Backup Job Completed"
objMessage.From = "dbanotification@uniconindia.in"
objMessage.To = "dbamonitoring@uniconindia.in"
objMessage.TextBody = "LD Oracle Export Backup Job Completed Successfully on " & strComputerName & " at " & FormatDateTime(Date,1) & " " & Time

'==This section provides the configuration information for the remote SMTP server.
'==Normally you will only change the server name or IP.
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

'Name or IP of Remote SMTP Server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "10.100.0.94"

'Server port (typically 25)
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

objMessage.Configuration.Fields.Update

'==End remote SMTP server configuration section==

objMessage.Send


---------------------------------------------Emailfail_export.vbs---------------------------


Set objMessage = CreateObject("CDO.Message")
strComputer = "." ' Name of the computer
objMessage.Subject = "LD Oracle Job Failed ORANOP02P"
objMessage.From = "dbanotification@uniconindia.in"
objMessage.To = "kgupta2@uniconindia.in"
objMessage.TextBody = "LD Oracle Job Failed on ORANOP02P !" & strComputer

'==This section provides the configuration information for the remote SMTP server.
'==Normally you will only change the server name or IP.
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

'Name or IP of Remote SMTP Server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "10.100.0.94"

'Server port (typically 25)
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

objMessage.Configuration.Fields.Update

'==End remote SMTP server configuration section==

objMessage.Send

---------------------------------
Posted by at No comments:
Labels: ,

Oracler RMAN Job Email Notification

------------------------------------------------RMAN.bat--------------------


@ECHO off
SETLOCAL
IF [%1]==[] goto s_start

ECHO GETDATE.cmd
ECHO Returns the date independent of regional settings
ECHO Creates the environment variables %v_year% %v_month% %v_day%
ECHO.
ECHO SYNTAX
ECHO GETDATE
ECHO.
ECHO.
GOTO :eof

:s_start

FOR /f "tokens=2-4 skip=1 delims=(-)" %%G IN ('echo.^|date') DO (
FOR /f "tokens=2 delims= " %%A IN ('date /t') DO (
SET v_first=%%G
SET v_second=%%H
SET v_third=%%I
SET v_all=%%A
)
)

SET %v_first%=%v_all:~0,2%
SET %v_second%=%v_all:~3,2%
SET %v_third%=%v_all:~6,4%

rem ECHO Today is Year: [%yy%] Month: [%mm%] Day: [%dd%]

ENDLOCAL & SET v_year=%yy%& SET v_month=%mm%& SET v_day=%dd%
ren C:\00\1.txt USED66665_%v_month%%v_day%%v_year%.txt


set oracle_sid=sns6



RMAN TARGET sys/oracle@sns1011srv cmdfile='c:\rman.sql'




if exist D:\archive0910\sns1011\RMANBACKUP_DB_SNS1011_%v_day%%v_month%%v_year% GOTO COMPLETED

if not exist D:\archive0910\sns1011\RMANBACKUP_DB_SNS1011_%v_day%%v_month%%v_year% GOTO FAILED

:COMPLETED
Cscript.exe C:\Email_rman.vbs
exit
:FAILED
Cscript.exe C:\Emailfail_rman.vbs


-----------------------------rman.sql----------------------------
show all;
BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT 'D:\archive0910\sns1011\rmanbackup_db_%d_%D%M%Y';
exit;


---------------------------------Email_rman.vbs-----------------------------------

Set wshShell = WScript.CreateObject( "WScript.Shell" )
strComputerName = wshShell.ExpandEnvironmentStrings( "%COMPUTERNAME%" )
'WScript.Echo "Computer Name: " & strComputerName
Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "LD Oracle RMAN Job Completed Successfully"
objMessage.From = "rjain@uniconindia.in"
objMessage.To = "kgupta2@uniconindia.in"
objMessage.TextBody = "LD Oracle RMAN Backup Job Completed Successfully on " & strComputerName & " at " & FormatDateTime(Date,1) & " " & Time

'==This section provides the configuration information for the remote SMTP server.
'==Normally you will only change the server name or IP.
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

'Name or IP of Remote SMTP Server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "10.100.0.94"

'Server port (typically 25)
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

objMessage.Configuration.Fields.Update

'==End remote SMTP server configuration section==

objMessage.Send


----------------------------------Emaildail_rman.vbs--------------------------------

Set objMessage = CreateObject("CDO.Message")
strComputer = "." ' Name of the computer
objMessage.Subject = "LD Oracle RMAN Job Failed ORANOP02P"
objMessage.From = "dbanotification@uniconindia.in"
objMessage.To = "kgupta2@uniconindia.in"
objMessage.TextBody = "LD Oracle RMAN Job Failed on ORANOP02P !" & strComputer

'==This section provides the configuration information for the remote SMTP server.
'==Normally you will only change the server name or IP.
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

'Name or IP of Remote SMTP Server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "10.100.0.94"

'Server port (typically 25)
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

objMessage.Configuration.Fields.Update

'==End remote SMTP server configuration section==

objMessage.Send

------------------------------------------
Posted by at No comments:
Labels: ,
Subscribe to: Posts (Atom)

Followers