RE: Database Outages - Best Practices

We do this on our 8i and 9i databases, as well, and see performance improvements and physical disk reclaimed. Hence the customer is happy and well inclined to give us a maintenance window whenever justified. Monthly purges are run against the larger databases. Once every 6 months we perform a table reorg to reclaim the free space.  

I just happen to be in the middle of this financials reorg and thus it was paramount in my mind and why I used it as an example. Forgive me for not being able to get my customer to commit the million or so dollars that will be needed to upgrade them to Financials 11i. They just don't see the need as long as 10.7SC is running good for them. Not all of us are lucky enough to be working with ONLY the latest and greatest releases of everything.  

Yes, you can rebuild indexes in place with the rebuild command (in 8 and 9) and the effect on users is minimal. We have done so on many occasions. Also, tables can now be 'moved' in 9i which greatly helps. Do you need to rebuild indexes? Well if you delete 10% or 20% of the rows in a multimillion row table, the leafs of your index will NOT automatically coalesce themselves. The holes will remain where rows were deleted. YES, in 9i you can issue 'alter index emp_idx coalesce;' and Oracle will coalesce leafs in the tree. This will cause you to have full blocks of free space in your leaves instead of blocks partially filled next to each other. But that is not possible in 8i and (oh my gosh someone is STILL on 7?) Version 7. As I mentioned, 8i does have the rebuild...online command.    

Our SOP is that no outage is required to move/reorg/rebuild (WHATEVER you wish to call it but they all have the same outcome) tables under 500M. Anything else requires a maintenance window. These are part of the SLA's we have in place.    

From: ryan_gaffuri_at_comcast.net [mailto:ryan_gaffuri_at_comcast.net] Sent: Thursday, February 17, 2005 10:03 AM To: Hollis, Les; davewendelken_at_earthlink.net; oracle-l_at_freelists.org Cc: Hollis, Les
Subject: RE: Database Outages - Best Practices  

this is an exceptional case and will not happen to many people these days. how many people are still on v7 with financials?  

understandable in your case, but most of us don't have to deal with this. i think people percieved your response to be a general case. nearly all down time these days is due to builds, data migrations, etc.... very little of it should be for administration.  

• Original message --------------

> That would be great IF the client were to pop the bucks for
> partitioning.
>
> PLUS this is a V7 database I'm talking about here
specifically....PLUS

> it is Oracle Financials which you don't want to muck around
with table

> definition's, etc...AOL doesn't like it PLUS....PLUS......
>
> You have a pretty good understanding of that part of
partitioning...but

> AGAIN, it is expensive and not all customers/clients are
willing to pay

> the price

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