Solved

Oracle 10g Database Copy from one machine to another - Windows XP

Posted on 2007-04-06
7
3,966 Views
Last Modified: 2013-12-18
I have Oracle 10g installed on 2 different machines both with Windows XP.  I need to copy the database from one machine into another.  The database is about 10GB and has links to metadata.  I tried the method of copying datafiles, log files & init files with creation of new control files.  But this does not work smoothly and I was not successful several times I tried.  I am trying typical imp/exp as well as impdp/expdp for the same, but they are physical path dependent. i.e., the tablespace path & other file paths are stored on absolute basis and are not relative.  When I export from one machine wherein datafiles are in f: drive and try to import the whole database dump into another system with only c: drive (no f: drive), it starts giving errors.  Please let me know if there is any easy and proven method for the same.
0
Comment
Question by:gweidner
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 7

Assisted Solution

by:gattu007
gattu007 earned 200 total points
ID: 18867199
The best option is to use RMAN duplicate ...

References
Note 259694.1 - Oracle10G RMAN Database Duplication

Subject:       RMAN: Creating a Duplicate Database
        Doc ID:       Note:73912.1

RMAN 'Duplicate Database' Feature in Oracle9i / Oracle 10G
        Doc ID:       Note:228257.1

example
======


1: Create an Oracle Password File for the Auxiliary Instance
-------------------------------------------------------------
For full details on how to create a password file please refer to Oracle9i Database Administrator's Guide.

  For example:  orapwd file=DUP.pwd password=manager entries=5


2: Ensure Oracle Net Connectivity to the Auxiliary Instance
------------------------------------------------------------
Modify the listener and tnsnames file to to able to connect to aux db
Test the connections using sqlplus - note that you must connect to the auxiliary instance with SYSDBA privileges, so a password file must exist.


3: Create an Initialization Parameter File for the Auxiliary Instance
----------------------------------------------------------------------
Copy the init.ora file for target database and make the necessary changes for teh duplicate database e.g Setup the required paremeters e.g DB_NAME, CONTROL_FILES and directories for bdump, udump,cdump etc...

  Parameter file 'D:\ORACLE\ORA901\DATABASE\INITV901DUP.ORA'


4: Start the Auxiliary Instance
--------------------------------
Create a new NT service for the duplicate database V901DUP using oradim.

  C:\>oradim -new -sid duplicate -intpwd sys -maxusers 5 -startmode auto -pfile  'C:\oracle\ora92\database\INITduplicate.ora'

  C:\> set ORACLE_SID=V901DUP
  C:\> sqlplus "sys/manager as sysdba"
  SQL> startup nomount


5: Mount or Open the Target Database
-------------------------------------

  C:\> set ORACLE_SID=V901
  C:\> sqlplus "/ as sysdba"
  SQL> startup


6: Make Sure You Have the Necessary Backups and Archived Redo Logs
-------------------------------------------------------------------
To list what backups you have - log into RMAN and query the RMAN catalog.

  C:\> rman catalog rman/rman@RMAN901 target sys/change_on_install@V901
  RMAN> list backup;


7: Log into RMAN Allocate Auxiliary Channels if Automatic Channels Are Not Configured
--------------------------------------------------------------------------------------
rman catalog rman/rman@RMAN901 target sys/change_on_install@V901 auxiliary sys/manager@V901DUP

The output from connecting to RMAN with a catalog, target and auxiliary instance will show some output similar to:-
 
  Recovery Manager: Release 9.0.1.1.1 - Production

  (c) Copyright 2001 Oracle Corporation.  All rights reserved.

  connected to target database: V901 (DBID=1452169997)
  connected to recovery catalog database
  connected to auxiliary database: V901DUP (not mounted)


Task 8: Run the RMAN duplicate command
---------------------------------------
The following run command renames the datafiles to a new location and create three redo log files for the duplicate database.  Note
the example shown below is a full duplicate of the target database.  If you want to create a duplicate database a week prior to current time you can use the SET UNTIL TIME 'SYSDATE-7' syntax.

  run {
       #Allocate the channel for the duplicate work
       Allocate auxiliary channel ch1 type disk;
       #set the new file names and locations for all datafiles
       SET NEWNAME FOR DATAFILE 1 TO 'D:\ORACLE\ORADATA\V901DUP\SYSTEM01.DBF';
       SET NEWNAME FOR DATAFILE 2 TO 'D:\ORACLE\ORADATA\V901DUP\UNDOTBS01.DBF';
       SET NEWNAME FOR DATAFILE 3 TO 'D:\ORACLE\ORADATA\V901DUP\CWMLITE01.DBF';
       SET NEWNAME FOR DATAFILE 4 TO 'D:\ORACLE\ORADATA\V901DUP\DRSYS01.DBF';
       SET NEWNAME FOR DATAFILE 5 TO 'D:\ORACLE\ORADATA\V901DUP\EXAMPLE01.DBF';
       SET NEWNAME FOR DATAFILE 6 TO 'D:\ORACLE\ORADATA\V901DUP\INDX01.DBF';
       SET NEWNAME FOR DATAFILE 7 TO 'D:\ORACLE\ORADATA\V901DUP\TOOLS01.DBF';
       SET NEWNAME FOR DATAFILE 8 TO 'D:\ORACLE\ORADATA\V901DUP\USERS01.DBF';
       SET NEWNAME FOR DATAFILE 9 TO 'D:\ORACLE\ORADATA\V901DUP\OEM_REPOSITORY.DBF';
       #Duplicate the database to V901DUP
       DUPLICATE TARGET DATABASE TO V901DUP
       #Create three redo logs for the duplicated database
       LOGFILE
         GROUP 1 ('D:\ORACLE\ORADATA\V901DUP\REDO01.LOG',
                  'D:\ORACLE\ORADATA\V901DUP\REDO01-2.LOG') SIZE 200K,
         GROUP 2 ('D:\ORACLE\ORADATA\V901DUP\REDO02.LOG',
                  'D:\ORACLE\ORADATA\V901DUP\REDO02-2.LOG') SIZE 200K,
         GROUP 3 ('D:\ORACLE\ORADATA\V901DUP\REDO03.LOG',
                  'D:\ORACLE\ORADATA\V901DUP\REDO03-2.LOG') SIZE 200K;
      }
     
     
0
 
LVL 7

Assisted Solution

by:gattu007
gattu007 earned 200 total points
ID: 18867302
You can do without RMAN also

if it is windows follow metalink notes

Subject:       How to make a copy of a database on the same Windows NT machine
        Doc ID:       Note:73301.1

unix

Note 18070.1:  HOW TO MAKE A COPY OF A DATABASE ON THE SAME UNIX MACHINE
0
 
LVL 1

Assisted Solution

by:gritscbs
gritscbs earned 100 total points
ID: 18868563
Two possible solutions:

Work around regarding the c: and f: drive...  map your c: drive to an f: drive mapping and perform the import via the mapped drive.  I have user this method in other situations but not with Oracle imports but it might be worth a try.

If there are not to many oracle users then create each user on the Oracle database your importing into.  Export individual users and then import by user into the other Oracle database.  User imports are not drive specific.  You will of course have to create the tablespace prior to creating the users.
0
How does your email signature look on mobiles?

Do your employees use mobile devices to reply to emails? With mobile becoming increasingly important to the business world, it is in your best interest to make sure that your email signature looks great across all types of devices.

 
LVL 1

Author Comment

by:gweidner
ID: 18874582
Thanks guys,
But few of the solutions are machine specific and that won't solve my problem.  And using RMAN, I need to try if its not machine / drive specific.  

Please let me know if anyone knows how I can copy Database Creation Template from one machine to other so that I can create a template of my source database including data and structure and copy the template to other machine and create a new database with copied template.

hope i am thinking something practical and not just theoretical.

DBCA is something which needs to be explored for this option.  please help me on this topic if anyone knows about the same.
0
 
LVL 7

Accepted Solution

by:
Docteur_Z earned 200 total points
ID: 18947732
On Windows the DB templates you've created are stored in :
$ORACLE_HOME\assistants\dbca\templates

Then copy the templates from 1 machine to another and use it with DBCA.
0
 
LVL 1

Author Comment

by:gweidner
ID: 18957111
Thanks Techies.....

I tried several alternative methods with all of your help and finally was successful by copying the DBCA template from one system to another.

That works better without any complication of path dependancy.

Thanks again to all.
0
 
LVL 7

Expert Comment

by:Docteur_Z
ID: 18957402
A *C* ?
Thanks for the points, but you may want to take a look at the FAQ concerning the grading

http://www.experts-exchange.com/help.jsp#hi73
0

Featured Post

Too many email signature updates to deal with?

Do you feel like you are taking up all of your time constantly visiting users’ desks to make changes to email signatures? Wish you could manage all signatures from one central location, easily design them and deploy them quickly to users? Well, there is an easy way!

Join & Write a Comment

Join Greg Farro and Ethan Banks from Packet Pushers (http://packetpushers.net/podcast/podcasts/pq-show-93-smart-network-monitoring-paessler-sponsored/) and Greg Ross from Paessler (https://www.paessler.com/prtg) for a discussion about smart network …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

758 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

19 Experts available now in Live!

Get 1:1 Help Now