Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Copying data files on another machine

Posted on 2008-11-01
Medium Priority
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
  • 4
  • 4
  • 3

Accepted Solution

vbsquickresponse earned 1400 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.
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 48

Assisted Solution

schwertner earned 600 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses
Course of the Month12 days, 3 hours left to enroll

564 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