Re: Moving an Instance

Carlos A. Gonzalez wrote:

> Dear Oracle Gurus;
>
> I need some help
>
> I am in the process of moving an Oracle instance from one host to
> another
>
> I have identified the following ways to do such operation:
>
> A) Export the whole database in the old host, create the database and
> corresponding
> tablespaces in the new host and then do the import.
>
> Advantages: Optimize space usage (Compress parameter)
> Disavantages: Too slow
>
> B) FTP the data files to the new host, copy the data files to the
> corresponding
> directories, edit the Control File and startup the instance.
>
> Advantages: Faster to do
> Disavantages: No way to optimize the old database.
>
> Our resident ORACLE Guru insists in doing it the following way, which
> I am
> kind of resisting
> beacuse besides the fact you have to identify every user/table,
> triggers
> and stored procedures
> will require special handling plus the fact that you may overlook some
>
> special tables, views,
> what not....
>
> Please comments on approach C) will be appreciated.
>
> C) Create a Database in the new host, establish a link to the old
> instance.
> and copy all the tables using something like:
>
> CREATE DATABASE LINK dblink
> CONNECT TO user IDENTIFIED BY password USING 'connect_string';
>
> CREATE TABLE new_table
> TABLESPACE tablespace
> AS SELECT * FROM old_table_at_dblink
>
> Advantages: Please comment
> Disavantages: Please comment

 Well, apart from flooding your network and making the job a whole lot more complex than it needs to be, I would say method C is great! :-)

Seriously, if I'm working on machines of a similar architecture and same release of operating system I go for the 'copy the files off my cold backup tape' approach. That works just fine, apart from having to edit the file locations of files mentioned in you init and config files, and obviously renaming the location of your datafiles.

Method B is the approach I'd use if there were differences between the platforms (OS, Oracle release, etc.) and/or I didn't have a recent full cold backup. One point worth mentioning here: DO NOT automatically choose to 'compress extents' because you think that will "Advantages: Optimize space usage (Compress parameter)" as you put it. If the database has been designed correctly 'compress extents' can give you a lot of problems, both from the point of view of space management and perfromance. Get you hands on a copy of 'Oracle 7 Server Space Management', a white papar from Oracle and read it fully. Also don't forget to take and deliberate stripping of the database into account, as again you might screw this up with compress extents.

It's also worth mentioning that you don't necessarily need to recreate your datafiles, a full import can do that for you. Still, you might want to recreate them, but do look closely at the 'import' documentation before you start (and the white paper on import on Oracle's Web site).

Steve Phelan. Received on Mon May 12 1997 - 00:00:00 CDT