How to enable and disable standard audit trail on Oracle Database 10g onward
Enable standard Audit Trail
1. Log in as SYS with SYSDBA privileges
[oracle@labdbt2 bin]$ sqlplus "/as sysdba"
2. Check the current settings
SQL> show parameter audit NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string /oracle/admin/dblabt1/adump audit_sys_operations boolean TRUE audit_syslog_level string audit_trail string NONE unified_audit_sga_queue_size integer 1048576
3. Set the type of auditing you want by setting the audit_trail parameter
SQL> alter system set audit_trail=DB scope=spfile; System altered.
4. if you need to set or change the auditing destination but if not, you can pass this step.
SQL> alter system set audit_file_dest='/oracle/admin/db12ct/adump' scope=spfile;
5. Restart the Oracle instance
System altered. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup; ORACLE instance started. Total System Global Area 2466250752 bytes Fixed Size 8795760 bytes Variable Size 603982224 bytes Database Buffers 1845493760 bytes Redo Buffers 7979008 bytes Database mounted. Database opened. SQL> show parameter audit NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string /oracle/admin/dblabt1/adump audit_sys_operations boolean TRUE audit_syslog_level string audit_trail string DB unified_audit_sga_queue_size integer 1048576
Disable standard Audit Trail
1. Log in as SYS with SYSDBA privileges
[oracle@labdbt2 bin]$ sqlplus "/as sysdba"
2. Check the current settings
SQL> show parameter audit NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string /oracle/admin/dblabt1/adump audit_sys_operations boolean TRUE audit_syslog_level string audit_trail string DB unified_audit_sga_queue_size integer 1048576
3. Set the type of auditing you want by setting the audit_trail parameter
SQL> alter system set audit_trail=NONE scope=spfile; System altered.
4. if you need to set or change the auditing destination but if not, you can pass this step.
SQL> alter system set audit_file_dest='/oracle/admin/db12ct/adump' scope=spfile;
5. Restart the Oracle instance
SQL> show parameter audit NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string /oracle/admin/dblabt1/adump audit_sys_operations boolean TRUE audit_syslog_level string audit_trail string DB unified_audit_sga_queue_size integer 1048576 SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup; ORACLE instance started. Total System Global Area 2466250752 bytes Fixed Size 8795760 bytes Variable Size 603982224 bytes Database Buffers 1845493760 bytes Redo Buffers 7979008 bytes Database mounted. Database opened. SQL> SQL> show parameter audit NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string /oracle/admin/dblabt1/adump audit_sys_operations boolean TRUE audit_syslog_level string audit_trail string NONE unified_audit_sga_queue_size integer 1048576
Auditing Oracle Database Activity
How to disable and enable audit privilege on Oracle Database 10g onward