Oracle 数据泵impdp 到远程服务器

Oracle data pump impdp to remote server

提问by Cal

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

我们有一个要导入 Amazon rds 服务器的转储文件。

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").

我还将 rdsdblink 连接字符串添加到 tnsnames.ora 文件并重新启动了 oracle 服务（“立即关闭”，然后是“启动”）。

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

连接到：Oracle 数据库 11g 版本 11.2.0.2.0 - 64 位生产
ORA-39001：无效的参数值
ORA-39200：链接名称“rdsdblink”无效。
ORA-02019: 找不到远程数据库的连接描述

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

我本地的oracle版本：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

远程oracle版本：Oracle Database 11g Release 11.2.0.2.0 - 64bit Production

回答by Alex Poole

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.

您已连接到远程数据库（通过dbuser/dbpsw@rdsdblink），但您的数据库链接是在本地数据库中创建的。目前，您正在尝试在远程数据库上运行导入，并且还具有到远程数据库的网络链接，并且该网络链接正在尝试使用该远程数据库上不存在的数据库链接。

The tnsnames.oraentry and the DB link are completely separate things.

该tnsnames.ora条目和链接数据库是完全不同的东西。

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

您需要在本地正常连接 - 可能使用您用于创建数据库链接的任何凭据。network_link然后，该参数将使由 启动的本地数据库会话impdp对远程服务器起作用；因此可以使用您的本地目录。

Except... it doesn't work like that. The remote database identified by the network_linkcan 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.

除了......它不会那样工作。标识的远程数据库network_link可以作为导入的源，完全不需要转储文件；但它不能成为从文件导入的目标。

From the impdpdocumentation:

从impdp文档：

The NETWORK_LINKparameter initiates an import via a database link. This means that the system to which the impdpclient 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.

该NETWORK_LINK参数通过数据库链接启动导入。这意味着impdp客户端连接到的系统联系 引用的源数据库 source_database_link，从中检索数据，并将数据直接写入连接实例上的数据库。不涉及转储文件。

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 DIRECTORYobject on the remote server, as logs etc. would be written there, even if you weren't copying your dump file over. Even with nologfileI believe it will error if you don't specify a directory or don't have permissions on it.

如果你真的想走这条路，我认为你需要一个从远程到本地的链接，并针对远程运行导入（就像你现在一样），但直接从你的模式中提取 - 而不是从以前的出口。您仍然需要访问DIRECTORY远程服务器上的对象，因为日志等会写入那里，即使您没有复制转储文件。即使nologfile我相信如果您没有指定目录或没有权限，它也会出错。

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

您在上一个问题中链接的文章也说了同样的话：

For imports, the NETWORK_LINKparameter 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 DUMPFILEparameter, a directory object is still required for the logs associated with the operation.

对于导入，该NETWORK_LINK参数还标识指向源服务器的数据库链接。这里的区别是对象直接从源导入到本地服务器，而不是写入转储文件。虽然不需要 DUMPFILE参数，但与操作相关的日志仍然需要目录对象。