Troubleshooting oracle database errors from cloud application I am trying to troubleshoot an issue that presents as different database errors such as, ORA-01000: maximum open cursors exceeded or Unable to create DB Connection.  I have reviewed the PLSQL to determine if cursors were left open and all are closed even if there is an error. 

The java application and background are as follows:
The original application was a 3-tiered system:


  GUI app. -> server app -> 11g Oracle database


The enhancement was to add an API service in a Pivotal Cloud Foundry (PCF) environment.
So this architecture was like this:


  Close Function: GUI app -> Server App -> API service -> Database.
  
  All other Functions: GUI app -> Server App -> Database.


This was put into production and run for a week without any database issues described above.
Then another enhancement was added in which the API Service communicates with several other services all in PCF in which 2 communicate with the same oracle database.  Now during heavy volume we are getting these database errors.

It seems to me that the Oracle database cannot keep up with the requests from these additional services.  But how can I demonstrate that.  We have AppD configured for the servers but not the database.  Are there queries that I can run in the prod env. that shows that these PCF applications are causing the issue?  Or should I look in another area?

Thanks,

UPDATE
I created a query that shows the total open cursor by sessionID
This is the query:

select b.sid, b.username, b.osuser, b.status, sum(a.value) total_opened_current_cursors
from sys.v_$statname c,  
     sys.v_$sesstat a,
     sys.v_$session b    
 where a.statistic#=c.statistic# and 
      b.sid=a.sid and 
      c.name in ('opened cursors current') and 
      (b.username is not null or b.username &lt;&gt; '' )   
group by b.sid, b.username, b.osuser
order by total_opened_current_cursors desc


Now, I need to link the sessionID with the application that has this session.
The osuser for the top ones is NULL.
Also, most of the sessions' status are INACTIVE
How to identify the application to the session?
Secondly, is the session is inactive, which I thought meant that no query is happending so why are there open cursors?

UPDATE
SORRY I ADDED THIS UPDATE TO THE ANSWER...SHOULD HAVE ADDED IT HERE
So, I wrote a query that returns the top 10 sessions with the highest open cursors

select * 
FROM 
(
select b.sid, b.username, b.osuser, b.status, sum(a.value) total_opened_current_cursors
from sys.v_$statname c,  
     sys.v_$sesstat a,
     sys.v_$session b    
 where a.statistic#=c.statistic# and 
      b.sid=a.sid and 
      c.name in ('opened cursors current') and 
      (b.username is not null or b.username &lt;&gt; '' )   
group by b.sid, b.username, b.osuser,  b.status
order by total_opened_current_cursors desc
)
WHERE ROWNUM &lt;= 10;


I found the SQL_TEXT that accounts for most of the open cursors...by far! (87%)
So, how do I find the query that calls this SQL?
There are at least 5 services that hit the database. Some of the services call PLSQL stored procedures some call raw SQL text.  The query that accounts for the open cursors is listed as a SELECT statement.  Does that mean it is NOT a stored procedure?  Or can this SELECT be called within the stored procedures.
How do I find the connection that uses this session?
ORA-01000: maximum open cursors exceeded is almost always an application error.  I'll put a large wager down that some part of the Java middle tier is failing to close a ResultSet in every case.  That causes it to leak cursors and will eventually cause this error.  You can increase the max_open_cursors setting in Oracle so that the cursor leak hits the limit more slowly.  You may want to combine that with more aggressive restarts of the middle tier and/or recycling of the connection pool as a temporary measure.  But the right answer would be to figure out where the application is leaking cursors and plug the leak.

Do you have any additional logging around the unable to create a connection error?  It is possible that you're hitting the maximum number of connections specified by your sessions setting (and the processes setting on which it depends).  If you really need to support more connections, you can always bump that up or move some of the connections over to shared server connections if they're using dedicated server connections today.  But I'd be concerned given the cursor error that the middle tier is also leaking connections.