Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Spring Batch - ORA 12516 SQLState 66000 - TNS:Listener could not available handler with matching protocol stack

When I run Spring batch to process more than 100 records, I am get following error,

 "Listener Refused the connection with the following error: ORA-12516,  
  TNS:Listener could not available handler with matching protocol stack". 
But when I run the batch to process less than 50 records, it works fine. 
In my batch's before step of my reader, I query DB to get records. 
e.g. If I get 100 records from DB, Using Loop, I extract a particular field from each record and using the particular fields i will query to another table. So the second query run for 100 times inside the for loop.
In Logs, I can see the batch runs for a while (queries some records inside for loop) and then it throws the error.
Please help me to solve this.
                It looks like you are opening more connections than what oracle can accept. Probably connections are not correctly closed. Please share your job configuration so we can see what could be wrong.
– Mahmoud Ben Hassine
                Aug 24, 2018 at 8:04
Oracle database server's value for "PROCESSES" has been configured too low
you can resolve it by steps 
Launch 'SQL Plus'
Logon as 'system'
Type the following command (to check that the database is using spfile):     
show parameter spfile
Assuming that it shows that you ARE using spfile, then type the following command:
alter system set PROCESSES=300 scope = spfile
Obtain some downtime (nobody using the databases) and restart the Oracle database server (or simply the relevant Oracle database) or you can edit it in notepad++
<bean id="dataSource"
    class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="${database.driverClassName}" />
    <property name="url" value="${database.url}" />
    <property name="username" value="${database.username}" />
    <property name="password" value="${database.password}" />
</bean>
to this 
<bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">
  <property name="driverClassName" value="${database.driverClassName}" />
    <property name="url" value="${database.url}" />
    <property name="username" value="${database.username}" />
    <property name="password" value="${database.password}" />
    <property name="connectionProperties"   value="initialSize=1,maxTotal=10" />
</bean> 
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.