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

From: Pete's <empete2000_at_yahoo.com>
Date: 26 Apr 2004 06:26:07 -0700
Message-ID: <6724a51f.0404260526.530dec29@posting.google.com>

D Rolfe <dwrolfeFRUITBAT_at_orindasoft.com> wrote in message news:<408BEA4F.4070801_at_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.

I think you're making a mountain out of a mole hill. It would be helpful to all if you posted the OS versions of both machines. Also one question for yourself, how much downtime are you allowed? I'm assuming this is a production DB move. Without knowing all the details and if it were me and I were allowed several hours of downtime, I would do the following:
1. Load & patch Oracle to the needed levels on Server 2. 2. Setup needed directory structures on Server 2 for the DB, also the init.ora.

3.  Shutdown db & listener on server 1.  
4.  Copy database from server 1 to server 2
5.  Start up db and listener on server 2.
6.  Change your tnsnames.ora file on all servers and clients to

reflect this change.
You're done.

If you were not allowed much downtime, then restore a cold backup of database onto server 2, i.e. I would do the following:

1. Same as above.
2. Same as above.
3. Startup db on server 2 but do not open.
4. Copy archives from the backup you used to the current ones created onto Server2.
5. Issue recover database to apply those logs.
6. Shutdown DB on server 1.
7. Copy all archive logs that are on server1 that ARE NOT on server 2.
8. Issue recover database again.
9. open your database, it should open normally, i.e. without a resetlogs.
10. Change all of your tns information on all servers and clients to reflect this change.

One note, try a test restore of the database to server 2 before migrating to it, i.e. test it first before actually moving it.

HTH,
Pete's Received on Mon Apr 26 2004 - 08:26:07 CDT