弄清 ORA-24777：不允許使用不可移植的資料庫鏈路 產生的原因，以及解決方案

標籤：

最近一個同事諮詢,說有一個含有dblink的SQL,在plsql中這個SQL可以正常執行,而在JAVA後台調用執行這個SQL執行要報錯：
ORA-24777：不允許使用不可移植的資料庫鏈路。

請同事跟蹤程式進去後,發現是通過dblink查詢某個遠程庫表,而且使用了XA分散式交易資料庫連接。
以前也知道XA有不少坑,都繞著走了。
但這次既然遇到了,就準備解決了方便以後,有個統一的解決方案。
去查了下metalink,恰好有一篇文章提到這個問題：ORA-24777 reported when using a database link from within an XA coordinated transaction (文檔 ID 1506756.1)
這個可能是一個bug,或者說是JDBC的一個坑,使用JDBC然後調用有dblink的分散式交易,可能會出現這個問題。

metalink提供了兩種方案解決ORA-24777：

1.把oracle伺服器模式設定成shared server；
ALTER SYSTEM SET DISPATCHERS="(PROTOCOL=TCP)" scope=both;
ALTER SYSTEM SET SHARED_SERVERS = 10 scope=both;

2.把dblink串連設定成shared模式。

第一種解決方案的影響較大,也有可能帶來其他問題出現。第二種成本低,對於系統本身影響小。因此建議選擇方案二。

常用類型的dblink都會很容易建立,這兒來說說怎麼建立shared的dblink
我們先來看看create database link的文法：

CREATE [SHARED][PUBLIC] database link link_name

[CONNECT TO [user][current_user] IDENTIFIED BY password]
[AUTHENTICATED BY user IDENTIFIED BY password]
[USING ‘connect_string‘]

說明：
(1) 許可權：建立資料庫連結的帳號必須有CREATE DATABASE LINK或CREATE PUBLIC DATABASE LINK的系統許可權,用來登入到遠端資料庫的帳號必須有CREATE SESSION許可權。
一個公用資料庫連結對於資料庫中的所有使用者都是可用的,而一個私人連結僅對建立它的使用者可用,切不能被授權使用。
(2)link : 當source端的資料庫GLOBAL_NAME=TRUE時,link名必須與遠端資料庫的全域資料庫名global_name相同；否則,可以任意命名。
(3)current_user : 使用該選項是為了建立global類型的dblink。在分布式體系中存在多個資料庫的時候,如果想要在每一個資料庫中都可以使用同樣的名字來訪問資料庫test,那在每個資料庫中都要建立一個到資料庫test的db_link,太麻煩了,所以現在有這個選項。只要建立一次,所有的資料庫都可以使用這個db_link來訪問了。要使用這個特性,必須有oracle nameserver或者ORACLE目錄伺服器。並且資料庫test的參數global_names=true.
(4)connectstring：連接字串,tnsnames.ora中定義遠端資料庫的串連串,也可以在建立dblink的時候直接指定，類似ezconnect。
(5)username、password：遠端資料庫的使用者名稱,口令。如果不指定,則使用當前的使用者名稱和口令登入到遠端資料庫,當建立connected user類型的dblink時,需要如果採用資料字典驗證,則需要兩邊資料庫的使用者名稱密碼一致。

對於shared dblink
其中建一個shared 的資料庫連接,同時要指定database link_authentication。
使用shared方式的 database link是資料庫會限制到遠端資料庫的串連的數量,這樣以避免過多的串連對遠端資料庫造成太大的壓力。
在使用shared database link的時候,到database link的串連會在串連以後與本地串連斷開,為防止未授權的session使用此連結而要求在建立shared database link的時候必須要指定database link_authentication。
(from Oracle document: A shared database link is a link between a local server process and the remote database. The link is shared because multiple client processes can use the same link simultaneously.)

如下命令建立一個shard dblink：
CREATE SHARED PUBLIC database link
sales.us.americas.acme_auto.com
CONNECT TO scott IDENTIFIED
BY tiger AUTHENTICATED BY
anupam IDENTIFIED BY bhide USING ‘sales‘;

其中
Connects To Database：sales using net service name sales
Connects As：scott using password tiger, authenticated as anupam using password bhide
使用AUTHENTICATED BY子句稍微有些困擾,但是由於實現共用連結的方式安全性決定它是必須的。這個例子中的使用者名稱和口令anupam/bhide必須在遠程系統上有效。

再舉例說明一下：
樣本1
CREATE SHARED DATABASE_LINK DB_LINK_NAME
CONNECT TO REMOTE_USER_NAME_1 IDENTIFIED BY USER_PASSWORD_1
AUTHENTICATED BY REMOTE_USER_NAME_2 IDENTIFIED BY USER_PASSWORD_2
USING ‘192.168.0.157:1521/SID‘
樣本2
CREATE SHARED DATABASE_LINK DB_LINK_NAME
AUTHENTICATED BY REMOTE_USER_NAME_2 IDENTIFIED BY USER_PASSWORD_2
USING ‘192.168.0.157:1521/SID‘
注意,樣本1中出現兩個REMOTE_USER_NAME分別是REMOTE_USER_NAME_1\REMOTE_USER_NAME_2,因為指定了SHARED關鍵字必須要求有AUTHENTICATION而這時CONNECT TO可以省略(如樣本2)。

建立完shared database link之後,jdbc執行這個SQL報錯的問題解決。

但另外一個問題又來了：每一個dblink有串連限制（預設是4）,如果超過串連限制數,會報錯：ORA-02020: too many database links in use

1.查看相關初始化參數：
SQL> show parameters open_links;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_links integer 4
open_links_per_instance integer 4

兩個的含義：
OPEN_LINKS_PER_INSTANCE specifies the maximum number of migratable open connections globally for each database instance. XA transactions use migratable open connections so that the connections are cached after a transaction is committed. Another transaction can use the connection, provided the user who created the connection is the same as the user who owns the transaction.

OPEN_LINKS_PER_INSTANCE is different from OPEN_LINKS, which indicates the number of connections from a session. The OPEN_LINKS parameter is not applicable to XA applications.

中文解釋一下：
open_links：oracle的官方解釋是每個session最多允許的dblink數量;
open_links_per_instance這個參數是指每個執行個體最多允許的dblink個數。

2.修改open_links
alter system set open_links=255 scope=spfile;
alter system set open_links_per_instance=255 scope=spfile;

3.使修改後重啟資料庫生效

弄清 ORA-24777：不允許使用不可移植的資料庫鏈路 產生的原因，以及解決方案