RE: Large sys.aud$

You do not want this to be sys.<anything> on the centralized database.

Something more like: hostname_dbname (if it is short enough across your server farm) to contain all the relevant data pulls you want to centralize probably works out better. Basically you don't want to be in the business of making custom versions of Oracle's sys owned tables. On future upgrades you'll then check all your centralized history objects for a change versus the previous release, and if required rename the changed objects <prev_name>_<upgrade_date> and install the new object definitions on your centralized database. One extra table that contains the detailed RDBMS version/patch/upgrade rows by date for each schema will give you a history of the relevant changes in case you build reporting tools on the centralized repository so you'll be able to query the highest dated row to choose the correct release of your reporting tools for a given <hostname_dbname>.

Regards,

mwf

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Terrian, Thomas J Mr CTR DLA J6DIB Sent: Monday, December 08, 2008 7:33 AM
To: oracle-l_at_freelists.org
Subject: Large sys.aud$

All, we have a requirement to keep 1 year worth of audit records. I am kicking around the idea of hourly moving all of the sys.aud$ records from each production database to a central database. That way it would keep sys.aud$ small for each database.

However, this will have its own set of complications......I would have to build new dba_audit views on the central database (in order to have a database name field), what happens when the structure of sys.aud$ changes between database versions, etc.

Has anyone tackled something like this already?

Another option would be to forget about the repository database idea .....instead hold 1 months worth of data online for each database and storing the other 11 months offline somewhere (maybe use RMAN for this?).

Again, any ideas?........

Thanks,
Tom Terrian

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Mon Dec 08 2008 - 10:47:21 CST