On Backup strategy

[jn]

i have 1 + GB sized oracle8i databases running on NT & UNIX.

is anyone out there using archive log mode and taking online backups ? can you pls help on these issues :
how is the overall database performance when running in archive log mode ?
how often do i need to take the online backup ?
if i take the backup daily, will there be performance issues ?

is there any better reliable backup strategy ?

Thanks,

 

[jcote]

At the shop where I work, we ONLY take online backups (even though in some situations, I think cold backup would be appropriate). We use online backup on all of our instances mostly because in most of the instances, we need 24/7, and we use the same strategy for the other instances, to have a single backup stategy.

The impact on datafiles of a tablespace when it is in online backup mode is that the header block which contains the last SCN number applied on that datafile is not updated during the time the tablespace is in "online backup" mode. That should not concern you. What might have an impact on performance is the fact that there is more disk activity since you have to read all the blocks. This has not proven to be a problem for us, even though there is activity all day (less at night time but still active). Still, I can't see why you would run the backups during the day?

The frequency of the backups will depend on how much update is done on the database. The more logs that will be archived during a given period, the longer it will take Oracle to re-apply the changes. We generate a couple of hundreds megs of log a day, and take dayly online backup.

 

[jn]

thanks 'jcote'.
will be taking backups every night, not every day.
somebody suggested export for backups, have any experience on it ?
thanks,

 

[carp]

Export is a good way to augment your backups (depending, of course, on your recovery strategies). However, it is NOT a substitute for backing up. In order for EXPORT to be of any use, you have to be able to IMPORT - which is impossible if you can't bring your database up!

 

[jn]

Thanks 'carp'.

 

[nontrad]

We use archive log mode for both our Dbs. I have noticed no decrease in performance by doing this and feel it is absolutely necessary when you have a Db with a lot of inserts & updates. No archive mode is fine for development OR static tables where the info could be reloaded from flat files if they are ever corrupted or something.

We do a backup once a night. We looked at the listener.log file and determined a time when there was little/no activity for the Db. Then in Unix, we set up a CRON job to do a backup automatically. We back up the Db and the control files. 3 AM BTW

There are no backup performance issues because we took the time to set up the backup when there would be little/no activity anyway.

 

[lfoata]

You database is quite small. Online backups, if sent to disks, should be very fast (depending on the controller and drive). For a 1Gig database, it should take between 30 mins and 60 mins.
What I do is take an online backup and wrte it to a dedicated disk (avoid IO contention) and then backup that disk to tape.
To do your online backup on unix, you may want to look at

http://www.backupcentral.com

for some automated scripts.
I do a full export and an online backup everyday, and a full file system offline backup one a week (if you can).
You may also want to look at RMAN (too much to describe here).
Archive mode does not impact performance very much , but you may want to trun it off if you are loading a bunch of data and then turn it back on.