Hi Steeve,
I'll reply to you off list, and hook you up with the right people in AWS. When
there's a solution, I'll circle back and post it here.
Jeremiah
----- Original Message -----
From: "Steeve Bisson" <sbisson@xxxxxxxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
Sent: Friday, May 13, 2016 10:30:33 AM
Subject: Oracle AWS RDS Perfstat option
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.
