We have a server with Oracle 11g and a database. The network administrator added an alias domain to the ip of the server where oracle is.
We haven't changed anything else. After that, when SSRS server asks data from oracle, sometimes (not all the time) throws one of the following errors:
- ORA-12518: TNS:listener could not hand off client connection
- ORA-12520: TNS:listener could not find available handler for requested type of server
The error does not happen always, but if multiple users are trying to run a report, sometimes the error raises, sometimes not.
I researched the listener.log and I found out that there are lines:
* service_update * my_service_name * 0
Below I give some examples of the listener.log and the different servers that connect to ask data:
01-FEB-2023 15:50:25 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORAMAIL2)(CID=(PROGRAM=....EmailImageProcessingService.dll)(HOST=WinServices)(USER=windows.service))) * (ADDRESS=(PROTOCOL=tcp)(HOST=00.00.00.000)(PORT=00000)) * establish * ORAMAIL2 * 0
01-FEB-2023 15:50:31 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORAMAIL2)(CID=(PROGRAM=....EmailImageProcessingService.dll)(HOST=WinServices)(USER=windows.service))) * (ADDRESS=(PROTOCOL=tcp)(HOST=11.11.11.111)(PORT=00000)) * establish * ORAMAIL2 * 0
01-FEB-2023 15:50:31 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORAMAIL2)(CID=(PROGRAM=oracle@oracle)(HOST=oracle)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=22.22.22.2222)(PORT=9076)) * establish * ORAMAIL2 * 0
01-FEB-2023 15:50:34 * service_update * oramail2 * 0
01-FEB-2023 15:50:37 * service_update * oramail2 * 0
01-FEB-2023 15:50:39 * (CONNECT_DATA=(SERVICE_NAME=ORAMAIL2)(CID=(PROGRAM=...\ReportingServicesService.exe)(HOST=SQL2019)(USER=SQLServerReportingServices))) * (ADDRESS=(PROTOCOL=tcp)(HOST=11.11.11.1111)(PORT=00000)) * establish * ORAMAIL2 * 0
I have checked the processes and sessions and we are much below the parameter limit:
select current_utilization, limit_value ,resource_name
From v$resource_limit
Where resource_name IN ('sessions','processes');
processes
CURRENT_UTILIZATION 37 LIMIT_VALUE 800
sessions
CURRENT_UTILIZATION 41 LIMIT_VALUE 1224
Nothing else changed except that the domain name has been added from the network administrator.
Do I need to change something in the listener?