比ORA-24777: 不允许使用不可移植的数据库链路更让人郁闷的事情

水深无声 2022-08-05 02:51 222阅读 0赞

现场有一个同步功能报错，内容如下：

java.sql.BatchUpdateException: **ORA-24777: 不允许使用不可移植的数据库链路**

at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:10317)

at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:216)

at weblogic.jdbc.wrapper.PreparedStatement.executeBatch(PreparedStatement.java:210)

**   1.了解了一下功能，大致如下：**

有A、B两个数据库，在A数据库上建了一个dblink，在XA的JDBC驱动下，通过dblink就会出现这种错误。

**   2.此错误有些生僻，直接查metalink见附录。两种解决方案供选择：**

a. Configure the database to allow the use of shared servers

ALTER SYSTEM SET DISPATCHERS="(PROTOCOL=TCP)" scope=both;

ALTER SYSTEM SET SHARED\_SERVERS = 10 scope=both;

b. Define the database link being utilised as a shared database link,一定不要加public

CREATE SHARED DATABASE LINK dblink\_name

\[CONNECT TO username IDENTIFIED BY password\]|\[CONNECT TO CURRENT\_USER\]

AUTHENTICATED BY schema\_name IDENTIFIED BY password

\[USING 'service\_name'\];

**   3.现场调整**

先用a方案在B数据库上调整了，用b方案在A数据库上做了调整，测试不行。再用a方案在A数据库上调整了，测试还是不行。

**   4.到现场发现问题**

发现同义词不对，就是没有用到这个shared的dblink，艹，这太让人郁闷了，调了半天，没有用到。

我认为的结论是：只要把A库中的dblink改为shared的方式就可以了。

### Using Database Links in Oracle XA Applications ###

Oracle XA applications can access other Oracle Database instances through database links with these restrictions:

*  They must use the shared server configuration.
    
    The transaction processing monitors (TPMs) use shared servers to open the connection to an Oracle Database A. Then the operating system network connection required for the database link is opened by the dispatcher instead of a dedicated server process. This allows different services or threads to operate on the transaction.
    
    If this restriction is not satisfied, then when you use database links within an XA transaction, it creates an operating system network connection between the dedicated server process and the other Oracle Database B. Because this network connection cannot be moved from one dedicated server process to another, you cannot detach from this dedicated server process of database A. Then when you access the database B through a database link, you receive an ORA-24777 error.
 *  The other database being accessed must be another Oracle Database.

If these restrictions are satisfied, Oracle Database allows such links and propagates the transaction protocol (prepare, rollback, and commit) to the other Oracle Database instances.

If using the shared server configuration is not possible, then access the remote database through the Pro\*C/C++ application by using `EXEC``SQL` `AT` syntax.

The `init.ora` parameter `OPEN_LINKS_PER_INSTANCE` specifies the number of open database link connections that can be migrated. These`dblink` connections are used by XA transactions so that the connections are cached after a transaction is committed. Another transaction can use the database link connection if the user who created the connection also created the transaction. This parameter is different from the `init`.`ora` parameter `OPEN_LINKS`, which specifies the maximum number of concurrent open connections (including database links) to remote databases in one session. The `OPEN_LINKS` parameter does not apply to XA applications.

<table> 
 <tbody> 
  <tr> 
   <td align="left"><span style="font-size:14px; text-align:right; padding:0px 6px 0px 0px; font-weight:bold">ORA-24777 reported when using a database link from within an XA coordinated transaction (文档 ID 1506756.1)</span></td> 
   <td align="right"><a title="转到底部" href="https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=355369996576011&amp;id=1506756.1&amp;_adf.ctrl-state=167b9s1q31_57#" style="font-size:11px; white-space:nowrap; text-decoration:none" rel="nofollow"><img src="https://support.oracle.com/epmos/ui/images/quicklink_smlarrowdown_enabled.gif" title="转到底部" alt="转到底部" style="border:none; vertical-align:middle; display:inline"><span style="color:rgb(0,50,134); margin-left:3px">转到底部</span></a></td> 
   <td><img src="/images/20220805/e5c1cdd6cbac433d97fdfe634d84b25e.png" alt="" width="20" height="10"></td> 
  </tr> 
 </tbody> 
</table>

--------------------

![t.gif][]

<table style="table-layout:fixed; overflow:hidden"> 
 <tbody> 
  <tr> 
   <td style="overflow:hidden; vertical-align:top"> 
    <div style="overflow:auto"> 
     <span style="font-size:12px"></span> 
     <p><strong>In this Document</strong><br> </p> 
     <table> 
      <tbody> 
       <tr> 
        <td></td> 
        <td><a href="https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=355369996576011&amp;id=1506756.1&amp;_adf.ctrl-state=167b9s1q31_57#SYMPTOM" rel="nofollow">Symptoms</a></td> 
       </tr> 
      </tbody> 
     </table> 
     <table> 
      <tbody> 
       <tr> 
        <td>&nbsp;</td> 
        <td><a href="https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=355369996576011&amp;id=1506756.1&amp;_adf.ctrl-state=167b9s1q31_57#CAUSE" rel="nofollow">Cause</a></td> 
       </tr> 
      </tbody> 
     </table> 
     <table> 
      <tbody> 
       <tr> 
        <td>&nbsp;</td> 
        <td><a href="https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=355369996576011&amp;id=1506756.1&amp;_adf.ctrl-state=167b9s1q31_57#FIX" rel="nofollow">Solution</a></td> 
       </tr> 
      </tbody> 
     </table> 
     <hr> 
     <p></p> 
     <table> 
      <tbody> 
       <tr> 
        <td><em>This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.<em></em></em></td> 
       </tr> 
      </tbody> 
     </table> 
     <h2 style="border-bottom-width:1px; border-bottom-style:solid; border-bottom-color:rgb(196,209,230); font-size:16px; background-color:rgb(119,119,115); color:rgb(255,255,255); text-transform:uppercase; padding-top:6px; padding-bottom:6px; padding-left:8px"> APPLIES TO:</h2> Oracle Server - Enterprise Edition - Version 10.2.0.5 to 11.2.0.3 [Release 10.2 to 11.2] 
     <br> Information in this document applies to any platform. 
     <br> 
     <h2 style="border-bottom-width:1px; border-bottom-style:solid; border-bottom-color:rgb(196,209,230); font-size:16px; background-color:rgb(119,119,115); color:rgb(255,255,255); text-transform:uppercase; padding-top:6px; padding-bottom:6px; padding-left:8px"> <a rel="nofollow"></a>SYMPTOMS</h2> 
     <p>ORA-24777 reported by a JDBC/XA application when utilising a database link between two Oracle databases.<br> <br> </p> 
     <h2 style="border-bottom-width:1px; border-bottom-style:solid; border-bottom-color:rgb(196,209,230); font-size:16px; background-color:rgb(119,119,115); color:rgb(255,255,255); text-transform:uppercase; padding-top:6px; padding-bottom:6px; padding-left:8px"> <a rel="nofollow"></a>CAUSE</h2> 
     <p><span>An ORA-24777 is a warning that you are trying to utilise a database link within an XA coordinated transaction and the connection to the source database which is trying to use the database link has been made through a dedicated server connection.<br> <br> This is not allowed and this is documented in following entry<br> <br> As per&nbsp;<span><a href="http://docs.oracle.com/cd/E11882_01/appdev.112/e25518/adfns_xa.htm#ADFNS809" title="Using Database Links in Oracle XA Applications" rel="nofollow">http://docs.oracle.com/cd/E11882_01/appdev.112/e25518/adfns_xa.htm#ADFNS809</a></span><br> <br> in the following section 'Using Database Links in Oracle XA Applications'</span><span>.</span><br> &nbsp;</p> 
     <h2 style="border-bottom-width:1px; border-bottom-style:solid; border-bottom-color:rgb(196,209,230); font-size:16px; background-color:rgb(119,119,115); color:rgb(255,255,255); text-transform:uppercase; padding-top:6px; padding-bottom:6px; padding-left:8px"> <a rel="nofollow"></a>SOLUTION</h2> 
     <p>Two options<br> <br> 1. Configure the database to allow the use of shared servers and then let the application use these as per the following documentation<span><br> <br> <span><a href="http://docs.oracle.com/cd/E11882_01/server.112/e25494/manproc003.htm#ADMIN00502" title="Configuring Oracle Database for Shared Server" rel="nofollow">http://docs.oracle.com/cd/E11882_01/server.112/e25494/manproc003.htm#ADMIN00502</a></span><br> <br> or establish whether the Java application needs to start an XA transaction.</span></p> 
     <p>OR<br> <br> 2. Define the database link being utilised as a shared database link, i.e,</p> 
     <p><span>CREATE SHARED DATABASE LINK</span>&nbsp;..</p> 
     <p>as per</p> 
     <p><a href="http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_5005.htm#SQLRF01205" title="Create Database link" rel="nofollow">http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_5005.htm#SQLRF01205</a></p> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

[t.gif]: /images/20220805/e5c1cdd6cbac433d97fdfe634d84b25e.png