首页 > 资讯 > 其他综合 > 正文

生产环境audit_trail参数作用和问题

19-01-30 来源：[db:作者]

收藏我要投稿

1、audit_trail 默认值

SQL> show parameter audit_trail

NAME TYPE VALUE

audit_trail string DB

SQL>

2、audit_trail静态参数

SQL> show parameter audit_trail

NAME TYPE VALUE

audit_trail string DB

SQL>

SQL> alter system set audit_trail=none scope=both;

alter system set audit_trail=none scope=both

ERROR at line 1:

ORA-02095: specified initialization parameter cannot be modified

SQL> alter system set audit_trail=none scope=spfile;

System altered.

SQL> startup force

ORACLE instance started.

Total System Global Area 1870647296 bytes

Fixed Size 2254304 bytes

Variable Size 1207962144 bytes

Database Buffers 654311424 bytes

Redo Buffers 6119424 bytes

Database mounted.

Database opened.

SQL> show parameter audit_trail

NAME TYPE VALUE

audit_trail string NONE

SQL>

3、audit_trail 记录数据库访问

在某些场景下，我们会遇到有密码错误连接，可能引起用户被锁，我们可以查看aud$这个基表，看看是那台应用服务器的错误连接，让后让研发去排查。所以我们在修改密码的时候一定要特别的注意，修改密码很简单，但是导致的结果可能非常严重。

新开一个窗口，故意输入错误密码。

<11g-ocp:orcl:/home/oracle>$sqlplus system/oracle1@orcl

SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 26 22:50:36 2019

ERROR:

ORA-01017: invalid username/password; logon denied

Enter user-name:

ERROR:

ORA-01017: invalid username/password; logon denied

Enter user-name:

ERROR:

ORA-01017: invalid username/password; logon denied

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

<11g-ocp:orcl:/home/oracle>$

查看那个用户从那台服务器尝试远程登录

SQL> audit session whenever not successful;

Audit succeeded.

SQL> select userid, userhost, terminal, clientid from aud$ where returncode=1017;

USERID USERHOST TERMINAL CLIENTID

SCOTT 11g-ocp pts/2

SCOTT 11g-ocp pts/0

SYSTEM AD\SY-NB-0023 SY-NB-0023

SYSTEM 11g-ocp pts/2

42 rows selected.

SQL>

4、audit_trail默认值DB，可能存在占用system表空间过大问题。

解决方法如下：

col segment_name for a15;

SELECT *

FROM (SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 MB

FROM DBA_SEGMENTS

WHERE TABLESPACE_NAME = 'SYSTEM'

GROUP BY SEGMENT_NAME

ORDER BY 2 DESC)

WHERE ROWNUM < 10;

SEGMENT_NAME MB

IDL_UB1$ 272

SOURCE$ 72

IDL_UB2$ 31

C_TOID_VERSION# 24

I_SOURCE1 13

ARGUMENT$ 12

JAVA$MC$ 12

C_OBJ# 12

IDL_CHAR$ 11

9 rows selected. --个人测试环境，aud$没什么记录

SQL>

-- truncate aud$ ###方法1表需要有相关的权限。

SQL> truncate table aud$;

Table truncated

###方法2，迁移表空间

col table_name for a20

col tablespace_name for a20

SELECT table_name, tablespace_name FROM dba_tables WHERE table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name;

TABLE_NAME TABLESPACE_NAME

AUD$ SYSTEM

FGA_LOG$ SYSTEM

SQL> col segment_name for a20

SQL> select segment_name,bytes/1024/1024 size_in_megabytes from dba_segments where segment_name in ('AUD$','FGA_LOG$');

SEGMENT_NAME SIZE_IN_MEGABYTES

FGA_LOG$ .0625

AUD$ .0625

SQL> alter system set db_create_file_dest='+data';

System altered.

SQL> create tablespace audit_tbs datafile size 100M autoextend on;

Tablespace created.

SQL> select name from v$datafile;

NAME

+DATA/orcl/system01.dbf

+DATA/orcl/sysaux01.dbf

+DATA/orcl/undotbs01.dbf

+DATA/orcl/users01.dbf

+DATA/orcl/datafile/audit_tbs.261.998610417

BEGIN

DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(

AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,

AUDIT_TRAIL_LOCATION_VALUE => 'AUDIT_TBS');

END;

BEGIN

DBMS_AUDIT_MGMT.set_audit_trail_location(

audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,

--this moves table FGA_LOG$

audit_trail_location_value => 'AUDIT_TBS');

END;

col table_name for a20

col tablespace_name for a20

SELECT table_name, tablespace_name FROM dba_tables WHERE table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name;

TABLE_NAME TABLESPACE_NAME

AUD$ AUDIT_TBS

FGA_LOG$ AUDIT_TBS

col index_name for a30

col table_name for a10

col tablespace_name for a20

set lines 120

select di.table_name, di.index_name,di.TABLESPACE_NAME from dba_indexes di where di.table_name='AUD$';

TABLE_NAME INDEX_NAME TABLESPACE_NAME

AUD$ SYS_IL0000000407C00040$$ AUDIT_TBS

AUD$ SYS_IL0000000407C00041$$ AUDIT_TBS

SQL>

点击复制链接与好友分享!回本站首页