31,19 €
Mehr erfahren.
- Herausgeber: Packt Publishing
- Kategorie: Wissenschaft und neue Technologien
- Sprache: Englisch
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.
Das E-Book können Sie in Legimi-Apps oder einer beliebigen App lesen, die das folgende Format unterstützen:
Seitenzahl: 236
Veröffentlichungsjahr: 2009
Ähnliche
Table of Contents
Mastering Oracle Scheduler in Oracle 11g Databases
Ronald Rood
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 pa zonder jullie was het nooit wat geworden.
About the Reviewers
Hans Forbrich has been around computers for 40 years. While studying for his B.Sc. EE in the 70s, he worked as a contract programmer to help pay for school. Hans has been working with the Oracle products since 1984. While at Nortel in the field service group, he met Oracle Database version 4. He joined Oracle Canada to work in the Communications vertical from 1996 to 2002. In 2003, Hans started Forbrich Computer Consulting Ltd., which has become a successful international Oracle consultancy and Oracle training partner based in St. Albert, near Edmonton, Alberta, Canada.
As an Oracle ACE Director and OCP, Hans frequently responds in various Oracle Forums, teaches for Oracle University, consults with Oracle customers on maximizing value from Oracle licenses, and speaks at Oracle User Group conferences around the world. He holds a strong belief that Oracle products provide significant value, and the key to extracting that value—and reducing the effective cost of the product—is in understanding the product and using the right tool for the job.
I thank my wife of 27 years for her patience, especially while I experiment in the lab. I also thank my two sons for their patience, their assistance at computer setups, and their help in those same experiments. (And I am proud to note that Son #1, aka Employee #2, aka Chief Network and Systems Administrator, has achieved his M.Sc. EE this past year!) Finally, I thank Edmonton Opera and my colleagues there for allowing me to break away from computers and unwind on stage with the Edmonton Opera Chorus.
Mennan Tekbir has a master's degree in Computer Sciences, and has worked for several telecommunications companies around the world. He is mainly focused on performance analysis, designing and implementing frameworks, automated PL/SQL code generation, and fraud detection with data mining.
Robin Buitenhuis is an experienced Oracle DBA. During his study at the Universiteit Twente, he came in contact with Oracle RDBMS 6.0 en Forms 2.3. From that moment on, he has always worked with Oracle products. He is also a member of the board of the Dutch Oracle Usergroup (www.ogh.nl).
Syed Jaffar Hussain has more than 16 years of Information Technology (IT) experience, which includes over eight years as a Production Oracle DBA. He is an Oracle ACE, Oracle 10g Certified Master (OCM), OCP DBA (v8i, 9i, and10g), and Oracle 10g RAC Certified Expert. He is currently involved in 8 node production and 6 node development RAC setup with more than 60 databases running across the nodes. He has a broad experience in Oracle advanced technologies such as RAC, DataGuard, RMAN, and Performance Tuning. He occasionally presents Oracle University five-day courses and one-day seminars on behalf of Oracle EMEA. He has also worked for a couple of multinational banks in Saudi Arabia. He is currently involved with the Oracle 11g RAC book.
He is a frequent contributor at Oracle OTN forums and regularly writes to his blog, (http://jaffardba.blogspot.com). He is reachable at <[email protected]>
I would like to thank my wife Ayesha and my three kids Ashfaq, Arfan, and Aahil for allowing me to concentrate on this task.
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:
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.
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 <[email protected]>, 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 <[email protected]>.
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 <[email protected]> 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 <[email protected]> if you are having a problem with any aspect of the book, and we will do our best to address it.
