使用db link报错 ORA-2020

生产一套10.2.0.2的数据库应用日志报ORA-2020错：

[10201959][read_holder_info2 [pid=10410]][open p1cardinfo cursor error!(sqlcode=-2020)]

 

ORA-2020错发生在一个分布式事务使用的dblink数超过参数open_links定义的阀值时：

 

oracle@ibmvs_a@/oracle $ oerr ora 2020

02020, 00000, "too many database links in use"

// *Cause:  The current session has exceeded the INIT.ORA open_links maximum.

// *Action: Increase the open_links limit, or free up some open links by

//          committing or rolling back the transaction and canceling open

//          cursors that reference remote databases.

 

一个分布式查询也是一个事务。设置open_links为0时将禁用分布式事务。以下是部分官方说明：

OPEN_LINKS specifies the maximum number of concurrent open connections to remote databases in one session. These connections include database links, as well as external procedures and cartridges, each of which uses a separate process.

Oracle counts one open link for the following:

For each user that references a public or private database link

For each external procedure or cartridge connection when it is executed for the first time

Both types of connections close when the session ends. You can also close a database link connection explicitly by issuing an ALTER SESSION CLOSE DATABASE LINK statement.

You should set this parameter to allow for the external procedure and cartridge connections expected during the session plus the number of databases referred to in typical distributed transactions (that is, a single SQL statement that references multiple databases), so that all the databases can be open to execute the statement. For example, if queries alternately access databases A, B, and C, and OPEN_LINKS is set to 2, time will be lost waiting while one connection is broken and another made. Increase the value if many different databases are accessed over time.

This parameter refers only to connections used for distributed transactions. Direct connections to a remote database specified as an application connects are not counted.

If you set OPEN_LINKS to 0, then no distributed transactions are allowed.

 

       想要模拟ORA-2020错很简单，以下是一个实验例子：

 

       首先确认本数据库的open_links参数值（默认为4）。

 

oracle@ibmvs_a@/oracle/product/10.2.0/network/admin $ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Jul 1 15:48:39 2011

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining optionsSQL> show parameter link

 

NAME                                 TYPE        VALUE

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

open_links                           integer     4

open_links_per_instance              integer     4

SQL>

SQL>

SQL> show parameter global_names

 

NAME                                 TYPE        VALUE

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

global_names                         boolean     FALSE

 

在tnsnames.ora文件中创建5个需要访问的数据库的别名：

 

U1 =

 (DESCRIPTION =

   (ADDRESS_LIST =

     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.191)(PORT = 1521))

   )

   (CONNECT_DATA =

     (SERVICE_NAME = UC)

     (SERVER = DEDICATED)

   )

 )

C1 =

 (DESCRIPTION =

   (ADDRESS_LIST =

     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.191)(PORT = 1521))

   )

   (CONNECT_DATA =

     (SERVICE_NAME = CR)

     (SERVER = DEDICATED)

   )

 )

D1 =

 (DESCRIPTION =

   (ADDRESS_LIST =

     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.191)(PORT = 1521))

   )

   (CONNECT_DATA =

     (SERVICE_NAME = DE)

     (SERVER = DEDICATED)

   )

 )

J1 =

 (DESCRIPTION =

   (ADDRESS_LIST =

     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.191)(PORT = 1521))

   )

   (CONNECT_DATA =

     (SERVICE_NAME = JB)

     (SERVER = DEDICATED)

   )

 )

H1 =

 (DESCRIPTION =

   (ADDRESS_LIST =

     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.191)(PORT = 1521))

   )

   (CONNECT_DATA =

     (SERVICE_NAME = HX)

     (SERVER = DEDICATED)

   )

 )

 

创建5个public database links：

 

SQL> create public database link U1 connect to linc identified by unix using 'U1';

SQL> create public database link C1 connect to linc identified by unix using 'C1';

SQL> create public database link D1 connect to linc identified by unix using 'D1';

SQL> create public database link J1 connect to linc identified by unix using 'J1';

SQL> create public database link H1 connect to linc identified by unix using 'H1';

 

进行分布式查询：

 

SQL> select count(*) from linc.VIPDB_S_T_N@U1;

 

  COUNT(*)

----------

      1109

 

SQL> select count(*) from linc.UCDB_S_T_N@C1;

 

  COUNT(*)

----------

        20

 

SQL> select count(*) from linc.DCDB_S_T_N@D1;

 

  COUNT(*)

----------

       150

 

SQL> select count(*) from linc.JBDB_S_T_N@J1;

 

  COUNT(*)

----------

        34

 

SQL> select count(*) from linc.HXDB_S_T_N@H1;

select count(*) from linc.HXDB_S_T_N@H1

                                     *

ERROR at line 1:

ORA-02020: too many database links in use

 

出现ORA-02020报错，超过open_links设定阀值。

回滚分布式事务后，可再次查询：

 

SQL> rollback;

 

Rollback complete.

 

SQL> select count(*) from linc.HXDB_S_T_N@H1;

 

  COUNT(*)

----------

       201

 

open_links参数为静态参数，调整该参数需要重启数据库：

 

SQL> alter system set open_links=10 scope=both;

alter system set open_links=10 scope=both

                 *

ERROR at line 1:

ORA-02095: specified initialization parameter cannot be modified

 

调整方法：

alter system set open_links=10 scope=spfile;

shutdown immediate;

startup

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20750200/viewspace-701218/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/20750200/viewspace-701218/