Sundays – a day of rest. Certainly true for me. Sunday morning is a time for lazing around leafing through the colour supplements and thinking about nothing in particular. Sunday 23rd October was a little bit different.
Wide-awake at 8 am ( I didn’t know that there was such a time as 8am on a Sunday), like several million others, I was wondering what would confront the All Blacks – the Gallic flair with which France had swept aside England or the Gallic shrug with which they had surrendered to Tonga ?
Look, I’m not really a New Zealander. Yes, I was born in Auckland but both my parents are English and I’ve lived most of my life in England. However, like anyone with a connection to the Land of the Long White Cloud, there is a part of my soul, however small, that takes the form of a Rugby ball.
At the end of the match, I was able to join my “fellow” Kiwis in, not so much paroxysms of joy as a huge collective sigh of relief.
On the whole though, I’d rather not have to see Sunday morning from that early on. So, if there is, for example, something that needs to run on my database on a Sunday morning, I’d rather the database just did it without my intervention.
What I plan to do here is :
- set up a scheduler job
- explore the ways in which we can control whether a class of job runs on a given database
- stop jobs running on database startup
- teach the scheduler how to tell the time – especially in terms of daylight saving
Creating a scheduler job
There is a really good and comprehensive guide on the myriad options available for Oracle’s database scheduler over at Oracle Base.
Here, I’m going to keep things quick and simple.
I’ve got a table that looks something like this :
CREATE TABLE reminders(
message VARCHAR2(4000),
msg_ts TIMESTAMP)
/
For some reason I can’t think of right now, I need to insert a row into this table at 8.30 every Sunday morning.
Rather than me having to drag my weary bones all the way to the keyboard, I can simply get the database to remember to do it. So, as a user with CREATE JOB privilege :
DECLARE
l_job_action VARCHAR2(500) :=
'BEGIN '
||'INSERT INTO reminders( message, msg_ts) '
||'VALUES(''What time do you call this ?'', SYSTIMESTAMP); '
||'END;';
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'wakeup_sun',
job_type => 'PLSQL_BLOCK',
job_action => l_job_action,
start_date => SYSDATE,
repeat_interval => 'freq=weekly; byday=SUN; byhour=8; byminute=30',
end_date => NULL,
enabled => TRUE,
comments => 'Do something automatically on Sunday morning');
END;
/
Notice anything odd about the START_TIME parameter ? Neither did I…at first. We’ll come back to this a little later. In the meantime, if we want to test that our job will actually do what we need it to, we can do a test run :
BEGIN
DBMS_SCHEDULER.RUN_JOB(job_name => 'wakeup_sun');
END;
/
PL/SQL procedure successfully completed.
SQL> SELECT *
2 FROM reminders;
MESSAGE
------------------------------
MSG_TS
---------------------------------------------------------------------------
What time do you call this ?
10-NOV-11 20.16.09.044987
We could even tidy things up a bit by creating a stored procedure to do the work and then just calling that from the scheduler job.
CREATE OR REPLACE PROCEDURE sunday_am_pr IS
BEGIN
INSERT INTO reminders( message, msg_ts)
VALUES( 'Now we know that DBA stands for Does Bugger All !', SYSTIMESTAMP);
END sunday_am_pr;
/
And now the job to call it …
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'sunday_am',
job_type => 'STORED_PROCEDURE',
job_action => 'sunday_am_pr',
start_date => SYSDATE,
repeat_interval => 'freq=weekly; byday=SUN; byhour=8; byminute=30',
end_date => NULL,
enabled => TRUE,
comments => 'Do something automatically on Sunday morning');
END;
/
The scheduler can’t tell the time
Let’s have a look at those jobs we just created in a little more detail :
SQL> SELECT TO_CHAR(start_date, 'DD-MON-RR HH24:MI:SS TZR') 2 FROM dba_scheduler_jobs 3 WHERE owner = USER 4 AND job_name = 'SUNDAY_AM'; TO_CHAR(START_DATE,'DD-MON-RRHH24:MI:SSTZR') -------------------------------------------------------------------------------- 10-NOV-11 20:22:55 +00:00 SQL>
Hmmm, the start time doesn’t seem to have an offset to account for daylight savings. So, rather than using SYSDATE as the START_TIME when defining a job, maybe we should be using SYSTIMESTAMP ?
Well, yes and no. SYSTIMESTAMP will work fine for jobs that have run intervals of less than 24 hours. However, for jobs such as this one, it will blithely ignore changes in daylight saving. So, when, in my case, we move from the optimistically named British Summer Time ( BST) back to GMT, the job will run an hour later than before.
If we’re going to get the job to run at the correct time, we need to be explicit.
Just in case you’ve been typing along whilst reading this post and are now busy cursing me as you’ll have to go back and drop and re-create any jobs you’ve already set up, this script should set things right :
set serveroutput on
DECLARE
CURSOR c_jobs IS
SELECT job_name, start_date
FROM dba_scheduler_jobs
WHERE owner = USER;
BEGIN
FOR r_jobs IN c_jobs LOOP
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => r_jobs.job_name,
attribute => 'start_date',
value => TO_TIMESTAMP_TZ(
TO_CHAR(r_jobs.start_date, 'DDMMRR HH24:MI:SS')
||'EUROPE/LONDON', 'DDMMRR HH24:MI:SS TZR'));
DBMS_OUTPUT.PUT_LINE('Job '||r_jobs.job_name||' altered.');
END LOOP;
END;
/
After running this script, if we re-run the same query we get…
SQL> select TO_CHAR(start_date, 'DD-MON-RR HH24:MI:SS TZR') 2 FROM dba_scheduler_jobs 3 WHERE job_name = 'SUNDAY_AM' 4 / TO_CHAR(START_DATE,'DD-MON-RRHH24:MI:SSTZR') -------------------------------------------------------------------------------- 10-NOV-11 20:22:55 EUROPE/LONDON
Help, my database is spamming me
So, you have your scheduled jobs running happily in the database. You probably have a few sending e-mails to you as the DBA to let you know all is well, or if there is a problem.
What happens when you refresh your test database with a copy of Live ?
Yep, as soon as you open the instance, the scheduler checks to see if any jobs should have run. If the current system time is later than the next_run_time, then they’ll kick off immediately. Even if the first thing you do after opening the database is to disable these jobs, your inbox is still going to be flooded with spam.
In the good old days of dbms_job, you could avoid this simply by setting the job_queue_processes parameter to 0. This solution has now finally found it’s way into 11gR2 as well. However, if you’re currently stuck somewhere between 10g and 11gR1 then you’ll need an alternative.
The workaround below is a quick n dirty version of this infinitely more elegant solution proposed by Dan Morgan.
Getting classy
The first thing we need to do is create a service. For our specific purposes, you can think of a service as….well…a thingy.
BEGIN
DBMS_SERVICE.CREATE_SERVICE(
service_name => 'not_so_fast',
network_name => 'some_random_string');
END;
/
Next, we create a job class and associate it with the service…
BEGIN
DBMS_SCHEDULER.CREATE_JOB_CLASS(
job_class_name => 'only_on_live',
service => 'not_so_fast');
END;
/
Finally, we assign our job to the job class
set serveroutput on
DECLARE
CURSOR c_jobs IS
SELECT job_name
FROM dba_scheduler_jobs
WHERE owner = USER;
BEGIN
FOR r_jobs IN c_jobs LOOP
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => r_jobs.job_name,
attribute => 'job_class',
value => 'only_on_live');
DBMS_OUTPUT.PUT_LINE('Job '||r_jobs.job_name||' added to ONLY_ON_LIVE class.');
END LOOP;
END;
/
NOTE – if you subsequently create any jobs that are assigned to a class owned by someone else then you need to GRANT EXECUTE ANY CLASS to the job owner. Otherwise, when you run the job you’ll get :
ORA-27486: insufficient privileges ORA-06512: at "SYS.DBMS_ISCHED", line 2751 ORA-06512: at "SYS.DBMS_SCHEDULER", line 1794 ORA-06512: at line 2
This happens even if you’re trying to run the job as sys as sysdba.
In order to enable or disable jobs in a specified class, all we need to do is enable or disable the service with which the class is associated.
BEGIN
DBMS_SERVICE.START_SERVICE( service_name => 'not_so_fast');
END;
/
You can see what services are running on your database by querying v$active_services…
SQL> SELECT name 2 FROM v$active_services 3 / NAME ------------------------------ not_so_fast XEXDB XE SYS$BACKGROUND SYS$USERS
If the service is not running, then none of the jobs associated with the class that is, in turn, associated with the service, will run.
So how do we stop the spam ? Dan’s solution used a simple config file based on the same format as oratab. Mine is a bit more basic, but does have the advantage of meaning that you have one less thing to remember when cloning your database.
Here, we’re going to use a database startup trigger :
CREATE OR REPLACE TRIGGER start_service_trg
AFTER STARTUP ON DATABASE
DECLARE
lc_live_name CONSTANT v$database.name%TYPE := 'XE';
l_db_name v$database.name%TYPE;
BEGIN
SELECT name INTO l_db_name FROM v$database;
IF l_db_name = lc_live_name THEN
DBMS_SERVICE.START_SERVICE( service_name => 'not_so_fast');
END IF;
END;
/
Simple really, only start the service if we’re running on production. When I start a test database (in this case, where the database name is not XE, the trigger will not enable the services ( and therefore the jobs) thus keeping my inbox spam free.
Regular readers may be wondering how I got through this entire explanation without any Monty Python references.
Take it easy, I’m just recovering
What about those times when you’re bringing the production database back up after some unforeseen downtime. The last thing you want is a load of potentially long-running jobs to kick off and hog all of the available database resources.
If you haven’t used the Services method above, you’re going to have to find an alternative solution…
STARTUP NOMOUNT ALTER SYSTEM ENABLE RESTRICTED SESSION;
Once you open the database, restricted session is still enabled and the jobs don’t run.
You can then disable them at your leisure before…
ALTER SYSTEM DISABLE RESTRICTED SESSION;
Once you open the database, restricted session is still enabled and the jobs don’t run.
You can then disable them at your leisure before…
ALTER SYSTEM DISABLE RESTRICTED SESSION;
Deb has been sulking most of the way through this post. In fact, she’s not been entirely happy ever since the French knocked out Wales in the Semis. Oh well, only another 4 years till the next World Cup. I mean, how long can a sulk last ?
