This content has been marked as final. Show 5 replies
Assuming Oracle 11 ( Oracle 10 may have issues )
1.Will auditing enable in production datbase will automatically sync in DR database?
2.Or we need to enable user level auditing manually in DR server?
The time it takes to bounce a system.
3.How much down time is require to enable user level audit?
Steps ( On Primary )
4. please provide me steps by steps procedure of user level auditing in dataguard enable.
alter system set audit_trail = DB scope=spfile Restart database
Note: To check what is set run: SELECT * FROM DBA_STMT_AUDIT_OPTS;
audit grant any object privilege; audit create session; audit alter user; audit create user; audit drop user; audit drop tablespace; audit grant any role; audit grant any privilege; audit alter system; audit alter session; audit delete on AUD$ by access; audit insert on AUD$ by access; audit update on AUD$ by access; audit delete table; audit create tablespace; audit alter database; audit create role; audit create table; audit alter any procedure; audit create view; audit drop any procedure; audit drop profile; audit alter profile; audit alter any table; audit create public database link;
A few checks
By Session SELECT A.USERNAME, OS_USERNAME, A.TIMESTAMP, A.RETURNCODE, TERMINAL, USERHOST FROM DBA_AUDIT_SESSION A WHERE USERNAME = ? By Create User SELECT OS_USERNAME, USERNAME, USERHOST, OBJ_NAME, TIMESTAMP FROM DBA_AUDIT_OBJECT WHERE ACTION_NAME = 'CREATE USER'; By Alter User SELECT OS_USERNAME, USERNAME, USERHOST, OBJ_NAME, TIMESTAMP FROM DBA_AUDIT_OBJECT WHERE ACTION_NAME = 'ALTER USER'; By Drop User SELECT OS_USERNAME, USERNAME, USERHOST, OBJ_NAME, TIMESTAMP FROM DBA_AUDIT_OBJECT WHERE ACTION_NAME = 'DROP USER'; Audit SELECT select username, priv_used, ses_actions from dba_audit_object where obj_name='EMP' and owner='SCOTT'; How to Audit User Connection, Disconnection Date and Time select sessionid, to_char(ntimestamp#,'DD-MON-YY:HH24:MI:SS') login, userid, to_char(logoff$time,'DD-MON-YY:HH24:MI:SS') logoff from sys.aud$ where userid='SCOTT';
My oracle version is Oracle9i Enterprise Edition Release *22.214.171.124.0* - 64bit & Oracle Database 10g Enterprise Edition Release *10.2.0.5.0* - 64bit.
1.I have to enable user level audit logs in 9i,10g , but am not sure whether in Oracle 9i,10g it will automatically sync or not?
2.If not then how to do manually user level auditing in oracle 9i,10g in both production & DR.
3.If not then please provide me step by step procedure.
4.After enable audit, any log issue or not?I mean logs will be in sync or not?
My production database version is Oracle9i Enterprise Edition Release *126.96.36.199.0* and DR database version is Oracle9i Enterprise Edition Release *188.8.131.52.1* .
If manually will do auditing,will version effect user level auditing or not?
Edited by: Meenakshy singh on Nov 27, 2012 2:02 PM
Edited by: Meenakshy singh on Nov 27, 2012 2:10 PM
Oracle 9 and 10 are going to be a problem.
I have these short notes on this :
If you are using a version lower than 11 you will probably get this error if set to DB :
Current primary database must have "audit_trail" set to "none" audit trail parameter should be in OS or NONE when standby database configured.
Here's my note ( to self ) on this :
ORA-16006: audit_trail destination incompatible with database open mode
So no its not going to sync, the best you can hope for is some OS audit files.
Once you upgrade to 11 everything you want is handled by Oracle.
If i set parameter audit_trail parameter = DB in primary database and set parameter audit_trail =OS in DR database, and after 4 month will delete data from both Production and DR database.In that case what happen.. (9i,10G)
1.can I easily delete data from DR database since audit_trail=OS and in production which is set to audit_trail =DB? or need take some precaution before deleting.
2.or can i set audit_trail =OS in both production and DR database?Any issues?
3.will it affect the system performance after enable audit_trail =OS?
4.since audit_trail=OS in both production and DR ,it will create a audit file in OS level.will it create any problem while deleting these files from OS LEVEL from both production and DR datbase.