This discussion is archived
4 Replies Latest reply: Dec 7, 2012 11:03 AM by Mark Malakanov (user11181920) RSS

Proactive Tablespace Management in a large environment

jfree427 Newbie
Currently Being Moderated
Hi, I was wondering how others in the community manage tablespaces proactively. In our situation we have thousands of oracle databases and are not using a primary DBA service model. We don't have DBA's looking at individual databases on a daily basis.

It often becomes a reactive measure to add space. Given the size of our environment tablespace management becomes a very tedious and time consuming action for us. Looking for way to minimize or automate this process.

I'm looking for a way to have a more "intelligent autoextend". With autoextend, the database engine won't take action until there is space pressure (ie. 99%). I'm looking for something when it crosses a defined thershold (ie. 85%).

To further add to the complexity we use shared storage (ASM) for many databases. There needs to be intelligence to check the available space first.

We are considering writing a custom script and using EM's Corrective Action to invoke the script based on the threshold. This will be a fairly significant development effort.

Is there anything from oracle "out of the box" that I should be considering?
How are others out there managing space in their large, complex oracle environments?

Ideas are much appreciated!
  • 1. Re: Proactive Tablespace Management in a large environment
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    We don't have DBA's looking at individual databases on a daily basis.
    DBA should not do this stupid task.

    The answer is - MONITORING
  • 2. Re: Proactive Tablespace Management in a large environment
    Iordan Iotzov Expert
    Currently Being Moderated
    A good proactive tablespace management policy should be based on forecasting. Follow the links below to my presentation about tablespace forecasting with Oracle EM.
    http://wp.me/a1DHW2-4e
    http://wp.me/a1DHW2-M

    Autoextend is a great feature, but it requires some planning - http://wp.me/p1DHW2-37

    Iordan Iotzov
    http://iiotzov.wordpress.com/
  • 3. Re: Proactive Tablespace Management in a large environment
    jfree427 Newbie
    Currently Being Moderated
    Thanks, Iordan. We actually do use EM repository information to "predict" tablespace growth and forecast it out. However, DBA's still need to take time out of their day to add the space.

    My ultimate goal is to not have DBA's add space entirely (or close to it) freeing them up to focus on performance tuning and other more exciting DBA tasks. As I stated, we have thousands of databases and our business is growing rapidly which means our databases are also growing rapidly.

    Thanks
    Jarred
  • 4. Re: Proactive Tablespace Management in a large environment
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    I'm looking for a way to have a more "intelligent autoextend". With autoextend, the database engine won't take action until there is space pressure (ie. 99%). I'm looking for something when it crosses a defined thershold (ie. 85%).
    What will be a benefit of such "intelligent autoextend"? What is a difference if a data file extended being 85% full versus 99% full?
    From Application perspective there is no difference, it got the space for data in any case.
    My ultimate goal is to not have DBA's add space entirely (or close to it)
    why the "out of box" autoextend does not work for you?
    Just specify MAXSIZE according to your storage and DBA do not need to add space entirely. Until disk is full.

    But. If you really do need some "intelligent autoextend" you (or your DBA) can write a PL/SQL script that can use any level of intelligence you put in its logic. It can forecast, extend datafiles, add datafiles, shrink them, even check disk space and send email about it.
    Writing this script will be that "more exciting DBA task" ;)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points