Solved

Copying data files on another machine

Posted on 2008-11-01
11
903 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
  • 4
  • 4
  • 3
11 Comments
 
LVL 2

Accepted Solution

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

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
 
LVL 47

Assisted Solution

by:schwertner
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!"
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 2

Expert Comment

by:vbsquickresponse
ID: 22862541
0
 
LVL 2

Expert Comment

by:vbsquickresponse
ID: 22862661
0
 
LVL 47

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 47

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now