Re: Segment growth monitoring

From: Jared Still <jkstill_at_gmail.com>
Date: Sat, 14 Nov 2015 11:37:45 -0800
Message-ID: <CAORjz=NStLT7-NywRd+4NOC44rc_vaapbZTrK1+zr1vzDu3LWg_at_mail.gmail.com>

On Fri, Nov 13, 2015 at 9:42 AM, Jared Still <jkstill_at_gmail.com> wrote:

> Probably every DBA of any worth at all has written something to track
> growth and avoid surprises.

It has been pointed out to me that this assessment is somewhat harsh.

I agree - my apologies to any that took offense.

Perhaps it should be restated in this manner:

DBA's should not
- be spending time reacting to space issues. - be spending time adding datafiles to tablespaces

This can all be automated for the most part.

The single most prevalent issue I see on database servers is running out of disk space.

A change in thinking needs to happen in regards to managing space.

For instance, it is not uncommon to have many Oracle instances running on a single server.

Have you carefully allocated how much FRA is available to each database, based on growth, trends, etc?

Consider not doing that. Over allocate the space available in the FRA for each instance.

So the over allocation is twice the available space.

Is that a problem?

If backups are configured to backup and clear out the FRA contents in a timely manner then it is not a problem.

What is a problem is when unanticipated and unplanned batch jobs start generating so much redo that the FRA allocate for that instance begins to fills up.

The oncall DBA is notified of course, but there isn't enough time to properly care for the issue before the FRA fills and the database is suspended.

This is known as an 'outage', an all too familiar word.

However, an over-allocated FRA allows much more time to deal with the issue before an outage occurs.

"But" you say, "isn't there the possibility of the entire FRA area filling, suspending all databases?"
(BTW: have you seen 12c?)

Sure, that is a possibility, but a manageable one.

Running multiple instances on a single server comes with risks. This is one way to mitigate risks.

How about tablespaces?

If you are getting paged for space issues, and adding datafiles manually, you are doing it wrong.

Use autoextend, bigfile tablespaces, whatever - keep it automated.

Use your well crafted reports to keep an eye on total storage use.

Don't worry about it unless you see unusual growth, and/or a previously defined threshold for percentage of use of total storage is reached.

There are much more interesting and beneficial tasks afoot for a DBA than dealing with space issues.

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist Principal Consultant at Pythian
Pythian Blog http://www.pythian.com/blog/author/still/ Oracle Blog: http://jkstill.blogspot.com Home Page: http://jaredstill.com (down for now - see archive.org)

--
http://www.freelists.org/webpage/oracle-l

Received on Sat Nov 14 2015 - 20:37:45 CET