【茶包射手日記】Oracle DBLink 遇分散式交易出錯

Oracle 問題又來惹… Orz

某 Package 原本執行正常,當被包入 TransactionScope 範圍啟動分散式交易會出現 ORA-24777: use of non-migratable database link not allowed 錯誤,爬文找到 Rico 的文章,提到 Procedure 使用 Non-Shared Database Link 會導致類似錯誤。

我在測試環境寫了一個使用 Non-Shared DBLink 的 Procedure:

create or replace procedure SP_AccessDBLink(
       p_Count out number
) is
begin
  select count(1) into p_Count from BlahTable@BlahDBLink;
end SP_AccessDBLink;

使用以下程式碼試著重現錯誤:(借用上次的範例修改)

static void testDbLink()
{
    using (OracleConnection cn = new OracleConnection(csOra))
    {
        cn.Open();
        var cmd = cn.CreateCommand();
        cmd.CommandText = @"sp_accessdblink";
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        var p = cmd.Parameters.Add("p_Count", OracleDbType.Decimal);
        cmd.ExecuteNonQuery();
        Console.WriteLine(p.Value);
    }
}
 
static void Main(string[] args)
{
    testDbLink();
 
    using (var tx = new TransactionScope())
    {
        querySqlServer();
        testDbLink();
        if (Transaction.Current != null)
        {
            Console.WriteLine(Transaction.Current.TransactionInformation.LocalIdentifier);
            Console.WriteLine(Transaction.Current.TransactionInformation.DistributedIdentifier);
        }
        tx.Complete();
    }
    Console.Read();
}

如程式碼所示,第一次執行成功,包入 TransactionScope 再執行即出錯,但錯誤訊息不太相同,為 ORA0-24778 cannot open connections(無法啟連線):

依據 Oracle 文件:

  • ORA-24777: use of non-migratable database link not allowed
    Cause: The transaction, which needs to be migratable between sessions, tried to access a remote database from a non-multi threaded server process.
    Action: Perform the work in the local database or open a connection to the remote database from the client. If multi threaded server option is installed, connect to the Oracle instance through the dispatcher.
  • ORA-24778: cannot open connections
    Cause: The migratable transaction tried to access a remote database when the session itself had opened connections to remote database(s).
    Action: Close the connection(s) in the session and then try to access the remote database from the migratable transaction. If the error still occurs, contact Oracle customer support.

二者都與 Transaction 有關,可以解釋先前加入 TransactionScope 才出錯的現象。進一步做了不同 ODP.NET 跟 Oracle Server 版本的比對,我崩潰了…

  • Unmanged ODP.NET 12.1 或 11.2.3 + Oracle 11.2
    ORA-24778 cannot open connections
  • Managed ODP.NET 4.121.2 + Oracle 11.2
    ORA-24778 cannot open connections
  • Unmanaged ODP.NET 11.2 + Oracle 10.2 伺服器A 上另一隻存取 Non-Shared DBLink 的 Package
    ORA-24777: use of non-migratable database link not allowed
  • Unmanaged ODP.NET 12.1 + Oracle 10.2 伺服器B 與 伺服器A (Non-Shared DBLink 未必出錯,登楞!)
    無錯誤
  • Managed ODP.NET 4.121.2 + Oracle 10.2 伺服器B
    ORA-02048 attempt to begin distributed transaction without logging on

我承認以上測試結果並未理出頭緒,但測到這裡我的座位旁已擠了滿滿的羚羊,加上缺少足夠權限在 Oracle 模擬某些條件,拎杯身心俱疲欲哭無淚,僅留下記錄等待有志之士們繼續努力…

歡迎推文分享:
Published 07 April 2017 06:38 PM 由 Jeffrey
Filed under:
Views: 2,820



意見

沒有意見

你的看法呢?

(必要的) 
(必要的) 
(選擇性的)
(必要的) 
(提醒: 因快取機制,您的留言幾分鐘後才會顯示在網站,請耐心稍候)

5 + 3 =

搜尋

Go

<April 2017>
SunMonTueWedThuFriSat
2627282930311
2345678
9101112131415
16171819202122
23242526272829
30123456
 
RSS
創用 CC 授權條款
【廣告】
twMVC

Tags 分類檢視
關於作者

一個醉心技術又酷愛分享的Coding魔人,十年的IT職場生涯,寫過系統、管過專案, 也帶過團隊,最後還是無怨無悔地選擇了技術鑽研這條路,近年來則以做一個"有為的中年人"自許。

文章典藏
其他功能

這個部落格


Syndication