Re: How to move Oracle database to a new machine?

From: D Rolfe <dwrolfeFRUITBAT_at_orindasoft.com>
Date: Sun, 25 Apr 2004 17:41:51 +0100
Message-ID: <408BEA4F.4070801@orindasoft.com>

Joan,
>
> Thanks David. Bear with me if my question sounds too fundamental.
>
> To be able to create the matching tablespaces, I would assume I need
> to know how tablespaces are set up on the old system, right? Can this
> information be abtained from Data Dictionary Views completely? Do I
> need to reinstall Oracle on the new machine first? (The both machines
> run Linux, I am not sure what version) Use Export in User mode or full
> database mode?
>
> Thank you very much!

You don't need to construct perfectly matching sets of tablespaces. You must get the names right and the sizes must ok, but if your DB is as small as you say that should not be a problem. You don't need to create tablespaces that have the exact same physical filenames - what matters from the perspective of 'import' is the tablespace name, not the names of its component files.

The two data dictionary views you should look at are:

DBA_TABLESPACES
DBA_DATA_FILES You might find this SQL Statement useful:

select t.tablespace_name, f.file_name, f.bytes/(1024*1024) size_mb from dba_tablespaces t

, dba_data_files f
where t.tablespace_name = f.tablespace_name /

You absolutely, positively, must install oracle on the new machine first. The fact that you even asked this question suggests that you should do a few dry runs before the real thing :). When you install Oracle get it to buid a starter database as well. Then you can add your stuff to it rather than having to figure out the magic DB creation incantation from scratch.

You need to read on the following:

Creating Databases and Tablespaces - start with the administrator's guide.
Oracle's defintion of a user/schema - How many oracle accounts do you need to move? 1? More than 1? Find out the usernames and passwords people use to access this data.
export and import - Start with the utilities handbook. Provided the database consists of one oracle user (see above) you should be able to get away with exporting a single user.

Don't expect this to work the first time. Write unix scripts for things - you may have to repeat different steps and life will rapidly get tedious if you don't. I would suggest you break this up as follows:

Script that runs export for the database owner on the old machine. If you have more than one oracle user you will need to export both of them and any grants that were set up.
Script that drops ("DROP USER foo CASCADE") and re-creates the user on the new machine. Make sure you never run it on the old machine.
Script that creates new tablespaces. You'll find that the "REUSE" option of "CREATE TABLESPACE" is helpful.
Script that imports the dmp file into the existing but empty user you created.

Orinda Software make OrindaBuild, which writes Java JDBC Calls To Run PL/SQL. Received on Sun Apr 25 2004 - 11:41:51 CDT