Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2004
    Posts
    4

    Unanswered: Review Oracle Audit Output

    Hi,

    I'm currently looking at auditing for my production databases. Whether the audit trail is output to either DB or OS, each has its +ve and -ve points.

    In my opinion, the DB output is definitely more readable, as compared to OS output. However, the risk is higher, as it resides in the SYSTEM tablespace. Thus, I'm assessing the OS output. Are there any reporting tools available in the market that can present the output in a presentable format.

    Please advise. Cheers...
    Last edited by robwhitz; 12-07-04 at 05:05.

  2. #2
    Join Date
    Nov 2004
    Posts
    4
    By the way, I'm running 8.1.6.

  3. #3
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246
    One of the main reasons for keeping audit data in the database is for ease of reporting. Google for Oracle related audit tools that read from the operating system's audit trail. You might find some home made stuff. With Oracle running on X number of operating systems, and every operating system formatting their audit trail differently, not many people want to make such reporting software.

    Question. Why do you say the audit data is MORE vulnerable in the SYSTEM tablespace than the operating system? There should be far fewer DBAs with access to your audit table than the number of admins with rights to your system audit trail. If you are in a large office with thousands of computers, you might have two dozens people with domain / sys admin rights, but less than a half dozen DBAs with rights on any particular database. (Also, it's in the protected SYS schema, which is more important to note than the tablespace since that's just a mapping between a table and an I/O device.)
    MarkRem
    Author, Oracle Database 10g: From Nuts to Soup
    http://www.remidata.com/book_nuts2soup.htm

  4. #4
    Join Date
    Nov 2004
    Posts
    4
    Quote Originally Posted by markrem
    One of the main reasons for keeping audit data in the database is for ease of reporting. Google for Oracle related audit tools that read from the operating system's audit trail. You might find some home made stuff. With Oracle running on X number of operating systems, and every operating system formatting their audit trail differently, not many people want to make such reporting software.

    Question. Why do you say the audit data is MORE vulnerable in the SYSTEM tablespace than the operating system? There should be far fewer DBAs with access to your audit table than the number of admins with rights to your system audit trail. If you are in a large office with thousands of computers, you might have two dozens people with domain / sys admin rights, but less than a half dozen DBAs with rights on any particular database. (Also, it's in the protected SYS schema, which is more important to note than the tablespace since that's just a mapping between a table and an I/O device.)
    I totally agree with you that reporting for audit trail kept in the database is definitely more intuitive than that in the OS.

    Perhaps I was not specific enough when I mention that storing the audit trail in the database is of higher risk. My concern was on the SYSTEM tablespace. What if the SYSTEM tablespace is being used up? The constant housekeeping of the audit trail will leave the tablespace pretty fragmented as well. All these will impact the performance of the database.

    As I'm on 8.1.6, it was not recommended by Oracle to store the audit trail on any tablespace, other than SYSTEM. So my next alternative is to look at OS.

  5. #5
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246
    Yes, I know what you mean, and I wish I knew of a solution like you asked for. However, I don't think one exists, so all I can offer is some of my experiences using sys.aud$ table in the system tablespace.

    First, it must remain in the SYSTEM tablespace. It is not created like normal tables. It's referenced by C pointers. If the database crashed and you did a recovery, the aud$ table can only be recovered in the SYSTEM tablespace. There are some teary-eyed people out there who posted to Oracle MetaLink and other forums: they moved aud$ to keep SYSTEM from becoming fragmented, and after a crash they lost the whole audit trail.

    Second, I agree with you that keeping several hundred gigabytes of audit data in the SYSTEM tablespace is a bad idea. Likewise, the new Oracle 10g really dropped the ball by moving the aud$ table to the new SYSAUX tablespace where it's the 800 pound gorilla in a tablespace designed to store nothing but catalogs.

    My recommendation has always been this ...
    1. create tablespace audit_save.
    2. copy the structure of table aud$ to a new table in tablespace audit_save. For example, create table sys.aud$save tablespace audit_save select * from aud$ where 1=2.
    3. create a stored procedure and dbms_job to move the data from sys.aud$ to sys.aud$save every night. For example, insert into aud$save select * from aud$ timestamp < trunc(sysdate) followed by delete from aud$ where timestamp < trunc(sysdate).
    The SYSTEM tablespace is no longer bothered by the large audit trail, and recovery is fully supported.

    The only remaining problem is how to best optimize it for reporting. You will want to index the new table, and if you have Oracle Partitioning you will probably want to create rolling partitions. For example, one partition per week that you can drop after N weeks have passed.
    MarkRem
    Author, Oracle Database 10g: From Nuts to Soup
    http://www.remidata.com/book_nuts2soup.htm

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •