JDE Database Connections

[owlearner]

Hello All,

We are currently on the following setup and have an issue with Oracle Database which is really causing major impact on production functioning.

Our setup...
Enterpeise One SP1 with Tools Rel 8.96C1
Oracle Application Server 10.1.2.0.2
Oracle 10g 10.2.0.3 on RAC


The number of oracle database connections are growing on the database and at one stage the DB instance is crashing due to the reason that DB conections are not getting cleaned up after a centain amount of time.

I have done the tuning to JDBJ.INI file as suggested by Oracle (after verifying our ini file) and fall into a bigger crash than before.

Oracle suggested us to kill the connection from the backend but that document applies only to dead conections.

Could you all in the group give us some clues on how to clear the DB connections from JDE without touching the database from backend?

Thanks

Aftab

 

[klwong]

I ran into a similar problem 2 winters ago. The client in question was also running out of Oracle connections. We tuned the INIT.ORA file according to various white and red papers, but the biggest thing was setting timeouts in JDBJ.INI.

Look for things like resultsettimeout, and make sure they do not equal -1. This holds the database connection open indefinitely, and the number of connections just increases until you hit your limit.

 

[cdawes]

Post your FULL configuration!

Are you 32 bit or 64 bit?
How many users?
How many web servers? How many instances.
Post your JAS.INI and JDBJ.INI (without password)
Post your Enterprise Server JDE.INI (without password)

Also search for a document titles "rp_orcl9.2_e18.9.pdf". Got a good section on Shared vs Dedicated Servers.

Colin

 

[sputnik]

Hi,

one of our ERP instances is running also on Oracle RAC 10g , HP Itanium 64 (service guard custer) and OAS. I can recal we had similar issues after GoLive. I think we tunned the memory and max connections on the Oracle side. But to be sure I need to check tomorrow my old emials. I get back to you.

Adrian

 

[sputnik]

Hi Aftab,

I checked my old emails but the issue which we ran into was regarding the maximum open cursors exceeded.

Adrian

 

[owlearner]

Hello All,

Thanks for the replies. here i have attached the jas,jdbj and jde .ini file for your reference.

We are on Windows 2003 64Bit Oracle DB
Win 2003 EE 32 Bit for both Web and APP
we have 6 Application servers and 6 web servers with two intances (one for production and the other for non production environments)

We have 300 concurrent users and this problem started happening only from May 17th. we have gone live with one of the modules and with one custom application along. the system was running fine with no issues till to that date even with Resultset=-1 in the jdbj file.

Initially this was crashing the JVM's and then started crashing the one DB with node eviction due to the lack of resources on OS for the new connections. i have done the analysis of heap dump using eclipse memory analyser and found there is one class jde connection pool taking huge amount of memory and not realeasing the connections.

I have come across something that some asynchrounous business functions do not realease the connections on DB even after the user sign out from the system? any ideas?

i am going to chnage the result set time out setting to 5 or 10 min to see this bring any chaneg in the number of connections.


Thanks

Aftab

 

[cdawes]

Just a few comments off the top. Looks like you've been reading the tuning manuals and you've been following them exactly. There are a few changes I would make to the Global Page size (change to 20 instead of 10 and users will love you) and with the Section Size (70 is just not sensical - who adds by multiples of 70?. 100 or 50 is better)

Looking at the JDBJ.INI:

[JDBj-CONNECTION POOL]
minConnection=0 --> yep good precaution
maxConnection=50 --> based on the number of JAS instances this is fine but you can support 400 concurrent users on one single physical machine with 4 JVM's
initialConnection=1 --> Depends on your P98PWSEC setup.

Are you using User based or System Based security? ie does each user have their own Oracle database account or do users share one or more common account? How many accounts?

[JDENET]
maxNetProcesses=15 --> Way too many for 300 users what are they doing?


[JDENET_KERNEL_DEF2]
krnlName=UBE KERNEL
maxNumberOfProcesses=4 --> Yes one per processor is the general idea but unless you have HUGE amounts of batch processing 2 is enough
numberOfAutoStartProcesses=2

[JDENET_KERNEL_DEF4]
krnlName=SECURITY KERNEL
maxNumberOfProcesses=3
numberOfAutoStartProcesses=0 --> starting one would not be bad

[JDENET_KERNEL_DEF6]
krnlName=CALL OBJECT KERNEL
dispatchDLLName=XMLCallObj.dll
dispatchDLLFunction=_XMLCallObjectDispatch@28
maxNumberOfProcesses=25 --> looks good. Most people set this to an insanely high number
numberOfAutoStartProcesses=0
singleThreadedMode=Y --> WHY? ARE YOU NOT ON 8.11 SP1? I would make my code thread safe and then turn this on..........easy money


The changes above won't significanly decrease the numbe rof connections so the issue is likely in the P98OWSEC configuration or within Oracle.

You can try the above, post your P98OWSEC config methodology and take a more detailed look at Oracle.

You're running Oracle 64 bit so given enouh memory there should be no issue. How much do you have for Oracle? Is it configured to use the memory?

Post the memory config of all the servers.

Just an FYI I took a client live with a 1000+ user config (300+ concurrent) with almost an identical configuration to yours on literaly 1/2 the hardware just 3 weeks ago - no connection issues, no performance issues and tons and tons of batch jobs running (2 Enterprise Servers, 2 Oracle RAC Servers, 2 Web Servers with 6 JVM''s spread across the 2 servers).

I would have loved to have been your hardware vendor on this one!


Colin


Colin

 

[owlearner]

Hello Colin,

Thanks for the detailed explanation. I will re-visit the INI's and try to tune them accordingly.

As far as the security is concerned, previously itwas one to one connection to DB (users has their accounts in DB). i have given a try with this too by chnaging all the users to one system proxy user except for few as per the business requirement. i have gained with this change with respect to connections and DB looks better than before.

But after changing this, there is a little bit of performance degradation. i dont know whether this is due to network or just because of this change. we have seen some queries struck up and continue to run for longer periods and causing this issue.

Do we need to tune the DB if we go for Single System Proxy user for all the users. let me know if you need any config files to look at DB end...like tnsnames etc

I would like to open a private thread to discuss further on our requirement if you are intrested. please drop me an email with your contact details on [email protected]

Thanks

Aftab

 

[owlearner]

Hi,

I have done the change to ResultSet timeout in jdbj file now this is not -1 now.

is it possible to share the tuning information of INIT.ORA file with your DB configuration.

Thanks

Aftab

 

[klwong]

Oof ... this was something I did 1.5 years ago, so I don't remember the line-by-line details, and I did not keep a copy of the init file. I do remember that they were on a server with Windows 2003 Standard and 4 GB of RAM. They had around 50 users, so with the server also running JDE services for 8.11, the configuration was a little iffy. We moved them to a dedicated Oracle database server, and kept the JDE services separate.

As for tuning Oracle itself, I referred to a couple of different sources. There are 2 documents here (link). One is a Red Paper written during the PeopleSoft regime on Oracle 9.2 with E1 8.9. It's old, but the principles are still the same. The other is an Oracle White Paper on 10g R2 Architecture on Windows, and talks about how best to configure a Windows server for Oracle. Then I've got a couple of other PowerPoint presentations (source unknown or forgotten, but I suspect Oracle itself) that talk about Best Practices for Oracle on Windows and Oracle Memory COnfiguration on Windows. Try searching on Oracle's web site for these 2.

 

[Kevin_Gray]

Hi owlearner,

I'm not sure if this will help at all or if it's even related to your issue but sometimes the problem can be so simple that things get over looked. I'm not sure if the attached enterprise server jde.ini file is correct and the one you are using (minus the passwords) or not but there is a typo mistake in the following section:

[JDENET_KERNEL_DEF5]
krnlName=LOCK MANA.GER KERNEL
dispatchDLLName=jdekrnl.dll
dispatchDLLFunction=_TM_DispatchTransactionManager@28
maxNumberOfProcesses=1
numberOfAutoStartProcesses=0

Maybe this isn't the issue but maybe it is...

Kevin

 

[mangesh.gadapa]

I am facing the same issue, can you please let me know if there was a solution to this issue.