Re: backup strategy

"Audun Jensen" <audun_j_at_yahoo.no> wrote in message news:Xns90549EA33691Eaudun.jyahoo.no_at_130.133.1.4...
> "Mike O" <ora7dba_at_yahoo.com> wrote in <976njd$cmm_at_nntpa.cb.lucent.com>:
>
> >Well, as a general rule I backup/secure my databases using not only a
> >tape system but also Oracle's EXP (export) utility. The export utility
> >is relatively common, in the shops that I have been in.
> >
> >The ability to import (IMP) a specific schema is handy and quicker to
> >use vs restoring a tablespace where the users objects are stored. Now
> >keep in mind, a seperate tablespace (tbs) would be better than placing
> >objects in a tbs with other objects not belonging to that user (for
> >obvious reasons). Recovery of that tbs would require taking it offline
> >which could affect more users than just the schema your concerned about.
> >
> >So the long and the short ... if this is an on the fly process .. I
> >would export before/after your process. It depends on your
> >requirements. Sounds like you maybe deleting or truncating or dropping
> >objects .. so if you need to go back to a process time (mins, hours,
> >days) .. just create your exports w/a time-date stamp.
> >
> >-Mike
>
> One major thing is that this is a 3rd party application, and I'm not sure
> how to deal with this (meaning I don't know much about these things in the
> first place and I have no idea of how our future customers DBA-routines
 are
> regarding this). This implies that my solution should be "flexible" so
> these sites could do things like they are used too.
>
> What I've thinking of is this:
> Use one data- and one index tablespace for all schemas (to avoid many
> tablespaces and also datafiles).

Tablespaces should accomodate segments of the same type, and the same growth and storage habits. Bunging every table into a single DATA tablespace is not a good idea if that means you end up with tiny 6K lookup tables sharing the same tablespace as giant 6Gb sales data tables. Or tables which are thrashed on a daily basis sharing the same tablespace as tables which don't get updated from one decade to the next. You'll be setting up for I/O conflicts, tablespace fragmentation and unnecessary quantities of backup.

> But then the database also should be exported for instance daily to allow
> for importing a customers project if needed.

Export is an extremely dodgy basis on which to rely for backup and recovery purposes. It is a useful adjunct to a normal, physical backup, but it shouldn't replace such backups. Having an export available gives you options when it comes to recovering from User errors (such as inadvertent truncates, commits or deletes). It gives you no real protection from media failure (loss of files or hard disks). The best you will be able to do in such circumstances is to re-create a bunch of tables as they were at the time of the last export -all transactions made after that time will be lost. Remember that you cannot apply redo to objects that are created as a result of importing from an export dump file.

Incidentally, it's also the case that anything but the simplest imports are prone to a multitude of errors. It frequently appears to depend on which way the wind is blowing, and you might get lucky. I wouldn't buy a system that relied on it, though.

> Is it possible to run an "online" export? If so, how is consistency dealt
> with? How long will (approx.) an export of 25 Gb data take?
>

Export can be run 'hot', and if you set the CONSISTENT flag to be 'Y', then whole database consistency is guaranteed. So is heart failure on the part of the DBA. Setting CONSISTENT=Y means that you'd better be prepared for your rollback segments to balloon in size, and for there to be a huge risk of the entire export failing with 'snapshot too old' error messages. It's also the case that a hot export will raise I/O that inevitably conflicts with the usual I/O patterns on the data files. Your users can expect to see performance degrade for the duration, and significantly so.

Regards
HJR
> TIA
>
> AJ
Received on Mon Feb 26 2001 - 09:27:43 CST