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>

How to Create an Oracle Database In Two Words: "CREATE DATABASE"

About five years ago, I wrote my first blog on this site, and titled it "DBA Minimalism".  Based upon the fact that I can count the hits on all my digits with room to spare, it was not well received.  Still, I thought it an interesting concept - how can I create an Oracle database in a minimum amount of time, in very few steps?

Since that time, a lot has changed in the Oracle infrastructure.  We now have ASM prevalent as a file system, managed systems are a big deal, OEM now has database administrative features including the ability to create a database, and Oracle's own tool for DB creation (dbca) has very much matured.

Still, there is an elegance to doing a complex task as simply as possible.  With many of these standard tools, you get many unnecessary add-on "features" in the form of schemas and applications (for example, scott/tiger, XDB and Oracle Spatial) that you may never use, need nor want.

While my original method will still work, a client recently tried it with an ASM file system and ran into a seeming roadblock.  Apparently, you cannot create a database in a single pass without an spfile on the ASM disks.  What this means is that you must first use my skeletal pfile and execute command "create spfile from pfile" while the database is in an unmounted state.  Then you can shutdown, startup (nomount) and issue the create database command with parameters for things like control files and undo tablespaces on the ASM diskgroups.

I don't really think Oracle planned this out very well.  You should not have had to make allowances for a different file system back end in order for a standard syntax like "create database" to work.  On the other hand, perhaps at the time, ASM was considered a nonstandard luxury option, and an add-on that required a little forward thinking.

I am still unsure about the whole move to ASM only databases that Oracle seems to be gravitating towards.   I suppose there's a benefit, certainly with engineered systems.  I do think we'll see the day when databases can be created, out of the box, with a default ASM setup.  This is not in 12c yet, as far as I can tell.  Now, 12c has a new feature (pluggable databases) which may make of creating new databases less and less of an issue.  With this feature, you can have a "multi-tenant" database topology where several sets of data housed in different DB instances in the past may now share overhead.

Time will tell how this feature works out.  I am doing research on it and may publish my findings in the future.

Oracle RAC Install Troubleshooting

Over the past year and a half, and especially in the past three months, I have been in complete RAC installation and upgrade mode.  Inevitably, when such projects come along, problems requiring detailed and exhaustively tedious troubleshooting will come up.  It's been my experience that the largest set of books, or even the internet, can contain the exact sequence of problems you may encounter.  It's not that they aren't there, just not in the condition and set of circumstances that meet your situation.

Just recently, a colleague asked for assistance doing the simplest step in the upgrade process from RAC 11.2.0.2 to 11.2.0.3, the software installation.  The following message was received before the guts of the installation could begin:

"[INS-40418] The installer has detected that Oracle Clusterware is not running on local node.  oracle recommends that you upgrade Oracle Clusterware in a rolling manner."

Now, needless to say, the above was untrue.  We verified, using:

crsctl stat res -t -init

And also:

ps -ef | grep ora (which shows oracle owned processes at the linux level )
ps -ef | grep grid (which shows grid owned processes in linux)

So the clusterware and associated processes WERE running on the local node at the time the installation was attempted.  We understand this is necessary so the upgrade process can collect information for the configuration of the new version.  We had just done several installs, had a 100+ page detailed procedure which we developed to assist and assure things were done consistently, and yet this condition had never occurred!

Luckily, this is a testing platform where a delay at least does not impact customers or revenue.  Still, at times when even experienced professionals are exasperated and clueless, Oracle support must be contacted.  This assures that the latest information on the software is available, and verifies we're using appropriate methods of installation and configuration.  In order to get the issue resolved expediently, we entered an SR (Service Request) on the Oracle Support website (support.oracle.com) at level 2, business impact, but workarounds are available.  The support analyst was prompt, but asked for an awful lot of information about the environment in a short period of time.  Luckily, Oracle now has diagnostic tools such as rdalite and diagcollection.pl to collect the data quickly.  An additional piece of luck  -   this was only a two-node RAC. We have installations at this site with up to 26 nodes!  But still, deadlines are deadlines, and critical test by the QA team was dependent upon this environment.

After two days of inquisition by an Oracle analyst, including a live webcast and deep-dive into the entire RAC infrastructure, the problem was still unsolved, until, my colleague Jayanth was skimming through the original installation and configuration to collect more data for Oracle, and stumbled across the actual problem.  The host file will not be published to protect the client's configuration security.

You had to really look for it, and neither the DBA in charge, this writer, the Oracle analyst or the various diagnostic scripts that Oracle recommends caught it, but there are TWO ENTRIES for ip xxx.xx.xx.xx in the hosts file.  Even though the Oracle message from their installation program says nothing about it, that is the TRUE REASON why the installation could not precede.

All if this makes me wonder if diagnostic messages are ever of any use at all!  They are often wrong, or simple misdirection, causing you to take even longer to do a relatively simple task.   The good news is, after this issue was finally discovered, the upgrade and patching was done in only a few hours, which is a very short period of time in RACworld!

I've gone on a lot longer about this particular issue than I meant.   I hope it someday helps someone else.   My next post will involve more detail on how to engage support, and what to do while you're working with them, and what your backup plan should be.  Good day!