TNPM Wireless "No ManagedConnections available within configured blocking timeout" error for scheduled Reports.

This issue can be Resolved by increasing pool connections size.
Below are the steps for increasing pool connections.
Changes need to be done in all 3 files.
 
1) /appl/virtuo/as/server/default/deploy/jms/jms-ds.xml
Increase by 20
 <max-pool-size>40</max-pool-size>
 
2) /appl/virtuo/as/server/default/deploy_core/na-defds-ds.xml
Increase by 30
                                
3) /appl/virtuo/as/server/default/deploy/hsqldb-ds.xml
Increase by 30
 
4) /appl/virtuo/as/server/default/deploy_core/na-alarm-ds.xml
Increase by 30
 
To make the changes effective, "as" service needs to be restarted.

If issue is not resolved by increasing the pool connections, then check on below suggestion.

•    Check the SID  which is blocking other sessions.

•    Use below queries for identifying the blocking sessions.
 
•    alter session set nls_Date_format = 'DD-MON-YY HH24:MI:SS';
 
1.     Identify blocking session:
 
           select l1.sid, ' IS BLOCKING ', l2.sid
           from v$lock l1, v$lock l2
           where l1.block =1 and l2.request > 0
           and l1.id1=l2.id1
           and l1.id2=l2.id2;
              
           You will get output like below :
 
            364 IS BLOCKING    2711
 
 
2.     Put the ids obtained from above query in below query:
 
           select * from
           v$session s , v$sql s1
           where s1.sql_id =s.sql_id
           and s.sid in (, ,);
           
•    Once you identified the blocking SID investigate the SID from the database queries.
 try to capture SQL ID’s stuck and tables which are getting locked at that time of issue.
•    If No blocking session is captured then check the no of reports, sbh and summary running to    identify the trend of reports.
•    See if there are any long running reports which are causing slowness in database and consuming more time than its regular time.

•    You can use below queries to find out the no of reports, SBH, summary and other database contents.

1.    alter session set nls_date_format= 'DD-MM-YY HH24:MI:SS'
 
2.    select sbh_name, last_run, last_run_result, LAST_RUN_SECS, last_run_text from sbh_definition def, sbh_history hist where def.sbh_id = hist.sbh_id;
 
3.    select distinct a.report_id, a.schedule_id, a.report_name, b.job_id, c.task_id, to_char(c.tstamp,'dd-MM-yyyy hh24:mi:ss'), to_char(c.started,'dd-MM-yyyy hh24:mi:ss'), to_char(c.ended,'dd-MM-yyyy hh24:mi:ss'), round((c.ended - c.started) * 86400) duration, c.status_code, c.message
                                from pe_report_schedule_vw a, active_job b, task_status c
                                where a.schedule_id = b.schedule_id
                                and b.job_id = c.job_id
                                and c.tstamp <= sysdate
                                and c.message != 'Scheduled to run'
                                and c.tstamp >= sysdate - 1
                                order by nvl(to_char(c.started,'dd-MM-yyyy hh24:mi:ss'),to_char(c.tstamp,'dd-MM-yyyy hh24:mi:ss')) desc, to_char(c.tstamp,'dd-MM-yyyy hh24:mi:ss') desc, c.task_id asc;
 
4.        select H.SUMMARY_HISTORY_ID, H.SUMMARY_ID, D.SUMMARY_NAME, H.SUMMARY_INTERVAL, H.FIRST_AVAIL, H.LAST_AVAIL,H.LAST_RUN_START,H.LAST_RUN_END, (H.LAST_RUN_END - H.LAST_RUN_START)*24*60 AS difference_in_minutes, H.LAST_RUN_RESULT,H.LAST_RUN_TEXT,H.LAST_SUCCESSFUL_RUN
                  from summary_history H,summary_definition D
                  where H.summary_id=D.summary_id;
 
5.        select *
                                from pe_report_schedule_vw a, active_job b, task_status c
                                where a.schedule_id = b.schedule_id
                                and b.job_id = c.job_id
                                and c.tstamp <= sysdate
                                and c.message != 'Scheduled to run'
                                and c.tstamp >= sysdate - 1
                                order by nvl(to_char(c.started,'dd-MM-yyyy hh24:mi:ss'),to_char(c.tstamp,'dd-MM-yyyy hh24:mi:ss')) desc, to_char(c.tstamp,'dd-MM-yyyy hh24:mi:ss') desc, c.task_id asc;
 
6.        select *
                  from summary_history H,summary_definition D
                  where H.summary_id=D.summary_id;

Use below queries to find long running sessions ( need to be executed when issue is encountered )

7.        select *
                 from v$session s, v$sql q
                 where s.sql_id = q.sql_id;
 
8.        SELECT *
                 FROM v$session_longops sl
                 INNER JOIN v$sql sq ON sq.sql_id = sl.sql_id
                 INNER JOIN v$session s ON sl.SID = s.SID AND sl.serial# = s.serial#
                 WHERE time_remaining > 0;
 
 
9.        select * from V$SQL_MONITOR;
 
10.        select * from v$session_longops  s, v$sql s2
                            where  s.sql_id = s2.sql_id;
11.        select * from
                 v$active_session_history  s, v$sql s2 where  s.sql_id = s2.sql_id;
 
12.        select *
                 from v$session b, v$process a
                 where
                 b.paddr = a.addr
                 and type='USER' ;

From the report query identify the number of connections at the time of issue and compare to other time in the day.
Consult your DBA if a SQL session which is blocking other sessions and backtrack to the job which is executing the SQL. For time-being you may disable the job and investigate the cause.

As a workaround for blocking sessions , if the SQL is executed by any of the application job you can try restarting "as", which will reset the pool connection and also end the blocking session.