OraFAQ Forum: Server Utilities » Copy database to another computer

Home » RDBMS Server » Server Utilities » Copy database to another computer (WIndows 10 12C)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Copy database to another computer [message #654330]

Sat, 30 July 2016 16:26

derrellgore
Messages: 20
Registered: July 2016

Junior Member

I am new to Oracle. I installed and created a new database...no problem.

I want to copy this database to a second computer. Can anyone tell me step by step how I can do this. Basically I want to copy the database take to anther computer which has Oracle installed and put the copy on it. So I end up with a main machine and a test machine.

And then when I have things working on TEST machine I want to be able to copy back to MAIN machine.

In Microsoft SQL I accomplish this with BACKUP/RESTORE....really easy. I haven't found away to do this in Oracle.

Thanks,
Derrell
derrellgore@windstream.net

Report message to a moderator

Re: Copy database to another computer [message #654333 is a reply to message #654330]

Sat, 30 July 2016 20:59

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

the whole Oracle Documentation set can be found at URL below

https://docs.oracle.com/database/121/nav/portal_booklist.htm

If you come from SQL Server, you'll need to be retrained; due to differences between Microsoft & Oracle
PLEASE take the time to Read The Fine Manual below

https://docs.oracle.com/database/121/CNCPT/toc.htm

For your specific problem, you need to use RMAN

https://docs.oracle.com/database/121/RCMRF/toc.htm
https://docs.oracle.com/database/121/BRADV/toc.htm

You could start with

RMAN> DUPLICATE DATABASE;

Report message to a moderator

Re: Copy database to another computer [message #654339 is a reply to message #654330]

Sun, 31 July 2016 07:00

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

For starters you need to fully understand that the term 'database' means something completely different in Oracle vs. mssql. Do not proceed until you sort that out.

Report message to a moderator

Re: Copy database to another computer [message #654343 is a reply to message #654330]

Sun, 31 July 2016 13:15

derrellgore
Messages: 20
Registered: July 2016

Junior Member

I have read about RMAN & Duplicate. I don't think this applies to what I am trying to do.

I want to make a backup and save it to a USB drive....take it home and restore on my computer. There is no connectivity between the two computers.

If I am understanding RMAN correctly it duplicates to another server it can talk to.

Also Restoring a database only seems to be back to original computer.

Thanks,
Derrell Gore

Report message to a moderator

Re: Copy database to another computer [message #654344 is a reply to message #654343]

Sun, 31 July 2016 13:21

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

1) Assume that DB Server OS & home computer OS are the same name, same edition, same version & same patch level.
2) then you can simply SHUTDOWN IMMEDIATE
3) copy all the necessary file onto USB
4) sneakernet USB home
5) copy all the files to the same exact location where they resided on DB Server
6) ensure your environment is the same at home as was on DB Server
7) STARTUP the database

Report message to a moderator

Re: Copy database to another computer [message #654345 is a reply to message #654343]

Sun, 31 July 2016 13:54

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

I want to make a backup and save it to a USB drive....take it home and restore on my computer. There is no connectivity between the two computers.

You can take a backup with RMAN and restore it on to the same or another server.
I'd say this is the original usage of RMAN.

Report message to a moderator

Re: Copy database to another computer [message #654372 is a reply to message #654330]

Mon, 01 August 2016 07:12

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

Given that you are starting with a MS SQL mindset, I'd be willing to wager that you do not really want to copy an Oracle database, but rather a schema from that database.

As I posted earlier, the very term 'database' has a far different meaning in the two products.
What MS SQL calls a "database" is more like what Oracle calls a "schema". In Oracle, a schema is the collection of all objects (tables, etc.) that belong to a given user.
Take a look at export and import (the respective utilities are expdp and impdp), documented in the Utilities Manual. For Oracle 11g, that is here.

Report message to a moderator

Re: Copy database to another computer [message #654385 is a reply to message #654372]

Mon, 01 August 2016 14:09

derrellgore
Messages: 20
Registered: July 2016

Junior Member

Ok...so I am looking at Data Pump Import/Export. This is probably what I am looking for...but my problem is I don't know enough about this to understand it. I am a programmer. I understand making tables, stored procedures, etc. Don't really understand the maintenance side of Oracle. There is obviously a lot more to this than MS SQL. I am new to this I know more than anyone else here so I have no one to ask.

So if I have a database(schema) what exactly would I do(syntax) to export from one location and then import in another location. There are so many parameters and switches I don't know where to begin.

I am trying to learn. My database is not in production. In fact, I just have it installed on my pc. I just want to be able to take it home so I can fiddle with it there to. Other than for that reason I wouldn't really care if I could do this. Eventually I will be working with a vendor in the cloud and will not need to know this. My main goal is to teach my self to program in Oracle.

Thanks,
Derrell

Report message to a moderator

Re: Copy database to another computer [message #654386 is a reply to message #654385]

Mon, 01 August 2016 14:15

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Export:

Code: [Select all] [Show/ hide]

E:\Temp\Listing>expdp help=y

Export: Release 11.2.0.4.0 - Production on Jeu. Juin 23 13:51:08 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


The Data Pump export utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:

   Example: expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

You can control how Export runs by entering the 'expdp' command followed
by various parameters. To specify parameters, you use keywords:

   Format:  expdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
   Example: expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=scott
               or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.
...

See first example (you can ignore the DIRECTORY parameter then Data Pump use the default DATA_PUMP_DIR.
Import:

Code: [Select all] [Show/ hide]

E:\Temp\Listing>impdp help=y

Import: Release 11.2.0.4.0 - Production on Jeu. Juin 23 13:51:29 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


The Data Pump Import utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:

     Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

You can control how Import runs by entering the 'impdp' command followed
by various parameters. To specify parameters, you use keywords:

     Format:  impdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
     Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

USERID must be the first parameter on the command line.
...

See example line.

[Updated on: Mon, 01 August 2016 14:16]

Report message to a moderator

Re: Copy database to another computer [message #654387 is a reply to message #654386]

Mon, 01 August 2016 15:15

derrellgore
Messages: 20
Registered: July 2016

Junior Member

So am I understanding that this with no parameters will do everything? or do I have to use parameters to do anything>

expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

Report message to a moderator

Re: Copy database to another computer [message #654388 is a reply to message #654387]

Mon, 01 August 2016 15:20

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

The first example is to export SCOTT schema (without directory):

Code: [Select all] [Show/ hide]

expdp scott/tiger DUMPFILE=scott.dmp SCHEMAS=scott

but it is not mandatory to connect with SCOTT, you can connect with a DBA account.

Report message to a moderator

Re: Copy database to another computer [message #654403 is a reply to message #654388]

Tue, 02 August 2016 04:24

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

Just a heads-up, the DIRECTORY parameter specified with the expdp command points to an Oracle Database Object not an O/S level file path/directory, as mentioned here.

The DBA_DIRECTORIES view will help you match up the Oracle Directory Name with its O/S counterpart.

HTH

[Edit: addendum]

[Updated on: Tue, 02 August 2016 04:38]

Report message to a moderator

Re: Copy database to another computer [message #654418 is a reply to message #654403]

Tue, 02 August 2016 09:20

derrellgore
Messages: 20
Registered: July 2016

Junior Member

Thanks...I haven't had a chance to try yet. I program in c# and just tried to write a procedure for a select statement and realized that doesn't work the same way as MS. That is another topic.

Report message to a moderator

Re: Copy database to another computer [message #654419 is a reply to message #654418]

Tue, 02 August 2016 09:22

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

https://www.google.com/webhp?hl=en&tab=ww#hl=en&q=oracle+c%23+example

Report message to a moderator

Previous Topic:	ORA-02374/ORA-01722/ORA-02372 while importing a 11.2.0.4-Dump into 12.1.0.2
Next Topic:	how get 12c backup

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 06 01:55:05 CDT 2025