Re: Datafile Resize/Max Extents Question

I didn't see the original question, but I have to say I think you're barking up the wrong tree here.

First off, MAXEXTENTS applies to segments, not datafiles. There's a MAXSIZE clause that applies to datafiles to stop them autoextending for ever. You should be allocating sufficient space for the segment as a whole, without getting too worried about *how* you then chop up the total into extents. 135 extents for a segment is not going to kill you, but you don't want to go beserk about it: 135000 extents would not be pleasant. The main thing is: how big does your segment need to be? Provided that gets converted into only a few hundred extents, you'll be fine.

Second, allowing data files to autoextend is never a particularly good idea unless you are managing the database with an extremely light touch and can't devote the time to do the job "properly".

Third, you are right that a different way of increasing the available space within a tablespace is to simply add additional files: alter tablespace blah add datafile 'name' size 2000m; will do the deed. You're allowed 1022 datafiles per tablespace. So at 2Gb a pop, that's a possible, er.... 2Tb tablespace, if my maths is still in functioning order.

Fourth, Backup Exec, like ArcServe, has an 'agent' which can be used to perform hot backups which will work. I've never used it myself, but someone was posting about it here just recently.

Fifth, your main concern with datafile sizes seem to centre around the fact that large, but mostly empty, datafiles are taking a lot of time to backup. That seems a bit odd to me in the first place, since Oracle will automatically 'round robin' the extent allocations for segments across all available data files within the tablespace, so all files should end up with more or less the same amount of data. That will only not be the case if the application is explicitly specifying the datafile from which the new extents the segments need should be carved.

It's a real shame you can't upgrade to something like 8.0, 8i or 9i, as RMAN will do hot backups for you, and automatically skip any stretches of unused blocks, which seems to be your central worry.

Regards
HJR

--
----------------------------------------------
Resources for Oracle: http://www.hjrdba.com
===============================


"Gary Luck" <klokwerks_at_yahoo.com> wrote in message
news:1b60dac2.0112300933.e2a23d9_at_posting.google.com...


> Thanks for the response...

>

> I'm not working at Agilent.  We're an Agilent customer using their

> scientific instrumentation and of course the Chemstore/Chemstation

> product.

>

> I have to say that we're not particularly happy with the technical

> competence of the gentleman from Agilent that came and set-up this

> product for us.  I wasn't part of the company when the database was

> implemented.

>

> If I could ask one more question:  If I resize my datafiles for this

> tablespace, and one day in the future all of the datafiles reach the

> MAX_EXTENT I have set for them, will it be possible to add more

> datafiles to the tablespace?  I think I read in the documentation

> somewhere that this could cause a problem.

>

> Thanks again, gary.

>

> ganesh_at_gtfs-gulf.com (Ganesh Raja) wrote in message

news:<a8aed4.0112190555.210c6e2a_at_posting.google.com>...


> > Comments Below....

> >

> > klokwerks_at_yahoo.com (Gary Luck) wrote in message

news:<1b60dac2.0112181014.3e13c49b_at_posting.google.com>...


> > > Firstly, I'm new to Oracle and I'd like to thank everyone that

contributes to


> > >

> > > this newsgroup- you have helped me immensely.

> > >

> > > I have an Agilent Chemstor database running Oracle 7.3.4 on NT

4.0SP6a.  When


> >

> > Are u working in Agilent ???

> >

> > >

> > > it was initially set up, the main data collecting tablespace was

created


> > >

> > > thusly:

> > >

> > > 15 datafiles of 2GB each

> > > 'last' datafile set to extend to 16GB by 1GB increments

> > >

> > > The company is only doing cold backups right now- once every Sunday

afternoon


> > >

> > > the database is manually shutdown and backed up to tape using Backup

Exec


> > >

> > > 8.6.  After testing disaster recovery scenarios with Backup Exec it

was found


> > >

> > > that using hot backups, the database was not recoverable.  When I was

hired,


> > >

> > > I was tasked with making hot backups work.  I have written a sql

script to


> > >

> > > perform the hot backups and run nightly using NT's 'at' command.  This

is


> > >

> > > running wonderfully on a replica test box I have been using.  The only

> > >

> > > problem is, it is taking a little over five hours nightly because of

all of


> > >

> > > the 'empty' data I'm backing up.  There is one datafile that has been

filled


> > >

> > > and one other that is about half-filled(1GB).  So essentially, I'm

backing up


> > >

> > > 30GB to get 3GB of data.

> > >

> > > On my test system I resized the empty datafiles to 200MB each and set

extents


> > >

> > > of 200MB to a maximum size of 2GB each.  I used Oracle's Storage

Manager to


> > >

> > > do this and it worked seemlessly.

> > >

> > > My question is this(finally):

> > >

> > > This will give me about 135 extents for this single tablespace- is

that too


> > >

> > > many?

> >

> > The Number of Extents do not Have any significance in performance of

> > the DB. U will get the same performance if it 1 or 100 or 1000

> > Extents.

> >

> > Also, should I use Server Manager or SQLplus to resize the datafiles

> > >

> > > instead of Storage Manager?

> >

> > That also makes no difference bcos Storage manager inturn uses the SQL

> > Statments to do the task for u.

> >

> > > What should I do about the 'last' datafile that

> > >

> > > is set to extend to 16GB?  That seems inexcuseable to me.

> >

> > That is surely inexcusable and will fail in Winnt as the Max Size of a

> > File Supported by NT on NTFS is only 4GB. So It will stall the oracle

> > one day.

> >

> > My Two Cents worth Do not Say Autoextend to u'r Files... Always Check

> > to see how they grow and extend them uerself... U will have more

> > control... And dont give Maxsize unlimited.

> >

> > >

> > > Again, thanks to all.

> >

> > No probs Buddy we are for each other....

> >

> > >

> > > Later, gary.

> > > klokwerks_at_yahoo.com

> >

> >

> >

> > Regards,

> > Ganesh R