We have already seen a lot of the power of the Scheduler. We worked with the Scheduler mostly using SQL*Plus and DB Console. Although everything can be done using SQL*Plus, it can hardly be called the "user interface" that the Scheduler deserves. The DB Console supports lots of features of the Scheduler, but it is not exactly a friendly tool. Also, it does not show the flexibility of the Scheduler and is not always available. The Grid control is more or less the same, but is comparatively hard to set up. Searching the Web for more tools is a bit disappointing. Since a few years, there is a nice little multiplatform, multidatabase tool called DbVisualizer. This tool offers tremendous flexibility by virtue of its design. In this chapter, we will walk through the tools, DB Console, and DbVisualizer and see how they work.
DB Console is a free tool that has been included in the database installation since Oracle 10g. It is a web application, and it works reasonably well in most browsers. It needs a separate configuration for every database that you want to work with, which makes it a burden for your system. When working with many databases, this hardly makes sense. In that case, Grid Control is a better choice; but, it lacks the features that are present in DB Console.
After the installation, the URL for DB Console is shown. When launching the browser to the specified URL, we get to see the logon screen as follows:
We can use this tool from a remote location as long as it is somehow reachable from your current location. This is great and can be very useful. After entering User Name and Password, we get to the DB Console home page. Here, you can see a quick overview of the database that the DB Console is working on:
In this screenshot, we can see the Resource Manager and Oracle Scheduler columns. For now, we just dive into the Oracle Scheduler column. The entries to manage Jobs, Chains, Schedules, Programs, Job Classes, Windows, Windows Groups, Global Attributes, and Automated Maintenance Tasks are all here. Missing from this list is an entry for the credentials. The Automated Maintenance Tasks screen is very nice to see. This view was missing in 10g which made the important tasks invisible.
Now, you can see them listed in the following screenshot:
On this screen, we can clearly see the Optimizer Statistics Gathering, Segment Advisor, and Automatic SQL Tuning jobs. Normally, we don't need to bother with them and can let them be. When needed, you now know where to find them. Clicking on Database Instance: shield01 brings us back to the previous screen. From there, we select the Jobs view that pops up on the screen:
This screen is mostly used to review jobs. It lists all the defined jobs that the connected user can see. It shows which jobs exist, their status information, and the job class that the job belongs to. This can be a long list when you use jobs that generate lots of other jobs. When a job is running, you can see in the following screenshot that the Scheduled Date column shows now running, and the Last Run Status column shows RUNNING:
Looking at the running jobs, we can see more details such as when the job started and how long it has been running. In this case, it is an external job and so there is no information for CPU Used, Session ID, and Resource Consumer Group.
Using the Stop Run button, we can stop the job. The other buttons are not unique for running jobs. After a while, all the jobs end up in the History section. So, in the History tab, we get to see which jobs ran recently.
When looking for a specific job execution, it could be easier to start from a job definition in the All tab. Click on the job name to see the job definition, which is ENV in this case.
On scrolling down, we come to the Operation Detail:
We can see that the job ran externally using the operating system user Nathan. Also, the job has a log file called job_84049_24 and it received a message on the standard error. The job operation is RUN, which means that it is a job execution. The status is SUCCEEDED, which means that the return code of the job was
0. There is no way to see the contents of the external log file using DB Console. If we want to see the external log file, we need to go to the server where the agent runs. There, we can find the log file in the agent's execution data directory in the logs directory.
The Chains entry in the Oracle Scheduler column in the Server tab is very useful. It shows the defined chains with buttons to create a job that runs the selected chain, and also edit, view, create, or delete a chain.
In the Create Job screen, we can enter the general properties of a job in the General tab. Scheduling can be set up in the Schedule tab. Here, the DB Console is a bit limited as we can only specify one Time and Repeat interval, and we cannot use the normal "frequency = definition" that
dbms_scheduler allows. We can see this in the following screenshot:
We can set a job to repeat, but when we want a job to run daily at
17:00, we cannot do that using this screen unless we have predefined a schedule that does this. In the Options tab, we can specify which events a job has to generate:
Go back to the Server page and select the Schedules entry in the Oracle Scheduler column. We come to the following screen where schedules are created and maintained:
See the lower part of the screen. It is very helpful and can prevent unpleasant surprises. This screen is very convenient because it immediately shows the results of your Scheduler definition.
Back in the main Scheduler column, we now pick the Programs entry to see where can we create, delete, copy, and edit program definitions. Even in an empty database, there are quite a lot of programs defined as you can see in the following screenshot:
Now, we click on the Edit button to see some more details. In this case, the program is of the PL/SQL type, so we don't have the option to add program arguments.
For the other types—STORED_PROCEDURE and EXECUTABLE—we have the facility to add arguments when appropriate. This is to say, when you select a procedure that has arguments, they are listed as named arguments with the option to give them a default value.
When the type is EXECUTABLE, Oracle has no way of knowing what the executable program expects, so there we can manually add arguments.
In the Scheduler Job Classes screen, we can manage job classes. Go and take a look at this page in a new database and check Log Retention Period. You might want to make some changes there. In this database, the job classes have already been modified to keep just 120-days' worth of logging as shown in the following screenshot:
In the Scheduler Windows page, we can define a window. Here, we can define which Resource Plan is active during which period. A window can be attached to a predefined schedule. However, it can also have its own repeat interval like jobs.
The last window is the one that holds the Global Scheduler Attributes.
It is important to have the correct time zone listed in the Global Scheduler Attributes. If the time zone is missing here, and if it is also missing in the job and the schedule definitions, switching to daylight saving time will be a problem.
The MAX_JOB_SLAVE_PROCESSES attribute can be used to put a little control over the number of the jobs that can maximally run together. This is the only option to limit jobs where Resource Manager is not an option for some reason.
There is no support for the remote job agent in Grid Control 10g. This is not strange considering the fact that this type of agent popped up in 11g for the first time. The GUI works the same as for DB Console.
This tool has some surprises. Initially, it was only a browsing tool, but that has changed over time. If you have the chance to play with it, Ctrl + click on everything you see in the object tree. When you do so, you will see a pop-up menu that lists actions that can be performed on the selected object. This is very context-sensitive and gives a clean user interface.
The Scheduler support built into DbVisualizer is the users' contributions, from Nathan Aaron and someone who is writing about Oracle Scheduler and was missing a simple Scheduler interface (that's me), to the tool. Take a look at http://dbvis.com/ and ask for a demo license. It works on almost anything that has a CPU in it. DbVisualizer uses the JDBC interface to connect to the database. When connected, you get to see a list of connections that can be grouped in folders.
The Scheduler support is located in Schemas and DBA Views.
The Oracle Scheduler support is grouped in the Scheduler subtree of Schemas. In this screenshot, you can see the jobs of MARVIN, the same as the ones shown earlier in DB Console. In the lefthand pane is the object tree, and in the righthand pane are the details of the selected object—in this case, the ENV job. The righthand pane also has a few tabs where the Scheduler job logs and the Scheduler job run details are shown for the selected job.
Take a little time to explore the tool and don't forget to right-click on everything you see in the object tree. You will like it. For example, see what happens when you right-click on Jobs:
Select the Create Scheduler Job option from the menu. You will have something similar to the following screenshot on your screen:
This Create Scheduler Job window pops up and allows you to define anything you want to put into a job. Don't forget that this is all contributed by users without even having to code a single line in the tool itself. It is all done in a text file called
oracle.xml. When enabling the Show SQL checkbox, you get to see the PL/SQL that is generated for you. This can easily be copied to the SQL editor in case it does not completely fit your needs. The only limitation around here is imagination and time. It does take a little time to implement these things. This tool has a very context-sensitive setup and only shows the actions that are appropriate for an active context. It doesn't exactly have a large bunch of buttons like some other applications, where every new option has a new button that is always present on the main toolbar.
This was again a quick tour through the Scheduler, purely using the currently available graphical tools. We saw DB Console—a web application that comes for free with the database and DbVisualizer—a client-server application. Both have their strong points. The customization of DbVisualizer is so strong that you really should take a look at it.
Finally, we have reached the end of this book. We have seen a lot of the Scheduler and hopefully more than what the PL/SQL packages and Types manual show. The goal was to explain how the Scheduler could be used and how to get most out of it in a step-by-step approach. We began with a simple job, waded through chains, and found out how to control the resource consumption. The newest thing we saw was the remote external job agent that is a great enhancement compared to the classical external job.
We have also seen some advanced items such as event handling, job generation, debugging Scheduler jobs, some real-life scripts, and jobs in RAC databases and logical standby databases. Finally, this chapter showed some tooling for the Scheduler and also showed that what has not been already made can be made by us.