SORRY ANDY I CAN’T HEAR YOU OVER ALL THESE CLOUDS
To Steeve: I agree, they should set this up in a separate tablespace by
default; though I’m sure the idea is that if you purge soon enough, you’ll
reuse the space regularly and it won’t be an issue.
On May 13, 2016, at 6:33 PM, Andrew Kerber <andrew.kerber@xxxxxxxxx> wrote:
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@xxxxxxxxxxxxxx
<mailto:sbisson@xxxxxxxxxxxxxx>> 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.
