Re: Auditing

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:3e2680f4$0$227$ed9e5944_at_reading.news.pipex.net...
> I have tried to avoid auditing (on principle) until now, but I am now
likely
> to have to implement AUDIT SELECT ON... for a number of tables in our
> intranet solution. Taking a quick browse through the docs I see
>
> The maximum size of the database audit trail (SYS.AUD$ table) is
determined
> by the default storage parameters of the SYSTEM tablespace, in which it is
> stored. 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 storage parameters for SYS.AUD$.
>
>
> --------------------------------------------------------------------------
> Note:
> Moving the SYS.AUD$ table out of the SYSTEM tablespace is not
> 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.
>
> --------------------------------------------------------------------------
>
>
> Is this in fact just rubbish as far as backup and recovery goes? I can't
see
> how this dire warning makes any sense at all. I'm not convinced about the
> upgrade scenario either, but that can be dealt with by purging the audit
> trail and moving the table back before upgrade.

Exactly. Oh, and move it back before you call Oracle Support, too (!). This is one of those mysterious one: the DBA courseware specifically mentions (chapter 18 page 21 in the 8i stuff, chapter 18 page 5 in the 9i version) precisely how to move move the table into its own tablespace. They also suggest there to create an index on AUD$ in its own separate tablespace, too (shades of a previous discussion, I think!).

So Oracle's own courseware tells you how to move it, and recommends that you do it. But no, it's not supported!!

I, too, can't make head nor tail of the dire warnings about backup and recovery scenarios. Either thing is available or it's not. The physical location of it is irrelevant. I suppose their concern is that in a separate tablespace, you might lose the data files associated with that tablespace. It's going to be tricky opening a database with auditing switched on when AUD$ can't be found. If you leave it in SYSTEM, then the loss of the system data file would mean you wouldn't be able to open the database anyway.

Maybe that's what they're getting at.

But its a counsel of despair at that point, isn't it: "If you stuff things up, you will be stuffed". Point rather is, I would hope, trained DBAs wouldn't lose the separate tablespace in the first place, and therefore wouldn't run into trouble.

Regards
HJR Received on Thu Jan 16 2003 - 04:04:31 CST