Database Software

I have tried to find information about this subject. It has been difficult so I thought I'd share what I know so far. If you have any comments or corrections, please let me know.

Background (status Nov 2009):
Server: RDBMS 10.2.0.4 with RAC on 2 nodes on HP-UX
Client: Java (1.4.2) client not running in any J2EE server (actually TIBCO Businessworks 5)
Dataflow: Both directions
Connection model: always on, listening to new messages using asynchronous message listeners
Programming model: JMS 1.02b compliant - no Oracle extensions to JMS used
Message type: JMS Text (XML content)
Problem: "performance", "delays", mysterious hangs without error messages, our inferior network with too many breaks, our firewalls - like ten between client and server - insanity, I know..., server side service breaks
Client setup: OJMS and jdbc thick ( oci ) upgrading to 10.2.0.4 from 10.2.0.3 (Nov 2009)

Current status: we usually need to restart client side if there is a network problem or service break on the database server side


h3. 1. Use a 10g OJMS client with the 9.2 RAC database.
(Metalink Note:412301.1)


h3. 2. OJMS client is the aqapi13.jar file.
You can get this file by installing RDBMS (e.g. WinXP version on your laptop and copying the file from there). See ( http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14257/aq_envir.htm#sthref369 ). For the 10.2.0.4 version you need to install the patches to the RDBMS. There is no special download site for this client so you need to grab the whole db and you need to install the patches to the RDBMS to get the latest versio. In my research I found that the aqapi13.jar is same for all platforms (LINUX, Solaris, ...). Or you can ask Oracle to send you the driver via METALINK.

I recommend 10.2.0.4 version of aqapi13.jar because there is a bug fix for the message listener. I haven't noticed yet any bugfixes in the 11.xx versions. (November 2009). There is some info about changing aqapi13.jar to support "BEA WLS" instead of Oracle J2EE server. And touching many parts of the api. But I have not tested it yet. Bug 7330091: AQ-JMS NEEDS MODIFICATIONS TO INTEGRATE WITH WEBLOGIC SERVER


h3. 3. You can use either jdbc thin or jdbc oci driver with OJMS.
There are differences however. See ( http://download.oracle.com/docs/cd/B19306_01/java.102/b14355/overvw.htm#CJAJFBAI and http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14257/aq_envir.htm#i1005893 ). Download driver from jdbc faq pages. The OCI driver is packaged as instantclient. I use instantclient. http://www.oracle.com/technology/tech/oci/instantclient/index.html


h3. 4. The use of full connect descriptors
removes the reliance or necessity of the TNSNAMES.ORA file on client side. You can use a long "serverUrl" with lots of options. See below. And you can create the connection factory like this "factory = AQjmsFactory.getConnectionFactory(serverUrl, info)" See http://download.oracle.com/docs/cd/B19306_01/network.102/b14212/concepts.htm#i1041378


h3. 5. Firewalls.
See ( http://download.oracle.com/docs/cd/B19306_01/java.102/b14355/apxtblsh.htm#CHDBBDDA and METALINK Note:39357.1, Note:343325.1) Use (ENABLE=BROKEN) and tune your client OS tcp parameters. This activates the tcp keepalive functionality. Check also this https://twiki.cern.ch/twiki/bin/view/PSSGroup/OCIClientHangProtection.
You might also use some additional features on server side like the ones discussed in note "A discussion of Dead Connection Detection, Resource Limits, V$SESSION, V$PROCESS and OS processes [ID 601605.1]".
Enable Oracle Net DCD by setting SQLNET.EXPIRE_TIME=1 in the sqlnet.ora file on the server-side.

h3. 6. OJMS connections and sessions.
JDBC thin supports only one JMS session per connection. JDBC OCI supports multiple sessions per connection. See ( http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14257/jm_create.htm#sthref1364 ). I use JDBC OCI.
But it seems that the latest versions of aqapi13.jar handle both thin and OCI so that you don't have to worry.


h3. 7. Use (SERVER=DEDICATED).
If your application waits for incoming messages then you should use dedicated server processes. See (http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14257/perform.htm#sthref561 ).

h3. 8. On server side (10.2 -> ) create a service for your OJMS connections.
This service needs to prefer only one node where connections are made. It is needed for two purposes: for performance and for error recovery/failover to work in your RAC.

You can use different tools to create the service. Here is the documentation

Administering Services with Enterprise Manager, DBCA, PL/SQL, and SRVCTL
http://download.oracle.com/docs/cd/B19306_01/rac.102/b14197/hafeats.htm#BABEDIIC

Cluster-Managed Database Services
http://download.oracle.com/docs/cd/B19306_01/em.102/b31949/database_management.htm#sthref574

DBMS_SERVICE
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_serv.htm#CHDHIEBI

Services in Oracle Database 10g
http://www.oracle-base.com/articles/10g/Services10g.php

Enabling distributed transactions
http://download.oracle.com/docs/cd/B19306_01/rac.102/b14197/hafeats.htm#RACAD7120

Here is an example ( Database DB, nodes DB1 and DB2 )
# Create services.
srvctl add service -d DB -s JMS_PREFER1 -r DB1 -a DB2
srvctl add service -d DB -s JMS_PREFER2 -r DB2 -a DB1
# Set transaction handling with sqlnet
EXECUTE DBMS_SERVICE.MODIFY_SERVICE(service_name => 'JMS_PREFER1', DTP=>TRUE);
EXECUTE DBMS_SERVICE.MODIFY_SERVICE(service_name => 'JMS_PREFER2', DTP=>TRUE);
# Start services
srvctl start service -d DB -s JMS_PREFER1
srvctl start service -d DB -s JMS_PREFER2


Your client side database url might look like this
jdbc:oracle:oci:@(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=host1)(PORT=1521))
(ADDRESS=(PROTOCOL=tcp)(HOST=host2)(PORT=1521))
(ENABLE=BROKEN)
(LOAD_BALANCE=no)
(FAILOVER=yes)
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=jms_prefer1)))


h3. 9. INSTANCE_NAME
If you can't use service names to prefer certain node (see above) you can use (INSTANCE_NAME=<your instance that owns the queues>) to force access to a queue from one instance only for best performance. See (http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14257/perform.htm#sthref558 ). But then there is no failover.

With all this in place my connect descriptor (=serverUrl) looks like this:
jdbc:oracle:oci:@(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=host1)(PORT=1521))
(ADDRESS=(PROTOCOL=tcp)(HOST=host2)(PORT=1521))
(ENABLE=BROKEN)
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=DB)
(INSTANCE_NAME=DB1))) /* FORCE ALL CONNECTIONS TO NODE DB1 */


h3. 10. Performance tuning
in METALINK (Performance Tuning Advanced Queuing Databases and Applications Note:102926.1). There are detailed instructions how to do dB/AQ housekeeping.


h3. 11. OJMS and TAF.
In my earlier tests I ended up in problems. I didn't find much about this subject. See OC4J Services Guide (so it's OC4J related, but you get the idea) http://download-uk.oracle.com/docs/cd/B14099_19/web.1012/b14012/jms.htm#i1087248 . TAF http://download.oracle.com/docs/cd/B19306_01/network.102/b14212/advcfg.htm#sthref1275 and http://www.ardentperf.com/2007/02/22/centralized-taf-configuration-in-10g/

Latest tests in November 2009 look however promising using 10.2.0.4 client and database.

h3. 12. OJMS and RAC issues.
There is a article in Metalink(Note:468999.1). Short version here. Applies to: 8.1.7.0.0 to 11.1.0.6.0

The JMS Message Listener code requires an Exception Listener to be created to monitor the established database connection.

By default the JMS message listener only checks whether the session close is called and not whether the connection is available. An Exception Listener is required to monitor the database connection itself. And here you must use the service that prefers only one node as described in point 8 above. Otherwise your message listener might not receive exceptions.

There is an example where the logic is following: if you get any JMS exception always close the connection and re-establish the connection.


h3. 13. OJMS and RDBMS 9.2.0.x on multiple instances

Check the following articles in Metalink
1) Slow Dequeue from AQ Queues Using RAC Database Note:312379.1
2) Dequeue Performance is poor in a RAC environment when Enqueue is performed on a Different Node Note:365807.1

When using multiple instances (dequeue/enqueue on different nodes) you should switch on global posts.

Dequeue does not see enqueue from another RAC/OPS node for 5 minutes. For 9.2.x you must set lmglobal_posts = TRUE to enable this fix. Solution:
Add internal paramter lmglobal_posts = TRUE to all nodes in the RAC instance.

To enable it:
SQL> connect / as sysdba
SQL> alter system set "_lm_global_posts"=true scope=spfile;
and bounce both nodes.

In 10g this is already on.


h3. 14. How to improve AQ performance using RAC?

RAC can be used to improve AQ performance by allowing different queues to be managed by different instances. You do this by specifying different instance affinities (preferences) for the queue tables that store the queues. http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14257/jm_create.htm#i1006177

You can think that each queue is "owned" by certain instance and "all operations" related to that queue are executed in the owner instance.

To optimize the client architecture you should connect all your clients (PL/SQL, OJMS etc.) for certain queue_table to the respective instance only. See the discussion above in point 8 to prefer connections to one node only.

You can check current instance affinity settings with
select queue_table, primary_instance, secondary_instance, owner_instance from dba_queue_tables;

You can set PRIMARY and SECONDAY instances using
DBMS_AQADM.ALTER_QUEUE_TABLE (queue_table=>'<qtablename>', primary_instance => <instance#>, secondary_instance => <instance#>);

Or when you create the queue table with DBMS_AQADM.CREATE_QUEUE_TABLE. See http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14257/aq_admin.htm#i1008361

If you don't set the primary or secondary instance (=0) then the owner_instance is set "runtime". In my environment I see that instance ownership is assigned to instance 1 always if primary setting was 0. I guess that the boot order of nodes influences this.


h3. 15. Client side settings
when using 10.1-10.2 client jars (Metalink Note 388291.1)

The message listener implementation has changed in 10.2; the listener is now polling the queue (rather than sleeping pending a notification message being received) with the sleep time increasing up 15000 millisecs.

Delays in notification observed for JMS Message listeners in a 10g environment. The default polling interval used by 10g clients was too large and needed to be reduced.

Tune the polling mechanism used by 10g JMS Message listeners by manipulating the
following properties (milliseconds) in your JMS code

System.setProperty("oracle.jms.minSleepTime","200");
System.setProperty("oracle.jms.maxSleepTime","500");

You alter the min and max sleep times so that you minimise delay on notification of messages arrival.


h3. 16. JMS Compliance

In Oracle Database 10g, Oracle JMS conforms to the Sun Microsystems JMS 1.1 standard. You can define the J2EE compliance mode for an Oracle Java Message Service (OJMS) client at runtime. For compliance, set the Java property oracle.jms.j2eeCompliant to TRUE e.g.

System.setProperty("oracle.jms.j2eeCompliant","TRUE")

For noncompliance, do nothing. FALSE is the default value. See http://download.oracle.com/docs/cd/B19306_01/server.102/b14257/jm_create.htm#CACCJDBI

h3. 17. 10.2.0.4

Check out the 10.2.0.4 jdbc drivers and latest aqapi13.jar available at least in latest WinXP 10.2.0.4. ojdbc14.jar, orai18n.jar and aqapi13.jar have changed recently. You can compare different jar file versions with a tool like http://www.extradata.com/products/jarc/ to see if there are any differences.


Enjoy,

Matti

Edited by: Matti on Dec 20, 2009 2:51 PM