Re: Cloning a Database

If the test and live databases are on different servers, but the same platform, you can do it cleanly from a cold backup (assuming the tape drives are compatible too).

It's worth taking a listing of the location of the datafiles on test and live before starting, and checking that you will be able to do a straight read from the tape without running into disk space problems - and working out where that too-big datafile is going to be moved to before reading from tape. .

Shut down the test instance,
zap the datafiles (make sure you zap the right ones if there's more than instance on the server) ,
copy the datafiles from the tape to the test server - keeping an eye on space if the layout of the datafiles is different, move datafiles as necessary
if the layout of the test server is the same as production and you haven't had to move any datafiles just issue the startup command if it's different and you've had to move datafiles around, startup mount and issue 'alter database rename file 'pathA/name' to 'pathB/name' then issue 'alter database open'.

Please note this works well on Unix (done every month or so here to keep our test system more-or-less in line with production incase something nasty happens that we need to look at) but there may be a couple of other things to do admin-wise if you are on NT

Works
"James Williams" <JKWMSGA_at_YAHOO.COM> wrote in message news:aib78f$jdr$1_at_slb3.atl.mindspring.net...
> Daniel,
>
> Can you tell me more about transportable tablespaces or point me to where
I
> can read about them?
>
> Export/Import is what I was thinking about using. Here's what I've come
up
> with, but I'm pretty sure I'm missing some steps.
>
> * connect to the live database
> * export to a file (I assume there's no way to harm the live database via
> the export command?)
> * disconnect
>
> * connect to the test database
> * get a list of every owner (I'm not sure how to do this from a script.
If
> necessary, I can get this data from DBA Admin. I can get the owner of
> objects from ALL OBJECTS. Where can I get the owner of every table?)
> * delete everything in the schema not owned by SYS or SYSTEM
> * commit
> * import the file
> * commit
> * recompile the stored procedures
> * disconnect
>
> * manually delete the import file
>
> Thanks
> James Williams
>
> "Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message
> news:3D484B7E.3FA3FA88_at_exesolutions.com...
> > James Williams wrote:
> >
> > > I have two databases in Oracle 8i. One is my live database; the other
> is
> > > used for testing. About once a week, I need to copy my live database
> over
> > > to the test database. Any pointers to tools or SQL commands to do
this
> > > would be appreciated.
> > >
> > > Thanks
> > > James Williams
> > > JKWMSGA_at_YAHOO.COM
> >
> > Transportable tablespaces
> > or
> > Export/Import
> >
> > quickly come to mind.
> >
> > Daniel Morgan
> >
>
>
Received on Fri Aug 02 2002 - 05:22:24 CDT