Hello Community,

      I need to use DBMS_AUDIT_MGMT to manage DB auditing on one of the databases. The requirement is to store one year's worth of data to another table (other than SYS.AUD$). I realize the package can be used to move SYS.AUD$ table outside of SYSTEM tablespace and define purging/archiving durations on that table. I am not very clear on archiving though. Can this package also be used to archive data from SYS.AUD$ periodically to another table before the purge kicks off on AUD$ or do I have to make a tiny custom script for that.

Also, the DB doesn't need to be in restricted mode in this situation, correct?

Thank you.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

The problem with audit is tjat it is a critical path for your database.
During any maintenance on SYS.AUD$ the users will wait. In order to minimize this unavailability i used to copy data from SYS.AUD$ with "insert as select". It has to be done before the purge and it has to be done manually.

PS: the package DBMS_AUDIT_MGMT only exists in db 11g. In previous versions you have to do all by yourself.
WaqasulhaqAuthor Commented:
Hi chlautau,
  Thanks for the response. I thought there was a dbms_audit_mgmt patch available for
Nevertheless, I also thought about doing it all manually. Please help me out with the steps:
1) generate report from sys.aud$  (requirement from my manager)
2) insert into table_a (select * from sys.aud$)
3) purge sys.aud$

Does the DB have to be in restricted mode for any activity on sys.aud$?
Basically I don't want to move sys.aud$ out of system tablespace anyways. But I do want to purge it via cronjob and prior to that, insert all records into table_a.

Thanks for your help.
WaqasulhaqAuthor Commented:
Is manually purging sys.aud$ (in system tablespace) supported by oracle on
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

If I were you I would move SYS.AUD$ from SYSTEM. It will grow a lot depending on how much audit you are going to do.

As I told you before any audit record maintenance serializes db operations, so you have to carefully analize when to purge AUD$; nevertheless, there is no need to restrict the db but any audited operation will wait for the purge operation to end.

When I had to audit the db I did the following:
- Move AUD$ to another tbs (if audit_trail=none it could be done at any time)
- Create mirror table to copy records
- create a procedure that: lock AUD$, copy to mirror table, truncate AUD$

The lock is necessary to avoid missing audit records.

Further, the mirror table should have no indexes to speed the copy. If you need to generate reports to your manager try to generate reports with day-1 info only. So, there will be no read at AUD$.
WaqasulhaqAuthor Commented:
Thanks again for the helpful response.
Is moving AUD$ table out of SYSTEM tablespace supported by oracle on I was under the impression that it isn't.
I don't exactly see the benefit of moving sys.aud$ table out of SYSTEM, when you are purging AUD$ anyways, why not do it within SYSTEM tablespace.
Im also confused about the statement that audited operations will wait during the purge but i still need to have a procedure to lock aud$ prior to copying to another table.

Moving AUD$ from system avoids fragmentation in SYSTEM. It is fine to move AUD$ away from SYSTEM in 10g.

Let me explain the need to lock AUD$ during copy. For instance, you are auditing db login (this event  happens almost all the time) and you need 1 second to copy data from AUD$ to mirror table. While you are copying data to mirror table users still connecting to database. If you purge tha AUD$ you will loose the audit records of logins the happened while you were copying data to mirror.

That's why you need to lock the AUD$ table.
WaqasulhaqAuthor Commented:
So, all the time when the SYS>AUD4 table is locked, the ongoing audit records will be queued during that time and then pushed into SYS.AUD$ once the lock is released AUD$ is purged?
Yes, that is how it works!
There will be no audit records lost.
WaqasulhaqAuthor Commented:
Appreciate the input.
I did however, notice on oracle's website that moving SYS.AUD$ out of system tablespace is not supported in 10g. Also, I NEED to use the auditvault with DBMS_AUDIT_MGMT in 10gR2.
The reason why my manager doesn't want me to move AUD$ out of SYSTEM is becuase in the future, we might have issues with DB upgrades or restores.

If I need to keep AUD$ in SYSTEM in 10g, shall I just 'delete from sys.aud$' giving the "where ntimestamp# >" clause instead of purging it and at the same time LOCK the table while records are being copied to the archive table. Will there still be fragmentation issues?
OK, do not move AUD$ from SYSTEM. There is going to be fragmentation issues but it is really not supported in 10g.

I would not do delete because it takes (much) longer than truncate and do not release space.

To avoid SYSTEM fragmentation you may redefine the storage parameter of AUD$ to keep a large fixed size, let's say 2GB (I do not know how big it is going to be), and control the number of records in AUD$ and start the copy of records/truncate routine.

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.