About Oracle 11g Audit files

About audits:

11g By default, the auditing option is enabled, and the default value of the Audit_trail parameter is DB, which indicates that the audit data will be recorded on the sys.aud$ Audit Dictionary base table in the database. In Oracle 10g, the default value of this parameter is none, which means that auditing is not enabled. Oracle claims that the audit logs enabled by default do not adversely affect the performance of most product databases, and Oracle recommends using an OS file-based audit logging method (OS audit trail files).

Because the Create session in 11g is logged as an audited permission, the system table space cannot be built due to disk space and will cause this audit record to fail, resulting in a normal user's new session not being created properly. Normal users will not be able to log in to the database. In this scenario, users can still create sessions with SYSDBA identities, delete a subset of records after the appropriate backup of the audit data, or truncateaud$ can resolve the problem.

When Audit_trail is set to OS, the audit log file is generated in the directory specified by the Audit_file_dest parameter. All of these files can be deleted or copied at any time.

The following permissions will be audited for all users:

sql> Select Privilege,success,failure fromdba_priv_audit_opts;

PRIVILEGE SUCCESS FAILURE

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

CREATE EXTERNAL JOB by Access

CREATE any JOB by Access by Access

GRANT any OBJECT PRIVILEGE by Access

Exempt Access POLICY by Access

CREATE any LIBRARY by Access by Access

GRANT any PRIVILEGE by Access

DROP Profile by Access

ALTER Profile by Access

DROP any PROCEDURE by Access

ALTER any PROCEDURE by Access

CREATE any PROCEDURE by Access by Access

ALTER DATABASE by Access

GRANT any ROLE by Access by Access

CREATE public DATABASE LINK by Access

DROP any TABLE by Access by Access

ALTER any TABLE by Access by Access

CREATE any TABLE by Access by Access

DROP USER by Access

ALTER USER by Access

CREATE USER by Access by Access

CREATE SESSION By access by Access

AUDIT SYSTEM by Access

ALTER SYSTEM by Access

Rows selected.

existing audit records in the current database, Logno/logoff for Audit Create session produced by:

Sql> Select Action_name,count (*) from Dba_audit_trailgroup by Action_name;

Action_name COUNT (*)

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

LOGOFF by CLEANUP 40

LOGON 460

LOGOFF 377

ALTER USER 2

SYSTEM GRANT 12

ALTER SYSTEM 10

CREATE public synonym 5

ALTER DATABASE 2

CREATE DATABASE LINK 1

DROP public synonym 5

96.216 SYSTEM table space is too large:

96.216 the Logoff/logoff is more than 200 million records, the aud$ table size is 80G.

Workaround:

1. when the aud$ table is too large, you can clear the trial data from the table:

sql> truncate TABLE sys.aud$;

2. You can close the trial of the Create session:

Sql> Noaudit Create session;

3. to shut down the database for interrogation, restart the database:

Sql> alter system set Audit_trail=none Scope=spfile;

About Oracle 11g Audit files