Mastering Oracle Scheduler in Oracle 11g Databases

Ronald Rood

• Publisher: Packt Publishing
• Year: 2009
• eBook ISBN: 9781847195982
• Edition: 1
• Topic: Computer Science
• Subtopic: Information Technology
• Index: Computer Science

In Detail

Scheduler (DBMS_SCHEDULER) is included in Oracle Database and is a tool for the automation, management, and control of jobs. It enables users to schedule jobs running inside the database such as PL/SQL procedures or PL/SQL blocks, as well as jobs running outside the database like shell scripts. Scheduler ensures that jobs are run on time, automates business processes, and optimizes the use of available resources.

You just need to specify a fixed date and time and Scheduler will do the rest. What if you don't know the precise time to execute your job? Nothing to worry about, you can specify an event upon which you want your job to be done and Scheduler will execute your job at the appropriate time. Although scheduling sounds quite easy, it requires programming skills and knowledge to set up such a powerful, intelligent scheduler for your project.

This book is your practical guide to DBMS_SCHEDULER for setting up platform-independent schedules that automate the execution of time-based or event-based job processes. It will show you how to automate business processes, and help you manage and monitor those jobs efficiently and effectively. It explains how Scheduler can be used to achieve the tasks you need to make happen in the real world. With a little understanding of how the Scheduler can be used and what kind of control it gives, you will be able to recognize the real power that many known enterprise-class schedulers - with serious price tags - cannot compete with.

You will see how running a specific program can be made dependent on the successful running of certain other programs, and how to separate various tasks using the built-in security mechanisms. You will learn to manage resources to balance the load on your system, and gain increased database performance.

A practical guide for Database Administrators and Developers to setting up enterprise class job scheduling

Approach

This is your practical guide to setting up a working environment and using Oracle Scheduler. Packed with simple examples and clear explanations, real-life scenarios are discussed to make you comfortable in implementing them in your own system.

Who this book is for

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

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

Mastering Oracle Scheduler in Oracle 11g Databases

Ronald Rood

---

Table of Contents

Mastering Oracle Scheduler in Oracle 11g Databases

Credits

About the Author

About the Reviewers

Preface

An overview of Oracle Scheduler

Database background

Scheduling events in the database

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Downloading the example code for the book

Errata

Piracy

Questions

1. Simple Jobs

Creating a user

Running your Jobs

PL/SQL block

Stored procedure

Executable

Program

Defining arguments for your jobs

Metadata arguments

Normal application arguments

Summary

2. Simple Chain

Jobs

Chains

Statuses

Chains and steps

Rules

Evaluation interval

Privileges

Steps to create a chain

Hands-on with chains

Programs

SHUTDOWN

COPYFILES

STARTUP

Program state

DB Console

Chain definition

Running the chain

Tricks with chains

Manipulating the running chains

Analyzing the chain

Summary

3. Control the Scheduler

Job creation

Job execution

Scheduler management

Logging

Log detail level

Log purging

Summary

4. Managing Resources

Resource consumer group

Creating resource consumer groups

Class

Plan

Window

Window groups

Monitoring

Problems with Resource Manager

Problems that Resource Manager solves

Summary

5. Getting Out of the Database

Security

Installation on Windows

Installation on Linux

Upgrading the remote job agent

Silent install of the remote job agent

Base release

Patch to the latest available level

Preparing the database for remote agent usage

Verifying the XDB installation

Setting the HTTP port

Creating remote Scheduler objects

Setting registration password

Configuring a remote agent

Troubleshooting

Multiple agents on the same host

Credentials

Creating job—targeting Unix

Creating job—targeting Windows

Runtime observations of the remote Scheduler

Resource Manager

Summary

6. Events

Monitoring job events

Events in chains

Event-based scheduling

Summary

7. Debugging the Scheduler

Unix—all releases

Windows usage

Bugs

Job environment

Checks to do in the database

Summary

8. The Scheduler in Real Life

Statistics collection

The schedule_run procedure

The run procedure

The statob procedure

The drop_jobs procedure

Generating the jobs

Performing the analysis

Generating the scheduled run

Backups

Things that can scare you

Diving into the code

Reading the event queue

Scheduling for the HOT backups

Scheduling the DARC process

Scheduling the final BARC process

How to use the calendar

Tools

How to schedule on the first day of a month

How to schedule only on Monday

How to schedule on the first Monday of a month

How to schedule on the first working day of a month

How to schedule on the first working Monday of a month

How to schedule on the nth Monday of a month

How to schedule on the last working day of a month

How to schedule in the first quarter of a year

How to schedule on the first Monday of the first quarter

Summary

9. Other Configurations

RAC

Job creation/modification

The job_class definition

Standby databases

Creating jobs in a logical standby database

Running jobs in a logical standby database

Summary

10. Scheduler GUI Tools

DB Console

Grid Control

DbVisualizer

Summary

Index

Mastering Oracle Scheduler in Oracle 11g Databases

Copyright © 2009 Packt Publishing

All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.

Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author, Packt Publishing, nor its dealers or distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book.

Packt Publishing has endeavored to provide trademark information about all the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.

First published: June 2009

Production Reference: 1100609

Published by Packt Publishing Ltd.

32 Lincoln Road

Olton

Birmingham, B27 6PA, UK.

ISBN 978-1-847195-98-2

www.packtpub.com

Cover Image by Ninoslav Babić (<[email protected]>)

Credits

Author

Ronald Rood

Reviewers

Hans Forbrich

Mennan Tekbir

Robin Buitenhuis

Syed Jaffar Hussain

Acquisition Editor

James Lumsden

Development Editor

Ved Prakash Jha

Technical Editors

Ishita Dhabalia

John Antony

Copy Editor

Sneha Kulkarni

Editorial Team Leader

Gagandeep Singh

Project Team Leader

Lata Basantani

Project Coordinator

Leena Purkait

Proofreader

Camille Guy

Indexer

Hemangini Bari

Production Coordinator

Shantanu Zagade

Cover Work

Shantanu Zagade

About the Author

Ronald Rood is an IT professional for over 20 years. His private domain is named after the first account that was created on a computer for him. That account was ronr on a DPS9 system, and so his private domain is called ronr.nl where you can contact him using <[email protected]>. He eagerly joined the Oracle community and became a skilled, innovating DBA and troubleshooter. He is a master of C, PRO*C, lots of scripting languages, and—of course—PL/SQL. Ronald’s real power is in the combination of the rich Unix world and Oracle. According to him, there is no such thing as a problem that cannot be solved; it just might take a little time.

Ronald speaks several languages such as Dutch, English, German, and some French. In the private time that he shares with his wife and two children, he likes to take an occasional dive (from the sky), fly with radio-controlled models, ride recumbents, and work as a volunteer for a local water scouts group.

Currently, Ronald is one of the Ciber Oracle consultants in The Netherlands and cooperates in many projects for many large companies. Before writing this book, Ronald wrote Oracle-related articles for the Dutch Oracle user group magazine. On his blog, you can find some short articles about various items, but most are related to Oracle.

I would like to thank everyone who helped me writing this book. Not in the least, Sandra for having lots of patience for me while writing, Silvana and Alex for being great children I can be proud of, Robin and the other reviewers for challenging me to write better, and Harold for showing me that there is a way that might or might not be seen. I also would like to thank my colleagues of Ciber for being my colleagues, and Mark for giving me the challenges that I like so much. Without my parents, I would never have existed at all, but they gave me the opportunity to study without ever asking anything in return. Bedankt ma, bedankt ...

Table of contents

• Mastering Oracle Scheduler in Oracle 11g Databases