oracle 审计变换表空间_【学习笔记】Oracle审计 将aud$相关对象迁移到其他表空间案例...

天萃荷净

运维DBA反映，在数据库开启审计功能时，相关数据存放在SYSTEM表空间中，将aud$相关对象迁移到其他表空间案例

在日常的数据库维护中，经常出现因为数据库登录审计的功能启动,导致system表空间被用满.从而出现异常,一般建议把aud$相关对象迁移到其他表空间,从而避免system被用完的风险.

1.人工移动move aud$相关对象

alter table AUDIT$ move tablespace users;

alter table AUDIT_ACTIONS move tablespace users;

alter table AUD$ move tablespace users;

alter table AUD$ move lob(SQLBIND) store as SYS_IL0000000384C00041$$ (tablespace users);

alter table AUD$ move lob(SQLTEXT) store as SYS_IL0000000384C00041$$ (tablespace users);

alter index I_AUDIT rebuild online tablespace users;

alter index I_AUDIT_ACTIONS rebuild online tablespace users;

--可能修改值(index和lob index)

SQL> select COLUMN_NAME,index_name from dba_lobs where owner='SYS' and table_name='AUD$';

COLUMN_NAME INDEX_NAME

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

SQLBIND SYS_IL0000000384C00040$$

SQLTEXT SYS_IL0000000384C00041$$

SQL> SELECT INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME='AUD$';

INDEX_NAME

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

SYS_IL0000000384C00040$$

SYS_IL0000000384C00041$$

SQL> SELECT INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME='AUDIT$';

INDEX_NAME

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

I_AUDIT

SQL> SELECT INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME='AUDIT_ACTIONS';

INDEX_NAME

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

I_AUDIT_ACTIONS

2.DBMS_AUDIT_MGMT实现迁移

conn / as sysdba

BEGIN

DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,

audit_trail_location_value => 'USERS');

END;

/

验证DBMS_AUDIT_MGMT效果

SQL> select segment_name,tablespace_name from dba_segments where

2 segment_name in('AUD$','SYS_IL0000000384C00040$$','SYS_IL0000000384C00041$$',

3 'AUDIT$','I_AUDIT','AUDIT_ACTIONS','I_AUDIT_ACTIONS');

SEGMENT_NAME TABLESPACE_NAME

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

AUDIT_ACTIONS SYSTEM

AUDIT$ SYSTEM

AUD$ SYSTEM

SYS_IL0000000384C00041$$ SYSTEM

SYS_IL0000000384C00040$$ SYSTEM

I_AUDIT_ACTIONS SYSTEM

I_AUDIT SYSTEM

SQL> BEGIN

2 DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,

3 audit_trail_location_value => 'USERS');

4 END;

5 /

PL/SQL procedure successfully completed.

SQL> select segment_name,tablespace_name from dba_segments where

2 segment_name in('AUD$','SYS_IL0000000384C00040$$','SYS_IL0000000384C00041$$',

3 'AUDIT$','I_AUDIT','AUDIT_ACTIONS','I_AUDIT_ACTIONS');

SEGMENT_NAME TABLESPACE_NAME

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

AUDIT_ACTIONS SYSTEM

AUDIT$ SYSTEM

AUD$ USERS

SYS_IL0000000384C00041$$ USERS

SYS_IL0000000384C00040$$ USERS

I_AUDIT_ACTIONS SYSTEM

I_AUDIT SYSTEM

通过试验证明DBMS_AUDIT_MGMT就是迁移了AUD$表中相关对象,对于和审计相关的其他几个对象并未迁移到其他表空间

3.aud$相关说明

1.DBMS_AUDIT_MGMT版本支持情况

It is still not supported (but it works) to use it on 10.2.0.4 and 11.1.0.7 for non-Audit Vault Environment.

Using RDBMS with Audit Vault, it is supported for 10.2.0.4.0 and 11.1.0.7.0

as the DBMS_AUDIT_MGMT Package is provided to be used with an Audit Vault Environment.

2.该包可以实现在线迁移，特别是在高业务的系统中,可以实现在线迁移,而人工的move操作不能实现在线处理

3.对于AUD$对象，如果登录审计数据不是非常重要,可以通过truncate来解决一时的问题，在业务高的系统，可能truncate不能马上操作成功，可以尝试使用11gr2的新特性alter session set ddl_lock_timeout = 10;来实现自动ddl尝试

4.如果确定不需要登录审计功能，可以通过设置audit_trail=none来关闭(需要重启实例)

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle审计 将aud$相关对象迁移到其他表空间案例