Solved

SYSAUX tablespace

Posted on 2014-02-03
10
1,761 Views
Last Modified: 2014-02-06
My manager has tasked me with purging some space from the SYSAUX tablespace in one of our databases that has grown large. He asked me to do this by doing the following:
Find large objects in tablespace
Export tables out
Remove OEM related data and anything else we can purge
Import back in which will remove the tablespace and renter it with the smaller data

My manager has left for a few weeks vacation and Id like to get this task complete for him although I need assistance. Im able to find the large objects in tablespace but dont know what is related to OEM, nor do I know what is capable of being purged from SYSAUX without messing up the database? Can any of you give me some insight? Thanks!
0
Comment
Question by:k3vsmith
  • 3
  • 3
  • 3
  • +1
10 Comments
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 39829559
see an aud$ table in sysaux tablespace ?
if so then auditing is switched on and still in sysaux tablespace.

it has to be moved to a different tablespace to be able to purge it
see here for a very detailed explanation
http://www.oracle-base.com/articles/11g/auditing-enhancements-11gr2.php
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 39829596
you could also set the purging policies to get rid of the old data in oem
http://docs.oracle.com/cd/E24628_01/doc.121/e24473/repository.htm#sthref572

allthough that might not retrieve the used tablespace
0
 

Author Comment

by:k3vsmith
ID: 39829678
Thanks for the quick response.
These are the top 10 objects in SYSAUX:

OWNER,SEGMENT_NAME,SEGMENT_TYPE,MB
SYS,SYS_LOB0001391512C00038$$,LOBSEGMENT,1446
SYS,WRH$_ACTIVE_SESSION_HISTORY,TABLE PARTITION,1223
SYS,WRH$_SQL_PLAN,TABLE,1088
SYS,WRH$_SYSMETRIC_HISTORY,TABLE,1014
SYS,WRH$_SYSMETRIC_HISTORY_INDEX,INDEX,627
SYS,WRH$_SYSMETRIC_SUMMARY,TABLE,621
SYS,WRH$_SYSSTAT_PK,INDEX PARTITION,536
SYS,SYS_LOB0001388926C00007$$,LOBSEGMENT,428
SYS,WRH$_BG_EVENT_SUMMARY,TABLE,381
SYS,WRH$_SYSSTAT,TABLE PARTITION,364
0
 
LVL 12

Expert Comment

by:praveencpk
ID: 39830034
the most of the objects is  OEM, AWR ,ASH  and LOB related objects. A LOB segment stores data for a CLOB/BLOB table column.
0
 
LVL 8

Expert Comment

by:Surrano
ID: 39831818
Bad news:
- AWR cannot be completely turned off
- *Any* manipulation of AWR data needs appropriate (paid) licenses (not included in Enterprise Edition)
- AWR snapshot size will grow over time (this is a known issue) so even deleting old snapshots is not a final solution, because the snapshot will eventually reach the size where only one snapshot fits.

Good news:
- AWR data is not needed for everyday operation, nor for mere mortals like DBAs, only for Oracle Support Requests. (you are allowed to provide them requested AWR information even if you don't have the license)
- Oracle handles the case when SYSAUX is full with no problems. In worst case some AWR stuff will fail. So what.

So you should apply two measures:
- ensure that SYSAUX tablespace is limited in size (e.g. current size + 10% would do)
- modify your DB free space checking tools so that they don't raise alarms on SYSAUX tablespace being 100% full.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 39831829
That's the trap everyone falls into
this is not true
(you are allowed to provide them requested AWR information even if you don't have the license)

it's stated in the license that support will ask your for AWR data because they are oblivious to what you are entitled to by your license
it's up to you (the dba) to know if you are licensed to that

after that the license people come in: i see you use AWR but aren't license for it
here's your extra fee you need to pay

so be careful with that
0
 
LVL 8

Expert Comment

by:Surrano
ID: 39831879
Well we are more foxy than that.

Oracle Support: "Please send AWR Reports"
Me: "I'm sorry but we don't have the necessary licenses."
Oracle Support: "That's no problem, Sir. Please send them."

Once we have it written in an email Oracle shouldn't complain, maybe they should subtract the license fee from the annual bonus of that given support expert. :P
0
 

Author Comment

by:k3vsmith
ID: 39833254
Thank you guys for the help. Do you guys have any insight as to how best to remove AWR data?
0
 
LVL 8

Accepted Solution

by:
Surrano earned 500 total points
ID: 39834825
Officially?
1. Buy license.
Otherwise you are not allowed to delete, change retention-- nothing.

Technically?
2. Check snapshot range to delete:
SELECT snap_id, begin_interval_time, end_interval_time FROM SYS.WRM$_SNAPSHOT
WHERE snap_id = ( SELECT MIN (snap_id) FROM SYS.WRM$_SNAPSHOT) and instance_number=1
UNION
SELECT snap_id, begin_interval_time, end_interval_time FROM SYS.WRM$_SNAPSHOT
WHERE snap_id = ( select start_snap_id-1 from dba_hist_baseline) and instance_number=1;
 

Open in new window

3. If there is only one row: nothing to delete.
If there are two rows: substitute the snap_id values to the following command:
exec dbms_workload_repository.drop_snapshot_range(low_snap_id => 10147, high_snap_id=>10210); 

Open in new window

4. To persistently adjust configuration, reduce retention as follows:
select * from  DBA_HIST_BASELINE; 
-- reduce AWR moving window size (example below with 4 days, specified in days)
exec DBMS_WORKLOAD_REPOSITORY.modify_baseline_window_size(window_size => 4);
-- reduce AWR retention parameter (same 4 days, specified in minutes)
exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention =>4*24*60);
-- check results
select * from  DBA_HIST_BASELINE; 

Open in new window


But as I said even this is a temporary solution; AWR will eventually grow over time to the limits of the tablespace.
0
 

Author Closing Comment

by:k3vsmith
ID: 39839779
Thank you all for your feedback. I have enough info to proceed.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now