Re: backup without archivelog mode

You've never had an import go wrong on you? A lot of things can make it seem a temperamental beast. Inadvertently doing the export with compress=y is always a fun one... try importing when the dump file is requesting a 2Gb initial extent in a tablespace that's been moderately subject to earlier fragmentation. Ever had a quota of zero on one of the tablespaces that import needs to create segments in? And so on.

In reality, of course, it's brutally logical in what it does... but there are plenty of traps for the unwary.

Regards
HJR

--
----------------------------------------------
Resources for Oracle: http://www.hjrdba.com
===============================


"daniel" <test_at_test.com> wrote in message
news:a7lts8$v9o$2_at_news6.svr.pol.co.uk...


> >>and import can sometimes seem to have a mind of its own

>

> really? in what way?

>

>

> --

> Regards,

>

> Daniel.

>

>

> "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message

> news:a7laq4$4na$1_at_lust.ihug.co.nz...

> > "obakesan" <cjundieseastwd_at_powerup.com.au> wrote in message

> > news:7emn8.11476$T4.89487_at_nnrp.gol.com...

> > > HiYa

> > >

> > > In article <a7jncp$jro$1_at_lust.ihug.co.nz>, some kind human wrote:

> > > >Export doesn't count as a backup.  It's a *logical* backup, true

> enough.

> > > >But that's not the same thing as a physical backup involving the

> copying

> > of

> > >

> > > I'm a little vague here, on inconsistent. If there is no activity on

the


> > > database, for hours at a time (after the loads, before the users start

> > reporting

> > > work) what will be missing from the database?

> > >

> > > I thought that inconsistent meant that there could be activity taking

> > place

> > > during the export, and so that would be missed. Is there some other

> > meaning?

> > >

> >

> > I think I've seen another post from you where you sort of say 'ah!

that's


> > why', so apologies if I wasn't making myself clear, but now I hope you

see


> > the actual problem, which is that even if you are not performing

> > transactions, Oracle is doing things to the datafiles all the time in

the


> > background (principally, checkpointing them, and writing to the header

> block

> > of the datafiles.  It's that activity which means you can't rely on a

> > physical copy of the data file being internally consistent.  You might

get


> > away with it.  But you might not.  And if a backup strategy is going to

be


> > any good, it needs to yield reliable results.

> >

> > > I understand that its a logical backup, but in reality for this type

of


> > > datawarehouse usage, which would be faster to restore?

> > >

> > > would there be a difference?

> > >

> >

> > Definitely.  But they're complementary strategies, not mutually

exclusive.


> > One of the problems with relying on exports is that in a data warehouse,

> > it's going to be quite something to perform the export in the first

place:


> > the dump file is going to get seriously large.  You'll maybe have to be

> into

> > things like named pipes or be selective about what you export in order

to


> > not hit filesystem size limits.

> >

> > Assuming you resolve that issue, though, exports are a reasonable way to

> > resolve user stuff-ups on a particular table (an accidental truncate,

for


> > example).  The physical backups are rather more useful ways of resolving

> > hardware/media problems (corrupt blocks, deleted files, that sort of

> thing).

> > So they're really addressing different situations, both have their

> > strengths, both have their weaknesses (recovering with import means the

> > table is out of date, for example, and you'll need to repeat loads you

> > performed that affected it; on the other hand, applying redo to a

restored


> > datafile means having to recover, say, 100 tables rather than 1 -but all

> 100

> > will be bang up-to-date at the end of the process, so no loads need to

be


> > repeated).

> >

> > > those are the gut points I am trying to get at here ... I asked

because


> I

> > dont

> > > have the experience to know myself (you may recall me just learning

> about

> > > hotbackup last month)

> > >

> >

> > Fair enough.  It's just that it's hard to be definitive on these sorts

of


> > comparisons, because it depends so much on the volume of your data, and

so


> > on.

> >

> > Re-reading your original post (which seems an awfully long time ago!),

the


> > issue I saw was that you were thinking of simply copying datafiles

> nightly,

> > without the 'begin backup' and 'end backup' commands, and not taking

> > archives.  Hopefully it's now clear that you can't do it like that if

the


> > database is up and running.

> >

> > Now there's a question of whether export/import would be a suitable

> > alternative.  My gut feel on that is no, not for a particular range of

> > failure scenarios.  Export can be ponderous, the dump files huge, and

> import

> > can sometimes seem to have a mind of its own.  Dump files have a habit

of


> > being treated like text files by certain DBAs of my acquaintence

(risking


> > loss of the dump file).  And, as snapshots, even when import does its

> stuff

> > without incident, you're left with an out-of-date table that needs

> > transactions/loads repeated to bring it up-to-date. Exports/Imports beat

> > having to do an "incomplete recovery" (with redo) any day, but that's a

> > fairly specialised use.

> >

> > As I say, they're complementary strategies, not mutually exclusive.

> >

> > Regards

> > HJR

> >

> > > thanks again for all your time folks

> > >

> > >

> > >

> > >

> > > See Ya

> > >

> > > --

> > > (when bandwidth gets better ;-)

> > >

> > > Chris Eastwood

> > > Photographer, Programmer, Motorcyclist and dingbat

> > >

> > > please remove   u n d i e s   for reply

> >

> >

>

>