Re: Auditing
- From: Mladen Gogala <mladen@xxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 27 Feb 2004 16:46:58 -0500
Let me bring the following to your attention:
~~~~~~~~~~~
Note:1019377.6: Script to move SYS.AUD$ table out of SYSTEM tablespace
Note:166301.1 : How to Reorganize SYS.AUD$ Table
Note:73408.1 : How to Truncate, Delete, or Purge Rows from the Audit Trail
Table SYS.AUD$
bug 531557 : TRIGGER ON AUD$ CAUSES CORE DUMPS
Here is what the script header says:
bstract
Move SYS.AUD$ out of the SYSTEM tablespace
Product Name, Product Version
Oracle Server Enterprise Edition
Versions 8.1.7 , 9.0.1 and 9.2.0.
Platform Generic
Date Created 29-OKT-2002
Instructions
Use sqlplus, connect as SYSDBA, do not use reserved words as AUDIT for
tablespace as in the example.
You should be aware that moving AUD$ out of SYSTEM tablespace is *not* a
supported procedure. Oracle does not support changing ownership of AUD$, or any
triggers on it. For a complete discussion on this topic see <Note 72460.1>
PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text
editors, e-mail packages, and operating systems handle text formatting (spaces,
tabs, and carriage returns), this script may not be in an executable state
when you first receive it. Check over the script to ensure that errors of
this type are corrected.
ET ECHO off
REM NAME: TFSAUDMV.SQL
REM USAGE:"@path/tfsaudmv.sql"
REM --------------------------------------------------------------------------
REM REQUIREMENTS:
REM Should be tun as SYS or connect internal
REM --------------------------------------------------------------------------
REM AUTHOR:
REM Scott Gossett
REM --------------------------------------------------------------------------
REM PURPOSE:
REM The purpose of this script is to move the existing SYS.AUD$ table
REM and its associated index I_AUD1 to a different tablespace.
REM This script creates a new tablespace AUD that will be used to
REM hold both objects. The example file size is too small for production
REM environment!!
REM ---------------------------------------------------------------------------
REM EXPLANATION:
REM Oracle stores audit trail records in the SYS.AUD$ base data dictionary
REM table. The problem is this table grows inside the SYSTEM tablespace
REM and must have records deleted from it or be truncated, otherwise it
REM takes up all the room in the system tablespace. This deleting and
REM truncating of the SYS.AUD$ table fragments the system tablespace.
REM
REM The following script allows a DBA to move SYS.AUD$ out of the SYSTEM
REM tablespace. By moving it out of system tablespace, control of the
REM table's size can be controlled without filling or fragmenting the
REM system tablespace.
REM ---------------------------------------------------------------------------
REM DISCLAIMER:
REM This script is provided for educational purposes only. It is NOT
REM supported by Oracle World Wide Technical Support.
REM The script has been tested and appears to work as intended.
REM You should always run new scripts on a test instance initially.
REM --------------------------------------------------------------------------
REM Main text of script fol
Given the author of the script, I'd think that it's OK to use it.
On 02/27/2004 02:20:44 PM, Pete Finnigan wrote:
> Hi,
>
> Oracle used to sanction moving aud$ and then they didn't sanction it
> anymore. Not sure what they current view on moving it is - probably best
> to simply ask. This was discussed on comp.databases.oracle.server last
> year 8/9/03 and this is what i answered then if it helps:
>
> <quote>
>
> You mention moving sys.aud$ to another tablespace, whilst this is common
> sense Oracle no longer support doing this as some poor customer could
> not recover when this had been done. I don't believe Oracle have changed
> advice on this as yet. There is a note on Metalink about it. The
> solution if it is moved is probably to switch audit off whilst
> recovering. Jonathan discussed the following with me some time back:
>
> <snip>
> I haven't done any tests on the theory - it was just
> a surmise that when the complainant was trying recovery,
> they needed to recover the tablespace with the aud$ table
> in it, but were running with audit on, so the recovery
> processes couldn't log themselves until after the recovery
> had completed.
> </snip>
>
> kind regards
>
> Pete
> <quote>
>
> The snip section was Jonathan Lewis's thoughts on this to me.
>
> hth
>
> kind regards
>
> Pete
> --
> Pete Finnigan
> email:pete@xxxxxxxxxxxxxxxx
> Web site: http://www.petefinnigan.com - Oracle security audit specialists
> Book:Oracle security step-by-step Guide - see http://store.sans.org for
> details.
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts: