Conventional and Direct Path Loads

This chapter describes SQL*Loader's conventional and direct path load methods. The following topics are covered:

• Data Loading Methods
• Conventional Path Load
• Direct Path Load
• Using Direct Path Load
• Optimizing Performance of Direct Path Loads
• Optimizing Direct Path Loads on Multiple-CPU Systems
• Avoiding Index Maintenance
• Direct Loads, Integrity Constraints, and Triggers
• Parallel Data Loading Models
• General Performance Improvement Hints

Data Loading Methods

SQL*Loader provides two methods for loading data:

• Conventional Path Load
• Direct Path Load

A conventional path load executes SQL INSERT statements to populate tables in an Oracle database. A direct path load eliminates much of the Oracle database overhead by formatting Oracle data blocks and writing the data blocks directly to the database files. A direct load does not compete with other users for database resources, so it can usually load data at near disk speed. Considerations inherent to direct path loads, such as restrictions, security, and backup implications, are discussed in this chapter.

The tables to be loaded must already exist in the database. SQL*Loader never creates tables. It loads existing tables that either already contain data or are empty.

The following privileges are required for a load:

• You must have INSERT privileges on the table to be loaded.
• You must have DELETE privileges on the table to be loaded, when using the REPLACE or TRUNCATE option to empty old data from the table before loading the new data in its place.

Figure 11-1 shows how conventional and direct path loads perform database writes.

Figure 11-1 Database Writes on SQL*Loader Direct Path and Conventional Path

Description of "Figure 11-1 Database Writes on SQL*Loader Direct Path and Conventional Path"

Conventional Path Load

Conventional path load (the default) uses the SQL INSERT statement and a bind array buffer to load data into database tables. This method is used by all Oracle tools and applications.

When SQL*Loader performs a conventional path load, it competes equally with all other processes for buffer resources. This can slow the load significantly. Extra overhead is added as SQL statements are generated, passed to Oracle, and executed.

The Oracle database looks for partially filled blocks and attempts to fill them on each insert. Although appropriate during normal use, this can slow bulk loads dramatically.

Conventional Path Load of a Single Partition

By definition, a conventional path load uses SQL INSERT statements. During a conventional path load of a single partition, SQL*Loader uses the partition-extended syntax of the INSERT statement, which has the following form:

INSERT INTO TABLE T PARTITION (P) VALUES ... 

The SQL layer of the Oracle kernel determines if the row being inserted maps to the specified partition. If the row does not map to the partition, the row is rejected, and the SQL*Loader log file records an appropriate error message.

When to Use a Conventional Path Load

If load speed is most important to you, you should use direct path load because it is faster than conventional path load. However, certain restrictions on direct path loads may require you to use a conventional path load. You should use a conventional path load in the following situations:

• When accessing an indexed table concurrently with the load, or when applying inserts or updates to a non-indexed table concurrently with the load
  To use a direct path load (with the exception of parallel loads), SQL*Loader must have exclusive write access to the table and exclusive read/write access to any indexes.
• When loading data into a clustered table
  A direct path load does not support loading of clustered tables.
• When loading a relatively small number of rows into a large indexed table
  During a direct path load, the existing index is copied when it is merged with the new index keys. If the existing index is very large and the number of new keys is very small, then the index copy time can offset the time saved by a direct path load.
• When loading a relatively small number of rows into a large table with referential and column-check integrity constraints. 
  Because these constraints cannot be applied to rows loaded on the direct path, they are disabled for the duration of the load. Then they are applied to the whole table when the load completes. The costs could outweigh the savings for a very large table and a small number of new rows.
• When loading records and you want to ensure that a record is rejected under any of the following circumstances:
  • If the record, upon insertion, causes an Oracle error
  • If the record is formatted incorrectly, so that SQL*Loader cannot find field boundaries
  • If the record violates a constraint or tries to make a unique index non-unique.

Direct Path Load

Instead of filling a bind array buffer and passing it to the Oracle database with a SQL INSERT statement, a direct path load uses the direct path API to pass the data to be loaded to the load engine in the server. The load engine builds a column array structure from the data passed to it.

The direct path load engine uses the column array structure to format Oracle data blocks and build index keys. The newly formatted database blocks are written directly to the database (multiple blocks per I/O request using asynchronous writes if the host platform supports asynchronous I/O).

Internally, multiple buffers are used for the formatted blocks. While one buffer is being filled, one or more buffers are being written if asynchronous I/O is available on the host platform. Overlapping computation with I/O increases load performance

Data Conversion During Direct Path Loads

During a direct path load, data conversion occurs on the client side rather than on the server side. This means that NLS parameters in the initialization parameter file (server-side language handle) will not be used. To override this behavior, you can specify a format mask in the SQL*Loader control file that is equivalent to the setting of the NLS parameter in the initialization parameter file, or set the appropriate environment variable. For example, to specify a date format for a field, you can either set the date format in the SQL*Loader control file as shown in Example 11-1 or set an NLS_DATE_FORMAT environment variable as shown in Example 11-2.

Example 11-1 Setting the Date Format in the SQL*Loader Control File

LOAD DATA
INFILE 'data.dat'
INSERT INTO TABLE emp
FIELDS TERMINATED BY "|"
(
EMPNO NUMBER(4) NOT NULL,
ENAME CHAR(10),
JOB CHAR(9),
MGR NUMBER(4),
HIREDATE DATE 'YYYYMMDD',
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
)

Example 11-2 Setting an NLS_DATE_FORMAT Environment Variable

On UNIX Bourne or Korn shell:

% NLS_DATE_FORMAT='YYYYMMDD'
% export NLS_DATE_FORMAT

On UNIX csh:

%setenv NLS_DATE_FORMAT='YYYYMMDD'

Advantages of a Direct Path Load

A direct path load is faster than the conventional path for the following reasons:

• Partial blocks are not used, so no reads are needed to find them, and fewer writes are performed.
• SQL*Loader need not execute any SQL INSERT statements; therefore, the processing load on the Oracle database is reduced.
• A direct path load calls on Oracle to lock tables and indexes at the start of the load and releases them when the load is finished. A conventional path load calls Oracle once for each array of rows to process a SQL INSERT statement.
• A direct path load uses multiblock asynchronous I/O for writes to the database files.
• During a direct path load, processes perform their own write I/O, instead of using Oracle's buffer cache. This minimizes contention with other Oracle users.
• The sorted indexes option available during direct path loads enables you to presort data using high-performance sort routines that are native to your system or installation.
• When a table to be loaded is empty, the presorting option eliminates the sort and merge phases of index-building. The index is filled in as data arrives.
• Protection against instance failure does not require redo log file entries during direct path loads. Therefore, no time is required to log the load when:
  • The Oracle database has the SQL NOARCHIVELOG parameter enabled
  • The SQL*Loader UNRECOVERABLE clause is enabled
  • The object being loaded has the SQL NOLOGGING parameter set

Restrictions on Using Direct Path Loads

The following conditions must be satisfied for you to use the direct path load method:

• Tables are not clustered.
• Tables to be loaded do not have any active transactions pending.
  To check for this condition, use the Oracle Enterprise Manager command MONITOR TABLE to find the object ID for the tables you want to load. Then use the command MONITOR LOCK to see if there are any locks on the tables.
• For versions of the database prior to Oracle9i, you can perform a SQL*Loader direct path load only when the client and server are the same version. This also means that you cannot perform a direct path load of Oracle9i data into a database of an earlier version. For example, you cannot use direct path load to load data from a release 9.0.1 database into a release 8.1.7 database.
  Beginning with Oracle9i, you can perform a SQL*Loader direct path load when the client and server are different versions. However, both versions must be at least release 9.0.1 and the client version must be the same as or lower than the server version. For example, you can perform a direct path load from a release 9.0.1 database into a release 9.2 database. However, you cannot use direct path load to load data from a release 10.0.0 database into a release 9.2 database.
• Tables to be loaded in direct path mode do not have VPD policies active on INSERT.

The following features are not available with direct path load:

• Loading a parent table together with a child table
• Loading BFILE columns
• Use of CREATE SEQUENCE during the load. This is because in direct path loads there is no SQL being generated to fetch the next value since direct path does not generate INSERT statements.

When to Use a Direct Path Load

If none of the previous restrictions apply, you should use a direct path load when:

• You have a large amount of data to load quickly. A direct path load can quickly load and index large amounts of data. It can also load data into either an empty or nonempty table.
• You want to load data in parallel for maximum performance.

Integrity Constraints

All integrity constraints are enforced during direct path loads, although not necessarily at the same time. NOT NULL constraints are enforced during the load. Records that fail these constraints are rejected.

UNIQUE constraints are enforced both during and after the load. A record that violates a UNIQUE constraint is not rejected (the record is not available in memory when the constraint violation is detected).

Integrity constraints that depend on other rows or tables, such as referential constraints, are disabled before the direct path load and must be reenabled afterwards. If REENABLE is specified, SQL*Loader can reenable them automatically at the end of the load. When the constraints are reenabled, the entire table is checked. Any rows that fail this check are reported in the specified error log.

Field Defaults on the Direct Path

Default column specifications defined in the database are not available when you use direct path loading. Fields for which default values are desired must be specified with the DEFAULTIF clause. If a DEFAULTIF clause is not specified and the field is NULL, then a null value is inserted into the database.

Indexes Left in an Unusable State

SQL*Loader leaves indexes in an Index Unusable state when the data segment being loaded becomes more up-to-date than the index segments that index it.

Any SQL statement that tries to use an index that is in an Index Unusable state returns an error. The following conditions cause a direct path load to leave an index or a partition of a partitioned index in an Index Unusable state:

• SQL*Loader runs out of space for the index and cannot update the index.
• The data is not in the order specified by the SORTED INDEXES clause.
• There is an instance failure, or the Oracle shadow process fails while building the index.
• There are duplicate keys in a unique index.
• Data savepoints are being used, and the load fails or is terminated by a keyboard interrupt after a data savepoint occurred.

To determine if an index is in an Index Unusable state, you can execute a simple query:

SELECT INDEX_NAME, STATUS
   FROM USER_INDEXES 
   WHERE TABLE_NAME = 'tablename';

If you are not the owner of the table, then search ALL_INDEXES or DBA_INDEXES instead of USER_INDEXES.

To determine if an index partition is in an unusable state, you can execute the following query:

SELECT INDEX_NAME, 
       PARTITION_NAME,
       STATUS FROM USER_IND_PARTITIONS
       WHERE STATUS != 'VALID';

If you are not the owner of the table, then search ALL_IND_PARTITIONS and DBA_IND_PARTITIONS instead of USER_IND_PARTITIONS.

Oracle Exp/Imp Utility

Here are the main topics for this article:
  1.  What is the Import/ Export Utility ?
  2.  Which are the Import/ Export modes ?
  3.  Is it possible to exp/ imp to multiple files ?
  4.  How we can use exp/ imp when we have 2 different Oracle database versions?
  5. What I have to do before importing database objects ?
  6.  Is it possible to import a table in a different tablespace ?
  7.  In which cases imp/exp is used ?
  8.  How we can improve the EXP performance ?
  9.  How we can improve the IMP performance ?
10. Which are the EXP options ? 
11. Which are the IMP options ? 
12. Which are the common IMP/EXP problems ? 

1.  What is the Import/ Export Utility ?

Export (exp), Import (imp) are Oracle utilities which allow you to write data in an ORACLE-binary format from the database into operating system files and to read data back from those operating system files.

2.  Which are the Import/ Export modes ?

a) Full export/export

       The EXP_FULL_DATABASE & IMP_FULL_DATABASE, respectively, are needed to perform a full export. Use the full export parameter for a full export. 

b) TablespaceUse the tablespaces export parameter for a tablespace export.

c) User
This mode can be used to export and import all objects that belong to a user. Use the owner export parameter and the fromuser import parameter for a user (owner) export-import. 

d) TableSpecific tables (or partitions) can be exported/imported with table export mode. Use the tables export parameter for a table export/ import mode. 

3. Is it possible to exp/ imp to multiple files ?

Yes, is possible. Here is an example:  

exp SCOTT/TIGER FILE=C:\backup\File1.dmp,C:\backup\File2.dmp LOG=C:\backup\scott.log

4.  How we can use exp/ imp when we have 2 different Oracle database versions?

• exp must be of the lower version
• imp must match the target version

5. What I have to do before importing database objects ?

Before importing database objects, we have to drop or truncate the objects, if not, the data will be added to the objects. If the sequences are not dropped, the sequences will generate inconsistent values.  If there are any constraints on the target table, the constraints should be disabled during the import andenabled after import.
  

6.  Is it possible to import a table in a different tablespace ?

By default, NO. Because is no tablespace parameter for the import operation.

However this could be done in the following manner:

• (re)create the table in another tablespace (the table will be empty)
• import the table using INDEXFILE parameter (the import is not done, but a file which contains the indexes creation is generated)
• modify this script to create the indexes in the tablespace we want
• import the table using IGNORE=y option (because the table exists)
• recreate the indexes

Here is an example of INDEXFILE:

7.  In which cases imp/exp is used ?

• Eliminate database fragmentation
• Schema refresh (move the schema from one database to another)
• Detect database corruption. Ensure that all the data can be read (if the data can be read that means there is no block corruption)
• Transporting tablespaces between databases
• Backup database objects

8.  How we can improve the EXP performance ?

• Set the BUFFER parameter to a high value (e.g. 2M)
• If you run multiple export sessions, ensure they write to different physical disks. 

9.  How we can improve the IMP performance ?

• Import the table using INDEXFILE parameter (the import is not done, but a file which contains the indexes creation is generated), import the data and recreate the indexes
• Store the dump file to be imported on a separate physical disk from the oracle data files
• If there are any constraints on the target table, the constraints should be disabled during the import and enabled after import
• Set the BUFFER parameter to a high value (ex. BUFFER=30000000 (~30MB)  ) and COMMIT =y  or set COMMIT=n (is the default behavior: import commits after each table is loaded, however, this use a lot of the rollback segments or undo space for huge tables.)
• use the direct path to import the data (DIRECT=y)
• (if possible) Increase DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior to 9i) considerably in the init.ora file
• (if possible) Set the LOG_BUFFER to a big value and restart oracle.

10.  Which are the EXP options ? 

EXP Option	Default value	Description
buffer		Specifies the size, in bytes, of the buffer (array) used to insert the data
compress	N	When “Y”, export will mark the table to be loaded as one extent for the import utility.  If “N”, the current storage options defined for the table will be used.  Although this option is only implemented on import, it can only be specified on export.
consistent	N	Specifies the set transaction read only statement for export, ensuring data consistency.  This option should be set to “Y” if activity is anticipated while the exp command is executing.  If ‘Y’ is set, confirm that there is sufficient undo segment space to avoid the export session getting the ORA-1555 Snapshot too old error.
constraints	Y	Specifies whether table constraints should be exported with table data.
direct	N	Determines whether to use direct or conventional path export.  Direct path exports bypass the SQL command, thereby enhancing performance.
feedback	0	Determines how often feedback is displayed.  A value of feedback=n displays a dot for every n rows processed.  The display shows all tables exported not individual ones.
file		The name of the export file. Multiple files can be listed, separated by commas.  When export fills thefilesize, it will begin writing to the next file in the list.
filesize		The maximum file size, specified in bytes.
flashback_scn		The system change number (SCN) that export uses to enable flashback.
flashback_time		Export will discover the SCN that is closest to the specified time.  This SCN is used to enable flashback.
full		The entire database is exported.
grants	Y	Specifies object grants to export.
help		Shows command line options for export.
indexes	Y	Determines whether index definitions are exported.  The index data is never exported.
log		The filename used by export to write messages.
object_consistent	N	Specifies whether export uses SET TRANSACTION READ ONLY to ensure that the data being exported is consistent.
owner		Only the owner’s objects will be exported.
parfile		The name of the file that contains the export parameter options.  This file can be used instead of specifying all the options on the command line for each export.
query		Allows a subset of rows from a table to be exported, based on a SQL where clause.
recordlength		Specifies the length of the file record in bytes.  This parameter affects the amount of data that accumulates before it is written to disk.  If not specified, this parameter defaults to the value specific to that platform.  The highest value is 64KB.
resumable	N	Enables and disables resumable space allocation.  When “Y”, the parameters resumable_name andresumable_timeout are utilized.
resumable_name		User defined string that helps identify a resumable statement that has been suspended.  This parameter is ignored unless resumable = Y.
resumable_timeout	2h	The time period in which an export error must be fixed.  This parameter is ignored unless resumable = Y.
rows	Y	Indicates whether or not the table rows should be exported.
statistics	ESTIMATE	Indicates the level of statistics generated when the data is imported.  Other options include COMPUTE and NONE.
tables		Indicates that the type of export is table-mode and lists the tables to be exported.  Table partitions and sub partitions can also be specified.
tablespaces		Indicates that the type of export is tablespace-mode, in which all tables assigned to the listed tablespaces will be exported.  This option requires the EXP_FULL_DATABASE role.
transport_tablespace	N	Enables the export of metadata needed for transportable tablespaces.
triggers	Y	Indicates whether triggers defined on export tables will also be exported.
tts_full_check	FALSE	When TRUE, export will verify that when creating a transportable tablespace, a consistent set of objects is exported.
userid		Specifies the userid/password of the user performing the export.
volsize		Specifies the maximum number of bytes in an export file on each tape volume.

Example: exp system/s              file=C:\emp.dmp tables=scott.emp log=C:\emp.log   (Windows)

      or      exp userid=system/s file=C:\emp.dmp tables=scott.emp log=C:\emp.log   (Windows)

USERID must be the first parameter on the command line.

11.  Which are the IMP options ?

IMP Option	Default value	Description
buffer		Specifies the size, in bytes, of the buffer (array) used to insert the data
commit	N	Specifies whether import should commit after each array insert. By default, import commits after each table is loaded, however, this use a lot of the rollback segments or undo space for huge tables.
compile	Y	Tells import to compile procedural objects when they are imported.
constraints	Y	Specifies whether table constraints should also be imported with table data.
datafiles (only with transport_tablespace)		This parameter lists data files to be transported to the database.
destroy	N	Overwrite tablespace data file
feedback	0	Determines how often feedback is displayed. A value of feedback=100 displays a dot for every 100 rows processed. This option applies to the total tables imported, not individual ones. Another way to measure the number of rows that have been processed is to execute the following query while the import is active: SELECT rows_processed    FROM v$sqlarea    WHERE sql_text like 'INSERT %INTO "%'        AND command_type = 2        AND open_versions > 0;
file		The name of the export file to import. Multiple files can be listed, separated by commas. When export reaches the filesize it will begin writing to the next file in the list.
filesize		Maximum size of each dump file
fromuser		A comma delimited list of schemas from which to import. If the export file contains many users or even the entire database, the fromuser option enables only a subset of those objects (and data) to be imported.
full		The entire export file is imported.
grants	Y	Specifies to import object grants.
help		Shows command line options for importimp -help   or   imp help=y
ignore	N	Specifies how object creation errors should be handled. If a table already exists and ignore=y, then the rows are imported to the existing tables, otherwise errors will be reported and no rows are loaded into the table.
indexes	Y	Determines whether indexes are imported.
indexfile		Specifies a filename that contains index creation statements. This file can be used to build the indexes after the import has completed.
log		The filename used by import to write messages.
parfile		The name of the file that contains the import parameter options. This file can be used instead of specifying all the options on the command line.
recordlength		Specifies the length of the file record in bytes. This parameter is only used when transferring export files between operating systems that use different default values.
resumable	N	When “Y”, the parameters resumable_name and resumable_timeout are utilized.
resumable_name		User defined string that helps identify a resumable statement that has been suspended. This parameter is ignored unless resumable = Y.
resumable_timeout	2h	The time period in which an error must be fixed. This parameter is ignored unless resumable=Y.
rows	Y	Indicates whether or not the table rows should be imported.
show	N	When show=y, the DDL within the export file is displayed.
skip_unusable_indexes	N	Determines whether import skips the building of indexes that are in an unusable state.
statistics	ALWAYS	Determines the level of optimizer statistics that are generated on import. The options include ALWAYS, NONE, SAFE and RECALCULATE. ALWAYS imports statistics regardless of their validity. NONE does not import or recalculate any optimizer statistics. SAFE will import the statistics if they appear to be valid, otherwise they will be recomputed after import. RECALCULATE always generates new statistics after import.
streams_configuration	Y	Determines whether or not any streams metadata present in the export file will be imported.
streams_instantiation	N	Specifies whether or not to import streams instantiation metadata present in the export file
tables		Indicates that the type of export is table-mode and lists the tables to be exported. Table partitions and sub partitions can also be specified.
tablespaces		When transport_tablespace=y, this parameter provides a list of tablespaces.
to_user		Specifies a list of user schemas that will be targets for imports.
transport_tablespace	N	When Y, transportable tablespace metadata will be imported from the export file.
tts_owners		When transport_tablespace=Y, this parameter lists the users who own the data in the transportable tablespace set.
userid		Specifies the userid/password of the user performing the import.

Example:  imp system/manager file=/APPS/x.dmp tables=x fromuser=cs touser=cs     (Unix)

   or          imp userid=system/manager file=/APPS/x.dmp tables=x fromuser=cs touser=cs   (Unix)

Note: USERID must be the first parameter on the command line.

12. Which are the common IMP/EXP problems?

• ORA-00001: Unique constraint ... violated - Perhaps you are importing duplicate rows. Use IGNORE=N to skip tables that already exist (imp will give an error if the object is re-created) or the table could be dropped/ truncated and re-imported if we need to do a table refresh..
• IMP-00015: Statement failed ... object already exists... - Use the IGNORE=Y import parameter to ignore these errors, but be careful as you might end up with duplicate rows.
• ORA-01555: Snapshot too old - Ask your users to STOP working while you are exporting or use parameter CONSISTENT=NO (However this option could create possible referential problems, because the tables are not exported from one snapshot in time).
• ORA-01562: Failed to extend rollback segment - Create bigger rollback segments or set parameter COMMIT=Y (with an appropriate BUFFER parameter ) while importing.

Difference between Rollback & Undo Segments

Till Oracle 8i the undo that was generated, used to be handled by the Rollback tablespace, which was dictionary managed. In this case we have to first create a Rollback tablespace, then (according to our needs) create rollback segments and assign it to the rollback tablespace.

Now, from Oracle 9i the new concept of undo tablespace is introduced, which helps the DBAs in the following ways:

1) It is locally managed.
2) The undo segments are created by oracle itself ( whereas in the case of rollback segments we create it).
3) The number of undo segments are generated by oracle itself.

The purpose of undo segment and rollback segment is the same except the creation and maintenance part. Both will do the same functionality,but in oracle 9i they made it simplify, because if rollback segments are used, we need to put them online in init.ora and needs to take care space management etc.

So in oracle 9i they introduced undo tablespaces on that we can create undo segments and space management will be take care by oracle managed files ,so its reduces the burden of DBA.

Security - Managing Audit Trails

Relocate the audit trail to a different tablespace and set up an automatic purge process to keep its size under control.

One of the most significant aspects of database security involves setting up auditing to record user activities. The very knowledge that a user’s actions are being recorded can act as a significant deterrent to prevent wrongdoers from committing malicious acts.

When auditing is enabled, the audit output is recorded in an audit trail, which is usually stored in the database in a table under the SYS schema called AUD$. It can also reside as files in the file system, and the files can optionally be stored in XML format. For more-precise control, the Fine Grained Auditing feature of Oracle Database 11g provides granular control of what to audit, based on a more detailed set of policies. Fine Grained Auditing audits are usually stored in another table, FGA_LOG$, under the SYS schema.

These various audit trails can quickly grow out of control when database activity increases. As audit trails grow, two main challenges must be addressed: 

1. Trails need to be kept to a manageable size (and old records purged) if they are to be used effectively in forensic analysis.
2. Because database-resident trails are typically stored in the SYSTEM tablespace, they can potentially fill it up—bringing the database to a halt. 

Fortunately, the new auditing features in Oracle Database 11g Release 2 can help address these challenges. These capabilities, implemented in a package called DBMS_AUDIT_MGMT, enable you to move audit trails from the SYSTEM tablespace to one of your choice.

The new auditing features also let you set up one-time and automated purge processes for each of your audit trail types. Historically, to purge an audit trail, you were generally forced to stop auditing (which may have required bouncing the database), truncate, and then restart auditing (and bouncing the database again).

In this article, you will learn how to use the new features in Oracle Database 11g Release 2 to manage your audit trails. 

Relocating the Audit Trail Tables

Let’s first examine how to relocate an audit trail from the default SYSTEM tablespace to a new one. In case you don’t already have a suitable target tablespace, the code below shows how to create one: 

create tablespace audit_trail_ts
datafile '+DATA'
size 500M
segment space management auto
/
 

For moving an audit trail to the new tablespace, Oracle Database 11g Release 2 provides a procedure in DBMS_AUDIT_MGMT called SET_AUDIT_TRAIL_LOCATION. Listing 1 shows how to move a “standard” audit trail, which is the Oracle Database audit recorded in the AUD$ table.

Code Listing 1: Relocating a standard audit trail 

begin
 dbms_audit_mgmt.set_audit_trail_location(
  audit_trail_type            => dbms_audit_mgmt.audit_trail_aud_std,
  audit_trail_location_value  => 'AUDIT_TRAIL_TS');
end;
/
 

This move operation can be performed even when the database is up and an audit trail is being written. The target tablespace (AUDIT_TRAIL_TS in this case) must be available and online. If the tablespace is not available, auditing will stop, also stopping the database in the process. You should therefore be very careful about where you create the tablespace. The location should be permanent (and not on a temporary file system such as /tmp), and the underlying hardware should be resilient against failures (using RAID-1, for example).

The procedure can also be used for Fine Grained Auditing audit trails. To move a Fine Grained Auditing audit trail, simply replace the value of the audit_trail_type parameter in Listing 1 with dbms_audit_mgmt.audit_trail_fga_std. If you want to move both the standard and Fine Grained Auditing audit trails to the new tablespace, use the dbms_audit.audit_trail_db_std value as the audit_trail_type parameter. 

Purging Old Data

Next, let’s examine how to purge audit trails. The audit management package includes a procedure that automatically performs the purge for you. But before you can actually use it, you must call a one-time initialization procedure—INIT_CLEANUP—to set up the audit management infrastructure. Listing 2 shows how to perform the initialization. 

Code Listing 2: Initializing cleanup of audit entries 

begin
  dbms_audit_mgmt.init_cleanup(
    audit_trail_type            => dbms_audit_mgmt.audit_trail_db_std,
    default_cleanup_interval    => 24 );
end;
 

The INIT_CLEANUP procedure takes two parameters, neither of which takes a default value: 

• audit_trail_type—designates the type of audit trail being initialized. For instance, audit_trail_aud_std indicates the standard database audit trail (the AUD$ table). Table 1 lists the possible values for this parameter and the audit trail types they represent.
• default_cleanup_interval—designates the default interval in hours between executions of automatic purge jobs (to be discussed later in this article).

Parameter	Description
audit_trail_aud_std	The standard AUD$ audit trail in the database
audit_trail_fga_std	The FGA_LOG$ table, for Fine Grained Auditing
audit_trail_db_std	Both standard and FGA audit trails
audit_trail_os	The OS audit trail
audit_trail_xml	The XML audit trail
audit_trail_files	Both OS and XML audit trails
audit_trail_all	All of the above

 Table 1: Types of audit trails for audit_trail_type

In addition to setting the default cleanup frequency, the INIT_CLEANUP procedure moves the audit trail out of the SYSTEM tablespace. If the FGA_LOG$ and AUD$ tables are in the SYSTEM tablespace, the procedure will move them to the SYSAUX tablespace. Needless to say, you should ensure that the SYSAUX tablespace has sufficient space to hold both of these tables. The process of moving data from one tablespace to the other can have an impact on performance, so you should avoid calling the procedure during peak hours.

If you have already relocated these two tables to another tablespace (as described in the previous section), they will stay in the new location and the procedure will execute much more quickly.

After calling the initialization procedure, you can perform the actual audit trail cleanup, but you likely wouldn’t just remove an audit trail blindly. In most cases, you would archive the trail first before performing a permanent purge. When doing so, you can call another procedure—SET_LAST_ARCHIVE_TIMESTAMP—to let the purge process know the time stamp up to which an audit trail has been archived. This procedure accepts three parameters: 

• audit_trail_type—the type of audit trail you are about to purge.
• last_archive_time—the last time the audit trail was archived for this type.
• rac_instance_number—with an Oracle Real Application Clusters (Oracle RAC) database, OS audit trail files exist on more than one server. It’s possible to archive these files at different times, so this parameter tells the purge process the archive time of each node (or instance number) of the cluster. This parameter is applicable to Oracle RAC databases only; it has no significance for single-instance databases. Furthermore, this parameter is irrelevant for database audit trails, because they are common to all Oracle RAC instances. 

After you set the archive time stamp, you can check its value from a data dictionary view, DBA_AUDIT_MGMT_LAST_ARCH_TS. Listing 3 shows how to set the cutoff time stamp to September 30, 2009 at 10 a.m. and subsequently check its value from the view.

Code Listing 3: Setting the last archived time 

begin
   dbms_audit_mgmt.set_last_archive_timestamp(
     audit_trail_type  => dbms_audit_mgmt.audit_trail_aud_std,
     last_archive_time => 
        to_timestamp('2009-09-30 10:00:00','YYYY-MM-DD HH24:MI:SS'),
     rac_instance_number  => null
   );
end;
/

SQL> select * from DBA_AUDIT_MGMT_LAST_ARCH_TS;

AUDIT_TRAIL           RAC_INSTANCE
——————————————————————————————————
LAST_ARCHIVE_TS
——————————————————————————————————
STANDARD AUDIT TRAIL  0
30-SEP-09 10.00.00.000000 AM +00:00
 

Now you can execute the purge. To do so, run the code shown in Listing 4. The CLEAN_AUDIT_TRAIL procedure in the listing accepts two parameters. The first one is audit_trail_type. The second parameter—use_last_arch_timestamp—specifies whether the purge should be performed, depending on the last archive time stamp. If the parameter is set to TRUE (the default), the purge will delete the records generated before the time stamp (September 30, 2009 at 10 a.m. in this case). If it is set to FALSE, all audit trail records will be deleted.

Code Listing 4: Purging a standard database audit trail 

begin
  dbms_audit_mgmt.clean_audit_trail(
   audit_trail_type        =>  dbms_audit_mgmt.audit_trail_aud_std,
   use_last_arch_timestamp => TRUE
  );
end;
/
 

This same procedure is also used to purge file-based audit trails such as OS file audit trails and XML trails. To purge those trails, just specify the appropriate value for the audit_trail_type parameter (as shown in Table 1). However, note that for file-based audit trails, only the files in the current audit directory (as specified by the audit_file_dest initialization parameter) will be deleted. If you have audit trail files in a different directory from the one specified in audit_file_dest, those files will not be deleted.

Note that in Microsoft Windows, audit trails are entries in Windows Event Viewer and not actual OS files. So purging OS-based audit trails on that platform will not delete the trails. 

Setting Up Automatic Purge

The foregoing process is good for a one-time purge of audit trails. To ensure that audit trails do not overwhelm their tablespace, you may want to institute an automatic purge mechanism. The DBMS_AUDIT_MGMT package has another procedure—CREATE_PURGE_JOB—to do just that. This procedure takes four parameters: 

• audit_trail_type—the type of the audit trail
• audit_trail_purge_interval—the duration, in hours, between executions of the purge process
• audit_trail_purge_name—the name you assign to this job
• use_last_arch_timestamp—an indication of whether the job should delete audit trail records marked as archived. The default is TRUE. If the parameter is set to FALSE, the procedure will delete the entire trail. 

Listing 5 shows how to create a purge job that deletes standard audit trail records every 24 hours. As with one-time purges, you can create different jobs for each type of trail—such as standard, Fine Grained Auditing, OS files, and XML—simply by specifying different values for audit_trail_type when calling CREATE_PURGE_JOB. You can even set different purge intervals for each audit trail type to suit your archival needs. For instance, you can use a simple database-link-based script to pull database audit trail records to a different database while using a third-party tool to pull the OS audit trails. The execution time of each approach may be different, causing the database records to be pulled every day while the OS files are being pulled every hour. As a result, you might schedule purge jobs with an interval of 24 hours for database-based trails and with an interval of one hour for OS-file-based trails.

Code Listing 5: Creating a purge job for a standard audit trail 

begin
   dbms_audit_mgmt.create_purge_job (
   audit_trail_type            => dbms_audit_mgmt.audit_trail_aud_std,
   audit_trail_purge_interval  => 24,
   audit_trail_purge_name      => 'std_audit_trail_purge_job',
   use_last_arch_timestamp     => TRUE
   );
end;
/
 

You can view information about automatic purge jobs by accessing the DBA_AUDIT_MGMT_CLEANUP_JOBS data dictionary view. It shows all the important attributes of the job, such as the name, the type of audit trail being cleaned, and the frequency. 

Setting Audit Trail Properties

Next Steps  READ more about  the audit management package database security

When setting up a purge job, you should always remember one very important fact. It performs a DELETE operation—not TRUNCATE—on database-based trails, so the purge operation generates redo and undo records, which may be quite significant, depending on the number of trail records deleted. A large deletion can potentially fill up the undo tablespace. To reduce the redo size of a transaction, the purge job deletes in batches of 1,000 and performs commits between them. If the database is very large, it may be able to handle much more redo easily. You can change the delete batch size by using the SET_AUDIT_TRAIL_PROPERTY procedure. Listing 6 shows how to set the delete batch size to 100,000. 

Code Listing 6: Setting the deletion batch size 

begin
 dbms_audit_mgmt.set_audit_trail_property(
  audit_trail_type            => dbms_audit_mgmt.audit_trail_aud_std,
  audit_trail_property        => dbms_audit_mgmt.db_delete_batch_size,
  audit_trail_property_value  => 100000);
end;
/
 

In addition to the db_delete_batch_size property referenced in Listing 6, you can use SET_AUDIT_TRAIL_PROPERTY to set several other important properties. They include the following: 

• file_delete_batch_size specifies how many OS audit trail files will be deleted by the purge job in one batch.
• cleanup_interval specifies the default interval, in hours, between executions of a purge job.
• os_file_max_age specifies how many days an OS file or an XML file can be left open before a new file is created.
• os_file_max_size specifies the maximum size of an audit trail file (in kilobytes). 

To find the current value of a property, you can check the data dictionary view DBA_AUDIT_MGMT_CONFIG_PARAMS. 

Conclusion

Audit trails establish accountability. In Oracle Database 11g, there are several types of audit trails—standard, fine-grained, OS-file-based, and XML. In this article, you learned how to relocate a database-based audit trail from its default tablespace—SYSTEM—to another one designated only for audit trails. You also learned how to purge audit trails of various types to keep them within a manageable limit, and you finished by establishing an automatic purge process.