8.1. Why Schedule Jobs from Within Oracle? Oracle DBAs who need to schedule a job for executioneither at a specific, future time or at regularly scheduled intervalsgenerally have two options from which to choose:
Use the operating system scheduler Execute the program or code segment from an operating system-specific utility such as cron in Unix or AT in Microsoft Windows. These tools let you execute an operating system-specific command as needed. You can even run an Oracle job from the frontend, usually by constructing it as a SQL script and then running it through SQL*Plus as a command-line execution.
Use the database scheduler from within Oracle Schedule the operation as a job in the database to be run either one time or repeatedly using DBMS_SCHEDULER. This database job runs within the context of the database, not the operating system.
Given that you have the ability to schedule jobs in the operating system using standard operating system utilities, why would you even consider scheduling them inside the database? There are several compelling reasons:
Database dependence An operating system job runs regardless of whether the database is available. So what happens if the job to collect optimizer statistics starts at its scheduled time but the database is down for some reason? The job fails, which may then lead to a variety of unpleasant consequences. Collecting statistics is a rather benign example; in other cases, this scenario could present more serious issues. Suppose, for example, that you have an operating system job that goes through a series of database and operating system tasks, and pages the on-call DBA when it fails. If the database is down, the page is still generated, albeit incorrectly. Of course, you could write the first few lines of your code in such a way that it checks for database availability before starting, but you will then have to add that code to every one of your utilities, which becomes a maintainability issue. On the other hand, a database job will run only when the database is up, never when it is down. This results in better and more accurate control over the execution process.
Protection Jobs are part of the database and therefore come with the same protection as the other objects in that database. You can grant or revoke access on specific jobs to or from individual users. When the database is backed up, so are the jobs, along with the metadata information on them (e.g., comments, schedules, etc.).
Operating system independence Consider the syntax of the cron command on Unix and the Windows Task Scheduler or the AT command on Windows. They are entirely different. What if you want to port your database from one platform to the other? You will have to change each execution to accommodate the target platform, which will be a rather painstaking process. On the other hand, if you perform scheduling from within the database, the database will carry its job definitions along with it; there will be no need to change any aspect of your job definitions.
Location independence Suppose you store all your utilities in the /u01/app/dbatools directory, but later you decide to move them to a different directory, /u02/app/dbatools. If you are using cron or AT for scheduling, this simple change will require you to make several changes in every one of your schedule definitions. On the other hand, if you use database scheduling, it's a snap; no location information needs to be provided.
Security When you call a PL/SQL code segment or even a simple SQL script from the operating system, you have to pass the username and password in the scheduled job. Let's look at the simplest case. Suppose that you have a shell script that calls a SQL script called myjob.sql as follows: $ORACLE_HOME/bin/sqlplus -s scott/tiger @/u01/app/dbatools/myjob.sql
The username (scott) and password (tiger) are passed in cleartext in the command. Anyone who looks for this process using a ps -aef command will easily see this crucial information, which of course opens up a security hole. You could try to minimize the risk of exposing Scott's password by creating the script a little differentlyplacing the username/password inside the SQL script file myjob.sql as a connection as follows: CONNECT scott/tiger blah ... blah ... blah ...
Now your calling script would look like this: $ORACLE_HOME/bin/sqlplus -s /nolog @/u01/app/dbatools/myjob.sql
The switch /nolog instructs SQL*Plus not to expect any login information in the command line (as there is no login). This approach is a little better: the username and password are not visible from the process lookup; however, if a user checks the script file, this information will be revealedand you certainly don't want your users to see each other's passwords. The last alternative would be to use what is called an OPS$ login , which allows the OS to perform the authentication . You could have a Unix user called scott and an Oracle user called OPS$SCOTT which is identified externally. The cron job would look like this: $ORACLE_HOME/bin/sqlplus -s / @/u01/app/dbatools/myjob.sql
There is a key disadvantage of this approach, however. Not only does it depend on your Oracle login being the same as your OS login, it also means that if someone gains access to your OS password, she can also connect to your database schema without further authentication. If, on the other hand, you use the database to schedule jobs, you will not need scripts of this kind, so the user's password will never be revealed. Many security-conscious organizations have strict rules against revealing passwords under any circumstances. Database scheduling is the only option in such environments.
Flexibility If you are using operating system scheduling and need to change a user's password, you will have to go through all of the necessary shell scripts to change that password. Some organizations may try to minimize this effort by creating a central password file and reading from it for every shell script. That may save some effort, but if a user wants to change his password, this password file will have to be updated as well. If operating system access to your database server is restricted to only DBAs (as it should be), a regular user won't be able to change the password file and will have to ask the DBA for help in making the change. Involving another person in the password management process is never a desirable situation. On the other hand, if you are using database scheduling, the password need not be stored anywhere.
All in all, if you need to execute Oracle-related functionality, it is easier, more secure, and more efficient to do so through DBMS_SCHEDULER.  | Throughout this chapter, I will refer to Oracle's scheduling subsystem as either DBMS_SCHEDULER or the Scheduler. |
|
|
