Re: LMTs
- From: Mayen.Shah@xxxxxxxxxx
- To: "Niall Litchfield" <niall.litchfield@xxxxxxxxx>
- Date: Tue, 11 Mar 2008 14:12:34 -0400
filesystem size, tape capacity, backup window duration, filesystem size on
clone databases, filesystem size on standbys
All of these are valid concerns but what does it has to do with whether
you have autoextend on or off? I believe in allocating space only when
needed. How many time you have been told by developer that they need xxx
Gb of space for their application and use only 50% for next few years?
Let's say tablespace A has 6 Gb of data. Projected growth is 1 Gb for next
month. You would configure this tablespace (datafile) to be 7 Gb + 10%
extra, I would configure this at 6Gb autoextensible to 7.5 Gb. I make sure
file system size, size at clone/standby site, tape capacity etc. etc. is
sufficient for 7.5 Gb. Additional 1.5Gb you have allocated is wasted
space until used. (luckily with LMTs this is not that big issue but I have
adopted this practice when we did not have LMT)
Bottom line is if database/tablespace needs space it needs space, whether
you allocate all now or little at time as and when needed. It is probably
personal preference.
Thanks
Mayen
"Niall Litchfield" <niall.litchfield@xxxxxxxxx>
Mar 11 2008 11:35 AM
To
Mayen Shah/ITS/Lazard@Lazard NYC
cc
joe_dba@xxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx, oracle-l-bounce@xxxxxxxxxxxxx
Subject
Re: LMTs
Hi Shah,
I'm not convinced that I'm any more likely to run out of space if my alert
is configured for 95% space usage or space used being within 5% of a
calculated max value. I entirely agree that the more databases and the
more tablespaces that you have the more difficult monitoring becomes - and
the more attractive automating space management becomes, but I'm always
going to have limits on
1. filesystem size
2. tape capacity
3. backup window duration
4. filesystem size on clone databases
5. filesystem size on standbys
I'm sure there are others, which is why my personal preference is for
fixed size datafiles and manual intervention. That said I only have 29
Oracle databases to monitor - though that does include 8 apps ones! I'm
sure that others will have significantly more.
On Tue, Mar 11, 2008 at 12:32 PM, <Mayen.Shah@xxxxxxxxxx> wrote:
Niall,
Only problem in setting autoextensible to off is if you run out space in
middle of business day, some transactions will fail and rest will be the
history......
In large environment with many production database, with many more
tablespaces it becomes difficult to monitor and maintain manually to make
sure each tablespace has enough free space. I am using all tablespace
autoextensible with fixed upper limit. I also have set up monitoring
(patrol in my case) to send e-mail any time any tablespace is autoextended
and mobile alert when actual size is within 5% of maxsize. If any
tablespace has multiple datafile, I have kept only one datafile as
autoextensible.
Mayen Shah
"Niall Litchfield" <niall.litchfield@xxxxxxxxx>
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
Mar 11 2008 05:37 AM
Please respond to
niall.litchfield@xxxxxxxxx
To
joe_dba@xxxxxxxxxxx
cc
oracle-l@xxxxxxxxxxxxx
Subject
Re: LMTs
On Mon, Mar 10, 2008 at 2:30 PM, Joe Smith <joe_dba@xxxxxxxxxxx> wrote:
CREATE TABLESPACE data
DATAFILE '/FS/data_s01.dbf' size 2000m autoextend on next 1m maxsize
12000m,
'/FS/data_s02.dbf' size 2000m autoextend on next 1m maxsize
12000m
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;
How do you control the size of LMTs.
If I remove the "autoextend on next 1m" I can't use the "maxsize" keyword.
How do I restrict the size of the datafiles for LMTs
Hey Joe (always wanted to say that sorry)
You have a choice. Either you want the datafiles to grow as needed and
limit the total size to which they can grow - i.e to be autoextensible -
in which case it makes sense that you need both the amount by which to
grow each time and the absolute limit. Alternatively you know how big you
want them to be and you just specify the fixed size for the datafile (no
autoextension at all).
I happen to prefer the latter - not least because it then becomes easy to
tell when you are running out of space in a tablespace (how much free
space is left), whereas when the datafiles are autoextensible it's very
easily to miscalculate how full a tablespace is. I also like to change
control space operations because they have an impact on clones, backups
dataguard space requirements and so on. If you do prefer to let Oracle
handle the growth then I'd suggest a rather larger next size than 1m. Once
you get to 2gb of data every time you add 1mb more data you'll be growing
the datafile which is a lot of growth operations. You'll also likely cause
more filesystem fragmentation - though you might not care about that.
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
Other related posts:
