Oracle 11g DRCP: Database Resident Connection Pooling - second attempt

In my last post I wrote about unsuccessful attempt to check functionality of DRCP: Database Resident Connection Pooling. Now it's time for success story :) The problem with previous situation was, that Oracle was not fully functional. And that was because I manually set, that i have all proper versions of linux packages, what was not true.
Now I've installed Oracle Enterprise Linux v 5.0 and Oracle 11g over it, with some help of Tim instructions.
I've added entry in TNSNAMES.ORA on linux box:

DB11GPOOL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.194.111)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DB11G)(SERVER=POOLED)
    )
  )

And tried to connect to pool

[oracle@localhost admin]$ sqlplus pooltest/pool@db11gpool
SQL*Plus: Release 11.1.0.6.0 - Production on Wed Aug 29 01:44:53 2007
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
ERROR:
ORA-12520: TNS:listener could not find available handler for requested type of server

So I've started connection pooling (as SYS at Oracle):

exec DBMS_CONNECTION_POOL.START_POOL();

And tried again:

[oracle@localhost admin]$ sqlplus pooltest/pool@db11gpool
SQL*Plus: Release 11.1.0.6.0 - Production on Wed Aug 29 02:02:37 2007
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select 1 from dual;
         1
----------
         1

This time successful login.
Now I've left this session untouched for some time (more then 300 second - default time-out for pool)

SQL> select 1 from dual;
select 1 from dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 2821
Session ID: 125 Serial number: 21

As you can see, session was timed-out by pool, and disconnected.
Ok, lets change parameters for pool to observer behaviour, when max number of session is reached.
Execute it as SYS:

begin
 DBMS_CONNECTION_POOL.ALTER_PARAM ('','MINSIZE','1');
 DBMS_CONNECTION_POOL.ALTER_PARAM ('','MAXSIZE','4');
end;
/

After I set it, I'was able to have 3 (not 4) simultaneous sessions using Connection pool. Whenever I start 4-th or 5-th session it is waiting for connection. You do not get any error, session just hung up. When one of active session is released (closed or timed-out) then one of waiting sessions is being connected. When I was looking for explanation, I found out one that in view V$CPOOL_STATS:

• NUM_OPEN_SERVERS=4 - Total number of busy and free servers in the pool (including authentication servers)
  
• NUM_BUSY_SERVERS=3 - Total number of busy servers in the pool (not including authentication servers)
  
• NUM_AUTH_SERVERS=1 - Number of authentication servers in the pool
  

So, we have to have at least 1 authentication server in our pool. We should be aware of this when we setup small number of MAXSIZE of pool.
When I first read about database connection pooling I was thinking that when you start using all connections in pool, than next attempt to connect will open new normal session. But it will not be good. Mechanism of queues is better, because client will not overload database server. Might be it's not good for clients, as they might wait long time. On the other hand in well written application sessions should be short and closed immediately when no more needed in request.
More tests of DRCP in next posts...
Cheers, Paweł

DRCP: Database Resident Connection Pooling

Finally I've found time and managed to install Oracle 11g. I did it on Fedora Core 5 run in VMWare Server on Windows 2K Pro.
My first attempt was to install it on Fedora Core 6 but I didn't succeed, and even I managed to corrupt VM totally. My second try was to create new VM with FC5. I've applied all available updates, and tried to install Oracle 11g. Oracle gave warning that some packages do not match requirements, but after manually marking check-box it proceeded and installed successfully. And seems to be fully functional. Do not try this for production DB :)
After that I've spend lot of time trying to connect to this DB from my Windows box. I was reconfiguring VM Network setting, FC Network setting many times without any sign of progress. Finally I found out that it can be firewall that is causing problem. It was really silly that I spend so much time to solve so simple problem...
Any way my Oracle 11g is up and running, so I started to test one of th features that are in my interest: DRCP: Database Resident Connection Pooling.
I've enables DRCP:

SQL> EXECUTE DBMS_CONNECTION_POOL.START_POOL();
PL/SQL procedure successfully completed.

And created sample user for tests:

SQL> create user pooltest identified by pool ;
User created.
SQL> grant connect to pooltest;
Grant succeeded.

I've added entry in TNSNAMES.ORA on Windows box:

DB11GPOOL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.194.111)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DB11G)(SERVER=POOLED)
    )
  )

And tried to connect using POOLED server:

c:\Temp>sqlplus pooltest/pool@db11gpool

SQL*Plus: Release 10.2.0.2.0 - Production on Mon Aug 27 22:30:08 2007

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

ERROR:
ORA-56606: DRCP: Client version doesnot support the feature

So, DRCP needs proper client version to work. It's not a good news, as I thought that this will not require any changes at client side :(
I have to make my test on single box, or install Oracle on one more box (VM). Ok, first try from the same box where DB installed:

[oracle@vm111 admin]$ sqlplus pooltest/pool@db11gpool

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Aug 27 22:41:51 2007

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Aborted

Seems that I have to dig little more more to make it working.

Cheers, Paweł

UPDATE: Success story on DRCP in next post.

It's feature not a bug ;)

Friend of mine send me an link to funny picture:


Cheers, Paweł

Oracle 11g is released - my links

As you probably know, Oracle 11g is released - now only for linux x86. I've already downloaded it, but had no time to install and play with it. Anyway I've decided to make summary of bloggers post on 11g for my future reference:

• Oracle 11g Documentation
• Oracle 11g new parameters by Howard Rogers "dizwell"
• Oracle 11g new parameters part 2 by dizwell
• Oracle 11g new parameters part 3 by dizwell
• Bind variable peeking in 11g by Yas
• Invisible Indexes by Yas
• Security for XMLDB (DBMS_NETWORK_ACL_ADMIN) by Marco Gralike
• 11g Native Web Services by Paul Gallagher (Tardate)
• FOLLOWS Clause in Create Trigger Statement by Jurgen Kemmelings
• New PL/SQL Compiler Warning in Oracle 11g by Jurgen Kemmelings
  
• Oracle 10.2 versus 11.1 by Julian Dyke
• Oracle11g: Where’s My Alert Log? by Kevin Closson
• Oracle Database 11g Release 1 (11.1) Installation On Enterprise Linux 4.5 and 5.0 by Tim Hall
  
• Oracle 11g Release 1 RAC On Linux Using NFS by Tim Hall
  

There is much more posts about Oracle 11g but those listed above got by attention.

Cheers, Paweł