Simple Steps to Enable Extended Auditing in Oracle







Enabling auditing in dbsqlplus / as sysdba

ALTER SYSTEM SET audit_trail=db,extended SCOPE=SPFILE;

shutdown immediate

startup
table for audit trailsDBA_AUDIT_TRAIL
view the size of auditselect round(bytes/1024/1024/1024) from dba_segments where segment_name ='AUD$';
see the current tablespace of audit tableselect tablespace_name from dba_tables where table_name='AUD$';
Audit notesMaster Note For Oracle Database Auditing (Doc ID 1299033.1)
To audit a specific schemaAUDIT ALL STATEMENTS BY <username> ;




Moving $AUD table from SYSTEM TS to a dedicated Table SpaceIf your AUD$ table is in SYSTEM and SYSTEM tablespace, Then it is advised to move the AUD$to a dedicated tablespace.


Use steps to move AUD$.select owner,segment_name,segment_type,tablespace_name,bytes/1024/1024 from dba_segments where segment_name='AUD$';



OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES/1024/1024

------- ------------- ------------ ---------------- ----------------

SYS AUD$ TABLE SYSTEM 16


Use the dbms_audit_mgmt to move the tablespace.BEGIN

DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,

audit_trail_location_value => 'AUDIT_DATA');

END;

/

PL/SQL procedure successfully completed.




check whether tablespace has been moved from system to AUDIT_DATA or not.select owner,segment_name,segment_type,tablespace_name,bytes/1024/1024 from dba_segments where segment_name='AUD$';

OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES/1024/1024

------- ------------- ------------------ ------------------------------ ---------------

SYS AUD$ TABLE AUDIT_DATA 16


REFERENCE:

Comments

Post a Comment

Popular posts from this blog

RMAN backup Status (Remaining Time and Percentage)

EBS R12.2 Autoconfig Failed. Resolved

Cleaning nodes in EBS databae.