Re: backup without archivelog mode

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 datafiles and controlfiles. The trouble with exports is that they are, effectively, snapshots of the database. They are good for recovering a specific table (or, if you are desperate, the entire database) to the time at which the export was taken. They don't permit you to recover all transactions affecting that table, however. What's more, you can't simply apply redo to a table that's been recovered from an export. It's a new object, after all, and the rows have almost certainly been re-inserted into the database at a quite different physical location from where they were originally -so the rowids contained within the redo stream can't make sense of the freshly-imported table, and hence the redo cannot be applied to the recovered table.

That changes a bit in 9i, where the idea of 'supplemental log groups' means that you can include, for example, a primary key in the redo stream. So in 9i, it *would* be theoretically possible to apply redo to a table recovered via export. That perhaps makes export a more meaningful form of backup.

Until then, it's a convenient way of recovering from user errors (drops, truncates, daft updates to the data) without the expense involved in incomplete recoveries. So it's a useful part of a backup strategy. It gives you options you would otherwise lack. But it doesn't really count as a backup in the sense of 'something that prevents data loss'.

The term "hot backup" specifically refers to taking physical backups (ie, copies of the relevant database files). It's therefore not appropriate to mention it in the context of export (which *has* to have the database fully open, otherwise it can't access the data in the data files, of course).

Regards
HJR

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


"Sean Comar" <skbansal_at_telocity.com> wrote in message
news:3c9d5d84$1_1_at_nopics.sjc...


> HJR,

>

> How about a complete full database export? I think, we can do a full

export


> while the database is running (in hot mode) and then, it can be imported

in


> case something bad does happen?

>

>

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

> news:a7hb54$9fg$1_at_lust.ihug.co.nz...

> > Archivelog mode simply means that you can guarantee recoverability of

all


> > transactions.  If you can guarantee recoverability by some other

> mechanism,

> > then no, you don't need archivelog mode.

> >

> > By that, I mean that these nightly loads must be coming from some

source.


> > If you can back that source up, and are prepared to repeat a nightly

load


> if

> > something happens to the database, who cares about archives?!  A lot of

> > sites, for example, will use SQL Loader to load millions of records from

a


> > text file.  Since the text file can be burnt to CD, or put on tape, the

> load

> > could be repeated if ever there was trouble on the database.

> >

> > But:  You can never, ever make a copy of any database file whilst the

> > database is running, without that copy being internally inconsistent.

And


> > only archive logs guarantee that you can make an internally inconsistent

> > datafile consistent.  So: your choice.  If you can repeat the loads, and

> > don't mind doing so, don't bother with archives.  But then all backups

> will

> > require that you shut your database down before making a backup.  And

> since

> > you mention a weekend shutdown window, it's sounding as though you will

> need

> > to sign up to perhaps repeating up to 6 or 7 nights'-worth of loads in

the


> > event of trouble (ie, you'd restore from the cold backup you took last

> > weekend, and therefore have to repeat that entire weeks' loads).

> >

> > If you want to take hot backups (which your use of the 'alter tablespace

> > Florence begin backup' command suggests you want to do) then you will

> > absolutely, positively have to be in archivelog mode.  There is no

> exception

> > to the rule that hot files cannot simply be copied.  It matters not the

> > slightest that no-one is doing anything but selects.  *Oracle* will be

> doing

> > things that cause the all-important SCNs to increment at unexpected

> > intervals -at which point, you wouldn't be able to use the hot-copied

> files.

> >

> > If your database is up when you take backups, regardless of what it's

> doing,

> > it's a hot backup.  You must behave as though it were a hot backup.  And

> hot

> > backups require archivelog mode.

> >

> > If that's a storage pain, do cold backups once a week, and be prepared

to


> > repeat data loads.

> >

> > The choice is entirely yours.

> >

> > Regards

> > HJR

> > --

> > ----------------------------------------------

> > Resources for Oracle: http://www.hjrdba.com

> > ===============================

> >

> >

> > "obakesan" <pellicle_at_hotmail.com> wrote in message

> > news:9baa2b9f.0203222306.388fb025_at_posting.google.com...

> > > HiYa

> > >

> > > if I have a database that is more like a datawarehouse in that it gets

> > > load files loaded into it nightly, but no other transactions take

> > > place

> > > that involve the update or insert of data during working hours, can I

> > > back this up with simply copying the datafiles, control files ...?

> > >

> > > I thought that there is no point in making archive redo logs as really

> > > all that will happen between sqlloads is some complex selects using

> > > packaged procedures, nothing that is transation oriented.

> > >

> > > If I copy the database files is there any meaning in issuing an

> > > alter tablespace boing_said_zebedee begin backup; command? will this

> > > be needed to gurantee consistency (in case a load is timed at the same

> > > point

> > > as the backup)? Is there any point in archiving redologs in this

> > > situation.

> > >

> > > Also I was thinking of a weekend down and full cold backup. As there

> > > will be no data loaded in this period, and no users on it either.

> > >

> > >

> > > thanks for your time

> >

> >

>

>