Solved

Copying data files on another machine

Posted on 2008-11-01
11
905 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

867 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

18 Experts available now in Live!

Get 1:1 Help Now