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.
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
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.
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?You could schedule a job with dbms_job to do the deletion.
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 commandDo 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.
delete from sys.aud$ where ntimestamp# < sysdate - 7 ;
but after deleting the data its showing the same size.why so?