Tuesday, November 25, 2014

A Quick Tour Of The Oracle Database SYSAUX Tablespace

The Oracle database SYSAUX tablespace was introduced in Oracle 10g.  It was made necessary by the constant issues surrounding operational object increases in number, size and purpose.  A document presenting it's use and purpose was first presented by Oracle in support documents nearly 10 years from the date of this writing:

SYSAUX New Mandatory Tablespace in Oracle 10g and higher (Doc ID 243246.1)

Since this introduction, more than a little water has run under the bridge, and the size and purpose of this tablespace have subtly evolved.  While the original intent may have been to maintain objects in support of auxiliary products, it's become a requirement that the tablespace and many of it's original contents be maintained.   At the same time, some objects originally specified for SYSAUX (such as those related to auditting) can optionally be stored elsewhere.  These facts can sometimes make it difficult to know what exactly SHOULD be in SYSAUX, and whether or not they can safely be added or removed.  

There are many support documents related to these issues, and with good reason.  I am writing this because I've never seen the topic covered comprehensively.   Here are some of the better Oracle support documents about SYSAUX, and required reading before even starting to consider management or maintenance of the beast:

How to Reduce SYSAUX Tablespace Occupancy Due to Fragmented TABLEs and INDEXes (Doc ID 1563921.1)

Suggestions if Your SYSAUX Tablespace Grows Rapidly or Too Large (Doc ID 1292724.1)

Troubleshooting Issues with SYSAUX Usage by the Automatic Workload Repository (AWR) (Doc ID 1399365.1)

Fixing a Corrupted SYSAUX Tablespace (Doc ID 950128.1)

How to Relocate the SYSAUX Tablespace (Doc ID 301186.1)

High Storage Consumption for LOBs in SYSAUX Tablespace (Doc ID 396502.1)

These are a few of what may be hundreds of articles available to those licensed for support.  However, what if you are either not licensed, do not have access, or wish to research beyond what is presented by support?  Since there's not much out there, perhaps that's why you're reading this.  I will attempt to present the topic at a high enough level to hopefully get you pointed in the right direction.  

I believe SYSAUX was originally intended as a "pressure relief valve" for the rapid expansion of the SYSTEM tablespace as Oracle databases grew larger, and supporting software to manage it grew as well.  Like any other such vaguely-purposed mechanism, doubts grew as when, why and how it should be used.  In a couple of product cases (OLAP, XDB), those product's objects were moved INTO sysaux from a dedicated tablespace in prior releases.  While SYSAUX may have achieved the loosely stated purpose of controlling the growth of SYSTEM (and consolidation of other tablespaces), how was the growth of SYSAUX to be controlled?  It got to the point where Oracle created a view (sys.v$sysaux_occupants)  to display all the objects and products within the SYSAUX ecosystem:


(1b) SYSAUX occupants space usage (v$sysaux_occupants)
********************************************************
|
| Occupant Name        Schema Name               Space Usage
| -------------------- -------------------- ----------------
| SM/AWR               SYS                      735,289.3 MB
| SM/OTHER             SYS                        9,464.2 MB
| SM/OPTSTAT           SYS                        1,415.3 MB
| SM/ADVISOR           SYS                          733.6 MB
| SQL_MANAGEMENT_BASE  SYS                          461.3 MB
| EM                   SYSMAN                       385.9 MB
| XDB                  XDB                          109.5 MB
| LOGMNR               SYSTEM                       106.9 MB
| SDO                  MDSYS                         87.2 MB
| AO                   SYS                           48.4 MB
| XSOQHIST             SYS                           48.4 MB
| XSAMD                OLAPSYS                       32.1 MB
| ORDIM/ORDDATA        ORDDATA                       22.1 MB
| WM                   WMSYS                         14.4 MB
| EXPRESSION_FILTER    EXFSYS                         9.8 MB
| JOB_SCHEDULER        SYS                            7.4 MB
| EM_MONITORING_USER   DBSNMP                         6.9 MB
| PL/SCOPE             SYS                            5.6 MB
| SMON_SCN_TIME        SYS                            3.3 MB
| LOGSTDBY             SYSTEM                         2.8 MB
| STREAMS              SYS                            2.3 MB
| ORDIM                ORDSYS                         1.1 MB
| AUTO_TASK            SYS                            0.6 MB
| AUDIT_TABLES         SYS                            0.0 MB
| ORDIM/ORDPLUGINS     ORDPLUGINS                     0.0 MB
| ORDIM/SI_INFORMTN_SC SI_INFORMTN_SCHEMA             0.0 MB
| STATSPACK            PERFSTAT                       0.0 MB
| TEXT                 CTXSYS                         0.0 MB
| TSM                  TSMSYS                         0.0 MB
| ULTRASEARCH          WKSYS                          0.0 MB
| ULTRASEARCH_DEMO_USE WK_TEST                        0.0 MB
|

| Others (Unaccounted space)                         28.1 MB

The view V$SYSAUX_OCCUPANTS has a column that will indicate IF the associated product can safely be moved to a different tablespace, and the Oracle-supplied package for doing so.  Keep in mind that some products MUST be in the SYSAUX tablespace, even if they can be moved operationally, in order for certain patches and upgrades to be successful.  This depends upon release and patch level, but should be covered in the readme for the particular patch or upgrade.  Also note that although Oracle will not support moving objects owned by user SYS, these packages do so and are authorized and supported by Oracle.  There are a great many service requests and errors reported from end users moving these objects on their own.  

Over time, several products evolved into or were created with objects in SYSAUX. One very good example is AWR.  Depending upon how large your tuning repository is defined, this can place space pressure on SYSAUX (see above!), and, according to Oracle, it's objects MUST remain there for compatibility and proper operation! Another famous occupant (perhaps by accident?!?) of SYSAUX is the AUD$ table. Originally, the table SYS.AUD$ was housed in the SYSTEM tablespace, but beginning with Oracle 11g, it became located in SYSAUX at database creation, but remained in SYSTEM during an upgrade (depending upon versions).  Later, Oracle devised a package called DBMS_AUDIT_MGMT to relocate this object, only after several support papers mistakenly directed customers to move the object by hand with occasionally disastrous results:

ORA-600[4412] After Moving The AUD$ Table To Another Tablespace (Doc ID 1059958.1)

There are great documents and blogs for properly moving AUD$ to it's own tablespace, and it's highly recommended beginning with Oracle 11gr2 and above.  In fact, any Oracle implementations that have it in SYSTEM or SYSAUX should move it immediately.  The best example is here:

SCRIPT: Basic example to manage AUD$ table in 11.2 with dbms_audit_mgmt (Doc ID 1362997.1)

I will post the code from the document for those who don't have access.  I have used this many times without issue:


-- Example re-locating AUD$ and setting up a purge job

set serveroutput on

prompt First Step: init cleanup (if not already)

BEGIN
IF NOT DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED
(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD)
THEN
dbms_output.put_line('Calling DBMS_AUDIT_MGMT.INIT_CLEANUP');
DBMS_AUDIT_MGMT.INIT_CLEANUP(
audit_trail_type => dbms_audit_mgmt.AUDIT_TRAIL_AUD_STD,
default_cleanup_interval => 24*7);
else
dbms_output.put_line('Cleanup for STD was already initialized');
end if;
end;
/

prompt Relocate AUD$ to a dedicated tablespace AUDIT_DATA

begin
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
audit_trail_type => dbms_audit_mgmt.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => 'AUDIT_DATA') ;
end;
/

prompt set last archive timestamp to a week before now

begin
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
last_archive_time => sysdate - 7);
end;
/

prompt setup a purge job


BEGIN
DBMS_AUDIT_MGMT.DROP_PURGE_JOB
(AUDIT_TRAIL_PURGE_NAME => 'Standard_Audit_Trail_PJ');
-- exception
-- when others then
-- null;
end;
/

BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
AUDIT_TRAIL_PURGE_INTERVAL => 24,
AUDIT_TRAIL_PURGE_NAME => 'Standard_Audit_Trail_PJ',
USE_LAST_ARCH_TIMESTAMP => TRUE );
END;
/

prompt call DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP regularly to advance
prompt the last archive ts, Audit Vault will do this for you automatically

prompt Optionally Schedule automatic advancement of the archive timestamp

create or replace procedure set_archive_retention
(retention in number default 7) as
begin
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
last_archive_time => sysdate - retention);
end;
/

begin
  DBMS_SCHEDULER.disable('advance_archive_timestamp');
  DBMS_SCHEDULER.drop_job('advance_archive_timestamp');
-- exception
-- when others then
-- null;
end;
/

BEGIN
   DBMS_SCHEDULER.create_job (
   job_name => 'advance_archive_timestamp',
   job_type => 'STORED_PROCEDURE',
   job_action => 'SET_ARCHIVE_RETENTION',
   number_of_arguments => 1,
   start_date => SYSDATE,
   repeat_interval => 'freq=daily' ,
   enabled => false,
   auto_drop => FALSE);
   dbms_scheduler.set_job_argument_value
    (job_name =>'advance_archive_timestamp',
     argument_position =>1,
     -- one week, you can customize this line:
     argument_value => 7);
   DBMS_SCHEDULER.ENABLE('advance_archive_timestamp');
End;
/

BEGIN
    DBMS_SCHEDULER.run_job (job_name => 'advance_archive_timestamp',
    use_current_session => FALSE);
END;
/

-- end example

SAMPLE OUTPUT

SQL> @audit_example
First Step: init cleanup (if not already)
Cleanup for STD was already initialized

PL/SQL procedure successfully completed.

Relocate AUD$ to a dedicated tablespace AUDIT_DATA

PL/SQL procedure successfully completed.

set last archive timestamp to a week before now

PL/SQL procedure successfully completed.

setup a purge job

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

call DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP regularly to advance
the last archive ts, Audit Vault will do this for you
Optionally Schedule automatic advancement of the archive timestamp

Procedure created.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

SQL>