RE: Move Database from one machine to another

On 23 Jun 2000, at 14:28, Mullings, Daniel wrote:

Date sent:      	Fri, 23 Jun 2000 14:28:44 -0800
To:             	Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Send reply to:  	ORACLE-L_at_fatcity.com
From:           	"Mullings, Daniel" <daniel.mullings_at_hq.gte.com>
Subject:        	RE: Move Database from one machine to another

>
> -----Original Message-----
> Sent: Friday, June 23, 2000 1:40 PM
> To: Mullings; Daniel; Multiple recipients of list ORACLE-L
...

>
> 2) IF possible make the mount points identical (not necessary, but sure
> helps)
>
> Regrettably, I'm not even close to being a DBA, just an application
> developer that's got a
> couple of servers with Oracle on them. One server is a P166 324mb ram
> and the other is a
> Compaq Proliant PII 266 512mb ram. Could you clarify mount points for
> me?

I think "mount point" is a SysAdmn term that roughly translates as "physical hard drive" in the PC world. :)

In the "old days" when large databases were on "big hardware", the physical database design required that various parts of the database by physically located on various hard drives for both performance and backup/recovery reasons (also in some cases, cost?).

(note: watch out, because in some environments eg, larger systems, "logical" volumes can be partitioned/mapped/etc to multiple physical hard drives and so forth. This usually only happens when a SysAdmn setting up a server is "DBA impaired". )

On a PC LAN server such as NT, you may not find that it matters much in the performance area, but watch out if you have RAID disks,etc and you are going to nuance your backup/recovery scheme, especially if the database app(s) are mission critical and so forth.

In the case of a non-mission critical app that can be *reliabably* cold backed up frequently (eg, once a day), the worst case disaster scenario is that a day's worth of insert/update/delete is lost.

(cold backup means: the database is "shutdown", and then the closed database files are backed up to tape. Hot backup is much more complicated, and is used on 24x7, or similar, systems where the database can not be "shutdown" regularly.)

If the users don't want to spend money having a SysAdmn and/or DBA make sure this is all going to be "done right", and they are willing to live with the possibility of loosing a day's worth of work, plus whatever time it will take to rebuild a dead/crashed database server (worse case scenario: you have to buy a new machine), have at it!

Also, go ahead and read/skim through the Oracle DBA guide(s)- in electronic form, it should be on the install media (CD containing the server software). Last time I looked, the explanations for all this were fairly good even though it can seem at first like there is no method to the madness of Oracle's complexity.

I think you can even find most of the docs in html/.pdf at

  http://technet.oracle.com/

That site requires registration, but it is free.

>
> 3) Copy (in BINARY ) all of the database and log files from the old to the
> new
> machine. If you want, you can copy the control and init files as well. If
> you
> don't you'll need to create an init file and use the output from an "alter
> database backup controlfile to trace;" command to recreate the control
> files.
> You will need to edit this file to make the commands real and to rename
> datafiles if needed.
>
> Will copying the files via explorer be sufficient? Or will I need to
> use xcopy/copy with a
> binary switch?

We do Netware, not NT, but as far as I know, you can use the Windows Explorer utility to transfer things around just fine. I use it on Win9x clients to create&move secondary, compressed backups of the database files and other similar stuff all the time, and have never seen a problem with "binary" issues.

>Also, do you have a list of files that I need to move
> or will those files with
> the latest modified date be the ones that I need?

Dude! sounds like you are going to have to go into the data dictionary and look for the names of the database files. Just guessing at the file system level may not be enough, although if you have fairly simple, standardized setups, you might be able to get away with it.

Oracle is *very* finicky about the database files being exactly the same when you started the database as when it was shutdown. Look for discussion of the "control file" in the Oracle DBA documentation.

>
> 4) startup nomount the database if you are using the control file rebuild
> method
> and run the sql generated from above. This will recreate the control
> file(s),
> mount the database, reset the logs, and open the database. In essence your
> done.
>
> Hopefully, I can use option 5.

Well, maybe somebody can explain why you might need to do this, then you can decide! :)

(actually, I just thought of this: there are probably some very helpful NT documents about the whole general issue at Oracle's Tech Support site. call them, or get on their web site.)

>
> 5) Assuming you don't want to recreate the control files, startup mount the
> database. Rename any files with the "alter database rename file <original>
> to
> <whatever_it_is_now>;" that need it. The "alter database open;". If all
> went
> well the database will open normally and your done.
>
> Do you happen to have the exact command to mount the database?
> Is it startup mount <filename.ora> ?

If I remember correctly, there aren't any filename parameters once you get to the point of "startup".

The datafiles are associated with a database "instance" at the point you do the "alter database rename file"... stuff.

Just in case you haven't thought about it, have you called your Received on Fri Jun 23 2000 - 22:59:26 CDT