Unable to connect to specific instance of Oracle RAC

Question

I did several operations by connecting successfully to all 4 instances but suddenly during one of the jobs, it failed with a TNS error. I checked the connect from SQL and one of the nodes is giving an error.

This is puzzling as I have done more than 20 operations just before this by connecting to specific instances, and on the 21st run this suddenly occurred.

I never made any changes anywhere and this just occurred suddenly! Any ideas?

SQL> connect system/oracle@realDB_1 as sysdba;

ERROR:

ORA-12520: TNS:listener could not find available handler for requested type of server


SQL> connect system/oracle@realDB_2 as sysdba;

Connected.

SQL> connect system/oracle@realDB_3 as sysdba;

Connected.

SQL> connect system/oracle@realDB_4 as sysdba;

Connected.

SQL> connect system/oracle@realDB_1 as sysdba;

ERROR:

ORA-12520: TNS:listener could not find available handler for requested type of 
server

Answer 1

If your TNS entry is incorrect for your current configuration, for example you specify (SERVER = SHARED) in your connection descriptor, but you have not configured shared servers, you will receive this error.

But since you said you had not changed anything and this error had occurred suddenly, I am going to assume your configuration is correct. In that case, the database instance may have reached the limit for the processes it can start. You should check the alert log for ORA-00020 errors, and query the highest ever (since the last restart) process utilization of your instance:

select max_utilization, limit_value from gv$resource_limit where inst_id = 1 and resource_name = 'processes';

If you find ORA-00020 errors, or max_utilization equals to limit_value in the output of the above query, the instance reached the limit at some point, and you may need to increase the value of the processes parameter or investigate the reason for having such number of processes.