Re: Question regarding Oracle backup

Tony Cogen wrote:

  Zoran wrote:
>
> Tony Fernandes wrote:
> >
> > Hi,
> >
> > Hv two databases production and test each having its own
 directory
> > structure for database files. The production database backup
> > is done daily by using the tar command and saving all the
 database
 data,
> > index, system and redo log files.
> > In order to restore a production backup into the test database,
 I
> > restore all the production files into the test databases
 directory
> > structure and rename all the files appropriately. I then
 recreate
 the
> > control file for the test database. All this works fine if the
> > production database is down when I recreate the test databases'
 control
> > file. If however the production database is up at the time I get
 an
> > error saying that a datafile is in use.
> >
> > Does anybody know why this happens and if so whats a good work
 around ?
> > Does anybody know of a quick way to take a backup of one
 database
 and
> > restore it into another database
> >
> > I have to often restore a production backup into a test database
 to
> > fix a bug and I wouldn't want to bring down the production
 database
 at
> > that time.
> >
> Have a look at export/import utilities. You could export all
 objects
> from a particular schema from your production db, then import from
 the
> export file into your test db under a different schema name (if
> desired).
>
> This way you can keep your prod db up all the time (and your test
 db).
> The downfall, here is that if it takes 10 minutes to do the
 export,
> online users may be updating tables during your export causing
 tables
> in your export to be "out of sync". Depending on your situation,
 this
> may or may not be a problem/concern.
>
> Another piece of advice for the import.... use commit=y. That way
> Oracle will commit after every insert instead on only once at the
 end
> of the entire import - you may run out of rollback segment space
 if
> you don't.
>
> Hope this helps, Zoran.

  Export relies on Oracle's read consistency. Meaning the data   exported to
  a file is as the table looked at the start of the export. The   transactions that happen during the export are not recorded in the   export file. Have you ever seen the message "table snapshot too   old"?

  The other thing to note is that you can perform incremental exports   from
  the PROD database and apply this to the TEST database.

  Tony Cogen

 Did not know that. But having heard it now, it makes sense. However, does Oracle provide read consistency across *ALL* tables at the point the export was started?

As an example, say you have ORDER_HEADER and ORDER_DETAIL tables. If it takes 10min to export ORDER_HEADER and 30min to export ORDER_DETAIL and if another user inserts an order (1 header and many details and committed) during the first 10 minutes of the export (while you're exporting ORDER_HEADER). Your ORDER_HEADER export would NOT contain the newly added order, but what about the subsequent ORDER_DETAIL export; would it contain the newly added details?

I think read consistency would NOT be given across all tables as of the start of the export. BUT if I'm wrong, then all the better. Does anyone know any better?

Zoran. Received on Mon Mar 10 1997 - 00:00:00 CST