- None – Database auditing is disabled - os – Enabled, audit logs are stored at OS level, not inside the database - db – Enabled, audit records are stored inside database (SYS.AUD$ table). If we are creating database by DBCA Utility , then default it is DB else it is none. - db,extended – Same as db but populates SQL_BIND & SQL_TEXT too - xml – Enabled, audit records are stored at OS level in XML format - xml,extended – Same as xml but populates SQL_BIND & SQL_TEXT too If auditing is not enabled, the Oracle Database Adapter cannot retrieve the information about when the user last accessed the account. To enable auditing , follow the below steps :

1
2
3
4
5

Show parameter audit;

alter system set AUDIT_TRAIL=<status value>, extended scope=spfile;

alter system set AUDIT_TRAIL= db, extended scope=spfile;

- Set the initialization parameter audit_trail to TRUE in the init.ora file (pfile). - issue the following command at the SQL prompt:  ALTER SYSTEM SET audit_trail=TRUE scope=SPFILE. - Restart the database instance. - To turn on the auditing for user  log on as a user with  admin authority and Issue the following command at the SQL  prompt: AUDIT CONNECT. - To verify that auditing is enabled on an instance, issue the following command at the SQL  prompt: SHOW PARAMETER AUDIT_TRAIL. AUDIT_FILE_DEST : The AUDIT_FILE_DEST parameter defines the OS level location of the audit trail files. By default it is set to adump location. All the SYS operations audit records goes to OS level into AUDIT_FILE_DEST location. audit_file_dest  default Location is  $ORACLE_HOME/rdbms/audit.  The default value is: ORACLE_BASE / admin/ ORACLE_SID/adump. This create files with the .aud extentionto audit operations. If you set audit_sys_operations=true .

There is a separate file for every session that connects as SYSDBA.

1
2
3
4
5
6
7

SQL> show parameter audit

NAME                   TYPE          VALUE
----------------------- ----------- --------------
audit_file_dest          string      /u01/app/oracle/admin/prim/audit
audit_sys_operations     boolean     TRUE
audit_trail              string      DB