Copying data files on another machine

Posted on 2008-11-01
Last Modified: 2012-05-05
I always wondered why simply copying Oracle data files to another machine is not sufficient to make Oracle work. Consider Microsoft SQL Server, in case you need to attach an existing database to an SQL Server instance running on another machine, simply copy two files viz. .mdf and .ldf to another machine. Whereas in Oracle I tried many times but it didn't work.

In case I don't have backup of the database and I want to copy this database to another machine running Oracle, how will I be able to do so?
Question by:rpkhare
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3

Accepted Solution

vbsquickresponse earned 350 total points
ID: 22857510
try this...

I want to copy an Oracle database from one server machine to another. It is going to be an exact replica of the existing database with the same tables, tablespaces and data. To do so, I shut down the current database and copied all the Oracle database files (control files, init.ora, data files, log files) to the target machine. I would like to know what I need to do now to start the Oracle database on the new machine? Was my procedure correct in copying the database files?  

Your procedure is correct for copying the database. I am assuming in my instructions below that you are going to rename the database from SRCDB to TGTDB. Here are the steps that you still need to follow:

1) Backup the source controlfile using the command:


A "CREATE CONTROLFILE" script will be written to your user dump directory; use ls -trl to find the file.

2) Copy the create controlfile script to the target machine (name it create.sql). I store all the scripts that I used to create the database in the $ORACLE_BASE/admin/$ORACLE_SID/create directory.

3) Modify the create.sql file. Change the create controlfile statement from:




4) Change the location of the redo logs.

5) Delete all the trace lines at the beginning of the file, up until the "create controlfile" statement. Under the "DATAFILE" statement, change the location of the datafiles. I prefer to delete all the lines, then use this command in vi to add the datafile locations back:

:r !ls /*/oradata/tgtdb/*.dbf

6) Delete the "recover database", "alter database open", and trace files from the create.sql script.

7) Start svrmgrl or sqlplus, connect internal, and execute the "create.sql" scripts, recover the database:

svrmgrl "command=connect internal"
recover database using backup controlfile ;

At this point you will be prompted for one redolog. Use the alert log from the SRCDB to determine this. For example, it will ask for arch000000000187, look in the alert log for the line that says:

Thread 1 advanced to log sequence 187
Current log# 3 seq# 187 mem# 0: /u02/oradata/TGTDB/redo03a.log

In this case you know to use redo03a.log that you copied over:

alter database open resetlogs;

This completes the process. I like to verify that I didn't miss any datafiles by executing the following command in sqlplus on the target:

    FILE_NAME  like '%MISS%' ;
LVL 48

Expert Comment

ID: 22857711
Because MS/SQL works only on Intel/Linux platform.

But this is also possible in Oracle, if the OS. Oracle versions are the same.
If you succeed to get the same directory structure after you install Oracle, you can copy the following files:

1. All .dbf files
2. All control files
3. The pasword file
5. online redo logs

Normally you will be able to start the instance.

Author Comment

ID: 22857872

That's simply a lot of work to just make the database run on another machine. While working on MySQL, I simply copied few files and there it started. Whereas, with Oracle I think there must, and always MUST, be a backup to restore the database on another machine.

Why the overall internal architecture of Oracle is so complicated? I think therefore it requires a dedicated DBA to handle this beast.
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

LVL 48

Assisted Solution

schwertner earned 150 total points
ID: 22862233
Backup and restore is nothing else but copying files.
The only problem is to know which files.

Why is Oracle so complicated?
Because it is very flexible.
1. It works on all OS platforms. To achieve this it is Java based. I mean the interfaces, not the engine. Java brings its complexity and its virtual machines with.
2. Oracle brings with a lot of tools and possibilities. They increase the possibility.
3. Oracle provides functionalities like Real Application Server, Data Guard, Oracle Replications (Streams) and many other things that simply do not exist or are limited in other DB.
4. You can not compare MS SQL with Oracle. MS SQL is small oficce product and has a share about 18% of the market, Oracle is a big enterprise product and hold about 45 % of the market. Possibly it will be worth to compare Oracle with DB 2 of IBM.
5. Finally there is a joke Please do not address it personaly to you. It is a joke I recall every  time I have to hit a nail in the wall ... very often I fail. Then I recall a sentence of one professor that claims: "Only simple persons think that there exist simple thinks!"

Author Comment

ID: 22862265
I have written an article on my blog. Please take a look at this:
LVL 48

Expert Comment

ID: 22865127
Yes, I read your article. It is a good investigation of the India job arena.
But you make a mistake.
You said: SQL Server is the most popular DB.... Yes, possibly in India.
In the world (and mainly in the States) Oracle is leading DB with a share
of about 45% of the market, DB 2 is the second player and MS/SQL is on third
Given that the majority of the Indian programmers will work for American companies
you have to pay attention on this figures.
Siebe CRM is not a rare product. It is the leader in the CRM software market.

Author Comment

ID: 22865843
Actually, the article is based on Indian IT market scenario. In the United States, IT adoption, IT education and IT awareness is better than India. United States is years ahead from other countries in terms of technology. Siebel may be the leader but it is rare in India. There is a handful of Indian students who have heard the name of DB2.
LVL 48

Expert Comment

ID: 22866961

Author Comment

ID: 22873957
Increased the points because the experts have given good solution above.

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question