Oracle data pump impdp to remote server

Question

We have a dump file that we want to import to an Amazon rds server.

This is what I did:

Create a public db link and verify it works:

create public database link rdsdblink
connect to dbuser identified by dbpsw
using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST='xxx')(PORT=1521)))(CONNECT_DATA=(SID=dbsid)))';

SQL> select * from dual @ rdsdblink;

D
-
X

Create a directory for the dump file:

CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS 'G:\DB';

Import the dump file:

impdp dbuser/dbpsw@rdsdblink tablespaces=EMP directory=DATA_PUMP_DIR dumpfile=EMP_dump.DMP logfile=EMP_dump.log network_link=rdsdblink

I have also added rdsdblink connection string to tnsnames.ora file and restarted oracle service ("shutdown immediate", then "startup").

The following error occured:

Connected to: Oracle Database 11g Release 11.2.0.2.0 - 64bit Production
ORA-39001: invalid argument value
ORA-39200: Link name "rdsdblink" is invalid.
ORA-02019: connection description for remote database not found

My local oracle version: Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

Remote oracle version: Oracle Database 11g Release 11.2.0.2.0 - 64bit Production

Ulaulah

Answer 1

You've connected to the remote database (via dbuser/dbpsw@rdsdblink), but your DB link is created in your local database. At the moment you're trying to run the import on the remote DB, with a network link also to the remote DB, and that network link is trying to use a DB link that doesn't exist on that remote DB.

The tnsnames.ora entry and the DB link are completely separate things.

You need to connect normally locally - using whichever credentials you used to create the DB link, probably. The network_link parameter will then make your local database session, that is started by impdp, act against the remote server; so your local directory can be used.

Except... it doesn't work like that. The remote database identified by the network_link can be used as the source of the import, without a dump file at all; but it can't be the target for an import from a file.

From the impdpdocumentation:

The NETWORK_LINK parameter initiates an import via a database link. This means that the system to which the impdp client is connected contacts the source database referenced by the source_database_link, retrieves data from it, and writes the data directly to the database on the connected instance. There are no dump files involved.

If you really wanted to go down this route, I think you would need a link from remote to local, and to run the import against the remote (as you are now), but to be pulling directly from your schema - not from a previous export. You'd still need access to a DIRECTORY object on the remote server, as logs etc. would be written there, even if you weren't copying your dump file over. Even with nologfile I believe it will error if you don't specify a directory or don't have permissions on it.

The article you linked to in your previous question said the same thing:

For imports, the NETWORK_LINK parameter also identifies the database link pointing to the source server. The difference here is the objects are imported directly from the source into the local server without being written to a dump file. Although there is no need for a DUMPFILE parameter, a directory object is still required for the logs associated with the operation.

Ornelas

Thank you I'm really new to this, so if I'll run import against the remote, how do I point to a local dump file? – Ulaulah

@Ulaulah - you can't. The file has to be on the server that the DB instance you're connected to is running on. It would have to be on the rds server, and I guess that's not an option? Otherwise you have to export and import as one step, with no intermediate dump file, and getting a DB link to go the other way might be problematic (assuming your listener isn't exposed to the Internet). – Ornelas

thanks for your help. We'll try to find a way to do it. – Ulaulah

@Ulaulah - have you seen this import guide? It includes an impdp into RDS but from EC - you can do the same from your DB but only if you can reach it, which is unlikely. If you can redo the export you could use exp andimp instead, which would work in your scenario. It might give you some other ideas too. – Ornelas