Sometimes when Run an ETL get errors like ORA-12516 or ORA-12519 or ORA-12520

This article describes how to troubleshoot the ORA-12516 and ORA-12519 errors:

• ORA-12519: TNS: no appropriate service handler found
• ORA-12516: TNS: listener could not find available handler with matching protocol stack
• ORA-12520: TNS:listener could not find available handler for requested type of server

If you find these errors in your TSCO logs (ETL engine, Application Server or ETL execution logs), ask your DBA to perform the following actions on your Oracle database:

1. verify that database has free space on its disks
2. modify two parameters to increase the number of processes and sessions

How to modify the processes and sessions parameters

The suggested values for a standard small or medium size installation are the following:

• processes=280
• sessions=300

Applying these settings requires a restart of the Oracle DB instance; you will need to open a maintenance window and stop all BCO processes and ETLs on the BCO Application Server and ETL Engine.

To apply the new settings, follow these steps:

1. access the BCO Console, navigate to Administration > Scheduler > ETL tasks and verify that no ETL tasks are running; set the filters as highlighted in the figure:
   
2. log in to your BCO machines (Application Server and ETL Engine) as BCO OS user and stop all BCO processes using the command $CPITBASE/cpit stop
   
3. log in to the database as sysdba via sqlplus using the command sqlplus '/as sysdba';

   Ask your DBA to execute the necessary operations on the database, since appropriate skills and sysdba permissions are needed

   Replace CAP with your SID
   
4. apply the new settings issuing the following commands:

   alter system set processes=280 scope=spfile;
   alter system set sessions=300 scope=spfile;

   
5. stop the database instance using the command shutdown immediate;
   
6. restart the instance using the command startup;
   
7. exit sqlplus using the command quit;
   
8. log in to your BCO machines (Application Server and ETL Engines) as BCO OS user and restart all BCO processes using the command $CPITBASE/cpit start
   
9. wait 10 to 15 minutes for the Component status checker system task to complete its execution
10. verify that your BCO instance was restarted correctly, checking the status of BCO components from the BCO Console, accessing the Administration > System > Status page