k3vsmith
asked on
SYSAUX tablespace
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!
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!
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
http://docs.oracle.com/cd/E24628_01/doc.121/e24473/repository.htm#sthref572
allthough that might not retrieve the used tablespace
ASKER
Thanks for the quick response.
These are the top 10 objects in SYSAUX:
OWNER,SEGMENT_NAME,SEGMENT _TYPE,MB
SYS,SYS_LOB0001391512C0003 8$$,LOBSEG MENT,1446
SYS,WRH$_ACTIVE_SESSION_HI STORY,TABL E PARTITION,1223
SYS,WRH$_SQL_PLAN,TABLE,10 88
SYS,WRH$_SYSMETRIC_HISTORY ,TABLE,101 4
SYS,WRH$_SYSMETRIC_HISTORY _INDEX,IND EX,627
SYS,WRH$_SYSMETRIC_SUMMARY ,TABLE,621
SYS,WRH$_SYSSTAT_PK,INDEX PARTITION,536
SYS,SYS_LOB0001388926C0000 7$$,LOBSEG MENT,428
SYS,WRH$_BG_EVENT_SUMMARY, TABLE,381
SYS,WRH$_SYSSTAT,TABLE PARTITION,364
These are the top 10 objects in SYSAUX:
OWNER,SEGMENT_NAME,SEGMENT
SYS,SYS_LOB0001391512C0003
SYS,WRH$_ACTIVE_SESSION_HI
SYS,WRH$_SQL_PLAN,TABLE,10
SYS,WRH$_SYSMETRIC_HISTORY
SYS,WRH$_SYSMETRIC_HISTORY
SYS,WRH$_SYSMETRIC_SUMMARY
SYS,WRH$_SYSSTAT_PK,INDEX PARTITION,536
SYS,SYS_LOB0001388926C0000
SYS,WRH$_BG_EVENT_SUMMARY,
SYS,WRH$_SYSSTAT,TABLE PARTITION,364
the most of the objects is OEM, AWR ,ASH and LOB related objects. A LOB segment stores data for a CLOB/BLOB table column.
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.
- 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.
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
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
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
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
ASKER
Thank you guys for the help. Do you guys have any insight as to how best to remove AWR data?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you all for your feedback. I have enough info to proceed.
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