?
Solved

Copying data files on another machine

Posted on 2008-11-01
11
Medium Priority
?
910 Views
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?
0
Comment
Question by:rpkhare
[X]
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
11 Comments
 
LVL 2

Accepted Solution

by:
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?  
 
Solution:

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:

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

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:

CREATE CONTROLFILE REUSE DATABASE "SRCDB" NORESETLOGS ARCHIVELOG

to:

CREATE CONTROLFILE SET DATABASE "TGTDB" RESETLOGS ARCHIVELOG

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"
@create
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:

select
    FILE_NAME, TABLESPACE_NAME, STATUS
from
    dba_data_files
where
    FILE_NAME  like '%MISS%' ;
0
 
LVL 48

Expert Comment

by:schwertner
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
4. The SPFILE
5. online redo logs

Normally you will be able to start the instance.
0
 
LVL 8

Author Comment

by:rpkhare
ID: 22857872
vbsquickresponse,

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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 48

Assisted Solution

by:schwertner
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!"
0
 
LVL 8

Author Comment

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

http://softwarefindings.blogspot.com/2008/11/less-adopted-technologies-giving-more_02.html
0
 
LVL 48

Expert Comment

by:schwertner
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
place.
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.
0
 
LVL 8

Author Comment

by:rpkhare
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.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 22866961
OK!
0
 
LVL 8

Author Comment

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

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious sideā€¦
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

764 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