Re: Oracle AWS RDS Perfstat option

From: Andrew Kerber <andrew.kerber_at_gmail.com>
Date: Fri, 13 May 2016 17:33:37 -0500
Message-Id: <A3F0C898-7689-4DE8-A4C4-65FF91910382_at_gmail.com>



I think there is a reason why I manage my own databases.

Sent from my iPad

> On May 13, 2016, at 12:30 PM, Steeve Bisson <sbisson_at_touchtunes.com> wrote:
>
> Hello all,
>
> 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)
> ----------
> 1868641075
>
> 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
>
> CONFIDENTIALITY CAUTION
> This e-mail and any attachments may be confidential or legally privileged. If you received this message in error or are not the intended recipient, you should destroy the e-mail message and any attachments or copies, and you are prohibited from retaining, distributing, disclosing or using any information contained herein. Please inform us of the erroneous delivery by return e-mail. Thank you for your cooperation.
> DOCUMENT CONFIDENTIEL
> Le présent courriel et tout fichier joint à celui-ci peuvent contenir des renseignements confidentiels ou privilégiés. Si cet envoi ne s'adresse pas à vous ou si vous l'avez reçu par erreur, vous devez l'effacer. Vous ne pouvez conserver, distribuer, communiquer ou utiliser les renseignements qu'il contient. Nous vous prions de nous signaler l'erreur par courriel. Merci de votre collaboration.

--
http://www.freelists.org/webpage/oracle-l
Received on Sat May 14 2016 - 00:33:37 CEST

Original text of this message