Re: Auditing

From: Mladen Gogala <mladen_at_wangtrading.com>
Date: Fri, 27 Feb 2004 16:46:58 -0500
Message-ID: <20040227214658.GA18579@mladen.wangtrading.com>

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_at_petefinnigan.com
> 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_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://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_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri Feb 27 2004 - 15:44:31 CST