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.
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.
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;
/
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
/
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;
/
Setting;Audit;trAil;ProPertieS
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_
PROPERT Y procedure. Listing 6 shows how to
set the delete batch size to 100,000.
In addition to the db_delete_batch_size
property referenced in Listing 6, you can
use SET_AUDIT_TRAIL_PROPERT Y to set
several other important properties. They
include the following:
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.
Arup Nanda (arup@
proligence.com) has
been an Oracle DBA
for more than 14 years,
handling all aspects of
database administration,
from performance tuning to security and
disaster recovery. He was Oracle Magazine’s
DBA of the Year in 2003.
NEXT STEPS
ConCluSion
Audit trails establish accountability. In Oracle
Database 11 g, 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
READ more about
the audit management package
download.oracle.com/docs/cd/E11882_01/
appdev.112/e10577/ d_audit_mgmt.htm
database security
oracle.com/technetwork/topics/security/
whatsnew
