Scheduler Enhancements in Oracle Database 12c

 

I’ve spent the last couple of days playing around with the scheduler enhancements in Oracle 12c.

I guess the big news is the new “script jobs”, which are pretty cool. This kind-of passed me by until Brynn Llewellyn mentioned them at UKOUG in his Multitenant presentation, at which point I made a note to check them out.

I’ve been having some trouble with the “BACKUP_SCRIPT” jobs up until a few minutes ago. My problem was I couldn’t see what the stdout/stderr text was, so I couldn’t determine why there were not working. The “$ORACLE_HOME/scheduler/log” directory was empty and there were no messages in the trace files or alert log. Then I stumbled upon the new columns added to the ALL_SCHEDULER_JOB_RUN_DETAILS view. The OUTPUT column, not surprisingly, gives you the output from the scripts. Once I could see the error message it took me a few seconds to fix the issue and Bingo! :)

The new job types are a nice addition, allowing you to run file-based scripts or incline scripts much more easily that before.

Cheers

Tim…

 

SQL Developer 3.1 : Scheduler Support…

 

I had a play around with the scheduler support in SQL Developer 3.1 today. I’m late to the party because most of it has been there since 3.0, but what the hell.

It’s more of an opinion piece, so it started as a blog post, but it got too big. Not really what I usually put on the website, but I figured if I put it on the blog it would get in the way of the movie and book reviews and that simply wouldn’t do… :)

I am still worried about feature creep turning SQL Developer into the new TOAD, but so far so good. I’m liking it more and more with each release.

Cheers

Tim…

New scheduler stuff in 11gR2…

 

There are some nice new features in the scheduler in 11gR2. I’ve written about them here:

Remember, there was already a bunch of new stuff added in llgR1, so there’s a lot of whiz-bang stuff if you are moving from 10g.

I do think there are some real security issues with some of this stuff if it is used unwisely though. The remote jobs (external and database) just strike me as a disaster waiting to happen in the wrong hands. :)

Cheers

Tim…

A Tale of 4 Schedulers…

 

A long time ago in a galaxy far, far away, there was only one Oracle scheduler. It was managed using the DBMS_JOB package and the job information was stored in a single table.

SELECT table_name
FROM   dba_tables
WHERE  owner = 'SYS'
AND    table_name LIKE 'JOB$%'
ORDER BY table_name;

TABLE_NAME
------------------------------
JOB$

1 row selected.

SQL>

Information about scheduled jobs was available from the “DBA_JOBS%” views.

SELECT view_name
FROM   dba_views
WHERE  owner = 'SYS'
AND    view_name LIKE 'DBA_JOBS%'
ORDER BY view_name;

VIEW_NAME
------------------------------
DBA_JOBS
DBA_JOBS_RUNNING

2 rows selected.

SQL>

Oracle 10g introduced a new scheduler that was managed using the DBMS_SCHEDULER package, whose job information was stored in the “SCHEDULER$%” tables.

SELECT table_name
FROM   dba_tables
WHERE  owner = 'SYS'
AND    table_name LIKE 'SCHEDULER$%'
ORDER BY table_name;

TABLE_NAME
------------------------------
SCHEDULER$_CHAIN
.
.
.
SCHEDULER$_WINGRP_MEMBER

22 rows selected.

SQL>

Information about scheduled jobs for this new scheduler was available from the “DBA_SCHEDULER%” views.

SELECT view_name
FROM   dba_views
WHERE  owner = 'SYS'
AND    view_name LIKE 'DBA_SCHEDULER%'
ORDER BY view_name;

VIEW_NAME
------------------------------
DBA_SCHEDULER_CHAINS
.
.
.
DBA_SCHEDULER_WINGROUP_MEMBERS

19 rows selected.

SQL>

In addition to the two internal schedulers, the OEM 10g DBConsole had its own scheduler, which stored job information in the “MGMT_JOB%” tables.

SELECT table_name
FROM   dba_tables
WHERE  owner = 'SYSMAN'
AND    table_name LIKE 'MGMT_JOB%'
ORDER BY table_name;

TABLE_NAME
------------------------------
MGMT_JOB
.
.
.
MGMT_JOB_VALUE_PARAMS

44 rows selected.

SQL>

To confuse matters further, the OEM 10g Grid Control repository had a similar scheduler to OEM 10g DBConsole, but with a few extra tables.

SELECT table_name
FROM   dba_tables
WHERE  owner = 'SYSMAN'
AND    table_name LIKE 'MGMT_JOB%'
ORDER BY table_name;

TABLE_NAME
------------------------------
MGMT_JOB
.
.
.
MGMT_JOB_VALUE_PARAMS

53 rows selected.

SQL>

Faced with the choice of four ways to schedule a job, most DBAs called “Tim Hall” decided to pick a single scheduler and keep things simple :)

Cheers

Tim…

PS. Don’t even get me started on OS schedulers and the Oracle Applications scheduler… đŸ˜‰