oracle dblink造成远程数据库session过多

￡神魔★判官ぃ 2022-08-05 05:26 208阅读 0赞

现场报网公司数据库连不上，先检查了下数据库processes=1500,session=2200.我觉得很大啊，这个数据库没有几个人用。

查看v$session中的session最多是哪个machine发起的，发现是省公司的数据库发起的session，找开发梳理了下业务，省公司同步dblink操作网公司表，且是通过weblogic的连接池。

哦，有点明白了，是dblink引起的，weblogic连接池是一直存在的，所以在网公司端session是不释放的，如果省公司把应用都停掉，那在网公司端的session都会释放。要验证想法，做个试验：

**目标：**数据库A上建dblink,修改数据库B上的表。

**环境准备：**

1.在数据库A上建dblink

create public database link TO\_B  
  connect to TEST\_DB identified by  TEST\_DB   
  using '(DESCRIPTION =(ADDRESS\_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.15.150)(PORT = 1521)))(CONNECT\_DATA =(SID = orcl)))';

2.在数据库B上建表

create table TEST(  ID NUMBER );

insert into test values(1);

**   开始测试：**

1.在数据库B上select count(1) from v$session;

2.在数据库A上update test@TO\_B set id=1;

commit;

3.在数据库B上select count(1) from v$session;可以看到涨了一个

**     对比测试：**

1.在数据库B上select count(1) from v$session;

2.在数据库A上update test@TO\_B set id=1;

commit;

**     alter session close database linkTO\_B;**

3.在数据库B上select count(1) from v$session;可以看到没有变化

总结： a. 出现这种问题，归根结底是操作dblink不规范，用了之后没有关闭。

b. 把weblogic的驱动从XA驱动改为普通的thin,同时把shared dblink改为普通的dblink。

有可能是bug:

<table> 
 <tbody> 
  <tr> 
   <td align="left"> 
    <div> 
     <div> 
      <span></span> 
     </div> 
     <div> 
      <span></span> 
      <table style="margin-left:0px; margin-right:0px"> 
       <tbody> 
        <tr> 
         <td align="left"> 
          <div> 
           <div> 
            <table> 
             <tbody> 
              <tr> 
               <td> 
                <div style="font-family:Tahoma,Verdana,Helvetica,sans-serif; margin-top:4px; padding-left:4px; white-space:nowrap"> 
                 <table style="padding:0px; width:11px"> 
                  <tbody> 
                   <tr> 
                    <td style="width:1px; min-width:1px; padding:0px; font-size:0px"></td> 
                    <td style="padding:0px; overflow:hidden; white-space:nowrap; vertical-align:middle; display:block; font-weight:bold; font-size:14px"> 
                     <table> 
                      <tbody> 
                       <tr> 
                        <td style="height:21px"></td> 
                        <td> 
                         <div title=" " style="white-space:nowrap; overflow:hidden; height:20px; line-height:14px; margin:0px; width:0px; max-width:0px"> 
                          <h1 style="display:inline; margin:0px; font-size:14px"></h1> 
                         </div> </td> 
                        <td>&nbsp;</td> 
                        <td style="width:8px">&nbsp;</td> 
                       </tr> 
                      </tbody> 
                     </table> </td> 
                    <td style="width:1px; min-width:1px; padding:0px; font-size:0px">&nbsp;</td> 
                   </tr> 
                  </tbody> 
                 </table> 
                 <div style="padding:0px 1px 0px 10px; vertical-align:top"> 
                  <div></div> 
                 </div> 
                </div> </td> 
              </tr> 
             </tbody> 
            </table> 
           </div> 
           <div> 
            <div style="margin-top:2px"></div> 
           </div> 
          </div> </td> 
        </tr> 
       </tbody> 
      </table> 
     </div> 
    </div> </td> 
   <td align="right"> 
    <table style="white-space:nowrap"> 
     <tbody> 
      <tr> 
       <td> 
        <div> 
         <div> 
          <div style="margin-top:6px"></div> 
         </div> 
         <div> 
          <a title="提供有关本页功能的反馈" style="color:rgb(0,113,194); padding-top:5px" rel="nofollow">提供反馈...</a> 
         </div> 
        </div> </td> 
       <td> 
        <div> 
         <div> 
          <div style="margin-top:1px"></div> 
         </div> 
         <div> 
          <div> 
           <div></div> 
           <div> 
            <img src="/images/20220805/ff17e9b2e70d40919e189d713c91fca4.png" alt="" width="100%" height="0px"> 
           </div> 
           <div> 
            <span></span> 
            <table style="border:2px solid green; margin:1px 4px; background-color:white"> 
             <tbody> 
              <tr> 
               <td style="padding:2px 6px">您已根据 ID 匹配定向到此 BUG。</td> 
               <td style="padding:2px 6px"><a href="https://support.oracle.com/epmos/faces/BugDisplay?id=18394488&amp;_adf.ctrl-state=bzjp9h83g_258&amp;_afrLoop=361425704860767#" style="font-size:11px; text-decoration:none; color:rgb(8,81,179)" rel="nofollow"><span style="white-space:nowrap">或者, 单击此处搜索此短语。</span></a></td> 
               <td style="padding:2px 6px"><a href="https://support.oracle.com/epmos/faces/BugDisplay?id=18394488&amp;_adf.ctrl-state=bzjp9h83g_258&amp;_afrLoop=361425704860767#" style="font-size:11px; text-decoration:none; color:rgb(8,81,179)" rel="nofollow">隐藏</a></td> 
              </tr> 
             </tbody> 
            </table> 
           </div> 
          </div> 
         </div> 
        </div> </td> 
      </tr> 
     </tbody> 
    </table> </td> 
   <td align="right"> 
    <table style="white-space:nowrap"> 
     <tbody> 
      <tr> 
       <td><img src="/images/20220805/ff17e9b2e70d40919e189d713c91fca4.png" alt="" width="12px" height="0px"></td> 
       <td> 
        <div> 
         <div> 
          <div style="margin-top:6px"></div> 
         </div> 
         <div></div> 
        </div> </td> 
       <td><img src="/images/20220805/ff17e9b2e70d40919e189d713c91fca4.png" alt="" width="12px" height="0px"></td> 
      </tr> 
     </tbody> 
    </table> </td> 
  </tr> 
  <tr> 
   <td colspan="3">&nbsp;</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td colspan="2"> 
    <div style="margin-top:10px"></div> </td> 
  </tr> 
  <tr> 
   <td align="left"><span style="font-size:14px; text-align:right; color:rgb(51,51,51); padding:0px 6px 0px 0px; font-weight:bold; font-family:Helvetica,sans-serif">Bug 18394488 : TOO MANY CONNECTIONS OVER SHARED DBLINK AFTER UPGRADE TO 11.2.0.4</span></td> 
   <td align="right"> 
    <table> 
     <tbody> 
      <tr> 
       <td><a title="单击此项可添加到收藏夹" href="https://support.oracle.com/epmos/faces/BugDisplay?id=18394488&amp;_adf.ctrl-state=bzjp9h83g_258&amp;_afrLoop=361425704860767#" style="font-size:11px; white-space:nowrap; text-decoration:none" rel="nofollow"><img src="https://support.oracle.com/epmos/ui/images/favorites_qualifier_notsel.png" title="单击此项可添加到收藏夹" alt="单击此项可添加到收藏夹" style="border:none; vertical-align:middle; display:inline"></a><span></span><img src="/images/20220805/ff17e9b2e70d40919e189d713c91fca4.png" alt="" width="20" height="10"></td> 
       <td><a title="通过电子邮件发送此文档的链接" href="https://support.oracle.com/epmos/faces/BugDisplay?id=18394488&amp;_adf.ctrl-state=bzjp9h83g_258&amp;_afrLoop=361425704860767#" style="font-size:11px; white-space:nowrap; text-decoration:none" rel="nofollow"><img src="https://support.oracle.com/epmos/ui/images/email_16.png" title="通过电子邮件发送此文档的链接" alt="通过电子邮件发送此文档的链接" style="border:none; vertical-align:middle; display:inline"></a><img src="/images/20220805/ff17e9b2e70d40919e189d713c91fca4.png" alt="" width="20" height="10"></td> 
       <td><a title="可打印页" href="https://support.oracle.com/epmos/faces/BugDisplay?id=18394488&amp;_adf.ctrl-state=bzjp9h83g_258&amp;_afrLoop=361425704860767#" style="font-size:11px; text-decoration:none; color:rgb(8,81,179)" rel="nofollow"><img title="可打印页" alt="可打印页" src="https://support.oracle.com/epmos/ui/images/print_ena.png" style="border:0px"></a><img src="/images/20220805/ff17e9b2e70d40919e189d713c91fca4.png" alt="" width="20" height="10"></td> 
       <td><a title="转到底部" href="https://support.oracle.com/epmos/faces/BugDisplay?id=18394488&amp;_adf.ctrl-state=bzjp9h83g_258&amp;_afrLoop=361425704860767#" 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><img src="/images/20220805/ff17e9b2e70d40919e189d713c91fca4.png" alt="" width="10" height="10"></td> 
      </tr> 
     </tbody> 
    </table> </td> 
  </tr> 
  <tr> 
   <td colspan="2"> 
    <div style="margin-top:5px"></div> </td> 
  </tr> 
  <tr> 
   <td colspan="2"><img src="/images/20220805/ff17e9b2e70d40919e189d713c91fca4.png" alt="" width="100%" height="1" style="background-color:silver"></td> 
  </tr> 
 </tbody> 
</table>

![t.gif][]

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

<table> 
 <tbody> 
  <tr> 
   <td align="left"><span style="font-size:14px; text-align:right; color:rgb(51,51,51); padding:0px 6px 0px 0px; font-weight:bold; font-family:Helvetica,sans-serif">类型</span></td> 
   <td align="left"><span style="font-family:Helvetica,sans-serif; font-size:14px; color:rgb(51,51,51)">B - Defect</span></td> 
   <td align="left"><span style="font-size:14px; text-align:right; color:rgb(51,51,51); padding:0px 6px 0px 0px; font-weight:bold; font-family:Helvetica,sans-serif">已在产品版本中修复</span></td> 
   <td align="left"><span style="font-family:Helvetica,sans-serif; font-size:14px; color:rgb(51,51,51)"></span></td> 
  </tr> 
  <tr> 
   <td align="left"><span style="font-size:14px; text-align:right; color:rgb(51,51,51); padding:0px 6px 0px 0px; font-weight:bold; font-family:Helvetica,sans-serif">严重性</span></td> 
   <td align="left"><span style="font-family:Helvetica,sans-serif; font-size:14px; color:rgb(51,51,51)">2 - Severe Loss of Service</span></td> 
   <td align="left"><span style="font-size:14px; text-align:right; color:rgb(51,51,51); padding:0px 6px 0px 0px; font-weight:bold; font-family:Helvetica,sans-serif">产品版本</span></td> 
   <td align="left"><span style="font-family:Helvetica,sans-serif; font-size:14px; color:rgb(51,51,51)">11.2.0.4</span></td> 
  </tr> 
  <tr> 
   <td align="left"><span style="font-size:14px; text-align:right; color:rgb(51,51,51); padding:0px 6px 0px 0px; font-weight:bold; font-family:Helvetica,sans-serif">状态</span></td> 
   <td align="left"><span style="font-family:Helvetica,sans-serif; font-size:14px; color:rgb(51,51,51)">36 - Duplicate Bug. To Filer</span></td> 
   <td align="left"><span style="font-size:14px; text-align:right; color:rgb(51,51,51); padding:0px 6px 0px 0px; font-weight:bold; font-family:Helvetica,sans-serif">平台</span></td> 
   <td align="left"><span style="font-family:Helvetica,sans-serif; font-size:14px; color:rgb(51,51,51)">212 - IBM AIX on POWER Systems (64-bit)</span></td> 
  </tr> 
  <tr> 
   <td align="left"><span style="font-size:14px; text-align:right; color:rgb(51,51,51); padding:0px 6px 0px 0px; font-weight:bold; font-family:Helvetica,sans-serif">创建时间</span></td> 
   <td align="left"><span style="font-family:Helvetica,sans-serif; font-size:14px; color:rgb(51,51,51); white-space:nowrap">2014-3-13</span></td> 
   <td align="left"><span style="font-size:14px; text-align:right; color:rgb(51,51,51); padding:0px 6px 0px 0px; font-weight:bold; font-family:Helvetica,sans-serif">平台版本</span></td> 
   <td align="left"><span style="font-size:14px; text-align:right; color:rgb(51,51,51); padding:0px 6px 0px 0px; font-family:Helvetica,sans-serif">7.1</span></td> 
  </tr> 
  <tr> 
   <td align="left"><span style="font-size:14px; text-align:right; color:rgb(51,51,51); padding:0px 6px 0px 0px; font-weight:bold; font-family:Helvetica,sans-serif">更新时间</span></td> 
   <td align="left"><span style="font-family:Helvetica,sans-serif; font-size:14px; color:rgb(51,51,51); white-space:nowrap">2015-6-5</span></td> 
   <td align="left"><span style="font-size:14px; text-align:right; color:rgb(51,51,51); padding:0px 6px 0px 0px; font-weight:bold; font-family:Helvetica,sans-serif">基本 Bug</span></td> 
   <td align="left"><a href="https://support.oracle.com/epmos/faces/BugDisplay?id=18759589&amp;refresh=N&amp;bugProductSource=Oracle" style="font-size:14px; text-decoration:none; color:rgb(0,113,194); font-family:Helvetica,sans-serif; padding-top:5px" rel="nofollow">18759589</a></td> 
  </tr> 
  <tr> 
   <td align="left"><span style="font-size:14px; text-align:right; color:rgb(51,51,51); padding:0px 6px 0px 0px; font-weight:bold; font-family:Helvetica,sans-serif">数据库版本</span></td> 
   <td align="left"><span style="font-family:Helvetica,sans-serif; font-size:14px; color:rgb(51,51,51)">11.2.0.4</span></td> 
   <td align="left"><span style="font-size:14px; text-align:right; color:rgb(51,51,51); padding:0px 6px 0px 0px; font-weight:bold; font-family:Helvetica,sans-serif">影响平台</span></td> 
   <td align="left"><span style="font-family:Helvetica,sans-serif; font-size:14px; color:rgb(51,51,51)">Generic</span></td> 
  </tr> 
  <tr> 
   <td align="left"><span style="font-size:14px; text-align:right; color:rgb(51,51,51); padding:0px 6px 0px 0px; font-weight:bold; font-family:Helvetica,sans-serif">产品源</span></td> 
   <td align="left"><span style="font-family:Helvetica,sans-serif; font-size:14px; color:rgb(51,51,51)">Oracle</span></td> 
   <td align="left"><a href="https://support.oracle.com/epmos/faces/BugMatrix?id=18394488" style="font-size:14px; color:rgb(0,113,194); padding-top:5px; font-family:Helvetica,sans-serif" rel="nofollow">与此 Bug 相关的知识, 补丁程序和 Bug</a></td> 
  </tr> 
 </tbody> 
</table>

![t.gif][]

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

![t.gif][]

Hdr: 18394488 11.2.0.4 RDBMS 11.2.0.4 PRG INTERFACE PRODID-5 PORTID-212 18759589
    Abstract: TOO MANY CONNECTIONS OVER SHARED DBLINK AFTER UPGRADE TO 11.2.0.4
     
    *** 03/13/14 12:14 am ***
     
    PROBLEM:
    --------
    The production database system upgraded from 11.2.0.1 to 11.2.0.4 in the 
    previous weekend.
    There are several shared db links on this database to other production 
    database that its version is 11.2.0.3 .
    When trying to connect sessions on 11.2.0.4 to 11.2.0.3 via shared db links 
    it opens too many connection on connected database.
    We tested this situation between 11.2.0.3 to 11.2.0.3 databases via shared db 
    link , opened session count is two (one of them is PSEUDO other is DEDICATED 
    )
    but between 11.2.0.4 to 11.2.04 opened session count is at least 10. When 
    1000 session trying to connect 11.2.0.4 to 11.2.0.3 via shared db links about
    10000 connection opening.
     
    This system working correctly before database upgrade.
    We didn't change any parameter, listener configuration or unix parameter.
     
    DIAGNOSTIC ANALYSIS:
    --------------------
    Checked with the customer on:
    1. Why are you using shared DBLINKS and SHARED servers ?
    Because main application works with shared dblinks. (from many years ) ... We 
    didn't use shared servers , after upgrade too many connection is opening and 
    we have to use it because of handle too many connection with less resource .
     
    2. Can you use standard DBLINKS and not use shared servers ?
    We cannot use standard db links because of application design... If Oracle 
    solve this problem we can use dedicated. (before upgrade we had used 
    dedicated )
     
    When checking with application development team on why are they using shared 
    db links ;
    they said that main banking application works in webspehere connection pool 
    using XA transaction and it must use shared db link to handle connections !
    It can't use standart db links . If oracle created shared db link feature why 
    are you offer to use standard db link ? Before upgrade everything works fine. 
    I am also repeating my words , we had used dedicated server , we have to use 
    shared server because of performance issue for now . If Oracle solve opening 
    many connection issue we will use dedicated server again . But shared db link 
    feautere will remain because of websphere XA transaction handling.
     
    3. How many sessions to server B does each of your application connections to 
    Server A open ?
    Its count didn't change , anyway after upgrade it is same that the previous 
    value before upgrade.
     
    ct found metalink document why they use shared db link.
    ORA-24777 reported when using a database link from within an XA coordinated 
    transaction (Doc ID 1506756.1)
    Solution from document;
     
    Two options
     
    1. Configure the database to allow the use of shared servers and then let the 
    application use these as per the following documentation
     
    http://docs.oracle.com/cd/E11882_01/server.112/e25494/manproc003.htm#ADMIN0050
    2
     
    or establish whether the Java application needs to start an XA transaction.
     
    OR
     
    2. Define the database link being utilised as a shared database link, i.e,
     
    CREATE SHARED DATABASE LINK ..
     
    They got this error many years ago ;
    ORA-24777 reported by a JDBC/XA application when utilising a database link 
    between two Oracle databases. And db links was configured to shared db links. 
    I explained before why we are using shared server ( performance )
     
    A complete test case epxlaining how a connection from 11.2.0.4. to 11.2.0.3 
    opens alot of connections: db_link.pdf
     
    WORKAROUND:
    -----------
    none
     
    RELATED BUGS:
    -------------
    Bug 18157790 - ORA-22 AFTER UPGRADE FROM 11.2.0.3 TO 11.2.0.4
    Bug 1559258  - PSEUDO SESSION INCREASE WITH XA + SHARED DBLINK 
     
    REPRODUCIBILITY:
    ----------------
    issue is reproducible on ct's env.
     
    TEST CASE:
    ----------
    provided
     
    STACK TRACE:
    ------------
     
     
    SUPPORTING INFORMATION:
    -----------------------
     
     
    24 HOUR CONTACT INFORMATION FOR P1 BUGS:
    ----------------------------------------
     
     
    DIAL-IN INFORMATION:
    --------------------
     
     
    IMPACT DATE:
    ------------

附录为官方文档：

# 32 Managing a Distributed Database #

## Closing Database Links ##

If you access a database link in a session, then the link remains open until you close the session. A link is open in the sense that a process is active on each of the remote databases accessed through the link. This situation has the following consequences:

*  If 20 users open sessions and access the same public link in a local database, then 20 database link connections are open.
 *  If 20 users open sessions and each user accesses a private link, then 20 database link connections are open.
 *  If one user starts a session and accesses 20 different links, then 20 database link connections are open.

After you close a session, the links that were active in the session are automatically closed. You may have occasion to close the link manually. For example, close links when:

*  The network connection established by a link is used infrequently in an application.
 *  The user session must be terminated.

To close a link, issue the following statement, where *linkname* refers to the name of the link:

ALTER SESSION CLOSE DATABASE LINK linkname;

Note that this statement only closes the links that are active in your current session.

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