If sysaux
tablespace of oracle instance grew huge and filled up the complete tablespace.
We were also not able to resize the tablespace to create some free space.
So here in this article we will discuss on how to create some free space in SYSAUX to continue normal database operations but before going into details we shall know few basics about SYSAUX to help understand the process better.
So here in this article we will discuss on how to create some free space in SYSAUX to continue normal database operations but before going into details we shall know few basics about SYSAUX to help understand the process better.
In Oracle
SYSAUX tablespace is considered as an auxiliary tablespace to the SYSTEM
tablespace. This is required by Oracle as a default tablespace for many
database features and products. Previous to SYSAUX oracle use to require
multiple tablespaces to support the same database features and products. Thus
using the SYSAUX tablespace reduces load on SYSTEM tablespace.
Restrictions
on SYSAUX tablespace
1.
Using SYSAUX DATAFILE clause in the CREATE DATABASE statement you can specify
only datafile attributes in SYSAUX tablespace.
2. You can not alter attributes like (PERMANENT, READ WRITE,EXTENT MANAGMENT LOCAL,SEGMENT SPACE MANAGMENT AUTO) with an ALTER TABLESPACE statement
3. SYSAUX tablespace cannot be dropped or renamed.
2. You can not alter attributes like (PERMANENT, READ WRITE,EXTENT MANAGMENT LOCAL,SEGMENT SPACE MANAGMENT AUTO) with an ALTER TABLESPACE statement
3. SYSAUX tablespace cannot be dropped or renamed.
Now coming to our issue of oracle sysaux
tablespace full, we need to do following to free up the space. To give a
prospect of what was the size of SYSAUX before cleanup we have put a screenshot
of SYSAUX before clean up below: ( 94% full)
1. Check sysaux table free size and what’s occupying SYSAUX tablespace:
Tablespace Used MB Free MB Total MB Pct. Free
------------------------- ----------- ----------- ----------- ----------
EXAMPLE 1,219 42 1,261 3.33
SYSTEM 2,438 634 3,072 20.64
SYSAUX 765 85 850 10.28
USERS 2 3 5 60
METALS 41 359 400 89.75
UNDOTBS1 36 609 645 94.42
6 rows selected.
Run below query to know what all occupants are
there in sysaux occupying all the space.
SQL> select
occupant_name,occupant_desc,space_usage_kbytes from v$sysaux_occupants;
OCCUPANT_NAME OCCUPANT_DESC SPACE_USAGE_KBYTES
-------------------------
--------------- -----------------------
LOGMNR LogMiner 14208
LOGSTDBY Logical Standby 1536
SMON_SCN_TIME Transaction Layer - SCN to TIME
mapping 3328
AUDSYS AUDSYS schema objects 1536
PL/SCOPE PL/SQL Identifier
Collection 1600
STREAMS Oracle Streams 1024
AUDIT_TABLES DB audit tables
0
XDB XDB
70400
AO Analytical Workspace
Object Table
41088
XSOQHIST OLAP API History Tables
41088
XSAMD OLAP Catalog
0
SM/AWR Server Manageability -
Automatic Workload Repository 25856
SM/ADVISOR Server Manageability - Advisor
Framework 10880
SM/OPTSTAT Server Manageability - Optimizer
Statistics History 42688
SM/OTHER Server Manageability - Other
Components 53184
STATSPACK Statspack Repository 0
SDO Oracle Spatial
80640
WM orkspace Manager
7360
ORDIM Oracle Multimedia ORDSYS
Components
448
ORDIM/ORDDATA Oracle Multimedia ORDDATA
Components
16512
ORDIM/ORDPLUGINS Oracle Multimedia ORDPLUGINS
Components
0
ORDIM/SI_INFORMTN_SCHEMA
Oracle Multimedia SI_INFORMTN_SCHEMA Components 0
EM Enterprise Manager Repository 0
TEXT Oracle Text 4032
ULTRASEARCH Oracle Ultra Search
0
ULTRASEARCH_DEMO_USER Oracle Ultra Search Demo User 0
EXPRESSION_FILTER Expression Filter System
0
EM_MONITORING_USER Enterprise Manager Monitoring User 512
TSM Oracle Transparent Session Migration User 0
SQL_MANAGEMENT_BASE SQL Management Base Schema
2496
AUTO_TASK Automated Maintenance
Tasks
320
JOB_SCHEDULER
Unified Job Scheduler
1536
32
rows selected.
We can clearly see AWR
consumes the good amount of space. Lets find the AWR consumption in MB.
SQL>
col
OCCUPANT_NAME form a25
col
OCCUPANT_DESC form a55
select
occupant_name,occupant_desc,space_usage_kbytes/1024 Space_usage_in_MB from
v$sysaux_occupants where occupant_name like '%AWR%';
OCCUPANT_NAME OCCUPANT_DESC
SPACE_USAGE_IN_MB
-------------------------
------------------------------------------------------- -----------------
SM/AWR Server Manageability -
Automatic Workload Repository
47.375
2.
Check AWR Retention Period:
First is to check what is the AWR retention
period in DB. For us it was set for 90 days which we did not require. You can
query using below SQL:
SQL> select retention from dba_hist_wr_control;
We reduced it to 7 day which
is 7*24*60 = 10080 minutes. In this example the retention
period is modified to 7 days (10080 minutes) and the interval between each
snapshot is 60 minutes.
execute
dbms_workload_repository.modify_snapshot_settings (interval => 60,retention
=> 10080);
In case while reducing the
retention period you encounter below error then check the MOVING_WINDOW_SIZE
value and update it to correct value and then execute the above AWR retention
query again.
execute dbms_workload_repository.modify_snapshot_settings(retention
=> 5760);
exec
DBMS_WORKLOAD_REPOSITORY.modify_baseline_window_size( window_size =>7);
SELECT
moving_window_size
FROM
dba_hist_baseline
WHERE
baseline_type = 'MOVING_WINDOW';
3.
Cleanup old AWR reports to free up space:
Once the retention period is set you can follow
below steps to cleanup the old AWR reports to free up space. Run below
query to find the oldest and newest AWR snapshots.
SELECT
snap_id,
begin_interval_time, end_interval_time
FROM
SYS.WRM$_SNAPSHOT
WHERE
snap_id = (
SELECT MIN (snap_id) FROM SYS.WRM$_SNAPSHOT)
UNION
SELECT
snap_id,
begin_interval_time, end_interval_time
FROM
SYS.WRM$_SNAPSHOT
WHERE
snap_id = (
SELECT MAX (snap_id) FROM SYS.WRM$_SNAPSHOT)
/
SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME
----------
--------------------------------------
----------------------------
161 11-SEP-18 02.30.18.274 PM 11-SEP-18 03.30.23.151 PM
162 11-SEP-18 03.30.23.151 PM 11-SEP-18 04.30.27.498 PM
Execute below command to cleanup all AWR reports between
snap_id 161 to 162.
BEGIN
dbms_workload_repository.drop_snapshot_range(low_snap_id
=> 161, high_snap_id=>162);
END;
/
If above removal process taking too much of time then you can
run below two sqls as sysdba to drop the old AWR’s and rebuild the
repositories. This process is very fast.
SQL> connect / as sysdba
SQL> @?/rdbms/admin/catnoawr.sql
SQL> @?/rdbms/admin/catawrtb.sql
After clearing up all
the AWR reports we were able to cleanup approx of 5GB space from SYSAUX
tablespace.
SYSAUX Tablespace Post AWR Cleanup:
SYSAUX Tablespace Post AWR Cleanup:
SQL> select
occupant_name,occupant_desc,space_usage_kbytes from v$sysaux_occupants where
occupant_name like '%AWR%';
OCCUPANT_NAME OCCUPANT_DESC SPACE_USAGE_KBYTES
--------------- ------------------------------------------------------- ------------------
SM/AWR Server Manageability - Automatic Workload Repository 35072
Tablespace Used MB Free MB Total MB Pct. Free
------------------------- ----------- ----------- ----------- ----------
EXAMPLE 1,219 42 1,261 3.33
SYSTEM 2,438 634 3,072 20.64
SYSAUX 639 211 850 24.82
USERS 2 3 5 60
METALS 41 359 400 89.75
UNDOTBS1 36 609 645 94.42
6 rows selected.
-------------------------------------------------END-------------------------------------------------------------
