|
|
Home | Switchboard | Unix Administration | Red Hat | TCP/IP Networks | Neoliberalism | Toxic Managers |
| (slightly skeptical) Educational society promoting "Back to basics" movement against IT overcomplexity and bastardization of classic Unix | |||||||
|
|
|
|
subject and body of the email notification can be customized using a number of
variables that are
identified using the "%variable-name%" format. Fortunately, the default values of the subject and body parameters
provide plenty of information, so customizing these values is not really necessary.events parameter determines which job
events fire a notification,
while the filer_condition parameter can reduce the notifications by filtering out those events that do not meet specific
criteria, based on the
SCHEDULER$_EVENT_INFO Object Type.sender parameter is not specified when defining the notification. Both these
scheduler attributes are set below.
CONN / AS SYSDBA
BEGIN
DBMS_SCHEDULER.set_scheduler_attribute('email_server', 'smtp.mycompany.com:25');
DBMS_SCHEDULER.set_scheduler_attribute('email_sender', '[email protected]');
END;
/
Next we must define a job to associate the notification to. The following job is an empty stub that fires once a minute for 1 hour.
CONN test/test
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'test_notification_job',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN NULL; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=minutely; bysecond=0',
end_date => SYSTIMESTAMP + 1/24,
enabled => TRUE);
END;
/
With the job in place we can associate an email notification with it. The following notification fires when the job starts or succeeds.
It uses the default subject and body.The following notification only fires if a job fails with a "600" error code.BEGIN DBMS_SCHEDULER.add_job_email_notification ( job_name => 'test_notification_job', recipients => '[email protected]', events => 'job_started, job_succeeded'); END; /
We can see the notification details using theBEGIN DBMS_SCHEDULER.add_job_email_notification ( job_name => 'test_notification_job', recipients => '[email protected]', events => 'job_failed', filter_condition => ':event.error_code=600'); END; /
%_SCHEDULER_NOTIFICATIONS views.We can use the same view to see the subject and body of the email that will be sent for the specific notification.SET LINESIZE 100 COLUMN job_name FORMAT A25 COLUMN recipient FORMAT A20 COLUMN filter_condition FORMAT A30 SELECT job_name, recipient, event, filter_condition FROM user_scheduler_notifications; JOB_NAME RECIPIENT EVENT FILTER_CONDITION ------------------------- -------------------- ------------------- ------------------------------ TEST_NOTIFICATION_JOB [email protected] JOB_STARTED TEST_NOTIFICATION_JOB [email protected] JOB_SUCCEEDED TEST_NOTIFICATION_JOB [email protected] JOB_FAILED :event.error_code=600 SQL>
COLUMN subject FORMAT A30
COLUMN body FORMAT A45
SELECT subject, body
FROM user_scheduler_notifications
WHERE event = 'JOB_STARTED';
SUBJECT BODY
------------------------------ ---------------------------------------------
Oracle Scheduler Job Notificat Job: %job_owner%.%job_name%.%job_subname%
ion - %job_owner%.%job_name%.% Event: %event_type%
job_subname% %event_type% Date: %event_timestamp%
Log id: %log_id%
Job class: %job_class_name%
Run count: %run_count%
Failure count: %failure_count%
Retry count: %retry_count%
Error code: %error_code%
Error message:
%error_message%
SQL>
Email notifications are removed using the
REMOVE_JOB_EMAIL_NOTIFICATION
procedure. In addition to the job_name parameter, it accepts recipients and events parameters.
In both cases, if these are set to NULL (the default values), it is assumed notifications for all recipients/events for the specific
job should be removed. The examples below show the removal of a specific event notification and all notifications for a specified job.BEGIN DBMS_SCHEDULER.remove_job_email_notification ( job_name => 'test_notification_job', recipients => '[email protected]', events => 'job_succeeded'); END; / SELECT job_name, recipient, event, filter_condition FROM user_scheduler_notifications; JOB_NAME RECIPIENT EVENT FILTER_CONDITION ------------------------- -------------------- ------------------- ------------------------------ TEST_NOTIFICATION_JOB [email protected] JOB_STARTED TEST_NOTIFICATION_JOB [email protected] JOB_FAILED :event.error_code=600 SQL> BEGIN DBMS_SCHEDULER.remove_job_email_notification ( job_name => 'test_notification_job'); END; / SELECT job_name, recipient, event, filter_condition FROM user_scheduler_notifications; no rows selected SQL>
CONN / AS SYSDBA
BEGIN
DBMS_SCHEDULER.set_attribute(
'file_watcher_schedule',
'repeat_interval',
'freq=minutely; interval=1');
END;
/
I've set the interval to 1 minute for these tests, but I would probably not set it that low in a production system.
BEGIN
DBMS_SCHEDULER.create_credential(
credential_name => 'local_credential',
username => 'oracle',
password => 'oracle');
END;
/
Next we create the file watcher itself. Using a "?" in the directory_path parameter signifies the ORACLE_HOME. The
file_name parameter can reference a specific file name or a wildcard. Setting the destination parameter to
NULL indicates the local server. For remote servers set it to a valid external destination, as shown by the ALL_SCHEDULER_EXTERNAL_DESTS
view.
BEGIN
DBMS_SCHEDULER.create_file_watcher(
file_watcher_name => 'test_file_watcher',
directory_path => '/tmp/test',
file_name => '*.txt',
credential_name => 'local_credential',
destination => NULL,
enabled => FALSE);
END;
/
Next we create a scheduler program to access the event raised by the file watcher. The program must reference the event_message
to retrieve information about the file, such as its name.
BEGIN
DBMS_SCHEDULER.create_program(
program_name => 'file_watcher_test_prog',
program_type => 'stored_procedure',
program_action => 'file_watcher_test_proc',
number_of_arguments => 1,
enabled => FALSE);
END;
/
BEGIN
DBMS_SCHEDULER.define_metadata_argument(
program_name => 'file_watcher_test_prog',
metadata_attribute => 'event_message',
argument_position => 1);
END;
/
Next we define the stored procedure that we referenced in the program definition. It must accept and argument of the
SCHEDULER_FILEWATCHER_RESULT
type. The procedure defined below inserts the file name and size into a table.
CREATE TABLE file_watcher_output (
message VARCHAR2(4000)
);
CREATE OR REPLACE PROCEDURE file_watcher_test_proc (p_sfwr SYS.SCHEDULER_FILEWATCHER_RESULT) AS
l_message file_watcher_output.message%TYPE;
BEGIN
l_message := p_sfwr.directory_path || '/' || p_sfwr.actual_file_name || ' (' || p_sfwr.file_size || ')';
INSERT INTO file_watcher_output (message)
VALUES (l_message);
COMMIT;
END;
/
Next we create a job that references the objects we've just created. The queue_spec parameter is set to the name of the
file watcher, while the program_name parameter is set to the name of the program object we defined, not the procedure name.
BEGIN
DBMS_SCHEDULER.create_job(
job_name => 'file_watcher_test_job',
program_name => 'file_watcher_test_prog',
event_condition => NULL,
queue_spec => 'test_file_watcher',
auto_drop => FALSE,
enabled => FALSE);
END;
/
By default, the arrival of new files will be ignored if the job is already running. If you need the job to fire for each new arrival,
regardless of whether the job is already running or not, set the PARALLEL_INSTANCES attribute for the job to true. The
job will then be run as a lightweight job.
BEGIN
DBMS_SCHEDULER.set_attribute('file_watcher_test_job','parallel_instances',TRUE);
END;
/
With all the pieces in place, we can now enable all the objects we created.
EXEC DBMS_SCHEDULER.enable('test_file_watcher');
EXEC DBMS_SCHEDULER.enable('file_watcher_test_prog');
EXEC DBMS_SCHEDULER.enable('file_watcher_test_job');
Next create some files in the directory monitored by the file watcher.Querying the output table allows us to see that the job was triggered as expected.$ echo "This is a test" > /tmp/test/test_file_1.txt $ echo "This is a test too" > /tmp/test/test_file_2.txt $ echo "Yes another test" > /tmp/test/test_file_3.txt
Information about file watchers is available from theSELECT * FROM file_watcher_output; MESSAGE -------------------------------------------------------------------------------- /tmp/test/test_file_1.txt (15) /tmp/test/test_file_2.txt (19) /tmp/test/test_file_3.txt (17) SQL>
*_SCHEDULER_FILE_WATCHERS views.General management of file watchers is similar to other scheduler objects. The majority of tasks are accomplished using theSET LINESIZE 100 COLUMN file_watcher_name FORMAT A20 COLUMN destination FORMAT A15 COLUMN directory_path FORMAT A15 COLUMN file_name FORMAT A10 COLUMN credential_name FORMAT A20 SELECT file_watcher_name, destination, directory_path, file_name, credential_name FROM user_scheduler_file_watchers; FILE_WATCHER_NAME DESTINATION DIRECTORY_PATH FILE_NAME CREDENTIAL_NAME -------------------- --------------- --------------- ---------- -------------------- TEST_FILE_WATCHER /tmp/test *.txt LOCAL_CREDENTIAL SQL>
SET_ATTRIBUTE,
ENABLE and DISABLE procedures. File watchers are dropped using the DROP_FILE_WATCHER procedure,
which will only work if there are no references to the file watcher, unless the force parameter is set.It is now possible to define one or more database destinations for the external destination defined for the remote agent.COLUMN destination_name FORMAT A20 COLUMN hostname FORMAT A20 COLUMN ip_address FORMAT A20 SELECT destination_name, hostname, port, ip_address FROM all_scheduler_external_dests; DESTINATION_NAME HOSTNAME PORT IP_ADDRESS -------------------- -------------------- ---------- -------------------- RAC1 rac1.localdomain 1500 192.168.2.101 SQL>
BEGIN
DBMS_SCHEDULER.create_credential(
credential_name => 'test_credential',
username => 'test',
password => 'test');
END;
/
COLUMN credential_name FORMAT A25
COLUMN username FORMAT A20
SELECT credential_name,
username
FROM user_scheduler_credentials;
CREDENTIAL_NAME USERNAME
------------------------- --------------------
TEST_CREDENTIAL test
SQL>
Next we define the database destination. The agent parameter should reference the destination name associated with the
remote agent. If the tns_name parameter is NULL, the default instance on the remote host is assumed. This default instance
is defined by the ORACLE_SID and ORACLE_HOME parameters in the "schagent.conf" file on the remote server.
If a tns_name is specified, it can either be a complete Oracle Net connect descriptor, or a regular alias, but it must
be resolvable on the database running the scheduler, not just the remote host.
BEGIN
DBMS_SCHEDULER.create_database_destination (
destination_name => 'rac1_rac1_dest',
agent => 'rac1',
tns_name => 'RAC1',
comments => 'Instance named RAC1 on host rac1.localdomain');
END;
/
SELECT destination_name, destination_type
FROM user_scheduler_dests;
DESTINATION_NAME DESTINAT
-------------------- --------
RAC1_RAC1_DEST DATABASE
SQL>
We are now able to create the remote database job itself. Notice the credential_name and destination_name
parameters are set. The destination_name here refers to the database destination we just defined, not the external destination
associated with the agent.We can display the run details for the remote database job, just like any other job.BEGIN DBMS_SCHEDULER.create_job ( job_name => 'remote_db_job', job_type => 'stored_procedure', job_action => 'test_proc', start_date => SYSTIMESTAMP, repeat_interval => 'freq=minutely; bysecond=0', end_date => SYSTIMESTAMP + 1/24, credential_name => 'test_credential', destination_name => 'rac1_rac1_dest', enabled => TRUE); END; /
SET LINESIZE 120
COLUMN job_name FORMAT A20
COLUMN destination FORMAT A20
COLUMN actual_start_date FORMAT A20
COLUMN run_duration FORMAT A20
COLUMN status FORMAT A10
SELECT job_name,
destination,
TO_CHAR(actual_start_date, 'DD-MON-YYYY HH24:MI:SS') AS actual_start_date,
run_duration,
status,
error#
FROM user_scheduler_job_run_details
WHERE job_name = 'REMOTE_DB_JOB'
ORDER BY actual_start_date;
JOB_NAME DESTINATION ACTUAL_START_DATE RUN_DURATION STATUS ERROR#
-------------------- -------------------- -------------------- -------------------- ---------- ----------
REMOTE_DB_JOB RAC1_RAC1_DEST 01-OCT-2009 11:34:35 +000 00:00:21 SUCCEEDED 0
REMOTE_DB_JOB RAC1_RAC1_DEST 01-OCT-2009 11:35:35 +000 00:00:06 SUCCEEDED 0
REMOTE_DB_JOB RAC1_RAC1_DEST 01-OCT-2009 11:36:34 +000 00:00:02 SUCCEEDED 0
REMOTE_DB_JOB RAC1_RAC1_DEST 01-OCT-2009 11:37:34 +000 00:00:02 SUCCEEDED 0
REMOTE_DB_JOB RAC1_RAC1_DEST 01-OCT-2009 11:38:34 +000 00:00:02 SUCCEEDED 0
REMOTE_DB_JOB RAC1_RAC1_DEST 01-OCT-2009 11:39:34 +000 00:00:02 SUCCEEDED 0
REMOTE_DB_JOB RAC1_RAC1_DEST 01-OCT-2009 11:40:34 +000 00:00:02 SUCCEEDED 0
SQL>
CREATE_GROUP procedure. This procedure is also used to manage window groups, but I will ignore
that in this discussion.group_type parameter determines the type of group created (DB_DEST, EXTERNAL_DEST). The member parameter
contains a comma separated list of destinations, which must already exist. The syntax for a destinations is as follows.The keyword "LOCAL" can be used to indicate the job should be run on the local machine also. In the case of remote external jobs the LOCAL keyword can be prefixed with a credential, but for database jobs it can not and always runs using the credentials of the owner of the job.[[schema.]credential@][schema.]destination
BEGIN
DBMS_SCHEDULER.create_group(
group_name => 'test_db_group',
group_type => 'DB_DEST',
member => 'LOCAL');
END;
/
The ADD_GROUP_MEMBER and REMOVE_GROUP_MEMBER procedures can be used to maintain the member list of the group.
The following example adds the destination created in the previous section to the destination group.
BEGIN
DBMS_SCHEDULER.add_group_member(
group_name => 'test_db_group',
member => 'test_credential@rac1_rac1_dest');
END;
/
The *_SCHEDULER_GROUPS and *_SCHEDULER_GROUP_MEMBERS views display information about the groups.With the destination group in place we can now create a job using it.COLUMN group_name FORMAT A20 SELECT group_name, group_type, enabled, number_of_members FROM user_scheduler_groups; GROUP_NAME GROUP_TYPE ENABL NUMBER_OF_MEMBERS -------------------- ------------- ----- ----------------- TEST_DB_GROUP DB_DEST TRUE 2 SQL> COLUMN member_name FORMAT A50 SELECT group_name, member_name FROM user_scheduler_group_members; GROUP_NAME MEMBER_NAME -------------------- -------------------------------------------------- TEST_DB_GROUP "TEST"."TEST_CREDENTIAL"@"TEST"."RAC1_RAC1_DEST" TEST_DB_GROUP LOCAL SQL>
The inclusion of theBEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name => 'multi_dest_job', job_type => 'stored_procedure', job_action => 'test_proc', start_date => SYSTIMESTAMP, repeat_interval => 'freq=minutely; bysecond=0', end_date => SYSTIMESTAMP + 1/24, credential_name => 'test_credential', destination_name => 'test_db_group', enabled => TRUE); END; /
credential_name parameter is unnecessary here, but it specifies a default credential to use if any
of the destinations in the group were defined without an explicit credential.
SET LINESIZE 120
COLUMN job_name FORMAT A20
COLUMN destination FORMAT A20
COLUMN actual_start_date FORMAT A20
COLUMN run_duration FORMAT A20
COLUMN status FORMAT A10
SELECT job_name,
destination,
TO_CHAR(actual_start_date, 'DD-MON-YYYY HH24:MI:SS') AS actual_start_date,
run_duration,
status,
error#
FROM user_scheduler_job_run_details
WHERE job_name = 'MULTI_DEST_JOB'
ORDER BY actual_start_date;
JOB_NAME DESTINATION ACTUAL_START_DATE RUN_DURATION STATUS ERROR#
-------------------- -------------------- -------------------- -------------------- ---------- ----------
MULTI_DEST_JOB LOCAL 01-OCT-2009 11:35:00 +000 00:00:00 SUCCEEDED 0
MULTI_DEST_JOB TEST_DB_GROUP 01-OCT-2009 11:35:00 +000 00:00:03 SUCCEEDED 0
MULTI_DEST_JOB TEST_DB_GROUP 01-OCT-2009 11:36:00 +000 00:00:03 SUCCEEDED 0
MULTI_DEST_JOB LOCAL 01-OCT-2009 11:36:00 +000 00:00:00 SUCCEEDED 0
MULTI_DEST_JOB LOCAL 01-OCT-2009 11:37:00 +000 00:00:00 SUCCEEDED 0
MULTI_DEST_JOB TEST_DB_GROUP 01-OCT-2009 11:37:00 +000 00:00:03 SUCCEEDED 0
MULTI_DEST_JOB RAC1_RAC1_DEST 01-OCT-2009 11:42:34 +000 00:00:02 SUCCEEDED 0
MULTI_DEST_JOB RAC1_RAC1_DEST 01-OCT-2009 11:43:34 +000 00:00:02 SUCCEEDED 0
MULTI_DEST_JOB RAC1_RAC1_DEST 01-OCT-2009 11:44:34 +000 00:00:02 SUCCEEDED 0
SQL>
Notice that there are entries for all destinations, along with a summary entry for the main initiating job.| Deprecated Procedure | New Procedure |
| CREATE_WINDOW_GROUP | CREATE_GROUP |
| DROP_WINDOW_GROUP | DROP_GROUP |
| ADD_WINDOW_GROUP_MEMBER | ADD_GROUP_MEMBER |
| REMOVE_WINDOW_GROUP_MEMBER | REMOVE_GROUP_MEMBER |
Society
Groupthink : Two Party System as Polyarchy : Corruption of Regulators : Bureaucracies : Understanding Micromanagers and Control Freaks : Toxic Managers : Harvard Mafia : Diplomatic Communication : Surviving a Bad Performance Review : Insufficient Retirement Funds as Immanent Problem of Neoliberal Regime : PseudoScience : Who Rules America : Neoliberalism : The Iron Law of Oligarchy : Libertarian Philosophy
Quotes
War and Peace : Skeptical Finance : John Kenneth Galbraith :Talleyrand : Oscar Wilde : Otto Von Bismarck : Keynes : George Carlin : Skeptics : Propaganda : SE quotes : Language Design and Programming Quotes : Random IT-related quotes : Somerset Maugham : Marcus Aurelius : Kurt Vonnegut : Eric Hoffer : Winston Churchill : Napoleon Bonaparte : Ambrose Bierce : Bernard Shaw : Mark Twain Quotes
Bulletin:
Vol 25, No.12 (December, 2013) Rational Fools vs. Efficient Crooks The efficient markets hypothesis : Political Skeptic Bulletin, 2013 : Unemployment Bulletin, 2010 : Vol 23, No.10 (October, 2011) An observation about corporate security departments : Slightly Skeptical Euromaydan Chronicles, June 2014 : Greenspan legacy bulletin, 2008 : Vol 25, No.10 (October, 2013) Cryptolocker Trojan (Win32/Crilock.A) : Vol 25, No.08 (August, 2013) Cloud providers as intelligence collection hubs : Financial Humor Bulletin, 2010 : Inequality Bulletin, 2009 : Financial Humor Bulletin, 2008 : Copyleft Problems Bulletin, 2004 : Financial Humor Bulletin, 2011 : Energy Bulletin, 2010 : Malware Protection Bulletin, 2010 : Vol 26, No.1 (January, 2013) Object-Oriented Cult : Political Skeptic Bulletin, 2011 : Vol 23, No.11 (November, 2011) Softpanorama classification of sysadmin horror stories : Vol 25, No.05 (May, 2013) Corporate bullshit as a communication method : Vol 25, No.06 (June, 2013) A Note on the Relationship of Brooks Law and Conway Law
History:
Fifty glorious years (1950-2000): the triumph of the US computer engineering : Donald Knuth : TAoCP and its Influence of Computer Science : Richard Stallman : Linus Torvalds : Larry Wall : John K. Ousterhout : CTSS : Multix OS Unix History : Unix shell history : VI editor : History of pipes concept : Solaris : MS DOS : Programming Languages History : PL/1 : Simula 67 : C : History of GCC development : Scripting Languages : Perl history : OS History : Mail : DNS : SSH : CPU Instruction Sets : SPARC systems 1987-2006 : Norton Commander : Norton Utilities : Norton Ghost : Frontpage history : Malware Defense History : GNU Screen : OSS early history
Classic books:
The Peter Principle : Parkinson Law : 1984 : The Mythical Man-Month : How to Solve It by George Polya : The Art of Computer Programming : The Elements of Programming Style : The Unix Hater’s Handbook : The Jargon file : The True Believer : Programming Pearls : The Good Soldier Svejk : The Power Elite
Most popular humor pages:
Manifest of the Softpanorama IT Slacker Society : Ten Commandments of the IT Slackers Society : Computer Humor Collection : BSD Logo Story : The Cuckoo's Egg : IT Slang : C++ Humor : ARE YOU A BBS ADDICT? : The Perl Purity Test : Object oriented programmers of all nations : Financial Humor : Financial Humor Bulletin, 2008 : Financial Humor Bulletin, 2010 : The Most Comprehensive Collection of Editor-related Humor : Programming Language Humor : Goldman Sachs related humor : Greenspan humor : C Humor : Scripting Humor : Real Programmers Humor : Web Humor : GPL-related Humor : OFM Humor : Politically Incorrect Humor : IDS Humor : "Linux Sucks" Humor : Russian Musical Humor : Best Russian Programmer Humor : Microsoft plans to buy Catholic Church : Richard Stallman Related Humor : Admin Humor : Perl-related Humor : Linus Torvalds Related humor : PseudoScience Related Humor : Networking Humor : Shell Humor : Financial Humor Bulletin, 2011 : Financial Humor Bulletin, 2012 : Financial Humor Bulletin, 2013 : Java Humor : Software Engineering Humor : Sun Solaris Related Humor : Education Humor : IBM Humor : Assembler-related Humor : VIM Humor : Computer Viruses Humor : Bright tomorrow is rescheduled to a day after tomorrow : Classic Computer Humor
The Last but not Least Technology is dominated by two types of people: those who understand what they do not manage and those who manage what they do not understand ~Archibald Putt. Ph.D
Copyright © 1996-2021 by Softpanorama Society. www.softpanorama.org was initially created as a service to the (now defunct) UN Sustainable Development Networking Programme (SDNP) without any remuneration. This document is an industrial compilation designed and created exclusively for educational use and is distributed under the Softpanorama Content License. Original materials copyright belong to respective owners. Quotes are made for educational purposes only in compliance with the fair use doctrine.
FAIR USE NOTICE This site contains copyrighted material the use of which has not always been specifically authorized by the copyright owner. We are making such material available to advance understanding of computer science, IT technology, economic, scientific, and social issues. We believe this constitutes a 'fair use' of any such copyrighted material as provided by section 107 of the US Copyright Law according to which such material can be distributed without profit exclusively for research and educational purposes.
This is a Spartan WHYFF (We Help You For Free) site written by people for whom English is not a native language. Grammar and spelling errors should be expected. The site contain some broken links as it develops like a living tree...
|
|
You can use PayPal to to buy a cup of coffee for authors of this site |
Disclaimer:
The statements, views and opinions presented on this web page are those of the author (or referenced source) and are not endorsed by, nor do they necessarily reflect, the opinions of the Softpanorama society. We do not warrant the correctness of the information provided or its fitness for any purpose. The site uses AdSense so you need to be aware of Google privacy policy. You you do not want to be tracked by Google please disable Javascript for this site. This site is perfectly usable without Javascript.
Last modified: March 12, 2019