2

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?

Improve this question
6
  • 1
    what is MAX_UTILIZATION of you rprocesses and sessions. I found some posts where they say that they run out of processes.
    – miracle173
    Feb 2 at 13:06
  • 2
    First, anything in the listener log that ends in zero is error code zero, as in there wasn't an error -- it worked as expected. 2nd, have you checked the tnsnames file / connection string on the SSRS server to see if it's connecting to a fully qualified service_name like ORCL.EXAMPLE.COM or ORCL.WORLD or just to the service name like ORCL?
    – CaM
    Feb 2 at 13:38
  • @miracle173 MAX_UTILIZATION is 63 --> (select max_utilization from v$resource_limit where resource_name ='processes') If I understand well, MAX_UTILIZATION is Maximum consumption of this resource since the last instance start-up, so it is below the limit value.
    – eathan
    Feb 2 at 13:51
  • 1
    yes. So you are far below the limit. MAX_UTILIZATION is the maximum since the last restart. Did you encounter the problem since the last restart?
    – miracle173
    Feb 3 at 12:09
  • yes, I have encountered the problem after the last restart. It happens some random times, lasts for some seconds, and then works fine again... and after some minutes or some hours we face it again for some seconds, and then works again fine (without having changed something)...
    – eathan
    Feb 6 at 11:31

1 Answer 1

Reset to default
0

The problem was MAX_UTILIZATION of processes, but it wasn't in the Oracle Server that was connected to SSRS !

In some reports in SSRS, there were some data that called some objects that referred data from another server (let's say Oracle Server B)! Oracle Server B sometimes reached the limit in processes, so the error raised in SSRS was from Oracle Server B, not from the Oracle Server connected to SSRS.

Below are the processes and sessions from Oracle Server B:

processes
CURRENT_UTILIZATION 591         LIMIT_VALUE  600 

sessions
CURRENT_UTILIZATION 497        LIMIT_VALUE   928

So I increased the processes limit:

alter system set processes=1200 scope=spfile;

and then I planned a database restart to implement the change.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.