Shulou(Shulou.com)06/01 Report--

1. Audit_trail default value

SQL > show parameter audit_trail

NAME TYPE VALUE

Audit_trail string DB

SQL >

2. Audit_trail static parameters

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 record database access

In some scenarios, we will encounter an incorrect connection with a password, which may cause the user to be locked. We can check the aud$ base table to see if it is the incorrect connection of that application server, and let R & D troubleshoot it later. Therefore, when we change the password, we must pay special attention to it. Changing the password is very simple, but the result may be very serious.

Open a new window and deliberately enter the wrong password.

$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

$

See which user is trying to log in remotely from that server

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. The default value of audit_trail is DB, which may take up too much system table space.

The solution is as follows:

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$ # # method 1 table requires relevant permissions.

SQL > truncate table aud$

Table truncated

.

# method 2: migrate tablespaces

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 >

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.