Skip to Main Content
Forums

ORDS, SODA & JSON in the Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Multiple db connections with RESTful Services

keyweeDec 31 2013

Hi,

We have the following existing configuration:

Oracle 11gR2 / APEX 4.2.1 / Oracle Web Server (Apache that came with 10g of the Database)

We are currently migrating to the APEX Web listener.

We have installed APEX Listener 2.0.5 in Standalone mode (at the moment) and created an admin account in order to carry out the administration under SQL Developer 4.0.0.13.

(all as per the standard installation documentation)

We have configured the development database connection using the initial "apex" Database Settings through SQL Developer and this works perfectly.

We then added RESTful services and this also works in the development database.

We then added the uat and production databases and set the URL mappings appropriately (all through the SQL Developer GUI).  We were then able to connect to our UAT database and our Production database through the same instance of the listener.

The URL mapping looks like this:

Request Path  /prd

In order to access the URL here: http://server1/apex/prd/wsprd/hr/empinfo/

Now we are trying to add the ability for RESTful services to UAT and Production and we're stuck.

Using the test REST services /hr requests that come with the Sample application (and accessing the Production database - wsprd is the APEX workspace and the name of the prefix set wtihin APEX itself for RESTful services)

http://server1/apex/prd/wsprd/hr/empinfo/

We keep getting the following error (after setting the "debug.printDebugToScreen=true" property - this saved our sanity during the setup!)

503 - Service Unavailable

Request Path passes syntax validation

Mapping request to database pool: PoolMap [_failed=false, _lastUpdate=-1, _pattern=/prd/, _poolName=prd, _regex=null, _type=BASE_PATH, _workspaceIdentifier=wsprd, _serviceName=null]

Applied database connection info

Attempting to process with PL/SQL Gateway

Not processed as PL/SQL Gateway request

Attempting to process as a RESTful Service

Determining if request can be dispatched as a Tenanted RESTful Service

Request path has one path segment, continuing processing

No Tenant Principal established yet, continuing processing

APEX_LISTENER pool exists, continuing processing

The actual error message amidst the Java exception messages is:

oracle.dbtools.common.jdbc.ConnectionPoolException: The pool named: prd_al is not correctly configured, error: ORA-01017: invalid username/password; logon denied

This seems straightforward enough, but all of the accounts within the APEX Listener setup have the correct passwords and have been tested separately through SQL Plus, and the validation step in SQL Developer passes them ok.

There is a hashed password within the prd_al.xml file but I'm unsure as to how this is generated.

Has anyone managed to setup multiple database connections with RESTful services on all of them?

Thanks,

k

Comments

sb92075
Does Java application utilize connection pooling?
I suspect it does not.
Is process high water count at or near PROCESSES limit?
758670
Yes - the app uses connection pooling. It is very multithreaded, so there may be 50+ threads inserting to the database at once.

Here are some statistics, let me know if there is more that would be useful.
SQL> select name, value from v$sysstat where name like '%logon%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
logons cumulative                                                       919
logons current                                                           31

SQL> select sessions_current, sessions_highwater from v$license;

SESSIONS_CURRENT SESSIONS_HIGHWATER
----------------   ------------------
               8                 80

SQL> show parameter sessions

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size           integer     0
java_soft_sessionspace_limit         integer     0
license_max_sessions                 integer     0
license_sessions_warning             integer     0
sessions                             integer     328
shared_server_sessions               integer
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     1000
log_archive_max_processes            integer     4
processes                            integer     200
Thank you for the help.
1 person found this helpful
932095
In our application also we are facing the same issue.

We are using the DBCP datasource for the connection pool. It is not happening all the times. randomly seeing this error.

===========
Caused by: oracle.net.ns.NetException: Socket read timed out
at oracle.net.ns.Packet.receive(Packet.java:321)
at oracle.net.ns.NSProtocol.connect(NSProtocol.java:287)
at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1054)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:308)
========


Can i know what is the solution for this issue.

Edited by: 929092 on 20-Apr-2012 08:54
I would assume it may be a OS IP layer or firewall or some "smart" network device that detects connections inactive more than specific time and force closes them.

So either to configure your connection pool to close idle connections before that network layer does it, or configure "keep alive" in it.
1 person found this helpful
1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jan 28 2014
Added on Dec 31 2013
#apex
0 comments
1,275 views