Today you will learn about managing job queues and the Oracle auditing facility. Job queues are used to schedule jobs during off-peak hours or to schedule certain tasks on a recurring basis. The Oracle job queue facility allows you to schedule single or recurring tasks in an easy and reliable manner.

The Oracle auditing facility allows the administrator to monitor or audit the system usage. Auditing is important if one or more incident has occurred to make you suspicious of the activity of one or more users. Through auditing, a record is made of the suspicious activity and can be used to track down the party at fault. Think of auditing as a way of logging activity in the system.

Oracle job queues are used to divert the execution of an operation to a later time. This allows you to schedule jobs.

Managing Job Queues

New Term: A job queue is a method whereby you can schedule a PL/SQL task to run at some time in the future, or even to run periodically. The job-scheduling task can be done either graphically through the Enterprise Manager or via the Oracle job-scheduling commands. As always, you will learn about both methods here.

Using the Oracle job-queuing facility, you can easily schedule jobs to be run at various times or on a regular schedule. Some examples of where this might be useful include

• Regularly scheduled jobs, such as end-of-day accounting--A job that must run every weekday can be scheduled to run unattended.

• Large jobs that need to be run during off-peak hours--If you schedule them, you need not be present at 3:00 a.m.

• A large number of jobs that must be run sequentially--In this case, the jobs might be so large that in order to reduce CPU usage, only one is run at a time.

---

NOTE: An advantage of scheduling jobs is that you need not be present when the job runs. A disadvantage is that if the job fails during its operation, nobody is around to restart it. Therefore, you should thoroughly test your job before queuing it.

---

Let's look at how to schedule jobs using both the Enterprise Manager and the stored procedures in the DBMS_JOB package.

Scheduling Jobs Using the Enterprise Manager

As you recall from Day 5, "Managing the Oracle RDBMS," the Enterprise Manager has a pane called the Job pane. Within this pane you can manage jobs that are running on this or other systems managed by this Enterprise Manager. By default, the Job pane is located in the lower-left corner of the Enterprise Manager, as shown in Figure 15.1.

Because this lesson is concerned only with the job-queuing functions of the Enterprise Manager, I closed the other Enterprise Manager panes for the remaining figures in today's lesson by deselecting them in the View drop-down menu.

To create a job using the Enterprise Manager, select the Create option by pulling down the Job menu. This will invoke the Create Job dialog box, as shown in Figure 15.2. It is from this dialog that you create an Oracle job, as you will see in this section.

Figure 15.1.

The Oracle Enterprise Manager.

Figure 15.2.

The Create Job dialog box.

Say you're the president of a dog club that keeps track of a large number of dogs and their owners. Each week, an export image of this database is sent out to various other dog clubs. Therefore, you want to create a job that, on a weekly basis, will export the Dogs database. The first task is to invoke the Create Job dialog, as you saw in the previous figure. This dialog has four different tabs that invoke different screens, each of which has its own function. These tabs are

• General

• Tasks

• Parameters

• Schedule

These screens are very flexible and easy to use.

The General Screen

Use the General screen to uniquely define the job. Type the name of the job and provide a description of it as well as defining the database, node, or listener that the job will act on. The General screen, filled in for this example, is shown in Figure 15.3.

Figure 15.3.

Use the General screen to define the job.

The Tasks Screen

You use the Tasks screen to define the task that the job will perform. There are a number of predefined tasks, or you can define your own, as shown in Figure 15.4.

Figure 15.4.

The Tasks screen is where you define what the job will do.

Possible choices include the following administrative tasks:

• Backup Tablespace

• Export

• Import

• Load

• Run DBA Script

• Run SQL*Plus

• Shutdown Database

• Startup Database

There are also a number of predefined, non-database administrative tasks, such as

• Broadcast Message

• Run OS Command

• Others

The appearance of the Parameters screen will vary depending on what type of task is selected in the Tasks screen.

The Parameters Screen

The Parameters screen allows you to further define the parameters for the task that was selected. Because I selected an export task for this example, this screen allows me to define further export parameters, as shown in Figure 15.5.

Figure 15.5.

You further define the task in the Parameters screen.

Here you can select the database to be exported as well as the export filename. You can also choose to select export objects or advanced export options. The advanced export options screen is shown in Figure 15.6.

Figure 15.6.

You can choose advanced options for exporting.

This gives you a wide range of options for the export operation. Similarly, other tasks have their own parameters you can set.

The Schedule Screen

The Schedule screen allows you to define how the job is to be run, as shown in Figure 15.7.

Figure 15.7.

You define the timing of the job in the Schedule screen.

Scheduling can be done at various rates, including

• Immediately

• Once

• Interval (for example, every x days)

• On Day of Week

• On Day of Month

Scheduling is very flexible and easy to set up and run. At this point, all that is necessary to schedule the job is to click the Submit button. You will see the scheduled job listed on the Active Jobs screen, as shown in Figure 15.8.

Figure 15.8.

The Active Jobs screen shows the status of active jobs.

---

NOTE: For job queuing to work properly, the user preferences must be set up correctly. The user for the database must be a valid database user with the system permissions necessary to perform the task required. The user for the node must be a valid NT user with "login as batch job" permission. This user must also have a password. Accounts with no passwords cannot be used. The preferences setup screen for the Enterprise Manager is shown in Figure 15.9. For my setup I created a batch account and added the proper privileges.

---

It is important for the Enterprise Manager to be properly configured for it to be able to submit jobs. After you have submitted the job, you can also modify it from the Enterprise Manager.

Figure 15.9.

Setting Enterprise Manager preferences.

Administering Jobs

After the job has been scheduled, you can administer it from the Enterprise Manager. You can't do much here except monitor the status and delete the job; these tasks can be accomplished from the Jobs menu. Selecting the Show Details option shows you how the job was submitted. Selecting the Remove Job option deletes the selected job.

Scheduling Jobs Using the Command-Line Facilities

It is possible to manage the Oracle job queues through the Server Manager or SQL*Plus rather than use the Enterprise Manager. This is very useful because all users can use Oracle queuing, and typically only administrators have access to the Enterprise Manager console.

A job is administered by calling the DBMS_JOB package just as you would call any other package. The stored procedures that make up this package are the following:

• BROKEN--Disables job execution without removing the job.

• CHANGE--Can be used to alter the job description, when it will run, or the interval between runs.

• INTERVAL--Alters the interval between runs.

• NEXT_DATE--Alters the next execution time of the job.

• REMOVE--Removes a job from the job queue.

• RUN--Forces a job to run.

• SUBMIT--Submits a job to the job queue.

• WHAT--Alters the job description.

Each of these options has its own parameters that call it. The following sections outline the parameters for a few of them.

SUBMIT

The SUBMIT function can be executed by calling the package with a syntax such as

DBMS_JOB.SUBMIT(
Job
What
Next_date
Interval
No_parse )

where Job is an output parameter and the others are input parameters.

The SUBMIT procedure returns the job number. Here is an example:

SQL> VARIABLE job number;
SQL> begin
  2  DBMS_JOB.SUBMIT(
  3  :job,
  4  `DELETE FROM dogs2;',
  5  SYSDATE,
  6  NULL);
  7  COMMIT;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> PRINT job;

      JOB
---------

        1

REMOVE

The REMOVE function can be executed by calling the package with syntax such as

DBMS_JOB.REMOVE(Job)

where Job is the job number.

An example of using DBMS_JOB.REMOVE is as follows:

SQL> begin
  2  DBMS_JOB.REMOVE(1);
  3  COMMIT;
  4  end;
  5  /

PL/SQL procedure successfully completed.

If you don't know the job number, you can try to find it by using the following query:

SQL> select job, next_date, next_sec from user_jobs;

      JOB NEXT_DATE NEXT_SEC
--------- --------- --------
        1 25-JUL-97 16:55:28
        2 25-JUL-97 17:07:51

Other activities, such as DBMS_JOB.BROKEN, use a similar syntax. You can find more details in the Oracle documentation.

Using Oracle Auditing

The Oracle auditing facility is used to log information about database operations such as when they occurred and who performed them. Auditing is important primarily if one or more incidents have occurred to make you suspicious of the activity of one or more users. Through auditing, a record is made of this activity that can be used to track down the party at fault.

---

TIP: For auditing to be effective, it is necessary for each user (especially the DBAs) to have his own account and use it. It does no good to audit database activity and determine that the SYSTEM user is at fault if you have 15 DBAs who use the SYSTEM account.

---

Auditing is not only used when you think you have some sort of problem; it can also give you useful information about the usage of your system that might help you to determine a better system configuration in the future. By having information about the activities that are going on within the Oracle RDBMS, you will be better able to provide the proper services to the user community.

Auditing, although useful, is very expensive in terms of both CPU overhead and disk usage. As such, you should use auditing very carefully and selectively. Let's look at how to set up the auditing process.

Developing an Auditing Strategy

Before attempting to start auditing, you must first decide what needs to be audited. This not only involves the activity that is to be audited, but the user accounts that are to be audited as well. The first step in developing an auditing strategy is to determine the purpose of auditing.

There may be one or more reasons why you want to audit. By determining these reasons, you can better put together the audit plan. You might perform auditing for either of the following reasons:

• Informational purposes--It is common to use auditing to retain specific historical information about your system. This can provide valuable insights.

• Suspicious behavior--It is more common to use the audit trail to investigate suspicious activities that have occurred in the database.

Depending on the reason for auditing, different guidelines should be followed. In this section you will learn how these different types of audits are handled.

Auditing for Informational Purposes

If you are auditing for historical information, you should determine which database activities will provide the most useful information for the audit. Because you are auditing for information only, decide which events are of interest to your audit and which events are not. Once you have compiled a list of pertinent activities, only those activities should be audited. Occasionally, you should extract the important information and purge the audit logs. In this way, you can keep down the size of the logs and still get the information you want.

Auditing for Suspicious Behavior

When auditing suspicious behavior, it is usually necessary to audit most database activity. If you limit the database activity that is being audited, you might miss some vital clue that can help you solve the mystery of the suspicious behavior. If you start out auditing all activity, you can reduce the auditing as you gather clues. When you have determined more information about the suspicious behavior, you can reduce the number of activities being audited.

Also, note that when auditing suspicious behavior it is necessary to protect the audit logs. If this undesirable behavior is intentional, the perpetrator might try to cover up his or her tracks by removing information from the audit trail. How to protect the audit trail is described later today in the section titled "Protecting the Audit Trail."

Creating the Audit Trail

To enable the audit trail, first you must run the administration SQL script CATAUDIT.SQL. This script is located in the directory OrantRdbms80Admin. This administrative SQL script will create a number of views into the audit tables; each view shows a different perspective of the tables. The CATAUDIT.SQL script should be run by the SYS user.

To remove the audit trail views when they are no longer needed, use the administrative SQL script CATNOAUD.SQL. This will remove the views and disable auditing.

The Format of the Audit Trail

Each entry in the audit trail contains a wealth of information about the event in question:

• Username

• Session identifier

• Terminal identifier

• Object being accessed

• Operation performed or attempted

• Completion code

• Date and time stamp

For some auditing events, this might be all the information that is provided. Other auditing events might provide more information than this.

Enabling Auditing

The audit trail contains a variety of information depending on how the system is configured. Auditing of particular functions and users is enabled with the AUDIT statement. Auditing is disabled using the NOAUDIT statement. By default, some operations are automatically audited. These operations include

• Instance startup

• Instance shutdown

• All connections to Oracle with SYSOPER or SYSDBA privileges

All other auditing information is enabled by specifying them individually using the AUDIT command. Auditing can be set on three different levels:

• Statements--Audit on particular SQL statements.

• Privileges--Audit on SQL statements that require a particular system privilege.

• Objects--Audit on statements that involve a particular table or other schema object.

Auditing Statements and Privileges

To audit SQL statements you should use the following syntax with the AUDIT SQL statement:

AUDIT
SQL_STATEMENT_OPTIONS or PRIVILEGE
[, SQL_STATEMENT_OPTIONS or PRIVILEGE ...]
[BY user_name [, user_name]...]
[BY SESSION or BY ACCESS]
[WHENEVER SUCCESSFUL or WHENEVER NOT SUCCESSFUL]

This statement is all that is necessary to enable auditing. The explanations of the parameters are as follows:

• SQL_STATEMENT_OPTIONS--With the audit command there are certain keywords, called statement options, that are used to indicate auditing of associated SQL statements. For example, the statement option CLUSTER indicates auditing on the CREATE CLUSTER, ALTER CLUSTER, DROP CLUSTER, and TRUNCATE CLUSTER SQL statements. A complete list of the statement options is given in the Oracle documentation.

• PRIVILEGE--Indicates that SQL statements authorized by this system privilege are to be audited. A complete list of system privileges is given in Appendix C, "Oracle Roles and Privileges."

• BYuser_name--Indicates that the SQL statements issued by this user or users will be audited. If omitted, the SQL statements for all users will be audited.

• BY SESSION--Causes Oracle auditing to write only one record for each SQL statement issued in a session, so duplicate SQL statements are not logged.

• BY ACCESS--The opposite of BY SESSION. Causes an audit record to be written for each SQL statement issued.

• WHENEVERE SUCCESSFU--Causes an audit record to be written only when the SQL statement was successful. If omitted, all SQL statements are audited regardless of success.

• WHENEVER NOT SUCCESSFUL --Causes an audit record to be written only when the SQL statement was unsuccessful. If omitted, all SQL statements are audited regardless of success.

Besides auditing particular SQL statements by statement or privilege, you may also audit schema objects, as shown in the next section.

Auditing Objects

Auditing schema objects is very similar to auditing SQL statements. You can also audit access to particular schema objects. The syntax used to audit objects is very similar to the syntax used to audit SQL statements, as shown here:

AUDIT
Object_Option [, Object_Option ...]
ON [schema.]object] or ON DIRECTORY dir_name or ON DEFAULT
[BY SESSION or BY ACCESS]
[WHENEVER SUCCESSFUL or WHENEVER NOT SUCCESSFUL]

This statement is all that is necessary to enable auditing. Following are the explanations of the parameters:

• Object_Option--Specifies the type of operation that is audited. These keywords are SQL commands such as ALTER, DELETE, SELECT, and so on, and are described in detail in the Oracle documentation. One of the object options is ALL, specifying that all operations are to be audited.

• ON schema.]object--Specifies the schema or schema object that will be audited.

• ON DIRECTOR dir_name--Specifies the directory that is being audited.

• ON DEFAULT--Specifies that objects that have not even been created will be audited with the same auditing options as other objects.

• BY SESSION--Causes Oracle auditing to write only one record for each SQL statement issued in a session, so duplicate SQL statements are not logged.

• BY ACCESS--The opposite of BY SESSION. Causes an audit record to be written for each SQL statement issued.

• WHENEVER SUCCESSFUL--Causes an audit record to be written only when the SQL statement was successful. If omitted, all SQL statements are audited regardless of success.

• WHENEVER NOT SUCCESSFUL --Causes an audit record to be written only when the SQL statement was unsuccessful. If omitted, all SQL statements are audited regardless of whether it is successful or not.

This is the syntax necessary to perform auditing on schema objects.

---

TIP: If you audit WHENEVER NOT SUCCESSFUL, a record is kept of unsuccessful attempts at various operations. If you suspect that a user is trying to tamper with the system, this information can be very useful.

---

Protecting the Audit Trail

It is important that you protect the audit trail if you suspect unusual behavior. In this manner, if there is malicious behavior going on, the party or parties involved cannot cover their tracks. You protect the audit trail with the following two steps:

1. Limit the DELETE ANY TABLE system privilege to the database administrators only. This keeps other users from being able to delete the audit trail.

2. Enable auditing on the audit trail itself. You do this with the following SQL statement:

AUDIT INSERT, UPDATE, DELETE
ON sys.aud$
BY ACCESS;

This will cause all access to the audit trail to be audited, thus indicating whether someone is covering up something.

Viewing Audit Information

As mentioned in the beginning of this lesson, there are a number of views into the audit trail created by the CATAUDIT.SQL administrative script. You can select these views to provide useful auditing information. Here is a description of the most relevant of the views:

• [USER or DBA]_AUDIT_OBJECT--Audit trail entries for objects that are audited.

• [USER or DBA]_AUDIT_SESSION--Audit entries for CONNECT and DISCONNECT operations.

• [USER or DBA]_AUDIT_STATEMENT--The audit trail concerning GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM commands.

• [USER or DBA]_AUDIT_TRAIL--All audit trail entries.

By selecting from these views, you can retrieve information about the audit trails. The specific information in the audit trails may vary, but all of them contain the same basic information:

• Username

• Session identifier

• Terminal identifier

• Object being accessed

• Operation performed or attempted

• Completion code

• Date and time stamp

The auditing information can be useful, but should be used with care to avoid consuming excess system resources. Using auditing as necessary and selectively can be very enlightening.

---

WARNING: Because of the overhead auditing adds to the system, it should be used sparingly. Only audit the events that are necessary and only audit when necessary.

---

Summary

In today's lesson you have learned about managing job queues and the Oracle auditing facility. Oracle job queues are used to divert the execution of an operation to a later time, thus allowing you to schedule jobs. By using the Oracle job-queuing facility, you can easily schedule jobs to be run at various times or on a regular schedule. This allows you to automate many of the repetitive tasks that need to be done. You have learned how to schedule jobs using both the Enterprise Manager and the stored procedures in the DBMS_JOB package.

The Oracle auditing facility is used to log various database operations such as when they occurred and who performed that operation. Auditing is important if one or more incidences have occurred to make you suspicious of the activity of one or more users. Through auditing, a record will be made of this activity that can be used in tracking down the party at fault. Auditing is also useful to periodically monitor the activity of the system and to allow you to make adjustments to improve performance or usability of the system.

What's Next?

In tomorrow's lesson, "Understanding Effective Backup Techniques," you will begin to learn about one of the most important tasks the DBA is charged with: backup and recovery. In the next three days you will learn various techniques for putting together a backup and recovery plan and various methods of quick recovery and fault tolerance. This is one of my favorite topics, and I hope you will enjoy it too.

Q&A

Q What is the job queue used for?

A The Oracle job-queuing mechanism is used to defer the execution of database operations or to run operations in the background.

Q Who can use the job queues?

A Any user can use the job queues, but use of the Oracle Enterprise Manager is usually limited to the database administrators.

Q What is auditing used for?

A Auditing is used either to characterize database activity or to find the source of suspicious activity.

Q Who uses auditing?

A Auditing is limited to database administrators. There is no reason for users to need to use the auditing facilities.

Workshop

The workshop provides quiz questions to help you solidify your understanding of the material covered and exercises to provide you with experience in using what you've learned. Find answers to the quiz questions in Appendix A, "Answers."

Quiz

1. What is the job-scheduling facility used for?

2. What kind of jobs can be scheduled?

3. Can only database operations be scheduled?

4. Should a new user account be created for job queuing?

5. What NT user right needs to be created for this user account?

6. Can jobs be run on remote systems?

7. What kinds of scheduling can be done?

8. What needs to be configured in the Enterprise Manager?

9. Why might you need to enable auditing?

10. What do you need to watch out for with auditing?

Exercises

1. Invoke the Enterprise Manager and look for any running jobs.

2. Create a test job using the Enterprise Manager. This job can be a simple select operation.

3. Schedule this job to run every week.

4. Delete this job from the job queue.

---

© Copyright, Macmillan Computer Publishing. All rights reserved.