14 Configuring and Managing Auditing

This chapter explains how to perform day-to-day audit administration tasks.

See Also:

Chapter 13, "Introduction to Oracle Fusion Middleware Audit Service" for background information about auditing in Oracle Fusion Middleware.

Understanding Audit Administration Tasks
Managing the Audit Data Store
Managing Audit Policies
Understanding Audit Timestamps
About Audit Logs and Bus-stop Files
Advanced Administration of the Database Store
Best Practices for Managing Audit Event Definitions

14.1 Understanding Audit Administration Tasks

As audit administrator, you should plan the site's audit setup carefully by following the steps in these areas:

Implementation Planning

This includes planning the type of store to use for audit records, data store configuration details, and so on.

See Section 14.2, "Managing the Audit Data Store" for details.
Policy Administration

You must configure the appropriate audit policies to ensure that the required audit events are generated.

This is an ongoing activity since the audit policies must be able to reflect changes to the application environment, addition of components and users, and so on.

See Section 14.3, "Managing Audit Policies" for details.
Reports Management

This includes planning for and configuring audit reports and queries.

See Chapter 15, "Using Audit Analysis and Reporting" for details.
Data Administration

This includes planning/increasing the database size required to store the audit data generated, backing up the audit data and, purging the audit data based on company policy.

See Section 14.6, "Advanced Administration of the Database Store" for details about audit data store administration.

14.2 Managing the Audit Data Store

In a production environment, a database audit data store provides the scalability and high-availability needed to store audit records.

In addition, an audit data store residing in a database allows the audit data to be viewed through query and reporting tools.

This section explains these audit data store management tasks in detail:

About the Audit Schema
About Audit Data Sources
Tuning the Bus-stop Files
Configuring the Stand-alone Audit Loader

14.2.1 About the Audit Schema

The audit schema (IAU schema) is a database structure for storing audit records, and is created along with the security store (OPSS schema).

For details of security schema creation, see Chapter 9, "Configuring the OPSS Security Store".

Note:

The bus-stop files store audit records in the absence of database storage.

Once the database schema is created, you can update the domain configuration to switch the audit data store for audit records.

14.2.2 About Audit Data Sources

In WebLogic Server, you configure database connectivity by adding data sources to your WebLogic domain. WebLogic JDBC data sources provide database access and database connection management.

As explained in Section 14.2.1, domain creation generates the audit schema, a data structure required to store audit records in a database. It also sets up an Oracle WebLogic Server audit data source that points to the audit schema. The audit data source thus enables you to directly query and run reports against the audit records in the database.

For details about domain creation in the OPSS context, see Section 7.3, "Creating an FMW Domain".

14.2.3 Tuning the Bus-stop Files

Once the bus-stop file reaches a certain size and all the data is uploaded to the database, the audit loader deletes the file from the operating system. Tuning the bus-stop files enables you to efficiently manage bus-stop files.

This section contains topics related to maintaining the bus-stop files that store audit records, including:

bus-stop file locations
file size

Note:

Manually purging audit files to free up space is not recommended. Instead, use file sizing features to control space, as described below.

14.2.3.1 Locating the Bus-Stop Files

Bus-stop files for Java components are located in:

$DOMAIN_HOME/servers/$SERVER_NAME/logs/auditlogs/Component_Type

Bus-stop files for system components are located in:

$ORACLE_INSTANCE/auditlogs/Component_Type/Component_Name

14.2.3.2 Sizing Bus-Stop Files

Java Components

The size of a file for the file storage mode can be managed using the audit.maxFileSize property described in the configuration file jps-config.xml. This property controls the maximum size of a bus-stop file for Java components.

Specify the sizes in bytes.

System Components

The size of a bus-stop file can be set in the auditconfig.xml file. For example:

<serviceInstance name="audit" provider="audit.provider">
      <property name="audit.maxFileSize" value="10240" />      
      <property name=" audit.loader.repositoryType " value="Db" />      
   </serviceInstance>

Note:

If you switch from file to database store for audit data, all the events collected in the audit files are pushed into the database tables and the audit files are deleted.

14.2.4 Configuring the Stand-alone Audit Loader

As shown in Figure 13-1, Common Audit Framework's audit loader moves records from bus-stop files to the audit data store. The mechanism driving the audit loader depends on the application environment:

Java EE components and applications deployed on Oracle WebLogic Server use the audit loader functionality provided through the application server. Although you can run the stand-alone audit loader, it is not needed in the JavaEE environment.
System components and non-Java applications use the audit loader functionality provided through the StandAloneAuditLoader java command.
Java SE applications, which run outside an application server container, also use the stand-alone audit loader.

This section explains how to set up and execute the stand-alone audit loader:

Configuring the Environment
Running the Stand-Alone Audit Loader

14.2.4.1 Configuring the Environment

Before you can run the stand-alone audit loader, you must a) configure certain properties, and b) ensure that the password for the database schema user exists in the secret store.

14.2.4.1.1 Configuring Properties for the Audit Loader

You must configure the following properties:

ORACLE_HOME environment variable. Set to the full path of the Oracle home.
COMMON_COMPONENTS_HOME environment variable. Set to the location containing the Java Required Files (JRF).
ORACLE_INSTANCE environment variable. Set to the full path of an Oracle instance.
auditloader.jdbcString system property. Set to the JDBC connection string for the database where audit data resides.
auditloader.username system property. Set to the username of a user who can run the audit loader.

14.2.4.1.2 Storing the Password for the Database Schema User

The password for the database schema user is kept in the secret store. Storing the password is a one-time operation for which you use the java StandAloneAuditLoader command with the -Dstore.password=true property.

Issue the StandAloneAuditLoader command to store the password as follows:

$JDK_HOME/bin/java 
-classpath $COMMON_COMPONENTS_HOME/modules/oracle.jps_12.1.3/jps-manifest.jar -Doracle.home=$ORACLE_INSTANCE -Doracle.instance=$ORACLE_INSTANCE -Dauditloader.jdbcString=jdbc:oracle:thin:@host:port:sid -Dauditloader.username=username oracle.security.audit.ajl.loader.StandaloneAuditLoader

You will be prompted for the password.

Note:

There should not be any spaces in the classpath specification.

This command generates a cwallet.sso file.

14.2.4.2 Running the Stand-Alone Audit Loader

Issue the StandAloneAuditLoader command to load audit records as follows:

$JDK_HOME/bin/java 
-classpath $COMMON_COMPONENTS_HOME/modules/oracle.jps_12.1.3/jps-manifest.jar -Doracle.home=$ORACLE_INSTANCE -Doracle.instance=$ORACLE_INSTANCE -Dauditloader.jdbcString=jdbc:oracle:thin:@host:port:sid -Dauditloader.username=username oracle.security.audit.ajl.loader.StandaloneAuditLoader

You can schedule this command through a batch or kron job so that audit records are periodically uploaded to the audit data store.

14.3 Managing Audit Policies

What is an Audit Policy?

An audit policy is a declaration of the type of events to be captured by the audit framework for a particular component. For Java components, the audit policy is defined at the domain level. For system components, the audit policy is managed at the component instance level.

For example, an audit policy could specify that all authentication failures should be audited for an Oracle Internet Directory instance.

How Policies are Configured

Oracle Fusion Middleware Audit Framework lets you configure audit policies and provides highly granular controls over the types of events and data being audited. You can configure policies through the Oracle Enterprise Manager Fusion Middleware Control UI tool or with WLST commands.

Policy changes do not require server or instance restart.

The remainder of this section explains how to view, and update audit policy:

Managing Audit Policies for Java Components with Fusion Middleware Control
Managing Audit Policies for System Components with Fusion Middleware Control
Managing Audit Policies at the Command Line (WLST)
Managing Audit Policies through API Programming

See Also:

Section 13.3.2, "Understanding Key Technical Concepts of Auditing" for additional background.
Appendix C for a list of Java components and system components.

14.3.1 Managing Audit Policies for Java Components with Fusion Middleware Control

The domain Audit Policy Settings page manages audit events for all Java components such as Oracle Access Manager, and system libraries like Oracle Platform Security Services.

Notes:

Audit policy for system components is managed in the component home pages. See Section 14.3.2, "Managing Audit Policies for System Components with Fusion Middleware Control"
See the note at the beginning of Section 14.3, "Managing Audit Policies" titled "Policy Changes Require Server or Instance Restart".

14.3.2 Managing Audit Policies for System Components with Fusion Middleware Control

This section describes how to view and update audit policies for system components.

Notes:

Audit policy for Java components is managed in the domain context. See Section 14.3.1, "Managing Audit Policies for Java Components with Fusion Middleware Control".
See the note at the beginning of Section 14.3, "Managing Audit Policies" titled "Policy Changes Require Server or Instance Restart". Oracle Internet Directory instances do not require a restart.

Audit policy for system components is managed in their home pages. The domain Audit Policy Settings page manages audit events for Java components running in the domain.

14.3.3 Managing Audit Policies at the Command Line (WLST)

This section explains how to view and update audit policies using the Oracle WebLogic Scripting Tool (WLST) command-line tool:

Viewing Audit Policies at the Command Line (WLST)
Updating Audit Policies at the Command Line (WLST)
Example 1: Configuring an Audit Policy for Users with WLST
Example 2: Configuring an Audit Policy for Events with WLST
What Happens to Custom Configuration when the Audit Level Changes?

Note:

When running audit WLST commands, you must invoke the WLST script from the Oracle Common home. See "Using Custom WLST Commands" in Administering Oracle Fusion Middleware for more information.

14.3.3.1 Viewing Audit Policies at the Command Line (WLST)

Take these steps to view audit policies at the command line:

Note:

This discussion assumes that you are invoking WLST interactively. For details about WLST and the different options for invoking the tool, see "Getting Started Using the Oracle WebLogic Scripting Tool (WLST)" in Administering Oracle Fusion Middleware.

Connect to the WebLogic Server using the following commands:

>java weblogic.WLST
>connect('userName', 'userPassword', 'url', 'adminServerName')

Use the getAuditPolicy command to view the audit policy configuration. For example, the following command shows audit policies set at the domain level:
```
wls:/mydomain/serverConfig> getAuditPolicy()
```
The following command shows audit policy for a specific component:
```
wls:/mydomain/serverConfig> getAuditPolicy(componentType="JPS")
```
For system components:
- obtain the MBean name using the getNonJava EEAuditMBeanName command. See Appendix C, "Oracle Fusion Middleware Audit Framework Reference." for details.
- Use the getAuditPolicy command and include the MBean name to view the audit policy configuration. For example:
```
wls:/mydomain/serverConfig> getAuditPolicy
 (on="oracle.security.audit.test:type=CSAuditMBean,name=CSAuditProxyMBean")
```

14.3.3.2 Updating Audit Policies at the Command Line (WLST)

Take these steps to update audit policies with the Oracle WebLogic Scripting Tool (WLST) command-line tool:

Note:

Connect to the WebLogic Server using the following commands:

>java weblogic.WLST
>connect('userName', 'userPassword', 'url', 'adminServerName')

Navigate the bean hierarchy to access the domain of interest. For example, if the domain is called mydomain:
```
wls:/mydomain/serverConfig>
```
Use the setAuditPolicy command to update the audit policy configuration.

For components that manage their policy locally, use the setAuditPolicy command and include an MBean name to update the audit policy configuration. The name for an Audit MBean is of the form:

oracle.as.management.mbeans.register:type=component.auditconfig,name=auditconfig1,instance=INSTANCE,component=COMPONENT_NAME

For example:

oracle.as.management.mbeans.register:type=component.auditconfig,name=auditconfig1,instance=instance1,component=oid1

For system components like Oracle HTTP Server or Oracle Internet Directory, explicitly call save after issuing a setAuditPolicy, or importAuditConfig, command.

If you do not invoke save, the new settings will not take effect.

In the following example, we navigate to the Root Proxy MBean in the tree and use invoke commands to call setAuditPolicy and save, respectively:

ORACLE_COMMON_HOME/common/bin/wlst.sh
connect('username', 'password', 'protocol://localhost:7001', 'localhost:7001')
custom()
cd('oracle.as.management.mbeans.register')
cd('oracle.as.management.mbeans.register:type=component,name=oid1,instance=instance1')
invoke('load',jarray.array([],java.lang.Object),jarray.array([],
java.lang.String)) 
setAuditPolicy(filterPreset='None',
 on='oracle.as.management.mbeans.register:type=component.auditconfig,
 name=auditconfig1,instance=instance1,component=oid1')
invoke('save',jarray.array([],java.lang.Object),jarray.array([],
java.lang.String))

JavaEE components do not need this step.

14.3.3.3 Example 1: Configuring an Audit Policy for Users with WLST

In this scenario, the domain's current policy audits a user named user1. The administrator would like to add two names, user2 and user3, to the list of users who are always audited, and remove user1 from the list.

The following invocation of setAuditPolicy performs this task:

setAuditPolicy
   (filterPreset="None",addSpecialUsers="user2,user3",removeSpecialUsers="user1")

14.3.3.4 Example 2: Configuring an Audit Policy for Events with WLST

In this scenario, the domain's current policy audits user logout events. The administrator would like to remove the logout events from the policy and, instead, audit login events.

The following invocation of setAuditPolicy for Oracle HTTP Server (OHS) performs this task:

setAuditPolicy (on="OHS mbean name")
(filterPreset="Custom",addCustomEvents="OHS:UserLogin",
removeCustomEvents="OHS:UserLogout")

Notice that the Custom filter preset was invoked to add and remove events.

Note:

This example uses the component type OHS for Oracle HTTP Server. Substitute the relevant component type when using the command.

14.3.3.5 What Happens to Custom Configuration when the Audit Level Changes?

When auditing is configured at the custom audit level, and you subsequently use WLST to switch to a different (non-custom) audit level, the custom audit settings are retained unless you explicitly remove those custom settings.

An example illustrates this behavior:

Custom audit level is set for a component's policy. An audit filter is specified as part of the configuration.
At run-time, audit data is collected according to the specified filter.
The component's audit policy is now changed from custom audit level to, say, the low audit level using the WLST setAuditPolicy command. However, the filter that was set up as part of the custom audit level persists in the audit configuration.
Audit data is now collected based on the low audit level, not the custom level.
The component's audit policy is changed back to custom level. An additional filter is added. This filter is appended to the originally configured filter. Unless the original filter is explicitly deleted, it remains part of the configuration.
At run-time, audit data is collected based on all prevailing filters at the custom level.

14.3.4 Managing Audit Policies through API Programming

The audit administration service provides a set of APIs that applications can use to retrieve and update audit policies. For details, see Section 23.5.

14.4 Understanding Audit Timestamps

Prior to 11g Release 1 (11.1.1.7), the audit service wrote out database records using the application server's time zone. Starting with 11g Release 1 (11.1.1.7), the audit service can account for different time zones for the application server and the audit data store.

This means:

New sites see audit events written in Coordinated Universal Time (UTC).
Sites that upgrade from 11g Release 1 (11.1.1.6), by default, continue to use the application server time zone for audit records unless you explicitly switch to UTC.

Note:

Records to bus-stop files always use UTC.

Audit Timestamps at New Sites

At new installations, audit records use UTC timestamps. A service property (audit.timezone=utc) in the default audit service configuration implements this convention.

Out-of-the-box, audit service configuration looks like this:

<serviceInstance name="audit" provider="audit.provider"       location="./audit-store.xml">
   <property name="audit.filterPreset" value="None"/>
   <property name="audit.timezone" value="utc" />
   <property name="audit.loader.repositoryType" value="File" />
   <property name="auditstore.type" value="file"/>
</serviceInstance>

Audit Timestamps at Upgraded Sites

Audit records that existed prior to 11g Release 1 (11.1.1.7) used the application server timestamp. After upgrading from that release, the audit service configuration remains unchanged and, by default, the records continue to be written using the application server timestamp.

If you wish to use the UTC timestamp after upgrading to 12c (12.1.3), you can add the service property audit.timezone=utc in the configuration file to get the UTC behavior.

If you switch to UTC after the upgrade, Oracle recommends that you move the existing records first to avoid any potential inconsistency which can affect reporting.

See Also:

Section F.2.8

14.5 About Audit Logs and Bus-stop Files

This section contains the following topics:

Where are Audit Logs Located?
About Audit Timestamps in Bus-stop Files

14.5.1 Where are Audit Logs Located?

Fusion Middleware Audit Framework provides a set of log files to help with audit administration. You can use these logs to trace errors and for diagnostic purposes when the audit framework is not functioning properly.

For a listing of all audit log locations, how to configure the loggers, and how to use the logs to diagnose issues, see Section J.1.1.7, "About Audit Loggers".

14.5.2 About Audit Timestamps in Bus-stop Files

Time stamps in the audit bus-stop files are recorded in Coordinated Universal Time. This may differ from the machine time depending on the machine's time zone setting.

See Also:

Section 14.4

14.6 Advanced Administration of the Database Store

The audit schema is created when the OPSS security schema is created. This section explains the organization of the audit schema and contains the following topics related to maintaining the schema:

Overview of the Audit Schema
Table Sizing: Base and Component Table Attributes
Performance Tuning
Planning Backup and Recovery
Importing and Exporting Data
Partitioning

See Also:

For more information on RCU, see Oracle Fusion Middleware Repository Creation Utility User's Guide.

14.6.1 Overview of the Audit Schema

The common tables in the Oracle Fusion Middleware Audit Framework schema consist of the following:

basic data in a base table
common attributes in the IAU_COMMON table
generic attributes in dedicated tables
custom attributes in the IAU_CUSTOM_nnn tables.

Not all tables are used at the same time. Typical sites using the dynamic metadata model (explained in Section 13.3.1.1) utilize the common and custom tables. The older model uses the base table and component-specific tables.

For details about these tables, see Section 13.4.

About the Bus-stop File

By default, the bus-stop file is maintained in the directory:

Weblogic Domain Home/servers/server_name/logs/auditlogs

with sub-directories for the components. For example, Oracle Platform Security Services (OPSS) bus-stop files reside in:

Weblogic Domain Home/servers/server_name/logs/auditlogs/JPS

Here is a sample bus-stop file for OPSS:

#Fields:Date Time Initiator EventType EventStatus MessageText HomeInstance ECID RID ContextFields SessionId TargetComponentType ApplicationName EventCategory ThreadId InitiatorDN TargetDN FailureCode RemoteIP Target Resource Roles CodeSource InitiatorGUID Principals PermissionAction PermissionClass mapName key
#Remark Values:ComponentType="JPS"
2008-12-08 10:46:05.492  - "CheckAuthorization" true "Oracle Platform Security Authorization Check Permission SUCCEEDED." - - - - - - - "Authorization" "48" - - "true" - - "(oracle.security.jps.service.policystore.PolicyStoreAccessPermission context=APPLICATION,name=SimpleServlet getApplicationPolicy)" - "file:/oracle/work/middleware/oracle_common/modules/oracle.jps_11.1.1/jps-internal.jar" - "[]" - - - -

Common Table and Custom Tables

The common table contains the basic data for an audit record and is related to the custom tables, which contain additional data, through the IAU_ID attribute.

Base Table and Component Tables

Likewise, the base table containing the basic audit record is related to the component-specific tables, which contain additional data, through the IAU_ID attribute.

14.6.2 Table Sizing: Base and Component Table Attributes

When generated, audit records are stored in a file; if an audit database store is configured, the audit loader stores each audit record as follows:

General information (such as Time, EventType, and EventStatus) is written into one row of the common table (dynamic model) or the base table.
Component-specific information (such as CodeSource) is written into one row of the custom table (dynamic model) or the component table.

The average record size for basic audit records is approximately 0.3 KB. When you plan for tablespace sizing:

use this number as a guideline for the average record size
monitor how audit database size is growing based on the audit policy selected and the level of activity
take into account the period of time for which the audit data is being stored.

The attributes of the base table and the component-specific tables respectively are derived from these files:

$ORACLE_HOME/modules/oracle.iau_12.1.3/components/generic/generic_events.xml

for the base table, and

$ORACLE_HOME/modules/oracle.iau_12.1.3/components/componentName/component_events.xml

for each component table.

Table C-6 lists the attributes defined in the base table IAU_BASE. Table C-7 likewise lists the attributes defined in the common table IAU_COMMON.

You can use the listAuditEvents WLST command to get a list of all attribute names for individual component tables.

See Also:

14.6.3 Performance Tuning

For efficient queries, an index is created by default on the Timestamp (IAU_TSTZORIGINATING) in the base table and on each of the component-specific tables.

The default index in IAU_BASE is named EVENT_TIME_INDEX, and in the component tables it is named tableName_INDEX (such as OVDCOMPONENT_INDEX, OIDCOMPONENT_INDEX, JPS_INDEX and so on).

Additional indexed columns and their indexes are as follows:

Timestamp (IAU_TSTZORIGINATING) in the common table, with an index called DYN_EVENT_TIME_INDEX.
IAU_AuditUser, IAU_ComponentType, IAU_EventCategory, IAU_EventType columns, all on the common table. Indexes are called DYN_USER_INDEX, DYN_COMPONENT_TYPE_INDEX, DYN_EVENT_CATEGORY_INDEX and DYN_EVENT_TYPE_INDEX respectively.

14.6.4 Planning Backup and Recovery

Compliance regulations require that audit data be stored for long periods. A backup and recovery plan is needed to protect the data.

A good backup plan takes account of these basic guidelines:

Growth rate of Audit Events

The number of audit events generated depends on your audit policy. The number of audit events generated daily determines, in turn, how often you want to perform backups to minimize the loss of your audit data.
Compliance regulations

Consult you organization's compliance regulations to determine the frequency of backups and number of years for which audit data storage is mandatory.
Online or Offline Data Management

Consult you organization's compliance regulations to determine the frequency of backups and the portion of audit data that needs to be easily accessible.

Oracle Database uses Oracle Recovery Manager (RMAN) for backup and recovery. For details, see:

http://www.oracle.com/technology/deploy/availability/htdocs/BR_Overview.htm

http://www.oracle.com/technology/deploy/availability/htdocs/rman_overview.htm

Note:

The translation table, IAU_DISP_NAMES_TL, needs to be backed up only once, since it should not change over time.

14.6.5 Importing and Exporting Data

You can import and export the audit schema to migrate data if you started with multiple audit databases and wish to combine them into a single audit data store, or if you wish to change the database to scale up.

Oracle Database sites can utilize the utilities of Oracle Data Pump to import and export data. For details, refer to:

http://www.oracle.com/technology/products/database/utilities/htdocs/data_pump_overview.html

14.6.6 Partitioning

Not all database systems support partitioning, all the tables in the audit schema are unpartitioned by default.

Since audit data is cumulative and older data is never removed, if you store a high volume of audit data you should consider partitioning the audit schema, as it will allow for easier archiving.

Benefits of partitioning include:

Improved Performance: If a table is range-partitioned by Timestamps, for example, queries by Timestamps can be processed on the partitions within that time-frame only.
Better Manageability: Partitions can be created on separate tablespaces (thus different disks). This enables you to move older data to slower and larger disks, while keeping newer data in faster and smaller disks.

In addition, partitioning makes archival much easier. For example, you can compress a singlve partition rather than having to partition the entire table.
Increased Availability: If a single partition is unavailable, for example, and you know that your query can eliminate this partition from consideration, the query can be successfully processed without needing to wait for the unavailable partition.

14.6.6.1 Partitioning Tables

Note:

This discussion applies to releases prior to 11g Release 1 (11.1.1.7). New 11g Release 1 (11.1.1.7) installations do not use these tables.

In this example, IAU_BASE is used as an example to demonstrate how to convert the unpartitioned tables in the audit schema into partitioned tables.

It is recommended that partitioning is done before using this schema for an audit data store to minimize the application down time.

Note:

Two sample SQL scripts are shipped with the product:

$MW_HOME/oracle_common /common/sql/iau/scripts/convertPartitionedTables.sql (linux) or %MW_HOME\oracle_common \common\sql\iau/scripts\convertPartitionedTables.sql (Windows) converts the base and component tables in the audit schema into partitioned tables.
$MW_HOME/oracle_common /common/sql/iau/scripts/createPartitionsByQuarter.sql (linux) or %MW_HOME\oracle_common \common\sql\iau/scripts\createPartitionsByQuarter.sql (Windows) creates partitions by quarter for the base and component tables in the audit schema.

The partitioning steps are as follows:

Rename the existing unpartitioned table. For example:
```
RENAME IAU_BASE TO IAU_BASE_NONPART;
```

Create a new partitioned table that follows the table structure of the unpartitioned table. This example uses the range-partitioning (by Timestamp) scheme:

CREATE TABLE IAU_BASE
PARTITION BY RANGE (IAU_TSTZORIGINATING)
(
    PARTITION IAU_BASE_DEFAULT VALUES LESS THAN (MAXVALUE)
)
AS SELECT * FROM IAU_BASE_NONPART;

Enable row movement to allow data to automatically move from partition to partition when new partitions are created. For example:
```
ALTER TABLE IAU_BASE
ENABLE ROW MOVEMENT;
```

Create a local prefix index for the partitioned table. For example:

ALTER INDEX EVENT_TIME_INDEX
RENAME TO EVENT_TIME_INDEX_NONPART;
 
CREATE INDEX EVENT_TIME_INDEX
ON IAU_BASE(IAU_TSTZORIGINATING) LOCAL;

Partitions can now be created. In this example partitions are created by calendar quarter:

ALTER TABLE IAU_BASE
SPLIT PARTITION IAU_BASE_DEFAULT AT (TO_DATE('01/04/2008', 'DD/MM/YYYY')) INTO (PARTITION IAU_BASE_Q1_2008, PARTITION IAU_BASE_DEFAULT)
UPDATE INDEXES;
 
ALTER TABLE IAU_BASE
SPLIT PARTITION IAU_BASE_DEFAULT AT (TO_DATE('01/07/2008', 'DD/MM/YYYY')) INTO (PARTITION IAU_BASE_Q2_2008, PARTITION IAU_BASE_DEFAULT)
UPDATE INDEXES;
 
ALTER TABLE IAU_BASE
SPLIT PARTITION IAU_BASE_DEFAULT AT (TO_DATE('01/10/2008', 'DD/MM/YYYY')) INTO (PARTITION IAU_BASE_Q3_2008, PARTITION IAU_BASE_DEFAULT)
UPDATE INDEXES;
 
ALTER TABLE IAU_BASE
SPLIT PARTITION IAU_BASE_DEFAULT AT (TO_DATE('01/01/2009', 'DD/MM/YYYY')) INTO (PARTITION IAU_BASE_Q4_2008, PARTITION IAU_BASE_DEFAULT)
UPDATE INDEXES;

Note:

New partitions should be created periodically for new quarters.

14.6.6.2 Backing Up and Recovering Partitioned Tables

Backup and recovery were discussed in Section 14.6.4, "Planning Backup and Recovery". Note that read-only tablespaces can be excluded from whole database backup, so long as a backup copy was created. Thus, you can improve performance by avoiding unnecessary backups for the partitions of archived data residing on those tablespaces.

14.6.6.3 Importing and Exporting Data

Import and export were discussed in Section 14.6.5, "Importing and Exporting Data". Keep in mind that with a range-partitioned table it is much more efficient to drop a partition when you want to remove old data, rather than deleting the rows individually.

ALTER TABLE IAU_BASE DROP PARTITION IAU_BASE_Q4_2008;

It is also easy to load a partition of new data without having to modify the entire table. However, you have to remove the default partition of "values less than (MAXVALUE)" first, and add it back once finished, using a command like the following:

ALTER TABLE IAU_BASE ADD PARTITION IAU_BASE_Q4_2008 VALUES LESS THAN ('01-JAN-2009');

Once partitions are created, you can purge or back up a particular partition. Refer to Section 14.6.6.4 for details.

In the database mode, the audit loader automatically manages bus-stop files.

14.6.6.4 Purging Data

Oracle Fusion Middleware Audit Framework provides SQL scripts to enable you to purge records from the audit store.

Location of Scripts

The purge scripts are located at:

[Linux]
$MW_HOME/oracle_common/common/sql/iau/scripts


[Windows]
%MW_HOME\oracle_common\common\sql\iau\scripts

The following scripts reside at this location:

auditDataPurge.sql
auditDeleteData.sql
auditReclaimSpace.sql

Deleting Audit Records

To delete records without taking any other action, use auditDeleteData.sql. The script takes two parameters:

Audit Schema user
Number of Days to keep - older records are deleted.

auditDeleteData.sql audit_schema_user number_of_days_to_keep

For example:

sqlplus> @auditDeleteData.sql DEV_IAU 100

deletes all records older than 100 days.

Reclaiming Space in Data Store

To reclaim space, use the auditReclaimSpace.sql script. The script takes only one parameter, the audit schema user.

@auditReclaimSpace.sql audit_schema_user

For example:

sqlplus> @auditReclaimSpace.sql DEV_IAU

Deleting Records and Reclaiming Space

To both delete audit records and reclaim space, use the auditDataPurge.sql script. This script takes two parameters:

Audit Schema user
Number of Days to keep; older records are deleted.

@auditDataPurge.sql audit_schema_user number_of_days_to_keep

For example:

sqlplus> @auditDataPurge.sql DEV_IAU 100

deletes all records older than 100 days, and enables row movements to shrink space.

14.6.6.5 Performing Tiered Archival

Partitioning enables individual partitions (or groups of partitions) to be stored on different storage tiers. You can create tablespaces in high-performance or low-cost disks, and create partitions in different tablespaces based on the value of the data or other criteria. It is also easy to move data in partitions between the tablespaces (storage tiers).

Here is an example:

ALTER TABLE IAU_BASE MOVE PARTITION IAU_BASE_Q1_2008
TABLESPACE AUDITARCHIVE UPDATE INDEXES;

Note :

Partitions can be moved only in Range, List, System, and Hash partitioning schemes.

Oracle Information Lifecycle Management (ILM) features streamlined data management through partitioning and compression. For details, refer to:

http://www.oracle.com/technetwork/database/enterprise-edition/index-090321.html

14.7 Best Practices for Managing Audit Event Definitions

This section provides tips and techniques for managing your site's audit configuration and maximizing the value of the collected audit data. It contains these topics:

14.7.1 General Naming Guidelines

Ensure that all audit objects including component type, category, event, and attribute conform to these naming conventions:

Do not use spaces or special characters in names used for componentType, Category, Event, or Attribute. Only English alphabet characters are allowed.
Use spaces only in display names.

14.7.2 Naming of Events

Follow these guidelines when naming audit events:

Do not prefix everything with the component name.
Try to make names as specific as possible. For example, use "HTTPResponse" instead of "Response" when defining the HTTP response code 400, 302 and so on.
Do not apply the "Event" suffix to all event names. For example, instead of "AuthenticationEvent", "PolicyEvent" and so on, simply use "Authentication", "Policy" and so on.

14.7.3 Optimizing Event Granularity

Follow these guidelines to optimize event granularity:

Try to define separate events for each operation. For example, instead of combining them into one "Policy" event and having an attribute "Operation" to distinguish between them, make "PolicyCreate" and "PolicyDelete" as separate events.
Do not define separate events for Success and Failure. For example, do not have "PolicyCreateSuccess" and "PolicyCreateFailure" as separate events - instead use the EventStatus attribute to record Success or Failure.

14.7.4 Categorizing Events

Follow these guidelines to categorize events:

Try to reference the System categories when applicable. For example, "UserSession" and "Authorization".
For configuration operations, make a category around a group of operations. For example, put PolicyCreate, PolicyDelete in a component specific category called "Policy"

14.7.5 Using Generic Attributes

Follow these guidelines to utilize generic attributes:

Try to include these key event attributes:
- Initiator - the user who did this action.
- MessageText - a short description of what happened. Instead of using static text like "Authentication event", put in actual data values to make the description human readable; for example, "jdoe logged in sucessfully" .
- EventStatus - outcome of the event - true (success) or false (failure)
- FailureCode - If failure, then the error code applicable to the failure.
Another commonly used attribute is Resource - the "object" that was affected . For example, a URI that was accessed, or a Permission that was granted. etc. Sometimes you may choose to not use Resource but your own custom attributes.

14.7.6 Using Component Attributes

Ultimately audit framework does a union of all the attributes that you define for each event and defines a database column for each attribute. So try to define as many common attributes as possible.

14.7.7 Cross-Linking

Try to put in enough information so that events generated by your component can be cross linked to other events - for example, by ECID, session id, user name, and so on.