解决关于aud$对象导致system表空间暴涨问题

OS VERSION : LINUX 6

ORACLE VERSION : 11.2.0.4

问题介绍：

早上接到公司其他部门数据库错误，由于该错误，前一天导致数据库无法正常登录

问题分析：

1.查看aud$段信息

SQL> select segment_name,bytes/1024/1024/1024 from dba_segments
  2  where segment_name = 'AUD$';


SEGMENT_NAME         BYTES/1024/1024/1024
-------------------- --------------------
AUD$                           61.3056641

aud$表61.3G的数据（有点让人诧异....）

2.查看aud审计策略

SQL> show parameter audit_trail


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      DB

发现数据库启用了DB级别的审计功能，相关审计级别如下：

None：是默认值，不做审计；

DB：将audit trail 记录在数据库的审计相关表中，如aud$，审计的结果只有连接信息；

DB,Extended：这样审计结果里面除了连接信息还包含了当时执行的具体语句；

OS：将audit trail 记录在操作系统文件中，文件名由audit_file_dest参数指定；

XML：10g里新增的。

备注：ORACLE11G中审计是默认开启的，而且是对DB进行审计;

可以看到，都是BY ACCESS

by access  每一个被审计的操作都会生成一条audit trail。

by session 一个会话里面同类型的操作只会生成一条audit trail，默认为by session。

whenever successful 操作成功(dba_audit_trail中returncode字段为0) 才审计。

whenever not successful 反之。省略该子句的话，不管操作成功与否都会审计。

原因找到了，aud使用的默认策略，基于DB操作审计，而且aud$表貌似从来都没有清理过。

问题解决：

一.清理aud$，关闭审计/修改审计策略

 1.在确认aud$数据毫无用处的情况下，可以直接truncate掉。

 2.关闭审计功能alter system set audit_trail=none scope=spfile; 重启数据库

二.迁移aud$

相关连接：

关于aud$对象相关处理

*********************************************相关介绍*********************************************

1.1 Oracle 11gR2 concepts

From：

Oracle Audit 审计 说明

1.1.1 Database Auditing

       Databaseauditing is the monitoring and recording of selected user database actions. You can use standard auditing to audit SQL statements,privileges, schemas, objects, and network and multitier activity.Alternatively, you can use fine-grained auditingto monitor specific database activities, such as actions on a database table ortimes that activities occur. For example, you can audit a table accessed after9:00 p.m.

 

Reasons for using auditing include:

       （1）Enabling future accountability for current actions

       （2）Deterring users (or others, such as intruders) from inappropriateactions based on their accountability

       （3）Investigating, monitoring, and recording suspicious activity

       （4）Addressing auditing requirements for compliance

1.1.2  Oracle Audit Vault

       OracleAudit Vault enables you to consolidate, report, and configure alerts foraudited data. You can consolidate audit data generated by Oracle Database andother relational databases. You can also use Oracle Audit Vault to monitoraudit settings on target databases.

 

1.2 Oracle 10gR2 concept

1.2.1 Overview of Database Auditing

      Auditingis the monitoring and recording of selected user database actions. It can bebased on individual actions, such as the type of SQL statement run, or oncombinations of factors that can include name, application, time, and so on.Security policies can cause auditing when specified elements in an Oracledatabase are accessed or altered, including content.

 

Auditing is generally used to:

       （1）Enable future accountability for current actions taken in aparticular schema, table, or row, or affecting specific content

       （2）Investigate suspicious activity. For example, if an unauthorizeduser is deleting data from tables, then the security administrator could auditall connections to the database and all successful and unsuccessful deletionsof rows from all tables in the database.

       （3）Monitor and gather data about specific database activities. Forexample, the database administrator can gather statistics about which tablesare being updated, how many logical I/Os are performed, or how many concurrentusers connect at peak times.

 

       Youcan use Enterprise Manager to view and configure audit-related initializationparameters and administer audited objects for statement auditing and schemaobject auditing. For example, Enterprise Manager shows the properties forcurrent audited statements, privileges, and objects. You can view theproperties of each object, and you can search audited objects by theirproperties. You can also turn on and turn off auditing on objects, statements,and privileges.

1.2.2 Types and Records of Auditing

Oracle allows audit options to be focused or broad. You can audit:

       （1）Successful statement executions, unsuccessful statement executions,or both

       （2）Statement executions once in each user session or once every timethe statement is run

       （3）Activities of all users or of a specific user

 

Oracle auditing enablesthe use of several different mechanisms, with the following features:

Table 20-1 Types of Auditing

 

                         

Type of Auditing	Meaning/Description
Statement auditing	Audits SQL statements by type of  statement, not by the specific schema objects on which they operate.  Typically broad, statement auditing audits the use of several types of  related actions for each option. For example, AUDIT TABLE tracks several DDL  statements regardless of the table on which they are issued. You can also set  statement auditing to audit selected users or every user in the database.
Privilege auditing	Audits the use of  powerful system privileges enabling corresponding actions, such as AUDIT CREATE  TABLE. Privilege auditing is more focused than statement auditing because it  audits only the use of the target privilege. You can set privilege auditing  to audit a selected user or every user in the database.
Schema object auditing	Audits specific  statements on a particular schema object, such as AUDIT SELECT ON employees.  Schema object auditing is very focused, auditing only a specific statement on  a specific schema object. Schema object auditing always applies to all users  of the database.
Fine-grained auditing	Audits data access  and actions based on content. Using DBMS_FGA, the security administrator  creates an audit policy on the target table. If any rows returned from a DML  statement block match the audit condition, then an audit event entry is  inserted into the audit trail.

1.2.3 Audit Records and the Audit Trails

       Audit records includeinformation such as the operation that was audited, the user performing theoperation, and the date and time of the operation. Audit records can be storedin either a data dictionary table, called the databaseaudit trail, or in operating system files, calledan operating system audit trail.

1.2.3.1 DatabaseAudit Trail

       The database audit trail is a single table named SYS.AUD$ in the SYS schema ofeach Oracle database's data dictionary. Several predefined views are providedto help you use the information in this table.

       Audit trail records can contain different types ofinformation, depending on the events audited and the auditing options set.The following information is always included in each audit trail record, if theinformation is meaningful to the particular audit action:

（1）User name

（2）Instance number

（3）Process identifier

（4）Session identifier

（5）Terminal identifier

（6）Name of the schema object accessed

（7）Operation performed or attempted

（8）Completion code of the operation

（9）Date and time stamp

（10）System privileges used

1.2.3.2 Auditing in a DistributedDatabase

       Auditing is siteautonomous. An instance audits only the statements issued by directly connectedusers. A local Oracle node cannot audit actions that take place in a remotedatabase. Because remote connections are established through the user accountof a database link, statements issued through the database link's connectionare audited by the remote Oracle node.

1.2.3.3 OperatingSystem Audit Trail

       Oracleallows audit trail records to be directed to an operating system audit trail ifthe operating system makes such an audit trail available to Oracle. If not, then audit records are written to a file outside thedatabase, with a format similar to other Oracle trace files.

       Oracleallows certain actions that are always audited to continue, even when theoperating system audit trail (or the operating system file containing auditrecords) is unable to record the audit record. The usual cause of this is thatthe operating system audit trail or the file system is full and unable toaccept new records.

       Systemadministrators configuring operating system auditing should ensure that theaudit trail or the file system does not fill completely. Most operating systemsprovide administrators with sufficient information and warning to ensure thisdoes not occur. Note, however, that configuring auditing to use the databaseaudit trail removes this vulnerability, because the Oracle database serverprevents audited events from occurring if the audit trail is unable to acceptthe database audit record for the statement.

1.2.3.4 Operating System AuditRecords

       Theoperating system audit trail is encoded, but it is decoded in data dictionaryfiles and error messages.

       （1）Action code describes the operation performed or attempted. The AUDIT_ACTIONS data dictionary table describes thesecodes.

       （2）Privileges used describes any system privileges used to perform theoperation. The SYSTEM_PRIVILEGE_MAP table describesall of these codes.

       （3）Completion code describes the result of the attempted operation.Successful operations return a value of zero, and unsuccessful operationsreturn the Oracle error code describing why the operation was unsuccessful.

1.2.3.5 RecordsAlways in the Operating System Audit Trail

       Somedatabase-related actions are always recorded into the operating system audittrail regardless of whether database auditing isenabled:

       （1）At instance startup, anaudit record is generated that details the operating system user starting theinstance, the user's terminal identifier, the date and time stamp, and whetherdatabase auditing was enabled or disabled. Thisinformation is recorded into the operating system audit trail, becausethe database audit trail is not available until after startup has successfullycompleted. Recording the state of database auditing at startup also acts as anauditing flag, inhibiting an administrator from performing unaudited actions byrestarting a database with database auditing disabled.

       （2）At instance shutdown, anaudit record is generated that details the operating system user shutting downthe instance, the user's terminal identifier, the date and time stamp.

       （3）During connections with administrator privileges,an audit record is generated that details the operating system user connectingto Oracle with administrator privileges. This recordprovides accountability regarding users connected with administratorprivileges.

 

       Onoperating systems that do not make an audit trail accessible to Oracle, theseaudit trail records are placed in an Oracle audit trail file in the samedirectory as background process trace files.

1.2.3.6 When Are Audit RecordsCreated?

       Anyauthorized database user can set his own audit options at any time, but the recording of audit information is enabled or disabled bythe security administrator.

       When auditing is enabled in the database, an audit record isgenerated during the execute phase of statement execution.

       SQL statements inside PL/SQL programunits are individually audited, as necessary, when the program unit is run.

       The generation and insertion of an audittrail record is independent of a user's transaction being committed. That is, even if a user's transaction is rolled back, theaudit trail record remains committed.

       Statement and privilegeaudit options in effect at the time a database user connects to the databaseremain in effect for the duration of the session. Setting or changing statementor privilege audit options in a session does not cause effects in that session.The modified statement or privilege audit options take effect only when thecurrent session is ended and a new session is created. In contrast, changes toschema object audit options become effective for current sessions immediately.

       Operations by the SYS user and by users connected through SYSDBAor SYSOPER can be fully audited with the AUDIT_SYS_OPERATIONS initializationparameter. Successful SQL statements from SYS are auditedindiscriminately. The audit records for sessions established by the user SYS orconnections with administrative privileges are sent to an operating systemlocation. Sending them to a location separate from the usual database audittrail in the SYS schema provides for greater auditing security.