Checkdb and update stats failing in Db13

[Former Member]

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-rr 1 oraprd sapsys 39060 Mar 31 05:11 cecxcyuj.sta

-rw-rr 1 oraprd sapsys 33003 Apr 7 06:04 cecyljnb.sta

-rw-rr 1 oraprd sapsys 39867 Apr 14 04:58 cecztudx.sta

-rw-rr 1 oraprd sapsys 24857 Apr 20 04:57 cedaxhbk.sta

-rw-rr 1 oraprd dba 921816 May 15 06:03 cedfosqq.sta

-rw-rr 1 prdadm sapsys 522 May 18 18:06 cedghuur.sta

-rw-rr 1 prdadm sapsys 522 May 19 10:10 cedglciw.sta

-rw-rr 1 prdadm sapsys 522 May 19 10:18 cedgldbf.sta

-rw-rr 1 prdadm sapsys 522 May 19 10:21 cedgldht.sta

Log of chk files u2013

-rw-rr 1 oraprd sapsys 43792 May 13 03:01 cedfgdib.chk

-rw-rr 1 oraprd sapsys 43791 May 14 03:04 cedflbde.chk

-rw-rr 1 oraprd dba 921816 May 15 06:03 cedfosqq.sta

-rw-rr 1 prdadm sapsys 659 May 17 03:00 cedfzuoi.chk

-rw-rw---- 1 prdadm sapsys 659 May 18 03:00 cedgesjj.chk

-rw-rr 1 prdadm sapsys 522 May 18 18:06 cedghuur.sta

-rw-rr 1 oraprd dba 44268 May 18 18:09 cedghuzu.chk

-rw-rw---- 1 oraprd sapsys 659 May 19 03:00 cedgjqeq.chk

-rw-rr 1 prdadm sapsys 522 May 19 10:10 cedglciw.sta

-rw-rr 1 oraprd dba 44268 May 19 10:17 cedglcwr.chk

-rw-rr 1 oraprd dba 141760 May 19 10:17 connPRD.log

-rw-rr 1 prdadm sapsys 522 May 19 10:18 cedgldbf.sta

-rw-rr 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

[Former Member · Accepted solution]

HI ,

Seems to be interesting...

Have you tried to delete the existing job, schedule new one and then check logs again.

Also check if you are able to run checkdb with user prdadm on os level.

Regards,

[Former Member · Accepted solution]

No I havent deleted the existing job and reschedule it. However, I have tried to execute in manullay from DB13 using start immediately option but the result is the same.

Sorry for complete chaos in the message. I should have check the preview.

Thanks and Regards,

Kedar

[Former Member · Accepted solution]

I am specifying my query again due to the chaos at 1st go.

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 *.chk

-rw-rr 1 oraprd sapsys 43792 May 13 03:01 cedfgdib.chk

-rw-rr 1 oraprd sapsys 43791 May 14 03:04 cedflbde.chk

-rw-rr 1 oraprd dba 921816 May 15 06:03 cedfosqq.sta

-rw-rr 1 prdadm sapsys 659 May 17 03:00 cedfzuoi.chk

-rw-rw---- 1 prdadm sapsys 659 May 18 03:00 cedgesjj.chk

-rw-rr 1 prdadm sapsys 522 May 18 18:06 cedghuur.sta

-rw-rr 1 oraprd dba 44268 May 18 18:09 cedghuzu.chk

-rw-rw---- 1 oraprd sapsys 659 May 19 03:00 cedgjqeq.chk

-rw-rr 1 prdadm sapsys 522 May 19 10:10 cedglciw.sta

-rw-rr 1 oraprd dba 44268 May 19 10:17 cedglcwr.chk

-rw-rr 1 oraprd sapsys 522 May 19 10:18 cedgldbf.sta

-rw-rr 1 prdadm sapsys 522 May 19 10:21 cedgldht.sta

-rw-rr 1 oraprd dba 44040 May 19 12:04 cedglmji.chk

-rw-rr 1 oraprd dba 195540 May 19 21:47 cedglood.sta

-rw-rw---- 1 prdadm sapsys 659 May 20 03:00 cedgonzq.chk

-rw-rr 1 prdadm sapsys 522 May 20 10:22 cedgqbhg.sta

I have tried to execute the command line of checkDB and update statistics from os level using oraprd user and it finished successfully. However, my automated schedule of 3AM is still failing with the same reason.

Edited by: Kedar Malekar on May 20, 2010 7:45 AM

[Former Member · Accepted solution]

Continuring with earlier reply.

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

[Former Member · Accepted solution]

HI,

Have you checked in DBCHECK is running fine with user prdadm on os level.

Is this a central system or distributed one!

Regards,

[Former Member · Accepted solution]

Hi,

Some time, problem occurs in DB13 but not in terminal as ora<sid> user.

The reason for this mostly incorrect permission of BRTools..

Refer the note for the same

[113747 - Permissions for DBA tools BRTools and SAPDBA.pdf|https://service.sap.com/sap/support/notes/113747]

Once this is verified as per the note, you can look somewhere for your problem.

Regards.

Rajesh Narkhede

[Former Member · Accepted solution]

Gagan - Its a central system. We have only one application server.

Rajesh - I will go through the notes and revert to you with my remarks.

Thanks and Regards,

Kedar

[Former Member · Accepted solution]

Rajesh,

I have gone through the note 113747 and I feel the permissions are set correctly.

Please find the permissinos as set below.

sap-prd:prdadm 1> cd /sapmnt/PRD/exe

sap-prd:prdadm 2> ls -ltr br*

-rwxrwxr-x 1 prdadm sapsys 1480656 Oct 11 2008 brrestore

-rwxrwxr-x 1 oraprd sapsys 3437688 Oct 13 2008 brarchive

-rwxrwxr-x 1 oraprd sapsys 3547760 Oct 13 2008 brbackup

-rwxrwxr-x 1 oraprd sapsys 4632408 Oct 13 2008 brconnect

-rwxrwxr-x 1 prdadm sapsys 3850224 Oct 13 2008 brrecover

-rwxrwxr-x 1 prdadm sapsys 4837672 Oct 13 2008 brspace

-rwxrwxr-x 1 oraprd sapsys 2175208 Oct 13 2008 brtools

Please, share your inputs.

Thanks adn Regards,

Kear

[JPReyes · Accepted solution]

Can you post the logs of DBCHECK ?

Regards

Juan

[Former Member · Accepted solution]

Hello Kedar,

Seems your permission are not correctly set on BR* just go to your /exe (Run Directory) and run SAProot.sh file

Run this executable under root user and just give.... if possible shutdown your system... (Hope its not Prod.)

$ ./SAProot.sh SID

Hope it will solve your issue !

[Former Member · Accepted solution]

Juan - The log for checkDB and update stats is below.

BR0801I BRCONNECT 6.40 (50)

BR0805I Start of BRCONNECT processing: cedgqbhg.sta 2010-05-20 10.22.48

BR0484I BRCONNECT log file: /oracle/PRD/sapcheck/cedgqbhg.sta

BR0252E Function fopen() failed for '/oracle/PRD/sapcheck/connPRD.log' at location main-13

BR0253E errno 13: Permission denied

BR0121E Processing of log file /oracle/PRD/sapcheck/connPRD.log failed

BR0806I End of BRCONNECT processing: cedgqbhg.sta 2010-05-20 10.22.58

BR0280I BRCONNECT time stamp: 2010-05-20 10.22.58

BR0804I BRCONNECT terminated with errors

Prateek - Yeah its production system. Other thing i dont think it should be pemission issue. As it was working absolutely fine till 13th May. Only after I have executed update statistics using brtools it is getting terminated from DB13 and reason i felt respective chk and sta file which is getting generated is getting prdadm owner instead of oraprd.

Thanks and Regards.

Kedar

[former_member227600 · Accepted solution]

sap-prd:prdadm 1> cd /sapmnt/PRD/exe
sap-prd:prdadm 2> ls -ltr br*
-rwxrwxr-x 1 prdadm sapsys 1480656 Oct 11 2008 brrestore
-rwxrwxr-x 1 oraprd sapsys 3437688 Oct 13 2008 brarchive
-rwxrwxr-x 1 oraprd sapsys 3547760 Oct 13 2008 brbackup
-rwxrwxr-x 1 oraprd sapsys 4632408 Oct 13 2008 brconnect
-rwxrwxr-x 1 prdadm sapsys 3850224 Oct 13 2008 brrecover
-rwxrwxr-x 1 prdadm sapsys 4837672 Oct 13 2008 brspace
-rwxrwxr-x 1 oraprd sapsys 2175208 Oct 13 2008 brtools

Permission is not set correctly for brarchive, brbackup, and brconnect . It sholud be 4775. And it lok like -rwsrwxr-x . s bit is missing. But i am not sure it will help you. As suggest in previous post. Please paste checkdb failure logs here.

[Former Member · Accepted solution]

I have already pasted the checkDB log. Is there anything that i should update? As far as permissions are concerned, even if they are not correctly how it was working fine tilll 13th May. I.e. Before I had executed the update statistics using brtools.

Best Regards,

Kedar

[Former Member · Accepted solution]

Hi,

The behavior of BRTools you are experiencing is because of incorrect permissions are set on BRTools.

Please carefully go through it and correct the permissions as per the note given in my previous post...

You missed the "sticky bit" in your brtools permissions...

Correct it as:

# cd /sapmnt/PRD/exe
# chmod 4775 brrestore
# chmod 4775 brrecover
# chmod 4775 brspace

# chown prdadm:sapsys brtools
# chmod 775 brtools

Regards.

Rajesh Narkhede

[Former Member · Accepted solution]

Thanks for your inputs. However, problem was with permissions for file connPRD.log file in SAPCHECK directory. It had permissions set as 544 for orasid user. I have changed it to 755 and problem got solved.

Best Regards,

Kedar

[Former Member · Accepted solution]

Hi,

Nice to see that your problem is resolved...

Please mark the thread as answered and close it...

Regards.

Rajesh Narkhede