topic Re: Qlik Replicate | Oracle Source | CDC in Qlik Replicate

Qlik Replicate | Oracle Source | CDC

dineshkumarl — Tue, 30 May 2023 07:42:04 GMT

Hi Team,

I am connecting to Oracle source to perform CDC only task. It was working fine earlier but now I receive error:
"Simultaneous SESSIONS_PER_USER limit".

The session limit is set to 10 at oracle's end.

Is there away by which we can control the session limit from Qlik replicate or any default number of sessions that Qlik replicate creates at oracle end?

I have 3 tables in the task currently. It was working fine when I had two tables in it.

Kindly suggest.

Re: Qlik Replicate | Oracle Source | CDC

SachinB — Tue, 30 May 2023 08:03:56 GMT

Hello @dineshkumarl ,

This error normally occurs when An attempt was made to exceed the maximum number of concurrent sessions allowed by the SESSIONS_PER_USER clause of the user profile.

You can ask the database administrator to increase the SESSIONS_PER_USER limit of the user profile.

Kindly refer to below link for the same.

https://www.dba-oracle.com/t_ora_02391_exceeded_silultaneous_sessions_user_limit.htm#:~:text=Cau

Regards,

Sachin

Re: Qlik Replicate | Oracle Source | CDC

dineshkumarl — Tue, 30 May 2023 08:08:10 GMT

Thank for your quick response. I have checked with my dba, that how I got to know the session limit is set to 10 currently.

However, I need information on what's the default number of session would a CDC only task create? and does the number of sessions increase with increase in the number of tables per task?

Can the number of sessions sent to source be controlled from Qlik Replicate's end?

My dba would not increase the number of sessions without providing complete information.

Re: Qlik Replicate | Oracle Source | CDC

sureshkumar — Tue, 30 May 2023 11:29:50 GMT

Hello @dineshkumarl

Please refer the below article regarding number of connections

Number of Connections Qlik Replicate Uses on Sourc... - Qlik Community - 2073073

Regards,

Suresh

Re: Qlik Replicate | Oracle Source | CDC

SachinB — Tue, 30 May 2023 13:10:05 GMT

Hello @dineshkumarl ,

Oracle connections from replicate machine

in regard to : 20 tasks connecting to 20 Oracle the same as having 200 tasks connecting to 200 Oracle" , 'Replicate establishes more than one connection when connecting to an Oracle source, so you should also take that into consideration.

The number of connections varies by endpoint, and may in fact vary by specific release as new requirements are implemented or optimizations are applied.

As an example, please see a list of potential connection one might see for an Oracle endpoint.

1 X metadata connection for a task

Full load:

2 connections per a used subtask (unload and LOB)

CDC:

1 LOB connection

1 SQL request connection

1 Deferred Constructor connection

If BFILE is in use – one connection per RAC node

If ASM is in use – one ASM connection per RAC node

If using parallelASMReadThreads, for ASM, you would multiply it by the number of parallelASMReadThreads.

Regards,

Sachin

Re: Qlik Replicate | Oracle Source | CDC

dineshkumarl — Tue, 30 May 2023 19:22:24 GMT

Thank you for your response. I have also referred: Solved: What's the reasons for havening many session creat... - Qlik Community - 2020237 for better understanding on the number of connections.

As per the above information and the community pages, I see the minimum connections Qlik replicate will create would be around 4.

For example: if I have one "CDC only task" connecting to Oracle source and using Replicate log reader.

The redo logs are placed in 3 different mount point, and we have created 3 different directories and reading redo logs from it.

In this task, I have 3 tables (none is parallel load, its CDC only task), the number of tables will increase in time, - - will the number of sessions created at oracle end would still be 4 or does the sessions increase with the number of tables added to the task?

- if the sessions are increased as per tables in the task, how can I determine the number of sessions with increase in number of tables?

Kindly break it down for me for better understanding.

/Thanks

Re: Qlik Replicate | Oracle Source | CDC

Bill_Steinagle — Wed, 31 May 2023 13:54:18 GMT

@dineshkumarl

Good day as per the post reply to this post if you add a Table to a Task this would cause the Table to do a Load if you have the Load option set which would create another connection (thread) for the Load. As noted in general any given Task can have up to 6 to 8 threads per Task so this also needs to be checked and confirmed depending on the number of Tasks connecting to the same Source. If you have many Tasks and are running into connection thread issues you may want to configure Log Stream Task. Create one Log Stream Task with all Tables and then you could setup multiple Replicate (Child) Tasks pointing to the Targets if there is more than one. Could the threads be orphaned threads from disconnects from the Task or hard stops? If the information provided needs more internal research you may want to open a new Support case to work with the QDI Team. Hope this is helpful.

Regards,

Bill Steinagle