Clever Geek Handbook

ORACLE / ASP.NET: ORA-2020 - Too Many Database Links ... what is causing this?

Here's the script ...

We have an internal website that has the latest version of ODAC (Oracle Client) installed. It opens connections to a database, runs a stored procedure or wrapped method, and then disconnects. Pooling is enabled and we are currently under 11g in both our development and test environments, but 10gR2 in our production environment. This happens during production.

A few days ago, a workaround process for the ORA-2020 error began. The process is called from a web page on our internal website. The user simply sets a date, clicks a button, and the job starts on a different system that is separate from the website. However, the call itself uses the database reference to run the function.

We calculated the SQL to find that it only uses one database link. And since these links are session based and the user does not exceed the default limit of 4 as possible, we get an ORA-2020 error.

We ran a series of tests to try and enforce the default constraint of 4. ODAC, from what I remember, fires a commit after every connection, and I can't start 4 DB links, then run a chunk of SQL with 1 DB link right after any mistakes. The only way I can cause this error is to run a query with 4 db links and then a function or dynamic SQL chunk with a db link. We do not have this problem as this problem is sporadic. This does NOT ALWAYS happen.

Questions

  • Is it possible that pooling allows user B to use user A's connection after starting the initial process, thus adding the open link number if user B runs a SQL statement with a lot of database links?
  • Is this a scenario where we have to overcome our 4 limit? What are the disadvantages of increasing the quantity?
  • Do I need to explicitly close open database links before disconnecting from the database? Oracle documentation seems to suggest it should happen automatically, but "on occasion" ... doesn't.
+3


source to share


2 answers


As a result, we increased the number of links, but we did not find the reason.



+1


source


First, a simple solution: I would double check that the default reference count in the production database is actually 4.

select *
  from v$system_parameter
 where name = 'OPEN_LINKS'

Assuming you can't quit so easily:

Is it possible that pooling allows user B to use user After startup started the connection by adding the open link number to it if user B runs an SQL statement against a larger link database?

You are saying that you are explicitly closing the session, which, according to the documentation , should mean that all references associated with that session are closed. Apart from this, I admit complete ignorance in this matter.

Is this a scenario where we have to overcome our 4 limit? What are the disadvantages of increasing the number?

There are no flaws that I can think of. Tom Keith suggests , though long ago, that each open database uses 500,000 PGA memory. If you don't have them, this will obviously cause a problem, but for most situations this should be more than normal.

There are, however, unintended consequences: Imagine that you have reached that number to 100. Someone is coding something that constantly opens links and draws a lot of data across all select * from my_massive_table

or similar. Instead of 4 sessions, you have 100 sessions trying to transfer hundreds of gigabytes at the same time. Your network is dying under stress ...



There's probably more, but you get the image.

Do I need to explicitly close open database links before disconnecting from the database? The Oracle documentation seems to suggest that it should automatically happen, but "on occasion" ... doesn't.

As you noted, the best answer is “maybe not,” which doesn't help much. You don't specify exactly how you end the session, but if you kill it rather than close it gracefully, then definitely.

Using a database link spawns a child process on the remote server. Since your server is no longer in full compliance with this process, there are many things that can cause it to become an orphan or otherwise not shut down when the parent process terminates. By no means does it happen all the time, but it can and does.

I would do two things.

  • In your process, if an exception is thrown, email the results of the next query to yourself.

    select * 
  from v$dblink

    At the very least, you will at least know which database links are open in a session and how they can be tracked.

  • Follow the guidelines for documentation; in particular the following:

    "You may need to close a link manually. For example, close links when:

    • A network connection established by a link is rarely used in an application.
    • The user session must be ended.

The first seems to fit your situation exactly. If your process is independent of time, what seems to be wrong, then what do you have to lose? Syntax:

alter session close database link <linkname>
+2


source






More articles:


All Articles