1、audit_trail 默認值
SQL> show parameter audit_trail

成都創(chuàng)新互聯(lián)公司客戶idc服務(wù)中心，提供成都多線服務(wù)器托管、成都服務(wù)器、成都主機托管、成都雙線服務(wù)器等業(yè)務(wù)的一站式服務(wù)。通過各地的服務(wù)中心，我們向成都用戶提供優(yōu)質(zhì)廉價的產(chǎn)品以及開放、透明、穩(wěn)定、高性價比的服務(wù)，資深網(wǎng)絡(luò)工程師在機房提供7*24小時標(biāo)準(zhǔn)級技術(shù)保障。

NAME TYPE VALUE

---

audit_trail string DB
SQL>

2、audit_trail靜態(tài)參數(shù)
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 記錄數(shù)據(jù)庫訪問
在某些場景下，我們會遇到有密碼錯誤連接，可能引起用戶被鎖，我們可以查看aud$這個基表，看看是那臺應(yīng)用服務(wù)器的錯誤連接，讓后讓研發(fā)去排查。所以我們在修改密碼的時候一定要特別的注意，修改密碼很簡單，但是導(dǎo)致的結(jié)果可能非常嚴重。
新開一個窗口，故意輸入錯誤密碼。
<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

Copyright (c) 1982, 2013, Oracle. All rights reserved.

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>$

查看那個用戶從那臺服務(wù)器嘗試遠程登錄
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/2
SCOTT 11g-ocp pts/0
SYSTEM AD\SY-NB-0023 SY-NB-0023
SYSTEM 11g-ocp pts/2
SYSTEM 11g-ocp pts/2
SYSTEM 11g-ocp pts/2
SYSTEM 11g-ocp pts/2
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. --個人測試環(huán)境，aud$沒什么記錄

SQL>
-- truncate aud$ ###方法1表需要有相關(guān)的權(quán)限。
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.

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>

當(dāng)前標(biāo)題：生產(chǎn)環(huán)境audit_trail參數(shù)作用和問題
文章路徑：http://www.aaarwkj.com/article38/goohpp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供App開發(fā)、網(wǎng)站設(shè)計公司、網(wǎng)站排名、網(wǎng)站維護、移動網(wǎng)站建設(shè)、標(biāo)簽優(yōu)化

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)