Initializing Processes

Oracle database background processes are initialized upon database instance startup. When the database instance is started up, the initialization parameter file is read. Thus, there are many parameters that may be set and readjusted in the parameter file that allow the DBA to control the processes that are used by a database instance. These processes are initialized through parameters such as the following:

PROCESSES

MTS_SERVERS

MTS_MAX_SERVERS

PARALLEL_MAX_SERVERS

MTS_DISPATCHERS

MTS_LISTENER_ADDRESS

MTS_SERVICE

MTS_MAX_DISPATCHERS\

CHECKPOINT_PROCESS

LOG_ARCHIVE_START

JOB_QUEUE_PROCESSES

Scheduling Batch Processes

Batch processing is typically as integral to Oracle processing as both user and system processing, particularly in environments such as online analytical processing (OLAP) systems. Batch processes are used to load data in batch, normally extracting data from one or more foreign databases or file systems and porting it into an Oracle OLAP database system. Batch processes are typically resource-intensive, and although they’re necessary to run, they should be scheduled around user processing. Usually, batch processes are scheduled to run during off-peak hours when very few or no users require access to the database. Batch processes should be monitored by the DBA, who should measure the required resources.

Auditing

One of the most prevailing fears of any taxpayer is being audited by the IRS. Theoretically, if you play by the rules you have nothing to worry about. Of course, filing a return can turn into a complex task for some people, and there will sometimes be errors as numbers are continuously juggled. In cases like this, the IRS may step in and offer “corrective action,” which may unfortunately involve penalties whether or not the error was intentional.

Auditing in an Oracle database works very similarly. Think of the database users as the taxpayers and the DBA as the IRS. The DBA has the option of enabling auditing in the database, and can choose to audit all database actions or just specific options.

Once activated, auditing may be left on all the time or turned on and used periodically—sort of a spot check, like that of the IRS. If malicious activity is suspected, the DBA may decide to turn on auditing. Upon acquiring evidence, action may be taken to reprimand the offending user. The IRS does not audit every individual, because it costs too much. Likewise, auditing in a database has costs as well.

When auditing is turned on, Oracle creates an audit trail, which is simply a table in the data dictionary. Later, we will discuss the data dictionary views that must be created, which are associated with the audit trail, its management, and the costs involved. For every audited action that occurs in the database, Oracle must make an entry in the audit trail. Because this will be discussed in more detail later in this chapter, you should note that there are costs incurred with auditing.

Typical reasons for database auditing include

•  Pinpointing misuse of user IDs/passwords

•  Pinpointing malicious activity

•  Measuring employees’ workload

•  Monitoring database changes on specific objects

•  Monitoring activities executed by privileged accounts

The following sections discuss auditing in further detail, beginning with the types of auditing. The various auditing options are also discussed, leading into enabling and disabling auditing. Finally, we discuss the overall management of the audit trail and show some examples.

Enabling and Disabling Auditing

Auditing must first be enabled before database actions can be audited and written to the audit trail. The database initialization parameter used to enable auditing is the AUDIT_TRAIL parameter. The options available with this parameter are as follows:

The DB option is typically used with Oracle8 to record audit trail information in the audit trail table, which resides in the data dictionary. In order for this parameter to take effect, the database instance must be shut down and restarted.

The Operating System Audit Trail

By default, Oracle audits database instance startup, shutdown, and any connections made to the database with administrative privileges, such as with SYSOPER and SYSDBA. Audit records of this nature are typically written to the operating system audit trail because the database is often unavailable when these actions occur. Setting the AUDIT_TRAIL parameter to OS will cause all audit records to be written to the operating system audit trail file.

The default location for the operating system audit trail is the same directory as the database alert log and background process trace file, which is designated by the database parameter BACKGROUND_DUMP_DEST. The use and location of the operating system audit trail is operating system-specific.

The Database Audit Trail

The database audit trail is composed of a single table that is stored in the data dictionary with the other system tables. The owner of the database audit trail is the user SYS. In addition to the audit trail table, there are a number of views that should be created when auditing is activated. These views contain categories of audit information and are much more user-friendly than a single table. The following sections describe the components of the database audit trail, and provide direction for creating the audit trail views and removing them.