We recently moved to exadata. In a day at least 3 times, users cannot login in. It shows signing in. There is no crash report.
Hi Steve,
Thank you to everyone for all your help.
We finally fixed the issue. It is quite wierd. One of the connection pools in the rpd was not checked for Enable Connection Pooling. We just tried that. That fixed the issue. Possible exadata was keeping all those sessions and not releasing them. Did not happen in earlier db versions.
Really, really appreciate all the help.
Thanks,
Paami.
@paamikumar That is great to hear. I will mark your response as the answer. I see you also made some additional improvements based on my comments in the SR. * soft nofile 131072 * hard nofile 131072. As well, as some other suggestions here: Cleaning up initialization blocks, and other items that may contribute to throughput on the system. We are happy your issue is resolved. Please do consider updating to OAS 2024 to stay within error correction policy for critical patch updates and features fixes.
We did not make any other changes.
Thank you for responding. Here is my response.
6. Checking ulimits. —-
ulimit -Ha core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited scheduling priority (-e) 0 file size (blocks, -f) unlimited pending signals (-i) 512994 max locked memory (kbytes, -l) 64 max memory size (kbytes, -m) unlimited open files (-n) 262144 pipe size (512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 real-time priority (-r) 0 stack size (kbytes, -s) unlimited cpu time (seconds, -t) unlimited max user processes (-u) 512994 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited
ulimit -a core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited scheduling priority (-e) 0 file size (blocks, -f) unlimited pending signals (-i) 512994 max locked memory (kbytes, -l) 64 max memory size (kbytes, -m) unlimited open files (-n) 4096 pipe size (512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 real-time priority (-r) 0 stack size (kbytes, -s) 8192 cpu time (seconds, -t) unlimited max user processes (-u) 512994 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited
Paami
Thank you so much Mostafa for the update. Nothing is crashing right now. Will run the query you provided when issue occurs.
We already have DEFAULT_DB_MAX_EXEC_TIME = 3600
MARKING_QUERY_MAX_LOGICAL_QUERY_EXEC_TIME = 120
Also added the following to the instanceconfig.xml. Let's see what tomorrow brings us.
<Query> <WorkerThreadSizeKb>4096</WorkerThreadSizeKb> </Query>
We ran the select count(*),program from gv$session where LAST_CALL_ET > 600 and status = 'INACTIVE' group by program; when we had the issue today.
Get only 6 records
rcu
COUNT(*)
PROGRAM
1
oraagent.bin@pdoradbadm02.eprod.com (TNS V1-V3)
java.lang.Thread
8
Oracle REST Data Services
2
com.informatica.jdbc.oraclebase.ddae
JDBC Thin Client
datawarehouse
oracle@pdoradb20corp.eprod.com (TNS V1-V3)
w3wp.exe
oraagent.bin@pdoradbadm01.eprod.com (TNS V1-V3)
9
nqsserver@pdobiapp02corp.eprod.com (TNS V1-V3)
we ran the query select count(*),program from gv$session where LAST_CALL_ET > 600 and status = 'INACTIVE' group by program; when we had the issue today. Just gets 6 records.
select count(*),program from gv$session where LAST_CALL_ET > 600 and status = 'INACTIVE' group by program;
