//
you're reading...
Database, Oracle Installation & Configuration

How to relocate the sys.aud$ table to another tablespace in Oracle 11g R2

When the Oracle database fail to allocate the next extent to the sys.aud$ table, all users will not able login to the database remotely. The solution of the issue is to relocate the sys.aud$ table to a dedicated tablespace and shrink the sys.aud$ table regularly.

There are some reasons for DBAs to relocated the sys.aud$ table to a dedicated tablespace.

First, the system tablespace can only be setup as manually extended tablespace. When the extend of the sys.aud$ table reach the max can be allocated, the database will stop audit all users’ login.

How to relocate the sys.aud$ table to another tablespace to fix the error

ORA-1653: unable to extend table SYS.AUD$ by 1574802 in tablespace SYSTEM

After checking, found SYSTEM tablespace is 10GB (large enough) and currently still having 12GB free space.

Let’s review the error message:

ORA-1653: unable to extend table SYS.AUD$ by 1574802 in tablespace SYSTEM

The error message indicate Oracle database want to allocate 1574802 blocks for a new extent. Let’s caculate it:

db_block_size    integer          8192
SYS@testdb > select 40964*8192/1024/1024 from dual;
1574802*8192/1024/1024
--------------------
12303.140625

We have two issues here:

1. why oracle want to allocate a so big extent?

2. why allocate 650m failed while there is still 12GB free space in SYSTEM?

For 2, it is because blocks must be sequentail in an extent.

For 1, let’s check the attribute for AUD$ table:

SYS@TESTDB> select EXTENTS,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS from dba_segments where SEGMENT_NAME='AUD$'
EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS
---------- -------------- ----------- ----------- -----------
26          16384   6450388992           1         505

Due to PCT INCREASE is 50, so next time oracle will require

12303×(1+50%)=18454.5MB

space for a new extent.

And NEXT_EXTENT too big is root cause in our case.

Solution:

First, we have to limit the ‘next extent’ to 10MB, we can correct the issue in below way:

sys@TESTDB> alter table aud$ STORAGE (NEXT 10m PCTINCREASE 0);
Table altered.

Then oracle will always use 10MB size for a new extent.

We used to encounter the issue when a new table failed to allocate a new extent while the tablespace having 12GB+ free space. Because the NEXT_EXTENT is bigger then that!

To check the NEXT_EXTENT issue, use below command:

sys@TESTDB> select owner,segment_name,segment_type,NEXT_EXTENT,PCT_INCREASE from dba_segments where NEXT_EXTENT/1024/1024>10;
OWNER                    SEGMENT_NAME SEGMENT_TYPE    NEXT_EXTENT PCT_INCREASE
----------- ------------------------- ------------  ------------- ------------
SYS                            I_AUD1        INDEX       66281472           50
SYSTEM      SYS_LOB0000985535C00039$$   LOBSEGMENT       13090816           50
SYSTEM           SYS_EXPORT_SCHEMA_01        TABLE       13090816           50

 

Second, relocate the sys.aud$ table to different tablespace.

create tablespace AUDIT datafile '+TESTDBDATA' size 4000M autoextend on extent management local segment space management auto;

–move the sys.aud$ table

BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => 'AUDIT');
END;
/

–move the FGA_LOG$ table

BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
audit_trail_location_value => 'AUDIT');
END;
/
Advertisements

About daviewning

I am an Oracle DBA

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: