Vivek's Blog: SYSAUX Tablespace Issues

Introduction

The SYSAUX tablespace, introduced in 10g, is always created at database creation. The SYSAUX tablespace serves as an auxiliary tablespace to the SYSTEM tablespace. It reduces the number of tablespaces required by the database. It also reduces the load on the SYSTEM tablespace.

Basic Diagnostic

A primary user of the SYSAUX tablespace is the Automatic Workload Repository.

Generating AWR Report

SQL> @$ORACLE_HOME/rdbms/admin/awrinfo.sql

This script will report general AWR information
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Specify the Report File Name
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrinfo.txt. To use this name,
press to continue, otherwise enter an alternative.

Enter value for report_name:

Using the report name awrinfo.txt
No errors.
No errors.
~~~~~~~~~~~~~~~
AWR INFO Report
~~~~~~~~~~~~~~~

Report generated at 04:11:25 on Aug 11, 2015 ( Tuesday ) in Timezone +05:30

Warning: Non Default AWR Setting!
--------------------------------------------------------------------------------
Snapshot interval is 60 minutes and Retention is 8 days

DB_ID DB_NAME HOST_PLATFORM INST STARTUP_TIME LAST_ASH_SID PAR
------------ --------- ---------------------------------------- ----- ----------------- ------------ ---
* 1387617462 ORCL db11g.profad.com - Linux x86 64-bit 1 21:55:26 (08/05) 160145 NO

To determine AWR objects taking large space

SQL> connect / as sysdba
SQL> @?/rdbms/admin/awrinfo.sql

If space usage is growing steadily for a segment even after retention period is expired and a partition is dropped, then this could be a known problem. You can search for known issues using component and segment name in the report output.

Issue#1

SYSAUX Tablespace Grows Rapidly or Too Large

By drilling into the AWRinfo.sql script, V$SYSAUX_OCCUPANTs and other related views the information and methods here may still prove useful for diagnosis of SYSAUX issues.

Changes:

Moving to RAC based configuration
New ORACLE database usage
Increase in metric usage or dependence
Closer or new monitoring of SYSAUX TBS growth leading to questions regarding allocation and growth sizes

Cause:

Lets investigate and confirme quickly using a few views and scripts,

- One important view is V$SYSAUX_OCCUPANTS
- Another very useful and provided script is the AWRINFO.SQL script found in the Oracle_home/rdbms/admin directory

A closer look at V$SYSAUX_OCCUPANTS*:

select OCCUPANT_NAME,OCCUPANT_DESC
from V$SYSAUX_OCCUPANTS
order by SPACE_USAGE_KBYTES desc;

OCCUPANT_NAME OCCUPANT_DESC
------------------------ ------------------------------------------------------
EM Enterprise Manager Repository
XDB XDB
SM/AWR    Server Manageability* - Automatic Workload Repository << This is a common area for high storage usage and growth
SM/OPTSTAT    Server Manageability* - Optimizer Statistics History << This is a common area for high storage usage and growth
SDO Oracle Spatial
XSOQHIST    OLAP API History Tables
AO    Analytical Workspace Object Table

The above query will give a quick assessment of the top storage consumers in the SYSAUX tablespace but not the actual storage used: We can alter the query...

select SPACE_USAGE_KBYTES, OCCUPANT_NAME
from V$SYSAUX_OCCUPANTS
order by 1 desc;

SPACE_USAGE_KBYTES OCCUPANT_NAME
------------------ --------------------------------
(1) 118144 SM/AWR
(2) 23360 SM/OPTSTAT
13696 LOGMNR
(3) 10368 SM/ADVISOR ***
6720 SM/OTHER
3584 WM
3264 SMON_SCN_TIME
...
...

AWRINFO.SQL report output:

The AWRinfo.sq. Report HEADER information includes

Generated at (report creation time)
Snapshot interval    (duration of snapshot interval)
Retention duration snapshots will be saved    (Least number of days we keep snapshots)
System and database information (What and where report is generated against)

Tips regarding SYSAUX Space Usage:

Make sure the SYSAUX tablespace is set to AUTOEXTEND off    -- This allows storage to be re-used vs. appended to
Confirm the STATISTICS_LEVEL value ** -- ALL is known to potentially be resource intensive while Basic and Typical are typically not
Check for the usage of advisors, baselines or sql tuning sets:    -- These require trapping information which is retained in snapshots even if the snapshot range was scheduled to be dropped
Run awrinfo.sql scripts on each instance -- To better report and verify which objects are consuming the most space in the SYSAUX tablespace.
Run queries against sysaux_occupants -- A simple method to get general information on SYSAUX storage consumption

Issue#2

Relocate the SYSAUX TABLESPACE

METHOD 1: move the datafile(s) with the database open.

1. ALTER TABLESPACE sysaux OFFLINE NORMAL;

2. Rename the datafile(s) at the OS level

3. ALTER TABLESPACE sysaux RENAME DATAFILE '\SYSAUX01.DBF';

4. ALTER TABLESPACE sysaux ONLINE;

NOTE :

This method may not work at all times as there are processes that need to use the SYSAUX tablespace and if they are using it ... the tablespace will not alter offline ... in which case you will need to use METHOD 2 or 3.

METHOD 2: move the datafiles in mount mode.

1. SHUTDOWN IMMEDIATE

2. Relocate the SYSAUX datafile(s) to the desired location

3. STARTUP MOUNT

4. ALTER DATABASE RENAME FILE
'/FULL_PATH_OF_OLD_LOCATION/AND_DATAFILE_NAME.DBF'
TO
'/FULL_PATH_OF_NEW_LOCATION/AND_DATAFILE_NAME.DBF';

5. ALTER DATABASE OPEN;

METHOD 3: move the datafiles by recreating the controlfiles.

1. ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

2. Locate the trace file created in step 1 ... it will be in the USER_DUMP_DEST

3. Edit the create controlfile statement in the created trace ... change the location of the SYSAUX datafile(s) to their desired location

4. SHUTDOWN the database

5. Relocate the SYSAUX datafile(s) to the desired location

6. Run the script that was created in step 1 and modified in Step 3

Issue#3

Fixing a Corrupted SYSAUX Tablespace

Solution:

SYSAUX was introduced in 10g to store all auxiliary database metadata related to Oracle options and features. This is a mandatory tablespace and cannot be dropped. Therefore it is important to have all objects in this tablespace accessible at all times.

To find out what is stored in this tablespace, look at V$SYSAUX_OCCUPANTS:

SQL> select occupant_name, space_usage_kbytes from v$sysaux_occupants;

# RMAN Block Recovery

RMAN block recovery is only possible if you have a backup and all archivelogs from prior to the time of the corruption.

a. Check for corruption

RMAN> backup validate check logical tablespace SYSAUX;

RMAN> backup validate check logical datafile 3;

b. Once the above RMAN validate is completed, all corruptions found will be written to this view:

SQL> select * from v$database_block_corruption;

c. If V$DATABASE_BLOCK_CORRUPTION returns more than one corrupted block, recover them

RMAN> blockrecover corruption list;

# Restore and Recover the Corrupted Datafile(s)

If you have a backup of the corrupted file(s), then restore and recover the datafile(s) from backup.

Confirm the backuppieces required:

RMAN> restore datafile 3 preview;

If you have all the available backups and archivelogs, then proceed with the restore and recovery:

RMAN> alter database datafile 3 offline;

RMAN> restore datafile 3;

RMAN> recover datafile 3;

SQL> alter database datafile 3 online;

# Export

If all of the above options are exhausted, the last resort is to export the database, tablespace(s), schema(s) or table(s), create a new database and import.

-----xXx-----

Vivek's Blog

SYSAUX Tablespace Issues