Oracle Audit Log Quick Setup

Oracle Audit Log Settings

Use the following statement to change the AUDIT_FILE_DEST parameter to write audit logs to the /audit_logs directory:

ALTER SYSTEM SET AUDIT_FILE_DEST='/audit_logs' SCOPE=SPFILE;

Grant user groups

chown -R oracle:dba /audit_logs

Save the query statement to a file through SQL statement

SELECT * FROM dba_audit_trail WHERE action_name LIKE '%SQL%' 
INTO OUTFILE '/your/path/to/' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';

After Oracle 11g is installed, the database audit function will be enabled by default, and the logs are saved in the SYSTEM tablespace.

This leads to the increasing space for SYSTEM. When the tablespace is full, it will cause the database to be unable to connect.

It is recommended to turn off this function after the database installation is completed and turn it on when necessary.

Parameter description

The audit function is controlled by the parameter audit_trail, and the value range and meaning are as follows:

Parameter value Description

DB (default) enables audit function.
OS writes audit records to a file in the operating system
TRUE Enables the audit function.
FALSE Turn off the audit function.
NONE Turn off the audit function.

Check whether the audit function is enabled

a) Log in to SQL plus as DBA, and all the following commands are executed in this mode;

sqlplus / as sysdba

b) View the value of audit_trail

SQL> SHOW PARAMETER AUDIT

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /data/app/oracle/admin/orcl/ad
                                                 ump
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      DB

According to the query results, the database audit function has been enabled.

Turn off database audit function

a) Execute the following statement to turn off the database audit function.

ALTER SYSTEM SET AUDIT_TRAIL='NONE' SCOPE=SPFILE;

b) Restart the database

SQL> shutdown immediate;
SQL> startup;

Verify that the audit has been closed

SQL> show parameter audit_trail

NAME TYPE VALUE

audit_trail string FALSE

Description: VALUE value is FALSE, and the surface audit function is turned off

Turn on the audit function

SQL&gt; alter system set audit_sys_operations=TRUE scope=spfile;--Audit management users(bysysdba/sysoperCharacter login)
SQL&gt; alter system set audit_trail=db,extended scope=spfile; -- This is to put the audit data into the database
SQL&gt; ALTER SYSTEM SET audit_trail='xml' SCOPE=spfile; -- This is to put the audit data intoxmlIn the file

Finally, restart the service and start it.

Delete the audit log:

When a very specific log has been formed, the records inside can be deleted. Currently, they are directly deleted and have no impact on the database.

Query the current log information：select * FROM $;
Delete existing audit information：DELETE FROM $;

Or quickly delete table information:

truncate table $;

It is generally recommended that if the audit function is not used after deploying oracle, it is turned off to save space.

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.