I had started a somewhat lengthy response, then took a "break" (i.e. had to do some real work), and came back to find that Mark had, of course, said it all far better...
One thing I wanted to mention is that autoextend is very useful for database migrations, where you're moving a database (usually using some form of export/import) from one place to another. Creating a reasonable set of datafiles initially really small on the destination database, then setting autoextend so they fill as needed, let's you avoid overallocating space. Then, when the migration is complete, you can disable autoextend (if you wish) and manage space however you are accustomed.
The only remaining thing I wanted to add is that I've always found it useful to be aware of situations where datafiles can (possibly) autoextend and run out of space on that file-system. It is pretty common to "over-subscribe" to a file-system using autoextend, since we cannot always predict which of several dozen or several hundred datafiles are going to suddenly grow like crazy, we set them all to autoextend large, thus cumulatively over-subscribing to the space in the file-system. Something like booking airline reservations, where they always overfill the planes, counting on the fact that a certain percentage of booked passengers will cancel or reschedule. So, I have a UNIX shell script which queries DBA_DATA_FILES, DBA_TEMP_FILES, and V$LOGFILE and summarizes space on mount-points, then runs the UNIX "df" command to see how much free space presently exists, and report situations where specific mount-points are "over-subscribed".
I've not previously posted this script to my website because .... well, I'm lazy ... but I'll be prettifying it and posting it tonight or in the next day or so. It'll be located at http://www.EvDBT.com/tools.htm and named "chk_mtpt_atrisk.sh" -- I wrote it a few years ago and found it useful several times since...
Hope this helps... Tim Gorman consultant - Evergreen Database Technologies, Inc. P.O. Box 630791, Highlands Ranch CO 80163-0791 website = http://www.EvDBT.com/ email = Tim@xxxxxxxxx mobile = +1-303-885-4526 fax = +1-303-484-3608 Yahoo IM = tim_evdbt Mark W. Farnham wrote:
Whether to turn autoextend on or not depends on far too many issues and varied situations of infrastructure, performance requirements, and acceptable costs of operations for anyone to claim there is a "best practice." However, please be clear that turning autoextend on does NOT require you to rely on autoextend. You can still plan and make allocations such that expected growth plus a reasonable margin for error will avoid autoextend actually taking place. On some of your databases you might consider autoextention a routine event and on other databases you might consider autoextention actually taking place to be an operational error. Likewise, you can work with the operating system team to give them growth estimates far enough ahead so they can work with your storage procurement folks to avoid being between a rock and a hard place. Planning a disk farm tends to produce a disk farm that functions better, even if deployed to merely avoid hot spots rather than to maximize throughput. Even if you are just adding media to an ASM disk group, planning when to add it so that dynamic rebalancing takes place when it is more convenient instead of when your system is busiest just makes sense. Having a safety net does not require you to fall off the trapeze. (Except of course on your test systems, where you should in fact test all your safety nets.) Eliminating the reasons for your lack of trust in both areas should become a priority for you. I would suggest you start with something like "In order to to my job correctly, I need to be able to know that..." rather than "You're all buffoons I wouldn't trust with my lunch order..." Regards, mwf -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Guillermo Alan Bort Sent: Monday, April 13, 2009 5:33 AM To: oracle-l Subject: Autoextend or not? So, We have a debate going on about allowing databases with datafiles with autoextend on... anybody has any input on this? any experience? My side: I don't like autoextend, I can't trust the unix team to do their job and I can't allow for a datafile to fill up the FS. Then again, I can't trust monitoring tools to do their job, so autoextend *could* save the day. thanks, and kind regards to all! Alan Bort Oracle Certified Professional -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l
-- //www.freelists.org/webpage/oracle-l
