1

I want to take the back up of my local Oracle instance and want it to import on my remote server.

I have searched the web but couldn't find any solution. The solution I got is:

  • Export from local and import into only local.
  • Export from Remote and import into only remote server.

But my requirement is:

I have a schema in my local oracle instance. Now I want to take it's backup and import it on to my remote server.

Below are commands I am running for exporting and importing.

for Local--

expdp HR/HR@ORCL directory=Export SCHEMAS=MUKESH DUMPFILE=MUKESH.dmp LOGFILE=MUKESH.log

impdp HR/HR@ORCL directory=Export SCHEMAS=MUKESH DUMPFILE=MUKESH.dmp LOGFILE=MUKESH.log

for Remote--

expdp FASTAdmin/password@db-m3-medium.coplvukvijdo.us-east-1.rds.amazonaws.com:1521/ORCL network_link=to_rds directory=Data_pump_dir dumpfile=MUKESH.dmp logfile=MUKESH.log SCHEMAS='MUKESH'

impdp FASTAdmin/password@db-m3-medium.coplvukvijdo.us-east-1.rds.amazonaws.com:1521/ORCL directory=DATA_PUMP_DIR dumpfile=MUKESH.dmp logfile=MUKESH.log SCHEMAS=MUKESH

Note:Please give me the solution using expdp and impdp command only.

halfer
  • 19,824
  • 17
  • 99
  • 186
Mukesh Singh
  • 303
  • 2
  • 5
  • 17
  • 1
    Have you copied the locally created dump file to the remote server's DATA_PUMP_DIR directory? – Alex Poole Jul 03 '15 at 11:24
  • [Amazon has documentation on how to do this](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Oracle.Procedural.Importing.html). – Alex Poole Jul 03 '15 at 11:29
  • Is there a any tool provided by oracle which can be used to transfer my local .dmp file on to my remote (RDS for Oralce from AWS) server. and which can tell whether the file has been transfered completed or in progress. – Mukesh Singh Jul 03 '15 at 11:49
  • Other than the [dbms_file_transfer package](http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_ftran.htm#CACFJEBH) the Amazon docs say to use? Have you tried to follow their instructions? – Alex Poole Jul 03 '15 at 11:54
  • They don't have any tool to do that. They have suggested me the tool i am using currently. I am even successfully able to transfer file but How can i get the status of transferred file using java code so that I can proceed to import it on to remote. Below is oracle pakage i am using. SQL> BEGIN 2 DBMS_FILE_TRANSFER.PUT_FILE( 3 source_directory_object => 'SOURCE_DIR', 4 source_file_name => 'test01.dbf', 5 destination_directory_object =>'DEST_DIR', 6 destination_file_name => 'test01.dbf', 7 destination_database => 'remote_db'); 8 END; 9 / – Mukesh Singh Jul 03 '15 at 12:06
  • If that completes and you don't get an exception then the file has transferred successfully? I'm not sure what you're asking - you think it might fail silently? – Alex Poole Jul 03 '15 at 12:09
  • @lalit-kumar-b - You added the expdp tag... how about adding wiki text, to, so the tag has some meaning to the rest of the community? – Mogsdad Jul 03 '15 at 16:10
  • @Mogsdad Sure thing, and I am *almost* ready with the content, it's just my regular day job that kept me busy(stuck with a failed RAC node to bring up of one of my clients), however, I will add the wiki soon. – Lalit Kumar B Jul 03 '15 at 16:15
  • @Mogsdad - maybe they should be synonyms of datapump anyway, if they're needed at all? Though that tag wiki could do with some work too... *8-) – Alex Poole Jul 03 '15 at 16:15
  • @AlexPoole - I thought so, but cheking Lalit's profile indicates he would be in a great position to know, so I left the editing to him. But without a wiki entry, a single-use tag is dooooommmed! – Mogsdad Jul 03 '15 at 16:17

1 Answers1

1

Three simple steps:

  • EXPDP on your local to generate the dump file.
  • Move the dump file to the remote server and place it in the required directory, by default from 10g and up you could use DATA_PUMP_DIR
  • EXPDP on the remote server using the dump file you placed in step 2.

In step 2, if you don't know the directory, you could do:

SELECT directory_path FROM dba_directories WHERE directory_name = 'DATA_PUMP_DIR';

See an example here.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • Yupe it's working fine. But When i run this it says PL/SQL executed successfully. But internally process doesn't complete. so my requirement is, There is any way to track whether the file has been transferred or still transferring so that i can proceed to import it. – Mukesh Singh Jul 03 '15 at 12:17