DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Oracle connection problem

Hybrid View

  1. #1
    Join Date
    May 2001
    Posts
    285

    Unhappy Oracle connection problem

    Hi,
    We have a customer reported that they are having connection problem with Oracle server while using our application. The problem was 'too many dead connections'.
    A few questions:
    1. How many is too many? Is there a limit being preset or set by us on the number of active/dead connection on an Oracle server?

    2. How to view the number of active/dead connections on an Oracle server? Is there a tool we can use?

    3. I am thinking to resolve their problem in this way:
    a) add resource_limit = true to init.ora file on the server
    b) create a profile with idle_time set inside.
    c) assign the profile to the db user.

    is this approach sounds fine to you?

    Thanks a lot,
    Elaine

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    What's the error number?
    Jeff Hunter

  3. #3
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843

    Re: Oracle connection problem

    1. How many is too many? Is there a limit being preset or set by us on the number of active/dead connection on an Oracle server?

    --I'm not sure here and you are referring INACTIVE session as Dead connection. As such there is no dead connection unless you kill it, it becomes Pseudo session.

    --Too many is as many you defined in the no of processes in init.ora (if its dedicated mode, if not based on your MTS config)

    2. How to view the number of active/dead connections on an Oracle server? Is there a tool we can use?

    --Check V$session view for status colum to figure out the session status

    3. I am thinking to resolve their problem in this way:
    a) add resource_limit = true to init.ora file on the server
    b) create a profile with idle_time set inside.
    c) assign the profile to the db user.

    is this approach sounds fine to you?

    --Totally Depend on the way your application works. No clue how your application works. Think about SQLNET_EXPIRE_TIME parm also. Search metalink for Deac connection detection on this parm usage, there are some limitations which stopped me to take advantage of the this parm.
    Reddy,Sam

  4. #4
    Join Date
    May 2001
    Posts
    285

    Thanks Jeff and Sam!

    Jeff --
    Error number is also I'd like to have since the customer didn't provide one. I've asked support to check with them.

    Sam --

    I have a question (maybe stupid) to ask you:
    Our application uses Oracle ODBC driver to connect to Oracle database, so does that have any implication on if we are still using SQL*Net? I think it still does, but not very clear on what's the relationship between SQL*Net and Oracle ODBC driver, i.e. how do they work together. Can you shed some light for me on this? Really appreciate it.

  5. #5
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    These 2 are different, doesn't have any implication on each other. One is API and other is oracle proprietory software for communication across database and its client. This is the general standard of Industry applications, I guess nothing to do with issues you have with dead connections

    ODBC:

    Open DataBase Connectivity (ODBC) is an Application Programming Interface (API) that allows a programmer to abstract a program from a database. When writing code to interact with a database, you usually have to add code that talks to a particular database using a proprietary language. If you want your program to talk to an Access, Fox and Oracle databases you have to code your program with three different database languages. This can be quite the daunting task causing much grief. Now, enter ODBC...

    When programming to interact with ODBC you only need to talk the ODBC language (a combination of ODBC API function calls and the SQL language). The ODBC Manager will figure out how to contend with the type of database you are targeting. Regardless of the database type you are using, all of your calls will be to the ODBC API. All that you need to do is have installed an ODBC driver that is specific to the type of database you will be using.

    Net8/SQL*Net:

    Oracle's remote data access software that enables both client-server and server-server communications across any network. Net8 supports distributed processing and distributed database capability. Net8 runs over and interconnects many communications protocols. Net8 is backward compatible with SQL*Net version 2.
    Reddy,Sam

  6. #6
    Join Date
    Apr 2001
    Location
    Louisville KY
    Posts
    295
    ODBC still uses SQL*NET/NET8/NET9 to talk to Oracle.

    It is possible to have 'dead' connections if the users re-ipl their machines or applications without cleaning quitting them. The ease of identifying these depends on the OS, protocol, etc.

    ODBC is a possible issue because this means the application may be opening multiple connections and not closing unneeded ones properly. It is not a database issue (unless you are limiting processes or sessions in init.ora or resource profiles) per se.

    I offer that the only way to have 'too many' sessions is if the sessions/resources limits are reached within the RDBMS or if the OS gives the 'cannot vfork' error. (And if you get the vfork message, it is possibly and OS issue.)
    Joseph R.P. Maloney, CSP,CDP,CCP
    'The answer is 42'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width