ETL Error - TNS:listener could not find available handler for requested type of server

Hello All,

If your ETLs are failing with the error, "TNS:listener could not find available handler for requested type of server" you will have to increase the processes in the Database by using the following commands.

alter system set processes=300 scope=spfile;    

alter system set sessions=300 scope=spfile;

This change requires a DB bounce. So, please bounce your DB server once done.

Appicable for: Oracle Only

Cheers,

Siva

Difference Between Two Dates Excluding Saturday and Sunday At Report Level In OBIEE 11g

Hey Guys,

Recently we got a requirement to find out the difference between two dates excluding the weekends i.e. Saturday and Sunday. I'm giving the report level formula that can be used to achieve this. Here the Presentation table I'm using is "Time" and the respective date columns are "Start Date" and "End Date". You need to replace these column names according to your Presentation Layer.

-TIMESTAMPDIFF(SQL_TSI_DAY,TIMESTAMPADD(SQL_TSI_DAY,-(DAYOFWEEK("Time"."End Date")-1),"Time"."End Date"),
TIMESTAMPADD(SQL_TSI_DAY,-(DAYOFWEEK(TIMESTAMPADD(SQL_TSI_DAY,6,"Time"."Start Date"))-1),TIMESTAMPADD(SQL_TSI_DAY,6,"Time"."Start Date")))/7*5+
MOD(7-DAYOFWEEK("Time"."Start Date"),6)+CASE WHEN DAYOFWEEK("Time"."End Date")-2>5 THEN 5 ELSE DAYOFWEEK("Time"."End Date")-2 END

Hope this helps!!!

Cheers,
Siva

DAC Tasks Fail With "Error: Java heap space" Fix

Hello Guys,

Today I will be discussing about a very common issue when running the DAC Execution Plans.

Many tasks in DAC EP will be failing at a time with "Error: Java heap space". There will not be any more details in the log too. To fix this issue we need to make some changes to the DAC server startup script on the DAC Unix or Windows server machine.


To resolve the issue:

1. Find out a file called startserver.sh/.bat under DAC server folder.

2. Back up this file.

3. Find an entry starting with java.... On this line, locate the -xms paramter and change it (i.e. to -Xms512m -Xmx1536m). You can increase these values according to the load on your server, though keeping huge values isn't recommended.

4. Save the file and close it.

5. Restart the DAC server and kickoff the load.

Cheers,
Siva