This
was tested on Oracle 11.2.0.2.7. I wondered what would happen if you
were auditing connections, SYS.AUD$ filled up then you tried to login
again. First I started auditing:
SQL> conn / as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> audit create session
2 /
Audit succeeded.
SQL> select value from v$parameter
2 where name = 'audit_trail'
3 /
VALUE
-------------------------------------------------------
TRUE
SQL>
SQL> select count(*) from sys.aud$
Then I checked that auditing was working:
SQL> select count(*) from sys.aud$
2 /
COUNT(*)
----------
0
SQL> conn andrew/reid
Connected.
SQL> show user
USER is "ANDREW"
SQL> conn / as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> select count(*) from sys.aud$
2 /
COUNT(*)
----------
2
SQL>
SQL> select count(*) from dba_extents
Next I looked at the current and maximum possible size of SYS.AUD$:
SQL> select count(*) from dba_extents
2 where owner = 'SYS'
3 and segment_name = 'AUD$'
4 /
COUNT(*)
----------
1
SQL> select next_extent, max_extents
2 from dba_tables
3 where owner = 'SYS'
4 and table_name = 'AUD$'
5 /
NEXT_EXTENT MAX_EXTENTS
----------- -----------
1048576 2147483645
SQL>
I tried to limit the maximum possible size of SYS.AUD$. I found that I could adjust the size of the next extent:
SQL> alter table sys.aud$ storage (next 8k)
2 /
Table altered.
SQL>
SQL> alter table sys.aud$ storage (maxextents 10)
... but not the maximum number of extents:
SQL> alter table sys.aud$ storage (maxextents 10)
2 /
alter table sys.aud$ storage (maxextents 10)
*
ERROR at line 1:
ORA-25150: ALTERING of extent parameters not permitted
SQL>
SQL> alter table sys.aud$ move tablespace users
So
the only way to make SYS.AUD$ run out of space is to set a limit on the
size of the tablespace it is in. To make this easier, I moved SYS.AUD$
to the USERS tablespace, which has 2 megabytes and autoextend off in
this database:
SQL> alter table sys.aud$ move tablespace users
2 /
Table altered.
SQL>
$count is 1
Then
I set up a loop in a UNIX shell script to connect to the database again
and again. Each iteration connected as ANDREW first then as SYS. It
started with a count of 1:
$count is 1
SQL*Plus: Release 11.2.0.2.0 Production on Tue Oct 16 18:37:44 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> USER is "ANDREW"
SQL> Connected.
SQL> USER is "SYS"
SQL> 2
AUDIT_ROWS
----------
4
SQL> 2 3 4
LOGONS_CUMULATIVE
-----------------
4505
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$count is 1986
Connection as user ANDREW failed with ORA-02002 when $count reached 1986:
$count is 1986
SQL*Plus: Release 11.2.0.2.0 Production on Tue Oct 16 18:51:36 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> USER is "ANDREW"
SQL> ERROR:
ORA-02002: error while writing to audit trail
ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table SYS.AUD$ by 8 in tablespace USERS
Connected.
SQL> USER is "SYS"
SQL> 2
AUDIT_ROWS
----------
3973
SQL> 2 3 4
LOGONS_CUMULATIVE
-----------------
8484
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
I
was still able to connect as SYS so, if I had wanted to continue using
the database I could have deleted rows from SYS.AUD$, enlarged the USERS
tablespace or simply stopped auditing.
No comments:
Post a Comment