How
to re-create Oracle environment with data?
What is the best method for recreating a
production Oracle environment to a test Oracle environment? Assuming we
cannot do a backup/restore of a production machine, what would be the best
method? Are Export and Import the only options, or can we do an image-copy?
Is there a utility in Oracle to do an image-copy? If we were to do
an Export and then an Import, we have to pre-define the DB structure on
the test machine and then perform the import?
Ans1:
I have never done it, but in theory it should
be possible to copy the
images and recreate the control file.
Issue an alter database backup control file
to trace before you start.
This trace file will end up in the background_dump_dest
directory of the
production database. Make any necessary
changes, copy the files, startup
nomount of the new instance, create controlfile
from the script you just
generated (not overwriting the old one of
course) and that should be it.
Assuming of course all files are of the
same size.
Hth,
Sybrand Bakker, Oracle DBA
Ans2:
I'm not sure why you can't take an OS backup
from the production machine.
I've done this a few times & I find
that copying the files is a lot quicker
than import/export (25 Gb database). In
fact, I'm doing it again next week.
You need a cold backup ideally, though I
guess you could take hot backups
and a bunch of archive redo log files and
recover it in its new location.
We take a full cold backup once a week by
offlining one set of mirrors and
dumping that to tape.
Assuming you can copy the files, the easiest
thing is to recreate an
identical directory structure on your dev
machine, copy all the files into
the right places (including control files
etc), edit any init.ora
parameters (you may not have enough memory
for a 1Gb SGA on your dev
machine;) and off you go. You'll need to
add a line to oratab and fiddle
around with your listener.ora & tnsnames.ora
files to set up any required
network services, aliases etc.
If you want to put the files in different
places, or just online some of
the DB (say if you are trying to recover
a table from an OS backup) then
you'll need to mount but not open the DB
and issue the appropriate 'alter
database rename/drop...' commands. There
is a White Paper on the Oracle
website about this.
-If we were to do an Export and then an Import,
we have to pre-define the
DB structure on the test- -machine and then
perform the import?
Can do. If you have an identical directory
structure, all you need to do is
create the database & the data dictionary.
The import will then create all
the tablespaces etc for you.
Cheers
Martin.
Ans3:
I have a script named dbcopy which does
it. Needs to be adapted, because
it assumes a number of client-specific things
about the way Unix
directories are organized but it can be
a good base for you to work on.
Can you e-mail me a reminder at [email protected]
?
Ans4:
A simple solution if you can't backup/restore....
- Shutdown Oracle
- FTP files over to your production server
(you might wan't to compress)
- Restart your development database
- Start your production database.
This assumes both server have exactly the
same directory structures.
Regards,
Marc Mazerolle