Re: Datafile Resize/Max Extents Question

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 Received on Wed Dec 19 2001 - 02:42:55 CST