ORA-12801: error signaled in parallel query server P000 ORA-01116: error in opening database file 189 ORA-01110: data file 189 ORA-27077: too many files open

 Error: During rebuild index getting the following error

 ORA-12801: error signaled in parallel query server P000
ORA-01116: error in opening database file 189
ORA-01110: data file 189: '/u02/app/oracle/oradata/bachstdb/micpdb/users04.dbf'
ORA-27077: too many files open

Changes:

There is no changes in the system .
 

Cause:

open files for oracle user is set to very low value 1024 .

Solution:

Last login: Tue Jul 11 08:07:39 2023 from 10.11.208.115
[oracle@DBBACH ~]$ ulimit -n
4096
[oracle@DBBACH ~]$

[oracle@DBBACH ~]$ 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) 1027294
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) 10240
cpu time               (seconds, -t) unlimited
max user processes              (-u) 2047
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited
[oracle@DBBACH ~]$

[oracle@DBBACH ~]$ vim /etc/security/limits.conf

oracle              soft    nproc   2047
oracle              hard    nproc   16384
oracle              soft    nofile  4096
oracle              hard    nofile  65536
oracle              soft    stack   10240

change the value from 4096 to 65536

$su - oracle

$ ulimit -n
65536

Now please execute index rebuild command

IMPORTANT: Be aware that increasing the limit directly/randomly will have impact on system resource as well
so you need to test it whether your system is feasible for increasing the limits.

ORA-00600: internal error code, arguments: [qosdExpStatRead: expcnt mismatch], [], [], [], [], [], [], [], [], [], [], []

 Reason of the error:

This is due to sys.exp_obj$. EXP_CNT mismatch rows of sys.exp_stat$

 

Try workaround of MOS  Note 28681153.8  at both CDB and PDB. 

Solution

 Ensure that you have taken a backup of your system before applying the recommended solution.

 

Below SQL is used to check issue data of some objects. if there are some rows return, that means data issue

With b as (
select count(*) cnt,objn,snapshot_id from sys.exp_stat$ es group by objn,snapshot_id)
select * from sys.exp_obj$ a, b where a.objn=b.objn and a.snapshot_id=b.snapshot_id
and a.EXP_CNT<>b.CNT;

 Find the details of the object

select  * from dba_objects where object_id='74329'

 

 

update sys.exp_obj$ a set exp_cnt=(select count(*) from sys.exp_stat$ b where
a.objn=b.objn and a.snapshot_id=b.snapshot_id ) where a.objn='74329';

 

 

With b as (
select count(*) cnt,objn,snapshot_id from sys.exp_stat$ es group by objn,snapshot_id)
select * from sys.exp_obj$ a, b where a.objn=b.objn and a.snapshot_id=b.snapshot_id
and a.EXP_CNT<>b.CNT;