Purpose:
~~~~~~~~
This document explains how to purge, truncate, or delete rows from the audit
trail table SYS.AUD$.
Scope & Application:
~~~~~~~~~~~~~~~~~~~~
This document is intended for DBA's or Oracle Support Analysts.
a) To delete rows from the database audit trail table, an appropriate privilege
is required. You must either be the user SYS, or a user with DELETE ANY TABLE
system privilege, or a user to whom SYS has granted the object privilege
DELETE on SYS.AUD$.
b) To purge audit records from the audit trail, delete all rows:
-- DELETE FROM sys.aud$;
To delete rows from the audit trail related to a particular audited table:
-- DELETE FROM sys.aud$ WHERE obj$name='<TABLENAME>';
c) If audit trail information must be archived, copy the relevant rows to another
table before deletion, using either:
-- CREATE TABLE <audit archive table>
AS SELECT * from sys.aud$ WHERE 1=2;
-- INSERT INTO <audit archive table>
SELECT <rows> FROM sys.aud$
-- EXPort the <audit archive table> to an OS file, but do not export SYS.AUD$
directly.
d) Reducing the size of the audit trail:
1. If you want to save information currently in the audit trail, copy it to
another table and optionally export that table.
2. SQL> connect / as sysdba
SQL> TRUNCATE TABLE sys.aud$;
Truncate uses the DROP STORAGE clause which keeps 'minextents' extents,
thus only 1 extent.
3. Reload archived audit trail records generated from Step 1.
The rows inserted require as many extents as necessary to contain current
audit trail rows, but no more.
CAUTION: SYS.AUD$ is the only SYS object that should ever be directly modified
**** The DELETE ANY TABLE privilege only applies to SYS objects if O7_DICTIONARY_ACCESSIBILITY=TRUE
Related Documents:
~~~~~~~~~~~~~~~~~~
Oracle7 Server Administrator's Guide, Oracle7 Server Reference Guide
Note:166301.1 How to Reorganize SYS.AUD$ Table
Note:731908.1 New Feature DBMS_AUDIT_MGMT To Manage And Purge Audit
Information
© 2008, www.oracledatabase12g.com. 版权所有.文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.
相关文章 | Related posts:
- Extracting Data from a Corrupt Table using DBMS_REPAIR or Event 10231
- How to Perform a Health Check on the Database [ID 122669.1]
- Collecting Diagnostic information for Oracle block corruption
- Practice:Demonstrating Oracle AUDIT Concepts and Procedures
- TROUBLESHOOTING: Possible Causes of Poor SQL Performance
- Oracle Block Cache Layer And Its Corresponding Corruption Error Messages.
- TECH: Database Block Checking Features
- THE GAINS AND PAINS OF NOLOGGING OPERATIONS
- Understanding Bootstrap Of Oracle Database
- Script to Collect RAC Diagnostic Information (racdiag.sql)
New Feature DBMS_AUDIT_MGMT To Manage And Purge Audit Information
Applies to:
Oracle Server – Enterprise Edition – Version: 10.2.0.3 to 11.1.0.7 – Release: 10.2 to 11.1
Oracle Audit Vault – Version: 10.2.3 to 10.2.3]
Information in this document applies to any platform.
Purpose
This document briefly introduces the new DBMS_AUDIT_MGMT package that is used by Audit Vault to manage and purge audit data. It is intended to provide references to the relevant patches and documentation.
Scope and Application
The requirement for the introduction of this package came from Audit Vault. It is currently not supported to use the DBMS_AUDIT_MGMT package outside of Audit Vault. Its features include the purge of audit records and can be used to move the audit tables AUD$ and FGA_LOG$ to another tablespace. It can also be used to purge OS audit files including XML files.The Audit Trail Cleanup functionality will be available with patchset 11.1.0.7, this means 11.1.0.7 databases do not need additional patches to become a source of an Audit Vault. The package is documented in the Audit Vault 10.2.3 Admin Guide. Oracle will not be supporting the DBMS_AUDIT_MGMT in versions 9.2.0.x or 10.1.0.x
New Feature DBMS_AUDIT_MGMT To Manage And Purge Audit Information
References to Patches
The DBMS_AUDIT_MGMT package will become available in Oracle 11.2, and in patchset 11.1.0.7 but has also been made available as a separately installable patch for other versions, here are the references:
RDBMS version 11.1.0.6 :
There is no backport that supplies DBMS_AUDIT_MGMT on 11.1.0.6. Please upgrade to the 11.1.0.7 patchset in order to use this functionality.
RDBMS version 10.2.0.3 :
Patch 6989148 MLR ON TOP OF 10.2.0.3 FOR AUDIT TRAIL CLEAN-UP
Other merge patches that include this fix available on some platforms:
Patch 7460180 MERGE LABEL REQUEST ON TOP OF 10.2.0.3 FOR BUGS 6041317 6989148
Patch 7428689 MERGE LABEL REQUEST ON TOP OF 10.2.0.3 FOR BUGS 6989148 7129285
Patch 7557322 MERGE LABEL REQUEST ON TOP OF 10.2.0.3 FOR BUGS 7460180 6115797 5679561 5711129
Patch 9100457 MERGE REQUEST ON TOP OF 10.2.0.3.0 FOR BUGS 7311381 7428689 8567112 5548510
RDBMS version 10.2.0.4 :
Patch 6996030 MLR ON TOP OF 10.2.0.4 FOR AUDIT TRAIL CLEAN-UP
Other merge patches that include this fix available on some platforms:
Patch 7506476 MERGE LABEL REQUEST ON TOP OF 10.2.0.4 FOR BUGS 5679561 6996030
Patch 7685199 MERGE LABEL REQUEST ON TOP OF 10.2.0.4 FOR BUGS 5679561 6996030 7581770
Patch 7714556 MERGE LABEL REQUEST ON TOP OF 10.2.0.4 FOR BUGS 7006588 6996030
Patch 8198020 MERGE LABEL REQUEST ON TOP OF 10.2.0.4 FOR BUGS 6133008 7685199
Patch 8295186 MERGE LABEL REQUEST ON TOP OF 10.2.0.4 FOR BUGS 7652915 7685199
Patch 8648568 MERGE LABEL REQUEST ON TOP OF 10.2.0.4 FOR BUGS 6805009 7685199
Patch 8605144 MERGE LABEL REQUEST ON TOP OF 10.2.0.4 FOR BUGS 7592082 7685199
Patch 9433518 MERGE REQUEST ON TOP OF 10.2.0.4.0 FOR BUGS 8605144 8254572
Patch 9577923 MERGE REQUEST ON TOP OF 10.2.0.4.0 FOR BUGS 8295186 8365964
Patch 9711694 MERGE REQUEST ON TOP OF 10.2.0.4.0 FOR BUGS 8605144 7023214
Patch 9729279 MERGE REQUEST ON TOP OF 10.2.0.4.0 FOR BUGS 8605144 9545854
Patch 9761176 MERGE REQUEST ON TOP OF 10.2.0.4.0 FOR BUGS 9070891 9442339
Patch 9386929 MERGE REQUEST ON TOP OF 10.2.0.4.0 FOR BUGS 9353049 6437406
Patch 10013692 MERGE REQUEST ON TOP OF 10.2.0.4.0 FOR BUGS 9725892 8365964
RDBMS version 10.2.0.4.2:
Patch 9064839 MERGE LABEL REQUEST ON TOP OF 10.2.0.4.2 FOR AUDIT TRAIL CLEAN-UP
RDBMS version 10.2.0.4.3:
Patch 9628655 MERGE REQUEST ON TOP OF 10.2.0.4.3 FOR BUGS 9466949 6991606
RDBMS version 10.2.0.4.4:
Patch 9650152 MERGE REQUEST ON TOP OF 10.2.0.4.4 FOR BUGS 9589005 6805009 6991606
On Windows this fix is included in the Windows bundles since 10.2.0.4 bundle 23. Please refer to note 161549.1 for advise on the latest bundle. Note that on Windows the auditing behavior doesn’t change unless Audit Trail Cleanup is initialized with DBMS_AUDIT_MGMT.INIT_CLEANUP.
Please see the associated READMEs for patch instructions, besides the introduction of DBMS_AUDIT_MGMT these patches also fix a number of related audit problems and are therefore recommended to apply when your database will become an Audit Vault source. Of the collection of changes to auditing as referenced from note 6023472.8 a notable change is that there will be no longer any updates to AUD$ .
Exceptions Introduced by the new DBMS_AUDIT_MGMT package.
The new package DBMS_AUDIT_MGMT introduces a new range of ORA errors that may be raised as exceptions, these are referenced in note 602050.1 . For the backported version of dbms_audit_mgmt available with the patches listed above for technical reasons development did not include any updated message files so this means if any of the new exceptions is raised you will see the error being displayed as follows:
*
ERROR en l�nea 1:
ORA-46257: Message 46257 not found; product=RDBMS; facility=ORA
; arguments: [CLEAN_UP_INTERVAL]
ORA-06512: en “SYS.DBMS_AUDIT_MGMT”, l�nea 41
ORA-06512: en “SYS.DBMS_AUDIT_MGMT”, l�nea 1569
ORA-06512: en l�nea 1
This is expected behaviour and you will have to lookup the error in note 602050.1, you can substitute ‘%s’ with the arguments that are passed, so in this example the error means:
“Property CLEAN_UP_INTERVAL cannot be cleared”
How to cleanup the log table FGA_LOG$
Applies to:
Oracle Server – Enterprise Edition – Version: 9.2.0.1 to 10.2.0.2
Information in this document applies to any platform.
Goal
How to cleanup the log table FGA_LOG$ ?
Solution
The FGA_LOG$ table can be deleted from or truncated to manage its space, for example:
Connected.
Table truncated.
Alternatively you can delete records based on the TIMESTAMP# (date) column as follows:
This deletes all rows older than 2 weeks.
Cleaning up the records in FGA_LOG$ is much like cleaning up audit records for standard auditing in table SYS.AUD$ .
Moving AUD$ to Another Tablespace and Adding Triggers to AUD$
Applies to:
Oracle Server – Enterprise Edition – Version: 9.2.0.1
Information in this document applies to any platform.
Purpose
===============
It is strongly recommended to use the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION
provided with the DBMS_MGMT package instead of using the method described in this document, see
note 731908.1 “New Feature DBMS_AUDIT_MGMT To Manage And Purge Audit Information”
for more information and references. Also please understand the movement of the
audit tables SYS.AUD$ (or SYSTEM.AUD$ in case of OLS) and FGA_LOG$ tables is supported
by the DBMS_AUDIT_MGMT package. But, this gives no support on adding triggers to AUD$ table.
Moreover many additional audit features such as Alerts are possible with Audit Vault and should not
need to be implemented on the source database with custom triggers but by using a supported product feature.
The procedure described in this note is not officially supported, because the Oracle code makes implicit assumptions about the data dictionary tables such as SYS.AUD$, which could cause problems with upgrades and backup / recovery scenarios. However, in most cases the procedure works as described. If you encounter problems using a trigger on the relocated AUD$ table Oracle Support may suggest to delete it.
In case you decide to move AUD$ to a different tablespace, and the tablespace or datafile where you relocated is unavailable, you will not be able to use audit anymore, therefore your applications might get errors such as ORA-9925 or ORA-9817 In such cases, please correct the problem before calling support.
For more information on this issue, please read Oracle 8i Administration Guide, Chapter 24 – “Auditing Database Use”, under topic “Controlling the growth and size of the audit trail”:
The maximum size of the database audit trail (SYS.AUD$ table) is predetermined during database creation. By default, up to 99 extents, each 10K in size, can be allocated for this table. You should not move SYS.AUD$ to another tablespace as a means of controlling the growth and size of the audit trail. However, you can modify the default storage parameters in SYS.AUD$.
This article contains a script to move the “SYS.AUD$” table from the user SYS and tablespace SYSTEM to another user and tablespace. It also gives an example of a trigger that could be added to the resultant table for recording additional audit information.
This article is distributed externally due to customer demand.
Scope and Application
For DBA’s requiring to move SYS.AUD$ from the SYSTEM tablespace or recording additional audit information.
Moving AUD$ to Another Tablespace and Adding Triggers to AUD$
Overview
This script has been tested with Oracle8i, Oracle9i. It is NOT supported because the following are NOT supported:
The reason the above are not supported is because the Oracle Code makes implicit assumptions about the data dictionary tables such as SYS.AUD$, which could cause problems with upgrades and backup/recovery scenarios. ‘Not supported’ means Oracle Support cannot involve development if problems arise because of the triggers you put on the AUD$ table. It is strongly advised to seek supported solutions such as Fine Grained Auditing that can be setup to use a handler function for any additional processing you require.
Moving AUD$
Oracle stores audit trail records in a single table, “SYS.AUD$”, being a base data dictionary table, it is not supported to change it. However, it is supported to modify the default storage parameters (except INITIAL) in “SYS.AUD$”.
By changing the “SYS.AUD$” storage parameters, the table can grow as large as you require, but only inside the SYSTEM tablespace. The Oracle8i / Oracle9i Administrator’s Guide covers ways of managing this growth within the SYSTEM tablespace, but without the additional flexibility provided by moving “SYS.AUD$” to another tablespace.
The following script will allow you to move the “SYS.AUD$” table out of the SYSTEM tablespace and from the SYS to SYSTEM user (although it could be any other user).
1. Start the database with :
2. Run the following in Sqlplus:
create table system.aud$
tablespace <tablespace name>
as select * from aud$;
create index system.i_aud1 on system.aud$(sessionid, ses$tid);
rename aud$ to aud$_temp;
create view aud$ as select * from system.aud$;
connect system/<password>
grant all on aud$ to sys with grant option;
grant delete on aud$ to delete_catalog_role;
3. Re-Start the database with :
4. Recreate the data dictionary views for auditing :
Overview of adding triggers to AUD$
Default Oracle auditing purely stores a record of the type of action performed by a user; it is often useful to record additional information about the action performed by the user.
The following is a example of a trigger that could be added after the above script has been run :
create table system.accounting
(USERNAME VARCHAR2(30),
LOGIN_TIME DATE,
LOGOFF_TIME DATE,
SCHEMANAME VARCHAR2(30),
OSUSER VARCHAR2(15),
PROCESS VARCHAR2(12),
MACHINE VARCHAR2(64),
TERMINAL VARCHAR2(8),
PROGRAM VARCHAR2(64),
TYPE VARCHAR2(10),
LOGICAL_READ NUMBER,
PHYSICAL_READ NUMBER,
SID NUMBER,
SESSION_SID NUMBER,
CPU_TIME NUMBER)
tablespace <tablespace name>;
create or replace trigger acc_trigger
after update on system.aud$ for each row
when (new.action# = 101 or new.action# = 102)
declare
v_conc_id number;
begin
v_conc_id := 99;
insert into system.accounting
select :new.userid,
:new.timestamp#,
:new.logoff$time,
ss.schemaname,
ss.osuser,
ss.process,
ss.machine,
ss.terminal,
ss.program,
ss.type,
:new.logoff$lread,
:new.logoff$pread,
st.sid,
ss.audsid,
st.value
from V$STATNAME S, V$SESSTAT ST, V$SESSION SS
where S.STATISTIC# = ST.STATISTiC# AND ST.SID = SS.SID
and :new.sessionid = SS.AUDSID
and S.NAME=’CPU used by this session’;
end;
/
An example of querying the accounting table is :
to_char(LOGIN_TIME,’dd.mm.yy:hh:mi’) login,
to_char(LOGOFF_TIME,’dd.mm.yy:hh:mi’) logout,
MACHINE,
CPU_TIME
from accounting;