This discussion is archived
5 Replies Latest reply: Jan 4, 2013 3:03 AM by JohnWatson RSS

Enable Auditing in production & DR server.

Meenakshy singh Newbie
Currently Being Moderated
Hi Gurus,

I have an issue regarding enable audit in DR server.

My oracle version is 9i with dataguard enabled.My requirement is to enable user level audit in PROD and DR server as well.Since DR is always in mount stage.

1)So how can I enable user level audit in DR Server?

2)As per my understanding when we enable audit in PROD database and not in DR database then more no. of logs will be generate in PROD,which will result in asynchronization of logs between Production & DR database.So how to avoid this one?

3)Is there any way to specify the size of user level audit?or any method or script to delete the old records?If so,can you provide me?

4)How long we need to do clean up of old data and how?




Thanks & Regards,
Meena
  • 1. Re: Enable Auditing in production & DR server.
    CKPT Guru
    Currently Being Moderated
    Hi Meena,
    My oracle version is 9i with dataguard enabled.My requirement is to enable user level audit in PROD and DR server as well.Since DR is always in mount stage.
    1)So how can I enable user level audit in DR Server?
    Enabling Auditing in primary database is sufficient. Auditing usually enabled to watch any DML's or any operations on table or failed logins and so on.
    In Standby database, there will be no modifications on objects or no user except SYS can connect if in mount status. Of course you can connect with other users if it is in "Open - Read Only Mode".

    If it is logical standby, then of course you can expect DML/DDL's on it. In your case enabling auditing on primary database is sufficient. From 11gRx versions auditing is more enhanced with Data Guard, You can refer MOS note *Is Oracle Auditing Working On A Standby Database ? [ID 835638.1]*

    2)As per my understanding when we enable audit in PROD database and not in DR database then more no. of logs will be generate in PROD,which will result in asynchronization of logs between Production & DR database.So how to avoid this one?
    Synchronization is in terms of Archive logs, Why you worried of synchronization of audit log files? Nothing to worry on synchronization of audit logs between primary and standby databases.

    3)Is there any way to specify the size of user level audit?or any method or script to delete the old records?If so,can you provide me?
    You can review auditing limitations of oracle 9i from below link
    http://docs.oracle.com/cd/B10501_01/server.920/a96524/c25audit.htm

    4)How long we need to do clean up of old data and how?
    This depends on business requirement, Lets suppose you have a 3 months of audit logs, If there are no such incidents or no requests to audit on particular time before than 1 month, Then you can cleanup all audit log files except this month.

    --Thanks                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
  • 2. Re: Enable Auditing in production & DR server.
    Shivananda Rao Guru
    Currently Being Moderated
    Hello,

    As said earlier, auditing cannot be enabled on a standby database because standby database cannot be logged in by any other user apart from SYS and even if you try to stop the recovery and open the standby, it would be in read only mode and does not allow to enable auditing.

    On the primary you can set the audit_trail to DB and on the standby you can have it set to NONE. Irrespective of having the auditing enabled, with more transactions occurring on the primary, archives are always generated and they will be taken care by Oracle to have them applied to standby.


    Regards,
    Shivananda
  • 3. Re: Enable Auditing in production & DR server.
    Meenakshy singh Newbie
    Currently Being Moderated
    Hi All,

    Noted with Thanks.

    have one more doubt---

    1)How to manage records after enable audit?I need to do only audit session & audit connect list of users logon,name and logoff time.

    For ex- I have to delete the records which is older than 2 months so that no space issue can occur.For that any script or any command to do it?Or we can do any automated purging?
    Kindly provide me step by step method.

    I tried in development server.I am trying to delete the data of last 7 days data by using this command
    delete from sys.aud$ where ntimestamp# < sysdate - 7 ;

    but after deleting the data its showing the same size.why so?
  • 4. Re: Enable Auditing in production & DR server.
    Acooper Explorer
    Currently Being Moderated
    End of Support and End of Life Support for Oracle 9

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1473143300346925692
  • 5. Re: Enable Auditing in production & DR server.
    JohnWatson Guru
    Currently Being Moderated
    >

    >
    For ex- I have to delete the records which is older than 2 months so that no space issue can occur.For that any script or any command to do it?Or we can do any automated purging?
    Kindly provide me step by step method.
    You could schedule a job with dbms_job to do the deletion.
    >
    I tried in development server.I am trying to delete the data of last 7 days data by using this command
    delete from sys.aud$ where ntimestamp# < sysdate - 7 ;

    but after deleting the data its showing the same size.why so?
    Do you mean that the table segment is the same size? That is normal, deleting rows will not reduce segment size. Which is fine, the space will be re-used as more audit rows are generated.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points