Re: Question regarding Oracle backup

From: Zoran <zoranm_at_echo-on.net>
Date: 1997/02/28
Message-ID: <33179DAF.42BC@echo-on.net>#1/1

------------2C1D686C7481
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset=us-ascii

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.

------------2C1D686C7481
Content-Transfer-Encoding: 7bit
Content-Type: text/html; charset=us-ascii

<DT>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).</DT>

<DT>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.</DT>

<DT>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.</DT>

<DT>Hope this helps, Zoran. </DT>

</BODY>
</HTML>
------------2C1D686C7481-- Received on Fri Feb 28 1997 - 00:00:00 CST