Preface

Welcome to the world of Oracle Scheduler! Oracle Scheduler is a free utility included in the Oracle database that makes the Oracle RDBMS the most powerful scheduling tool on our planet (and in the known parts of the galaxy).

An overview of Oracle Scheduler

The Oracle Scheduler can be used to automate not only the simple maintenance tasks, but also the complex business logic. Traditionally, only PL/SQL could be executed in the Scheduler. Later, operating system scripts were added to it, and now we can run jobs on remote systems and cross platform as well. This can turn the Oracle Scheduler into the spider in your Web, controlling all the jobs running in the organization and giving you a single point for control.

Database background

Relational database management systems (RDBMS) can be very powerful. With a little code, we can use the RDBMS as a filesystem, mail server, HTTP server, and now also as a full-blown job Scheduler that can compete very well with other commercial job Schedulers. The advantage that Oracle gives us is in terms of price, flexibility, and phenomenal power. The scheduling capabilities are all a part of the normal Oracle license for the RDBMS, whereas others have a serious price tag and often require a database for the repository to store the metadata of the jobs.

Scheduling events in the database

Since Oracle added the procedural option to the database, they also included some scheduling power provided by dbms_jobs. Although a bit restricted, it was used extensively. However, no one would even think about using this as an Enterprise-level Scheduler tool. This changed when Oracle introduced 10gR2. In this release, Oracle could not only start jobs that ran outside the database, but they also added the job chain.

In 11g, Oracle also added the option to run jobs on remote systems where no database is running. Now it's time to rethink what the database actually is. In the early days, a database was just a bunch of code that could hold data in tables. Today, the Oracle RDBMS can do that—and that too well—along with many more things. In fact, the Oracle RDBMS can perform so many tasks so amazingly, that it's surprising that we still call it just a database. We could easily turn it into a personal assistant.

Oracle Scheduler 11g can:

• Run jobs where no database ever was before

• Use different operating system credentials per job

• React on events

• Schedule jobs on multiple platforms simultaneously

• Give a tight security

What this book covers

Chapter 1 will get you going with the Scheduler as quickly as possible. In the end, you will automate simple tasks that are now maintained in cron, task manager, or the good old DBMS_JOB package, for example.

Chapter 2 will show you a lot of possibilities of chains with many examples and explanations. In short, it will tell you all you ever wanted to know about chains, but were afraid to ask.

Chapter 3 is for all you people living in an organization that requires strict job separation. This chapter will show how to make good use of the Scheduler and apply job separation.

Chapter 4 is a very important chapter that explains how to crank up the power of a system to the limits by combining the Scheduler and the Resource Manager. Here you will find how to get the best out of your system.

Chapter 5 will be of a great help in setting up remote external jobs introduced in Oracle 11g. How is this related to the old-fashioned local external jobs that we know since Oracle 10g and why we should get rid of the old external jobs? Get your answers here.

Chapter 6 helps the reader to get a firm grip on events and explains how to make good use of events. Events sound like voodoo, but in the end are an extra tool found in the Scheduler.

Chapter 7 considers the fact that when the jobs get more complicated, it gets harder to understand why something works differently than planned. This chapter gives the reader a fresh look at how to follow and debug Scheduler jobs.

Chapter 8 will give you some creative implementations of more or less common tasks—this time implemented using the Scheduler. This chapter gives a working code with clear explanations. This broadens the horizon and will take down the barriers that might exist between other environments and Oracle.

Chapter 9 shows how the Scheduler can be used in other configurations such as standby databases and RAC.

Chapter 10 shows how the Scheduler can be managed and monitored remotely through a web interface.

What you need for this book

Most examples will run fine with just a database. When the Oracle Scheduler Agent is discussed, you will also need a remote Scheduler Agent installed and running. In this case, the version of the database has to be at least 11.1.0.6 because this is the first release that includes the remote Scheduler Agent support. The installation, upgrade, and configuration of the Oracle Scheduler Agent are explained in the book. The agent can be installed either local to the database or remote to the database, that is, on a different computer than where the database is running. The location of the agent does not make a real difference for working with the book. This book is about a very valuable tool in the database, the Oracle Scheduler. So it is quite understandable that you will need access to a database. It can run on any platform. At the time of writing this book, I used Enterprise Linux, Red Hat Linux, Solaris, and MAC OS X, and even Windows. The Oracle Scheduler Agent can be on a different platform than that of the database. It is helpful if you have access to DB Console or Grid control, but it is not required. All the examples are with the PL/SQL code, which can be used from any tool that we normally use to work with the database.

Who this book is for

This book is intended for administrators and developers who currently use tools such as cron, DBMS_JOB, and the task manager, but who now want more control or have a need to scale up to tools that can handle the network. Complex tasks can be built that can easily control business process and enable the completion of important tasks in a limited time.

The reader is expected to have some experience of Oracle Database Management, and a working knowledge of SQL and PL/SQL.

Conventions

In this book, you will find a number of styles of text that distinguish between different kinds of information. Here are some examples of these styles, and an explanation of their meaning.

Code words in text are shown as follows: "The DBMS_SCHEDULER package is available to the public."

A block of code will be set as follows:

CREATE OR REPLACE PROCEDURE SNAP as
begin
null;
insert into session_log select * from v$session where sid = (select sid from v$mystat where rownum = 1);
insert into session_stat_log select * from v$mystat;
end SNAP;

When we wish to draw your attention to a particular part of a code block, the relevant lines or items will be shown in bold:

!/bin/sh
# set this if you change your Agent home
EXECUTION_AGENT_HOME="/data/app/oracle/product/schagent/11.1.0.6"
# set this to use a different data directory for the Agent
# EXECUTION_AGENT_DATA=""

Any command-line input or output is written as follows:

ps –ef|grep ora_cjq0_${ORACLE_SID}

New terms and important words are shown in bold. Words that you see on the screen, in menus or dialog boxes for example, appear in our text like this: "For 10g, Resource Manager is on the Administration tab.".

Note

Warnings or important notes appear in a box like this.

Note

Tips and tricks appear like this.

Reader feedback

Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or may have disliked. Reader feedback is important for us to develop titles that you really get the most out of.

To send us general feedback, simply drop an email to <[e-mail]>, and mention the book title in the subject of your message.

If there is a book that you need and would like to see us publish, please send us a note in the SUGGEST A TITLE form on www.packtpub.com or email <[e-mail]>.

If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide on www.packtpub.com/authors.

Customer support

Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.

Downloading the example code for the book

Visit http://www.packtpub.com/files/code/5982_Code.zip to directly download the example code.

The downloadable files contain instructions on how to use them.

Errata

Although we have taken every care to ensure the accuracy of our contents, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in text or code—we would be grateful if you would report this to us. By doing so, you can save other readers from frustration, and help us to improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/support, selecting your book, clicking on the let us know link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata added to any list of existing errata. Any existing errata can be viewed by selecting your title from http://www.packtpub.com/support.

Piracy

Piracy of copyright material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works in any form on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.

Please contact us at <[e-mail]> with a link to the suspected pirated material.

We appreciate your help in protecting our authors, and our ability to bring you valuable content.

Questions

You can contact us at <[e-mail]> if you are having a problem with any aspect of the book, and we will do our best to address it.