Pages

Friday, October 7, 2011

EXTOL & Oracle Express -=- Strange Database Error ORA-12519

Hi!

Today I had a mysterious error when using EXTOL Business Integrator 2.5 I have a business process in which I have some objects that do a lookup into an Oracle Express 11g database. I'm facing to a strange behaviour. Some times, my business processe is successful and some times not.

When I go into the Log Auditor, Application and the Process tab, I'm getting this following error:

[ERROR] Transformation ended in error, please refer to the information on the Translation Message Tab.
When I look on the Translation Messages tab, Step #2: Execute Transformation - Single Output, I'm getting this error:

Message ID: 273
Primary: SQL Access error: XREF_TradingPartner_Location: The connection to DriverID: a8ece772aeaf4130be919d5e9698ebca DatabaseURL: jdbc:oracle:thin:@localhost:1521:XE is null.
Secondary:
Category: General
Type: Hard_Error
Process ID: 859
Issued by: class com.extol.transformationrules.SQLAction
Msg file: messages.emf
Issued on: Friday October 7, 2011 10:45:00.746 AM EDT

In order to have more information on the issue, I've looked into the server log file at
EBI_INSTALLATION\jboss\server\ebi\log\server.full

I've found the following stacktrace

2011-10-07 10:22:52,402 INFO [STDOUT] Caused by: java.sql.SQLException: Listener refused the connection with the following error:
ORA-12519, TNS:no appropriate service handler found
The Connection descriptor used by the client was:
localhost:1521:XE

According to Oracle, here's the definition of the error (http://ora-12519.ora-code.com/)

ORA-12519:

TNS:no appropriate service handler found
Cause: The listener could not find any available service handlers that are appropriate for the client connection.
Action: Run "lsnrctl services" to ensure that the instance(s) have registered with the listener, and are accepting connections.


At this point, it looks like Oracle Express is not accepting enough connexions. When EBI try to do some lookups in the database some times, Oracle Express ran out of available connexions.

We will verify this assumption


Step #1 - Verifying the configuration of Oracle Express

- Open Windows Command Manager and type sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on Ven. Oct. 7 11:47:30 2011

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

Enter user-name: SYSTEM
Enter password:

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

SQL> show parameter processes

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 0
db_writer_processes integer 1
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 4
log_archive_max_processes integer 4
processes integer 40


We see that the limit of concurrent processes in 40

Verify how many concurrent processes the system has actually
SQL> select count(*) from v$process;

COUNT(*)
----------
29


The hypothesis is valid.


Step #2 - Increase the value up to 200 processes

In SQLPlus,

SQL> ALTER SYSTEM SET processes=200 scope=spfile;

System altered.


Step #3 - Restart the server

You can verify that setting has been applied by doing a "show parameter processs" (see step #1)

Step #4 - Relaunch EBI's Business Processes

You can now relaunch the business processes many times in EBI to verify that the problem is solved.



No comments:

Post a Comment