Re: Datafile Resize/Max Extents Question

postbus_at_sybrandb.demon.nl (Sybrand Bakker) wrote in message news:<a20d28ee.0112190042.7b08e68b_at_posting.google.com>...
> 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
> >
> > 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? Also, should I use Server Manager or SQLplus to resize the datafiles
> >
> > instead of Storage Manager? What should I do about the 'last' datafile that
> >
> > is set to extend to 16GB? That seems inexcuseable to me.
> >
> > Again, thanks to all.
> >
> > Later, gary.
> > klokwerks_at_yahoo.com
>
> First of all: get away from 7.3.4. It has been desupported for almost
> a year now, and newer versions have better facilities to deal with
> your problems.
> Rman in version 8.0 and beyond is capable of backing up only the
> changed blocks (instead of the full 30G). This would make this whole
> discussion more or less academical.
> Secondly: as this is 7.3.4 space management is very costly. The number
> of free extents highly depends on how often your tables extend. I
> would consider it more important those free extents match extents to
> be allocated in size.
> I don't think NT is ever going to allow 16G datafiles. It might have a
> limit of 2G.
> It doesn't matter whether you use sql*plus or svrmgr23 to resize your
> datafiles. The command is part of the sql language. I would choose
> sql*plus for it's editing facilities
>
> Hth
>
> Sybrand Bakker
> Senior Oracle DBA

Thanks for the response...

I can't get away from 7.3.4- its a canned package for the Chemstore product from Agilent. I would love to go to 8, but they don't produce a version of Chemstore on it yet.

I checked my max extents for each tablespace and they are set to 249 so I'm assuming I'm in good shape there. As for the datafile set to grow over 2GB- do you think I should bring this down to 2GB for max extent size and then add new datafiles to the tablespace when the need arises?

Again, thanks much. Received on Wed Dec 19 2001 - 10:52:20 CST