• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 126
  • Last Modified:

Need help importing Oracle .dmp file using remote computer connected to Amazon RDS Oracle Instance

I have an Oracle .dmp file (65 GB) that I have transferred to a remote Amazon Web Services RDS Oracle instance.  The file is an original export (pre version 10) and cannot be imported using the data pump (impdp) commands - unfortunately I also don't have the option to get the file exported using the data pump or I wouldn't be asking this question.  I am currently connected to the Oracle instance using SQLPlus on a windows machine at my home office.  I was able to create a linked server and transfer the file to the remote server into the data pump directory.  However where I am getting stuck is the imp (not impdp command) gives me a cannot open file for read error when I specify the following import command:

imp username/password@remoteserverinstancepath file='/rdsdbdata/datapump/dumpfile.dmp' touser='import_user' fromuser='export_user' log='C:\Temp\importlog.log'

I was able to start up the import from my machine using a the same file but in a local directory on my pc but this is has been running for almost a day and is going very slowly and I may need to abort this import as it looks like it will take days if not a week to complete.  My hunch is my file path is not lining up in the imp command and needs to identify the linked server somewhere in the path.  Unfortunately I cannot find any information on performing a remote import using the original imp commands.  Any help would be appreciated.
0
Doug Hardie
Asked:
Doug Hardie
  • 4
2 Solutions
 
slightwv (䄆 Netminder) Commented:
Using classic imp the file needs to be visible to the machine running the program.  I've also read a lot where Oracle tools have problems using UNC naming conventions.

So, the machine that runs imp needs connectivity to the database and direct access to the file.

Can you SSH into the database server and does that server have direct access to the file?  If so, you can run imp from the database server.

Remember that the classic imp running remotely will run at network speed.  If you have a slow network connection, the data transmission will be slow.
0
 
Doug HardieAuthor Commented:
Thank you for the suggestion.  I will give it a try tonight and let you know my findings.
0
 
Doug HardieAuthor Commented:
SSH is unfortunately not an option.  I have changed the way I am doing my import to separate the data to do schema only for now.  I will suggest that if you are going to use Amazon RDS for Oracle.  Make sure you can use the data pump or migration tools, if you want to use the original export for a large database, forget it.

Thanks again for your suggestion.
0
KuppingerCole Reviews AlgoSec in Executive Report

Leading analyst firm, KuppingerCole reviews AlgoSec's Security Policy Management Solution, and the security challenges faced by companies today in their Executive View report.

 
Geert GOracle dbaCommented:
import without the indexes  
this is usually the slowest part

with imp you can also dump the statements for the indexes into a file > see indexfile parameter
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sutil/oracle-original-import-utility.html#GUID-DC9BBA88-3201-45BF-9F49-83BE6E3530F4
run the index statements  after the import manually

splitting those statements into multiple pieces and running them simultaneously can speed up the process

if you have enterprise, you can build the indexes with parallel n at the end (where n is number of cores on your server)
0
 
Doug HardieAuthor Commented:
Thanks for the great suggestion.  I will try it if I need to proceed any further with the job.  For now I will consider this matter closed.

-Doug
0
 
Doug HardieAuthor Commented:
I gave up on trying to run a full import.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Firewall Management 201 with Professor Wool

In this whiteboard video, Professor Wool highlights the challenges, benefits and trade-offs of utilizing zero-touch automation for security policy change management. Watch and Learn!

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now