无法在 Oracle-DB 中创建到远程数据库的数据库链接

Can't create Database Link to remote DB in Oracle-DB

提问by user748261

We have a CRM system in our company, which uses an Oracle 11g database. It is developed by a third party vendor.

我们公司有一个 CRM 系统，它使用的是Oracle 11g 数据库。它由第三方供应商开发。

We do not have access to the server which runs the CRM system. But nevertheless, we have working DBA login data available to us (SYS user). It consists of:

我们无权访问运行 CRM 系统的服务器。但是尽管如此，我们有可用的 DBA 登录数据（SYS 用户）。它包括：

• server IP: 172.1.2.3
• port: 1521
• SID: abc
• user: sys
• password: *

• 服务器IP：172.1.2.3
• 端口：1521
• SID：abc
• 用户：sys
• 密码：*

We can use this to access the DB with Oracle SQL Developer 3.1(Connections >> Properties)

我们可以使用它通过Oracle SQL Developer 3.1（连接 >> 属性）访问数据库

Now parts of the data must be copiedout of the CRM-database into an otherOracle database, which resides on another server.

现在，必须将部分数据从 CRM 数据库复制到位于另一台服务器上的其他Oracle 数据库中。

To my understanding, I'd need to create a database link in my target database. I tried something like this:

据我了解，我需要在目标数据库中创建一个数据库链接。我试过这样的事情：

CREATE PUBLIC DATABASE LINK xxx CONNECT TO sys IDENTIFIED BY ***** USING 'MYTNSENTRY'

My tnsnames.orais as follows:

我的tnsnames.ora如下：

MYTNSENTRY =
 (DESCRIPTION = 
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 172.1.2.3)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = abc)
 )
)

.... and my listener.oralook like this:

.... 和我的listener.ora看起来像这样：

MYLISTENER=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=172.1.2.3)(PORT=1521))
      ))
SID_LIST_MYLISTENER=
  (SID_LIST=    
    (SID_DESC=
      (SID_NAME=MYTNSENTRY)
      (ORACLE_HOME=C:\somepath) # path to Oracle home of target DB
      (PROGRAM=extproc)))

Is PROGRAM=extproc the right choice? There are a couple of other programs to pick. I couldn't even start the listener with lsnrctlbecause it could not "verify the user" or something. Ironically, the listener-setup and database link to a MS SQL serverwork smoothly.

PROGRAM=extproc 是正确的选择吗？还有一些其他程序可供选择。我什至无法使用lsnrctl启动侦听器，因为它无法“验证用户”或其他什么。具有讽刺意味的是，侦听器设置和到MS SQL 服务器的数据库链接工作顺利。

Now despite lacking some vital information about the CRM DB system, one can still connect to the DB in SQL Developer. Shouldn't it also be possible to make a connection between two Oracle DBs? Please help me with the setup and the creation of the database link.

现在，尽管缺乏有关 CRM 数据库系统的一些重要信息，但仍然可以在 SQL Developer 中连接到数据库。不应该也可以在两个 Oracle DB 之间建立连接吗？请帮助我设置和创建数据库链接。

----- EDIT: --------

- - - 编辑： - - - -

Alex Poole's hint helped me get it to work. I used

Alex Poole 的提示帮助我让它发挥作用。我用了

  show parameters service_names;

to get the full service name. It has the form abc.def, with defbeing the domain. Thusly, I added the domain name to the TNS alias in tnsnames.ora:

获取完整的服务名称。它的形式为abc.def，其中def是域。因此，我将域名添加到tnsnames.ora 中的 TNS 别名中：

MYTNSENTRY =
 (DESCRIPTION = 
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 172.1.2.3)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = abc.def)
 )
)

The connection can be tested with tnsping MYTNSENTRYon the command prompt of the target DB server. The tnsnames.ora is local. However, I deleted all changes to the "local" listener.ora,since the listener indeed resides on the CRM server.

可以在目标数据库服务器的命令提示符下使用tnsping MYTNSENTRY测试连接。tnsnames.ora 是本地的。但是，我删除了对“本地” listener.ora 的所有更改，因为监听器确实驻留在 CRM 服务器上。

The SQL command is mostly unchanged, but now the connection works:

SQL 命令几乎没有变化，但现在连接有效：

CREATE PUBLIC DATABASE LINK xxx CONNECT TO some_user IDENTIFIED BY ***** USING 'MYTNSENTRY'

回答by Alex Poole

You've said the SIDis abc, but in your tnsnames.orayou've got the SERVICE_NAMEin the CONNECT_DATAsection. They are not always the same thing - see this question, or this Ask Tom entry. You haven't actually said what error you're getting, but just changing that to SID =might make a difference.

你已经说过SIDis abc，但在你的部分中tnsnames.ora你已经得到了。它们并不总是相同的 - 请参阅此问题或此Ask Tom 条目。您实际上并没有说明您遇到了什么错误，但只是将其更改为可能会有所作为。SERVICE_NAMECONNECT_DATASID =

The listener.ora, and indeed the listener, are on the server that hosts the CRM database, not on the one that hosts your 'target' database. As you can connect from SQL Developer that is apparently already configured. The tnsnames.oradoes need to be local.

的listener.ora，而事实上听众，在服务器上托管CRM数据库，而不是在一个承载您的“目标”数据库。因为您可以从显然已经配置的 SQL Developer 进行连接。在tnsnames.ora确实需要地方。

But if you do know the service_namefor the CRM database you can skip that and use the EZCONNECTsyntax to define everything in the link:

但是，如果您确实知道service_nameCRM 数据库的名称，则可以跳过它并使用EZCONNECT语法来定义链接中的所有内容：

CREATE PUBLIC DATABASE LINK xxx
CONNECT TO non-sys IDENTIFIED BY *****
USING '//172.1.2.3:1521/service_name';

Check your SQL Developer configuration to see if that is already using the service name, rather than SID, and if not you'll need to discover it. If you had access to the CRM server you could use lsnrctlto find the service names that are registered, but as you don't seem to you will need to connect to the database and run show parameters service_namesor select value from v$parameter where name = 'service_names';.

检查您的 SQL Developer 配置，看看它是否已经在使用服务名称，而不是 SID，如果没有，您需要发现它。如果您有权访问 CRM 服务器，您可以使用它lsnrctl来查找注册的服务名称，但您似乎不需要连接到数据库并运行show parameters service_names或select value from v$parameter where name = 'service_names';。

You need more privileges to create a public link than a private one, and public is potentially less secure as it exposes your CRM database to anyone on your target one. So I'd only make it public if really needed, and either way connect to a read-only account if you're able to create one.

您需要比私有链接更多的权限来创建公共链接，而公开链接的安全性可能较低，因为它会将您的 CRM 数据库暴露给目标链接上的任何人。因此，我只会在真正需要时将其公开，并且如果您能够创建一个只读帐户，则无论哪种方式都可以连接到只读帐户。

Also note that if your target database has global_namesset to truethe database link name has to match the remote service name.

另请注意，如果您的目标数据库已global_names设置为true数据库链接名称，则必须与远程服务名称匹配。

回答by DCookie

Not only should you NOT connect as SYS unless necessary, you CANNOT connect as SYS over a database link.

除非必要，您不仅不应该以 SYS 身份连接，而且您不能通过数据库链接以 SYS 身份连接。