Skip Headers

Oracle® Audit Vault Administrator's Guide Release 10.2.3.1 Part Number E13841-02 |
Home | Book List | Contents | Index | Contact Us

Previous | Next
| View PDF

# 4 Administering the Oracle Audit Vault Repository

This chapter contains:

- About the Administrative Tasks in This Chapter

- Monitoring the Audit Vault Server SYSAUX Tablespace Space Usage

- Monitoring Audit Vault Server Archive Log Disk Space Usage

- Monitoring the Audit Vault Server Flash Recovery Area

- Managing Oracle Audit Vault Backup and Recovery Operations

- Using a Collection Agent to Listen to Oracle Database RAC Nodes

- Configuring Collection Agent Connectivity for Oracle Database RAC

- Purging the Oracle Source Database Audit Trail Records

## 4.1 About the Administrative Tasks in This Chapter

This chapter describes important administrative tasks to perform on the Oracle Audit Vault system. These tasks are especially important if your audit data collectors are collecting high volumes of audit records and rapidly filling the default tablespace and disk space.

## 4.2 Monitoring the Audit Vault Server SYSAUX Tablespace Space Usage

The Oracle Audit Vault Server database contains the `SYSAUX` tablespace, which by default has one data file. The `SYSAUX` tablespace is a locally managed tablespace with automatic segment space management.

You should monitor the space usage for the `SYSAUX` tablespace and create additional data files for storage as needed. Remember that if you use the procedures in Section 4.8 to clean up the audit trail, the `SYSAUX` tablespace by default will store the audit trail.

See Oracle Database Administrator's Guide for more information about the `ALTER TABLESPACE` SQL statement, which you can use to add more storage data files. For information about optimizing a tablespace, see Oracle Database Performance Tuning Guide.

## 4.3 Monitoring Audit Vault Server Archive Log Disk Space Usage

By default, `ARCHIVELOG` mode is enabled in the Audit Vault Server database. The `ARCHIVELOG` mode copies filled online redo logs to disk. This enables you to back up the database while it is open and being accessed by users, and to recover the database to any desired point in time. You should monitor the disk space usage for the redo logs.

See Oracle Database Administrator's Guide for more information about changing the `LOG_ARCHIVE_DEST_``n` location to relocate these archive log files to larger disks. For information about backing up the archive logs, see Oracle Database Backup and Recovery Advanced User's Guide.

## 4.4 Monitoring the Audit Vault Server Flash Recovery Area

By default, the Audit Vault Server database has the following initialization parameter settings:

- The `DB_RECOVERY_FILE_DEST_SIZE` initialization parameter is set to 2 GB.

- The `DB_RECOVERY_FILE_DEST` initialization parameter is set to the default flash recovery area, typically the `ORACLE_HOME/flash_recovery_area` directory.

Ensure that the size of the flash recovery area is large enough to hold a copy of all data files, all incremental backups, online redo logs, archived redo logs not yet backed up on tape, control files, and control file auto backups. This space can fill up quickly, depending on the number of collectors configured, the scope of the audit record collection being administered, and the backup and archive plans that you have in place.

You can use Oracle Enterprise Manager Database Control to monitor the available space in the flash recovery area. Monitor the percent space that is usable in the Usable Flash Recovery Area field under the High Availability section on the Home page. Check the alert log in the Database Console for messages. When the used space in the flash recovery area reaches 85 percent, a warning message is sent to the alert log. When the used space in the flash recovery area reaches 97 percent, a critical warning message is sent to the alert log.

You can manage space in the flash recovery area by adjusting the retention policy for data files to keep fewer copies or reduce the number of days these files stay in the recovery window. Alternatively, increase the value of the `DB_RECOVERY_FILE_DEST_SIZE` initialization parameter to accommodate these files and to set the `DB_RECOVERY_FILE_DEST` initialization parameter to a value where more disk space is available. See Oracle Database Administrator's Guide and Oracle Database Backup and Recovery Basics for more information.

## 4.5 Managing Oracle Audit Vault Backup and Recovery Operations

When you back up Oracle Audit Vault, you must back up the database, the Audit Vault Server home, and the Audit Vault collection agent home.

See Also:

Oracle Database Backup and Recovery Basics for more information about backing up a database.

Backing Up the Database

After cleanly shutting down the instance following the analysis of the database, you should perform a full backup of the database. Complete the following steps:

1. Log in to Oracle Recovery Manager (RMAN):

       rman "target / nocatalog"

2. Issue the following `RMAN` commands:

       BACKUP DATABASE FORMAT 'some_backup_directory%U' TAG before_upgrade;
       BACKUP CURRENT CONTROLFILE TO 'save_controlfile_location';

Backing Up Audit Vault Server Home and Audit Vault Collection Agent Home

Back up or copy the Audit Vault Server home and the Audit Vault collection agent home to separate directories.

## 4.6 Using a Collection Agent to Listen to Oracle Database RAC Nodes

In an Oracle Real Application Clusters (Oracle RAC) environment, after you have configured the Audit Vault collection agent, the node on which the collection agent was installed has its listener set to listen only to that node. Thus, only that node can be specified to which to connect. However, you can configure the listener to listen to the other nodes.

For the OSAUD and DBAUD collectors, you must update the `tnsnames.ora` file during installation of the Audit Vault collection agents.

After you configure the collection agent, the `tnsnames.ora` file located in `$ORACLE_HOME/network/admin` has an alias similar to the following:

    AV =
      (DESCRIPTION =
         (ADDRESS = (PROTOCOL = TCP)(HOST = node01) (PORT = 1521))
         (CONNECT_DATA =
           (SERVICE_NAME = avsrv.example.com)))

For high availability, you may need to edit the Audit Vault collection agent home `tnsnames.ora` file after you have configured the collection agent, and then add the host and port of the other listeners.

For example:

    AV =
      (DESCRIPTION =
         (ADDRESS = (PROTOCOL = TCP)(HOST = node01)(PORT = 1521))
         (ADDRESS = (PROTOCOL = TCP)(HOST = node02)(PORT = 1521))
         (ADDRESS = (PROTOCOL = TCP)(HOST = node03)(PORT = 1521))
         (ADDRESS = (PROTOCOL = TCP)(HOST = node04)(PORT = 1521))
         (LOAD_BALANCE = yes)
         (CONNECT_DATA =
           (SERVER = DEDICATED)
             (SERVICE_NAME = avsrv.example.com)
         )
       )

For the REDO collector, you must log in using the source user account at the source database and then re-create the database link for `avsrv.example.com`. The new database link can either have a list of host and port numbers or point to a `tnsnames` entry with the list of host and port numbers.

## 4.7 Configuring Collection Agent Connectivity for Oracle Database RAC

When you add an Oracle source database to Oracle Audit Vault, you must provide the `host`:`port`:`service` information for the source database being added. This information is used for the following tasks from the collection agent:

- REDO collector: Starting and stopping the capture process on the source

- DBAUD collector: Retrieving rows from `AUD$` and `FGA_LOG$` tables

- Policy management: Retrieving source dictionary information

Typically, when the Oracle Database instance on the host goes down or if the host computer goes down, the connectivity to the source database from the Oracle Audit Vault collection agent is broken. Any attempt to perform these tasks is unsuccessful because this connection is not available:

You can do any or all of the following operations to make the connection between the source and the Audit Vault collection agent more highly available.

- In the Audit Vault collection agent home, update the tnsnames.ora file to include additional host or port information for the service. This file is located in the `$ORACLE_HOME/network/admin` directory. You can add options for load balancing and failure in the connect string. For additional information, see Oracle Database Net Services Administrator's Guide.

- Configure a listener on the Oracle RAC nodes to support connecting to remote nodes and configuring the Oracle Database to communicate with remote listeners. If the Oracle Database instance goes down, then the listener on the host can create connections on a different Oracle RAC node. For additional information, see Oracle Database Net Services Administrator's Guide.

- Provide host information using the virtual IP address of the node instead of the physical IP address. If the host computer goes down, then all traffic to the host is redirected to a different node.

## 4.8 Purging the Oracle Source Database Audit Trail Records

This section contains:

- General Steps for Purging the Oracle Database Audit Trail

- Step 1: Prepare the Oracle Database Audit Trail for Purging

- Step 2: Create a Job to Automatically Purge the Oracle Database Audit Trail

- Step 3: Optionally, Set a Record Batch Size for the Purge Operations

- Step 4: Perform Maintenance Tasks as Needed

### 4.8.1 General Steps for Purging the Oracle Database Audit Trail

An Oracle Database administrator (not necessarily an Oracle Audit Vault administrator) is responsible for purging audit data from the Oracle source database.

Follow these general steps to purge the Oracle Database audit trail records from an Oracle source database:

1. If necessary, tune online and archive redo log sizes to accommodate the additional records generated during the audit table purge process.

   For more information about tuning log files, see Oracle Database Performance Tuning Guide and Oracle Database Administrator's Guide.

2. Complete the preparatory steps described in Section 4.8.2.

   You must download and install the `DBMS_AUDIT_MGMT` PL/SQL package, which is available as a patch set from the OracleMetaLink Web site. After you install this package, you must move the database audit trail to a different tablespace before you can purge the audit trail.

3. Configure an automatic purge job by following the steps in Section 4.8.3.

4. After you configure the purge time for the automatic purge job and before the purge occurs, optionally configure the audit records for batch deletions. For very large audit trails, deleting the records in batches helps to speed the purge process. See Section 4.8.4.

5. Perform maintenance tasks as needed, as described in Section 4.8.5.

Note:

Oracle Database audits all deletions from the audit trail, without exception.

See Also:

- Chapter 14 for information about the DBMS_AUDIT_TRAIL PL/SQL package

- Chapter 13 for information about data dictionary views that you can use while completing these steps

### 4.8.2 Step 1: Prepare the Oracle Database Audit Trail for Purging

This section contains:

- Step 1A: Download the DBMS_AUDIT_MGMT Package

- Step 1B: Move the Database Audit Trail to a Different Tablespace

#### 4.8.2.1 Step 1A: Download the DBMS_AUDIT_MGMT Package

The `DBMS_AUDIT_MGMT` PL/SQL package enables you to perform the following tasks with the Oracle Database audit trail:

- Move the database audit trail from the `SYSTEM` tablespace to a different tablespace, such as the `SYSAUX` tablespace.

- Set the size and age of the operating system audit trail file before creating a new operating system audit trail file.

- Purge the audit trail records, either by manually purging the records or by creating a purge job.

The `DBMS_AUDIT_MGMT` PL/SQL package is available in a patch set. Check OracleMetaLink and the Oracle Audit Vault Release Notes for information about the specific Oracle Database versions you can use with this package.

The OracleMetaLink Web site is at

`https://metalink.oracle.com`

If you do not have a current Oracle Support Services contract, then you can access the same information at the following Web site:

`http://www.oracle.com/technology/support/metalink/content.html`

See the following sections for information about using the `DBMS_AUDIT_MGMT` package:

- Section 4.8 for general procedures for using the `DBMS_AUDIT_MGMT` package

- Chapter 14 for reference information on the DBMS_AUDIT_MGMT package

- Chapter 13 for information about the data dictionary views that accompany the `DBMS_AUDIT_MGMT` package

#### 4.8.2.2 Step 1B: Move the Database Audit Trail to a Different Tablespace

The `SYSTEM` tablespace stores the database audit trail `AUD$` and `FGA_LOG$` tables. When you initialize the purge process, by default Oracle Database moves the `AUD$` and `FGA_LOG$` tables to the `SYSAUX` tablespace. If you prefer to store these tables in a different tablespace, follow the procedures in this section.

Be aware that moving the database audit trail tables to a different tablespace can take a while, so you may want to do this during a time when database activity is slow.

To move the database audit trail from `SYSTEM` to a different tablespace:

1. Log in to SQL*Plus as an administrator who has the `EXECUTE` privilege on the `DBMS_AUDIT_MGMT` PL/SQL package.

   For more information about the `DBMS_AUDIT_MGMT` PL/SQL package, see Chapter 14.

2. Check the tablespace to which you want to move the database audit trail tables.

   You may need to optimize and allocate more space to this tablespace, including the `SYSAUX` auxiliary tablespace. For more information, see Oracle Database Performance Tuning Guide.

3. Run the `DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION` PL/SQL procedure to specify the name of the destination tablespace and move it to that tablespace.

   For example:

       BEGIN
        DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
         AUDIT_TRAIL_TYPE            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
         AUDIT_TRAIL_LOCATION_VALUE  => 'AUD_AUX');
       END;

   In this example:

   - `AUDIT_TRAIL_TYPE`: Refers to the database audit trail type. Enter one of the following values:

     - `DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD`: Refers to the standard audit trail table, `AUD$`.

     - `DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD`: Refers to the fine-grained audit trail table, `FGA_LOG$`.

     - `DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD`: Refers to both standard and fine-grained audit trail tables.

   - `AUDIT_TRAIL_LOCATION_VALUE`: Specifies the destination tablespace. This example specifies a tablespace named `AUD_AUX`.

### 4.8.3 Step 2: Create a Job to Automatically Purge the Oracle Database Audit Trail

The automatic purge job deletes all audit records that were created before the last recorded timestamp. Be aware that purging the audit trail, particularly a large one, can take a while to complete. Consider scheduling the purge job so that it runs during a time when the database is not busy.

To set up an automatic purge job:

- Step 2A: Ensure That the Collectors Are Enabled

- Step 2B: Initialize the Audit Trail Cleanup Operation

- Step 2C: Create the Purge Job

#### 4.8.3.1 Step 2A: Ensure That the Collectors Are Enabled

Ensure that the Oracle Audit Vault collectors are recording timestamps and archiving the audit trail records. See Section 2.9 to check the status of the collectors. To find the last recorded timestamp, query the `LAST_ARCHIVE_TS` column of the `DBA_AUDIT_MGMT_LAST_ARCH_TS` data dictionary view, described in Section 13.2. If the collectors are disabled, then this view shows the last recorded timestamp that occurred before the collector was disabled.

#### 4.8.3.2 Step 2B: Initialize the Audit Trail Cleanup Operation

Before you can purge the audit trail, you must initialize the audit trail cleanup operation. For the database audit trail, if you have not moved the database audit trail tables (`SYS.AUD$` and `SYS.FGA_LOG$`) from the `SYSTEM` tablespace to another tablespace, this process moves these tables to the `SYSAUX` tablespace or to the tablespace that you specified in Section 4.8.2.2. Be aware that moving these tables takes a while, so you may want to schedule the initialization process during time when the database is not busy.

To initialize the audit trail cleanup operation:

1. Log in to SQL*Plus as an administrative user who has the `EXECUTE` privilege on the `DBMS_AUDIT_MGMT` PL/SQL package.

2. Initialize the audit trail cleanup operation by running the `DBMS_AUDIT_MGMT.INIT_CLEANUP` procedure.

   For example:

       BEGIN
        DBMS_AUDIT_MGMT.INIT_CLEANUP(
         AUDIT_TRAIL_TYPE            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_AUD,
         DEFAULT_CLEANUP_INTERVAL    => 12 );
       END

   In this example:

   - `AUDIT_TRAIL_TYPE`: Enter one of the following values:

     - `DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD`: Standard audit trail table, `AUD$`

     - `DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD`: Fine-grained audit trail table, `FGA_LOG$`

     - `DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD`: Both standard and fine-grained audit trail tables

     - `DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS`: Operating system audit trail files with the `.aud` extension. (This setting does not apply to Windows event log entries.)

     - `DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML`: XML operating system audit trail files with the `.xml` extension

     - `DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES`: Both operating system and XML audit trail files

     - `DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL`: All audit trail records, that is, both database audit trail and operating system audit trail types

   - `DEFAULT_CLEANUP_INTERVAL`: Specify the desired default hourly purge interval (for example, `12` for every 12 hours). The `DBMS_AUDIT_MGMT` procedures use this value to determine how to purge audit records. The timing begins when you run the `DBMS_AUDIT_MGMT.INIT_CLEANUP` procedure. To update this value later, set the `DBMS_AUDIT_MGMT.CLEAN_UP_INTERVAL` property of the `DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY` procedure.

#### 4.8.3.3 Step 2C: Create the Purge Job

Create the purge job by running the `DBMS_AUDIT_MGMT.CREATE_PURGE_JOB` PL/SQL procedure.

For example:

    BEGIN
      DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
       AUDIT_TRAIL_TYPE            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_AUD,
       AUDIT_TRAIL_PURGE_INTERVAL  => 12,
       AUDIT_TRAIL_PURGE_NAME      => 'Standard_Audit_Trail_PJ',
       USE_LAST_ARCH_TIMESTAMP     => TRUE );
    END;

In this example:

- `AUDIT_TRAIL_TYPE`: Enter one of the following values:

  - `DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_AUD`: Standard audit trail table, `AUD$`

  - `DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD`: Fine-grained audit trail table, `FGA_LOG$`

  - `DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD`: Both standard and fine-grained audit trail tables

  - `DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS`: Operating system audit trail files with the `.aud` extension. (This setting does not apply to Windows event log entries.)

  - `DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML`: XML operating system audit trail files with the `.xml` extension

  - `DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES`: Both operating system and XML audit trail files

  - `DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL`: All audit trail records, that is, both database audit trail and operating system audit trail types

- `AUDIT_TRAIL_PURGE_INTERVAL`: Specify the hourly interval for this purge job to run. The timing begins when you run the `DBMS_AUDIT_MGMT.CREATE_PURGE_JOB` procedure, in this case, 12 hours after you run this procedure. Later on, if you want to update this value, run the `DBMS_AUDIT_MGMT.SET_PURGE_JOB_INTERVAL` procedure.

- `USE_LAST_ARCH_TIMESTAMP`: Enter either of the following settings:

  - `TRUE`: Deletes audit records created before the last archive timestamp. To check the last recorded timestamp, query the `LAST_ARCHIVE_TS` column of the `DBA_AUDIT_MGMT_LAST_ARCH_TS` data dictionary view, described in Section 13.2. The default value is `TRUE`.

  - `FALSE`: Deletes all audit records without considering the last archive timestamp.

### 4.8.4 Step 3: Optionally, Set a Record Batch Size for the Purge Operations

When Oracle Database purges records from the database audit trail, it deletes them in batched groups during the cleanup process. Before the purge occurs, you can set the number of records that best suits your environment. If the database audit trail is very large (and audit trails can grow quite large), deleting the records in groups facilitates the purge operation. To find the current batch size setting, query the `PARAMETER_NAME` and `PARAMETER_VALUE` columns of the `DBA_AUDIT_MGMT_CONFIG_PARAMS` data dictionary view, which is described in Section 13.1.

To set a record batch size, use the `DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY` procedure.

For example:

    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 this example:

- `AUDIT_TRAIL_TYPE`: Specifies the audit trail type, which in this case is the database system audit trail. Enter one of the following values:

  - `DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_AUD`: Standard audit trail table, `AUD$`.

  - `DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD`: Fine-grained audit trail table, `FGA_LOG$`.

- `AUDIT_TRAIL_PROPERTY`: Uses the `DBMS_AUDIT_MGMT.DB_DELETE_BATCH_SIZE` property to indicate the batch size setting. To find the status of the current property settings, query the `PARAMETER_NAME` and `PARAMETER_VALUE` columns of the `DBA_AUDIT_MGMT_CONFIG_PARAMS` data dictionary view.

- `AUDIT_TRAIL_PROPERTY_VALUE`: Sets the number of audit records to be 100,000 for each batch. Enter a value between 100 and 1000000. To determine this number, consider the total number of records being purged, and the time interval in which the purge operation is performed. The default is 10000.

### 4.8.5 Step 4: Perform Maintenance Tasks as Needed

This section contains:

- Verifying That the Audit Trail Is Initialized for Cleanup

- Enabling or Disabling an Audit Trail Purge Job

- Setting the Default Audit Trail Purge Interval for Any Audit Trail Type

- Setting the Default Audit Trail Purge Job Interval for a Specified Purge Job

- Clearing the Database Audit Trail Records Batch Size

- Canceling the Initialization Cleanup Settings

- Deleting an Audit Trail Purge Job

- Configuring Tracing Debug Levels for Purge Operations

- Setting the Size of the Operating System Audit Trail

- Setting the Age of the Operating System Audit Trail

#### 4.8.5.1 Verifying That the Audit Trail Is Initialized for Cleanup

You can check if the audit trail has been initialized for cleanup by running the `DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED` function. If the audit trail has been initialized, then this function returns `TRUE`. Otherwise, it returns `FALSE`.

For example:

    SET SERVEROUTPUT ON
    BEGIN
     IF
       DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD)
     THEN
       DBMS_OUTPUT.PUT_LINE('AUD$ is initialized for cleanup');
     ELSE
       DBMS_OUTPUT.PUT_LINE('AUD$ is not initialized for cleanup.');
     END IF;
    END;

In this example, the audit trail type is the database standard audit trail. To select a setting for a different audit trail, choose from the `AUDIT_TRAIL_TYPE` settings described in Step 2B: Initialize the Audit Trail Cleanup Operation.

#### 4.8.5.2 Enabling or Disabling an Audit Trail Purge Job

To enable or disable an audit trail purge job, use the `DBMS_AUDIT_MGMT.SET_PURGE_JOB_STATUS` PL/SQL procedure.

For example:

    BEGIN
     DBMS_AUDIT_MGMT.SET_PURGE_JOB_STATUS(
      AUDIT_TRAIL_PURGE_NAME      => 'OS_Audit_Trail_PJ',
      AUDIT_TRAIL_STATUS_VALUE    => DBMS_AUDIT_MGMT.PURGE_JOB_ENABLE);
    END

In this example:

- `AUDIT_TRAIL_PURGE_NAME`: Specifies a purge job called `OS_Audit_Trail_PJ`. To find existing purge jobs, query the `JOB_NAME` and `JOB_STATUS` columns of the `DBA_AUDIT_MGMT_CLEANUP_JOBS` data dictionary view.

- `AUDIT_TRAIL_STATUS_VALUE`: Enter one of the following properties:

  - `DBMS_AUDIT_MGMT.PURGE_JOB_ENABLE`: Enables the specified purge job.

  - `DBMS_AUDIT_MGMT.PURGE_JOB_DISABLE`: Disables the specified purge job.

#### 4.8.5.3 Setting the Default Audit Trail Purge Interval for Any Audit Trail Type

You can set a default purge operation interval, in hours, that must pass before the next purge operation occurs for a specified audit trail type.

For example:

    BEGIN
     DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
      AUDIT_TRAIL_TYPE            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
      AUDIT_TRAIL_PROPERTY        => DBMS_AUDIT_MGMT.CLEAN_UP_INTERVAL,
      AUDIT_TRAIL_PROPERTY_VALUE  => 24);
    END

In this example:

- `AUDIT_TRAIL_TYPE`: Specifies the audit trail type, which in this case is the database system audit trail. Choose from the following settings:

  - `DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_AUD`: Standard audit trail table, `AUD$`

  - `DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD`: Fine-grained audit trail table, `FGA_LOG$`

  - `DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD`: Both standard and fine-grained audit trail tables

  - `DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS`: Operating system audit trail files with the `.aud` extension. (This setting does not apply to Windows event log entries.)

  - `DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML`: XML operating system audit trail files with the `.xml` extension

  - `DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES`: Both operating system and XML audit trail files

  - `DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL`: All audit trail records, that is, both database audit trail and operating system audit trail types

  You can set a default interval for multiple audit trail types, so long as they do not conflict. For example, you can set individual intervals for the `DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_AUD` and `DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD` properties, but not for the `DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD` property.

- `AUDIT_TRAIL_PROPERTY`: Sets the `DBMS_AUDIT_MGMT.CLEAN_UP_INTERVAL` property to indicate the purge operation interval setting. To find the current property settings, query the `PARAMETER_NAME` and `PARAMETER_VALUE` columns of the `DBA_AUDIT_MGMT_CONFIG_PARAMS` data dictionary view. The timing begins when you set the `DBMS_AUDIT_MGMT.CLEAN_UP_INTERVAL` property.

- `AUDIT_TRAIL_PROPERTY_VALUE`: Updates the default hourly interval set by the `DBMS_AUDIT_MGMT.INIT_CLEANUP` procedure. Enter a value between 1 and 999.

#### 4.8.5.4 Setting the Default Audit Trail Purge Job Interval for a Specified Purge Job

You can set a default purge operation interval, in hours, that must pass before the next purge job operation occurs. The interval setting that is used in the `DBMS_AUDIT_MGMT.CREATE_PURGE_JOB` procedure takes precedence over this setting.

For example:

    BEGIN
     DBMS_AUDIT_MGMT.SET_PURGE_JOB_INTERVAL(
      AUDIT_TRAIL_PURGE_NAME       => 'OS_Audit_Trail_PJ',
      AUDIT_TRAIL_INTERVAL_VALUE   => 24 );
    END

In this example:

- `AUDIT_TRAIL_PURGE_NAME`: Specifies the name of the audit trail purge job. To find a list of existing purge jobs, query the `JOB_NAME` and `JOB_STATUS` columns of the `DBA_AUDIT_MGMT_CLEANUP_JOBS` data dictionary view.

- `AUDIT_TRAIL_INTERVAL_VALUE`: Updates the default hourly interval set by the `DBMS_AUDIT_MGMT.CREATE_PURGE_JOB` procedure. Enter a value between 1 and 999. The timing begins when you run the purge job.

#### 4.8.5.5 Clearing the Database Audit Trail Records Batch Size

To clear this setting, use the `DBMS_AUDIT_MGMT.CLEAR_AUDIT_TRAIL_PROPERTY` procedure.

For example:

    BEGIN
      DBMS_AUDIT_MGMT.CLEAR_AUDIT_TRAIL_PROPERTY(
       AUDIT_TRAIL_TYPE        =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
       AUDIT_TRAIL_PROPERTY    =>  DBMS_AUDIT_MGMT.DB_DELETE_BATCH_SIZE,
       USE_DEFAULT_VALUES      =>  TRUE );
    END;

In this example:

- `AUDIT_TRAIL_TYPE`: Specifies the audit trail type, which in this case is the database system audit trail. Enter one of the `AUDIT_TRAIL_TYPE` values listed in Section 4.8.4.

- `AUDIT_TRAIL_PROPERTY`: Specifies the `DB_DELETE_BATCH_SIZE` property. Query the `PARAMETER_NAME` and `PARAMETER_VALUE` columns of the `DBA_AUDIT_MGMT_CONFIG_PARAMS` data dictionary view to find the current value of this property.

- `USE_DEFAULT_VALUES`: Enter one of the following values:

  - `TRUE`: Clears the current audit record batch size and uses the default value, `10000`, instead.

  - `FALSE`: Oracle Database does not set any batch size for audit records. The default setting is `FALSE`.

#### 4.8.5.6 Canceling the Initialization Cleanup Settings

You can cancel the `DBMS_AUDIT_MGMT.INIT_CLEANUP` settings, that is, the default cleanup interval, by invoking the `DBMS_AUDIT_MGMT.DEINIT_CLEANUP` procedure.

For example, to cancel all purge settings for the standard audit trail:

    BEGIN
     DBMS_AUDIT_MGMT.DEINIT_CLEANUP(
      AUDIT_TRAIL_TYPE  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD);
    END;

In this example:

- `AUDIT_TRAIL_TYPE`: Enter one of the `AUDIT_TRAIL_TYPE` settings listed in Step 2B: Initialize the Audit Trail Cleanup Operation.

#### 4.8.5.7 Deleting an Audit Trail Purge Job

To delete an audit trail purge job, use the `DBMS_AUDIT_MGMT.DROP_PURGE_JOB` PL/SQL procedure. To find existing purge jobs, query the `JOB_NAME` and `JOB_STATUS` columns of the `DBA_AUDIT_MGMT_CLEANUP_JOBS` data dictionary view.

For example:

    BEGIN
     DBMS_AUDIT_MGMT.DROP_PURGE_JOB(
      AUDIT_TRAIL_PURGE_NAME  => 'FGA_Audit_Trail_PJ');
    END

In this example:

- `AUDIT_TRAIL_PURGE_NAME`: Specifies a purge job called `FGA_Audit_Trail_PJ`.

#### 4.8.5.8 Configuring Tracing Debug Levels for Purge Operations

To diagnose errors, you can set the trace level for purge operations. Oracle Database creates trace files in the location set by the `USER_DUMP_DEST` initialization parameter. To find this location, log in to SQL*Plus and enter `SHOW PARAMETER USER_DUMP_DEST`.

As an example of the type of error the trace debug levels can catch, suppose you try to move the database audit trail table from `SYSTEM` to a different tablespace. Before moving the tables to the new tablespace, Oracle Database checks the space of the destination tablespace to ensure that it can hold the database audit trail tables. The debug log level can reveal if there is not enough space.

Use the `DBMS_AUDIT_MGMT.SET_DEBUG_LEVEL` PL/SQL procedure to set the trace level.

For example:

    BEGIN
    DBMS_AUDIT_MGMT.SET_DEBUG_LEVEL(
     DEBUG_LEVEL   => TRACE_LEVEL_DEBUG);
    END

In this example:

- `DEBUG_LEVEL`: Specify one of the following values:

  - `TRACE_LEVEL_ERROR` records errors. This is the default setting.

  - `TRACE_LEVEL_DEBUG` records detailed information that you may want to capture for debugging purposes. Use this setting to diagnose a problem that is occurring.

#### 4.8.5.9 Setting the Size of the Operating System Audit Trail

To control the size of the operating system audit trail, set the `DBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE` property by using the `DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY` PL/SQL procedure. Remember that you must have the `EXECUTE` privilege for the `DBMS_AUDIT_MGMT` PL/SQL package before you can use it. When the operating system file meets the size limitation you set, Oracle Database stops adding records to the current file and then creates a new operating system file for the subsequent records.

If you set both the `DBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE` and the `DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE` (described in Section 4.8.5.9) properties, then Oracle Database performs the action based on the property value limit that is met first.

For example:

    BEGIN
      DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
       AUDIT_TRAIL_TYPE            =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
       AUDIT_TRAIL_PROPERTY        =>  DBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE,
       AUDIT_TRAIL_PROPERTY_VALUE  =>  102400);
    END;

In this example:

- `AUDIT_TRAIL_TYPE`: Specifies the operating system audit trail. Enter one of the following values:

  - `DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS`: Operating system audit trail files with the `.aud` extension. (This setting does not apply to Windows event log entries.)

  - `DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML`: XML audit trail files.

  - `DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES`: Both operating system and XML audit trail files.

- `AUDIT_TRAIL_PROPERTY`: Specifies the `DBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE` property to set the maximum size. To find the status of the current property settings, query the `PARAMETER_NAME` and `PARAMETER_VALUE` columns of the `DBA_AUDIT_MGMT_CONFIG_PARAMS` data dictionary view.

- `AUDIT_TRAIL_PROPERTY_VALUE`: Sets the maximum size to `102400` kilobytes. The default setting is 10,000 kilobytes (approximately 10 MB). Do not exceed 2 gigabytes.

Clearing the DBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE Setting

To clear the maximum file size setting, use the `DBMS_AUDIT_MGMT.CLEAR_AUDIT_TRAIL_PROPERTY` procedure.

For example:

    BEGIN
      DBMS_AUDIT_MGMT.CLEAR_AUDIT_TRAIL_PROPERTY(
       AUDIT_TRAIL_TYPE        =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
       AUDIT_TRAIL_PROPERTY    =>  DBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE,
       USE_DEFAULT_VALUES      =>  TRUE );
    END;

In this example:

- `AUDIT_TRAIL_TYPE`: Specifies the operating system audit trail. Enter one of the `AUDIT_TRAIL_TYPE` values described in Section 4.8.5.3.

- `AUDIT_TRAIL_PROPERTY`: Specifies the `DBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE` property. Query the `PARAMETER_NAME` and `PARAMETER_VALUE` columns of the `DBA_AUDIT_MGMT_CONFIG_PARAMS` data dictionary view to find the current status of this property.

- `USE_DEFAULT_VALUES`: Enter one of the following values:

  - `TRUE`: Clears the current value and uses the default value, 10,000 kilobytes, instead.

  - `FALSE`: Oracle Database does not use a default maximum size for the operating system or XML file growth. The files will continue to grow without limitation unless you configure the `DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE` property. The default setting is `FALSE`.

#### 4.8.5.10 Setting the Age of the Operating System Audit Trail

To control the age of the operating system audit trail, use the `DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY` PL/SQL procedure. Remember that you must have the `EXECUTE` privilege for the `DBMS_AUDIT_MGMT` PL/SQL package before you can use it. When the operating system file meets the age limitation you set, Oracle Database stops adding records to the current file and then creates a new operating system file for the subsequent records. For more information about the `DBMS_AUDIT_MGMT` PL/SQL package, see Oracle Database PL/SQL Packages and Types Reference.

If you set both the `DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE` and the `DBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE` (described in Section 4.8.5.9) properties, then Oracle Database performs the action based on the property value limit that is met first.

For example:

    BEGIN
      DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
       AUDIT_TRAIL_TYPE            =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
       AUDIT_TRAIL_PROPERTY        =>  DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE,
       AUDIT_TRAIL_PROPERTY_VALUE  =>  10 );
    END;

In this example:

- `AUDIT_TRAIL_TYPE`: Specifies the operating system audit trail. Enter one of the following values:

  - `DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS`: Operating system audit trail files with the `.aud` extension. (This setting does not apply to Windows Event Log entries.)

  - `DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML`: XML audit trail files.

  - `DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES`: Both operating system and XML audit trail files.

- `AUDIT_TRAIL_PROPERTY`: Specifies the `DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE` property to set the maximum age. To find the status of the current property setting, query the `PARAMETER_NAME` and `PARAMETER_VALUE` columns of the `DBA_AUDIT_MGMT_CONFIG_PARAMS` data dictionary view.

- `AUDIT_TRAIL_PROPERTY_VALUE`: Sets the maximum age to 10 days. Enter a value between 1 and 495. The default age is 5 days.

Clearing the DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE Setting

To clear the maximum file age setting, use the `DBMS_AUDIT_MGMT.CLEAR_AUDIT_TRAIL_PROPERTY` procedure.

For example:

    BEGIN
      DBMS_AUDIT_MGMT.CLEAR_AUDIT_TRAIL_PROPERTY(
       AUDIT_TRAIL_TYPE        =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
       AUDIT_TRAIL_PROPERTY    =>  DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE,
       USE_DEFAULT_VALUES      =>  TRUE );
    END;

In this example:

- `AUDIT_TRAIL_TYPE`: Specifies the operating system audit trail. Enter one of the `AUDIT_TRAIL_TYPE` values listed in Section 4.8.5.9.

- `AUDIT_TRAIL_PROPERTY`: Specifies the `DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE` property. Query the `PARAMETER_NAME` and `PARAMETER_VALUE` columns of the `DBA_AUDIT_MGMT_CONFIG_PARAMS` data dictionary view, described in Section 13.1, to find the current status of this property.

- `USE_DEFAULT_VALUES`: Specify one of the following values:

  - `TRUE`: Clears the current value and uses the default value, 5 days, instead.

  - `FALSE`: Oracle Database does not use a default maximum age for the operating system or XML file growth. The files will continue to age without limitation unless you configure the `DBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE` property. The default setting is `FALSE`.