Re: Datafile size for backup consideration...

Hi,
I was told that running incremental tape backups is *not* the safest approach, it is better to do daily full backups (LAN servers, DDS3/4 DAT tapes are relatively cheap, USA $20-$30 each. My last estimate for a year's worth of full monthly/ weekly/ daily backup tapes was a cost of about $800- $1,500, depending on media type, for about 10-20Gb storage.)

*Warning* You substantially increase risk of data loss caused by bad backups if you incrementally append backups on your tapes. Disaster recovery is about worst case scenarios. What happens if both the disks *and* your backup tapes containing the latest incremental backups fail at the same time? It usually going to be a lot better if you have full daily backups (or at least separate incrementals!).

That way your exposure to a worst case scenario loss is probably going to be limited to only one day of changes.

Anyway, assuming that you are going take the risk and do incremental, appended backups until your tapes are nearly full, see the following.

(People, please correct this analysis as needed)

Scenario #1: big data file:

       note: use "create tablespace" syntax once

• *spend more* money on backup tapes (unless you use compression???)
• probably better performance (you may need to do testing to determine this with certainty)

Scenario #2: smaller data files, added incrementally:

      note: assuming you have done your research for storage
      parameters (which you have to do for either big or small files
      anyway), the "alter tablespace" syntax for adding the small
      data files is easy. You may even have the option of using a
      graphical tool (EOM?) depending on platform. Caution: senior
      DBAs usually want you to use scripts instead.

• *save* money on tapes (assuming no compression?)
• you may eventually see performance problems after adding many incremental files as database grows. If so, you have the possible option of reorganizing into a big file (export/import) when that happens. Please do some planning for system down time. Also, If you have a lot of small files, consider the extra complication of keeping track of all of them in case you need to do disaster recovery.

Ask Oracle tech support if guidelines are available for your platform.

If possible, please refer this issue to your system administrator (SA) and management for discussion/approval prior to making a final decision. If they want to save money on tapes, and thereby increase risk of data loss, they should take responsibility, not you.

best wishes,
ep

On 19 Jul 2000, at 18:26, Slamet Fusianto wrote:

Date sent:      	Wed, 19 Jul 2000 18:26:00 -0800
To:             	Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Send reply to:  	ORACLE-L_at_fatcity.com
From:           	"Slamet Fusianto" <yangken_at_cbn.net.id>
Subject:        	Datafile size for backup consideration...

> Dear DBAs,
>
> When we first create tablespace (user, index, temporary, etc) and datafiles,
> should we consider a large amount of datafiles ? What I mean is now I make one
> datafile for tablespace User is 500 MB. I consider this to be for 3 years ahead.
> Now if I take backup for this tablespace only (for example), it will surely make
> tape backup full after couple of weeks (capacity of tape backup is 4 GB it means
> that if I take full backup every week, the tape will be fulled in 2 months).
> Sure how many tape backups will be used is not our prime considerations, but
> cost will not be so high.
> But I want to ask is should I make a smaller data file, just say 50 MB and then
> after running few months, I can add extents or make new datafiles for this
Received on Wed Jul 19 2000 - 21:58:01 CDT