Re: backup strategy

"Howard J. Rogers" <howardjr_at_www.com> wrote in <3a9a75f1_at_news.iprimus.com.au>:

>
>"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
>

So, as DBAs, what would you prefer?
Given that each customers project's is created as a separate schema (there can be several thousands of them and that performance is not critical), would you like

- one tablespace for all projects
- one tablespace for each customer (several projects on one tablespace)
- one tablespace for each project

AJ Received on Tue Feb 27 2001 - 05:38:09 CST