Dear All,
I have executed the update statistics on all tables using brtools. It got terminated as it has failed to collect statistics for certain tables whose size is large. The reason for failure was "SYSTEM" tablespace was getting full. In our system, default temporary tablespace was set to "SYSTEM" instead of PSAPTEMP.
We have daily scheduled the CheckDB and Update statistics from DB13 at 3 AM. This was running fine till i had executed the update statistics using brtools. Now it is failing with return code 0003 saying failed to connect the database. Reason for this error, the respective sta and chk file was getting generated from user prdadm and group sapsys. Earlier i.e. before i had executed the update statistics using brtools all the chk and sta files were getting generated with user oraprd and group prdadm. You can refer the below list of chk and sta files for past few days.
sap-prd:prdadm 11> ls -ltr *.sta
-rw-r
r
1 oraprd sapsys 39060 Mar 31 05:11 cecxcyuj.sta
-rw-r
r
1 oraprd sapsys 33003 Apr 7 06:04 cecyljnb.sta
-rw-r
r
1 oraprd sapsys 39867 Apr 14 04:58 cecztudx.sta
-rw-r
r
1 oraprd sapsys 24857 Apr 20 04:57 cedaxhbk.sta
-rw-r
r
1 oraprd dba 921816 May 15 06:03 cedfosqq.sta
-rw-r
r
1 prdadm sapsys 522 May 18 18:06 cedghuur.sta
-rw-r
r
1 prdadm sapsys 522 May 19 10:10 cedglciw.sta
-rw-r
r
1 prdadm sapsys 522 May 19 10:18 cedgldbf.sta
-rw-r
r
1 prdadm sapsys 522 May 19 10:21 cedgldht.sta
Log of chk files u2013
-rw-r
r
1 oraprd sapsys 43792 May 13 03:01 cedfgdib.chk
-rw-r
r
1 oraprd sapsys 43791 May 14 03:04 cedflbde.chk
-rw-r
r
1 oraprd dba 921816 May 15 06:03 cedfosqq.sta
-rw-r
r
1 prdadm sapsys 659 May 17 03:00 cedfzuoi.chk
-rw-rw---- 1 prdadm sapsys 659 May 18 03:00 cedgesjj.chk
-rw-r
r
1 prdadm sapsys 522 May 18 18:06 cedghuur.sta
-rw-r
r
1 oraprd dba 44268 May 18 18:09 cedghuzu.chk
-rw-rw---- 1 oraprd sapsys 659 May 19 03:00 cedgjqeq.chk
-rw-r
r
1 prdadm sapsys 522 May 19 10:10 cedglciw.sta
-rw-r
r
1 oraprd dba 44268 May 19 10:17 cedglcwr.chk
-rw-r
r
1 oraprd dba 141760 May 19 10:17 connPRD.log
-rw-r
r
1 prdadm sapsys 522 May 19 10:18 cedgldbf.sta
-rw-r
r
1 prdadm sapsys 522 May 19 10:21 cedgldht.sta
I had tried to execute the command line of checkDB and update statistics from os leve using oraprd user and it finished successfully. However, my automated schedule of 3 AM is still failing with the same reason.
Note - I had executed the update statistics using brtools on 14th May. You can refer the respective sta and chk files with time stamp 06:03 and date 15th May. After that run all the stats are getting executed under user prdadm.
I have executed my update stats using brtools again after changing the default temporary tablespace. It has finished successfully. Even after that my checkDB and update statistics using brtools have failed with the same reason.
Please, advice me on the same.
Thanks and Regards,
Kedar