Tuesday, November 20, 2012

ORA-02002

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>

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>

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>

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

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>

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

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: