Oracle AWS RDS Perfstat option

I have an Oracle Database hosted on AWS RDS. The Oracle version is Standard Edition One 11.2.0.4. The perfstat option was added to the Instance to help troubleshooting. Perfstat is managed by AWS, so you can add the feature by a simple click in the option group of the Instance.

My Instance yesterday had the following issue: ORA-00257: archiver error. Connect internal only, until freed.

Since the Instance run on RDS, my only option was to increase the storage Size by 5-10GIG so I can start troubleshooting.

Once I could connect to the Instance, I notice The perfstat schema was 18GIG in size! SQL> select sum(bytes) from dba_segments where owner = 'PERFSTAT' order by bytes desc; SUM(BYTES)

Since no purging strategy was put in place, I decided to start purging data. But to my surprise, the default tablespace of the perfstat schema was in the SYSAUX tablespace.

Now that I have freed up the space, I would like to reduce the size of my tablespace to a reasonable size, but AWS support dont offer that. I cannot do it myself, since there is no sysdba access. So I am stuck creating a new Instance and do an export/import into a brand new one.

Now my Instance is 20GIG bigger than it should be. And my data will never be able to use that space, since sysaux is using it.

AWS Support offers me this approach for the future: Enable the perfstat option.
create tablespace perfstat ..
alter user perfstat default tablespace perfstat ; alter table ... move tablespace perfstat alter index ... rebuild tablespace perfstat

I get that I should of not just blindly enable perfstat option and not purge my data. This was not a good move on my part. But I would of hope AWS would setup perfstat with a dedicated tablespace for it. Seems to me the SYSAUX tablespace is not the best approach. It make sense to put statistical data in sysaux, but if shit hits the fan, it is a mess trying to shrink the sysaux tablespace. A simple drop tablespace perfstat would of done the trick.

What do you think of this?

-Steeve

--

http://www.freelists.org/webpage/oracle-l Received on Fri May 13 2016 - 19:30:33 CEST