0

I have a specific need for data pump and I am having a hard time searching for a solution.

Currently, I have a exp/imp program that exports tables (selectively based on queries) from one database, and imports that same data into another database. This program and the dump files reside on a common server that can access both the source and destination databases. This is a totally automated process. It works good, albeit slowly.

Due to various reasons, I must migrate this program to use data pump. The biggest change now is the location of the dmp files. I also have very limited access to the database servers themselves, but I can run data pump.

The process will be run from the same common server, but the exported files will now reside on the database server for the source database. No issue there. I can create dmp files using expdp.

My issue is how to get that same data into the destination database. When I run impdp, it is expecting a data_pump_dir in the destination area (not source area). Again, this is automated, and I don't have the luxury of being able to transfer dmp files using scp or ftp or anything like that.

What can I use to overcome this problem using datapump?

Gary G
  • 41
  • 2
  • scp/sftp can be automated. network shared directories are also a thing. – Mat Feb 06 '18 at 21:40
  • Right, but I don't really have access to do that. Ideally, I'd have this process set to run separately on the database servers. Export on source, import on destination, and an scp to transfer the files, but that is not possible. – Gary G Feb 06 '18 at 21:52
  • 1
    If you have a db link between them, https://stackoverflow.com/questions/21412533/oracle-data-pump-impdp-to-remote-server – Kris Rice Feb 06 '18 at 22:16

1 Answers1

1

No reason you cannot configure an external directory on BOTH databases:

CREATE DIRECTORY mydumpdir AS '/whatever/the/path/is';

Then, impdp and expdp will take the DIRECTORY argument as mydumpdir

Make sure you configure permissions for the Oracle schemas/users to read/write to the directory AND the oracle process account should have OS level rights to read/write to that location also. The expdp server should also have write access as it might be trying to write reports to the locations or you might be using to do file cleanup.

Ctznkane525
  • 7,297
  • 3
  • 16
  • 40