Database check and update stats after Kernel upgrade

[Former Member]

Hi All,

I know this is an old topic but still I wish to bring this in as I am unable to figure out by myself what the issue would be.

I did Kernel patch upgrade from 741 PL 15 to 110.

After that DB check and update stats are failing with the below log.

Job started

Step 001 started (program RSDBAJOB, variant &0000000000001, user ID DDIC)

Execute logical command BRCONNECT On host SAPHR

Parameters: -jid CHECK20150217024618 -u / -c -f check

BR0801I BRCONNECT 7.40 (12)

BR0805I Start of BRCONNECT processing: cepwngil.chk 2015-02-17 02:46:19

BR0252E Function fopen() failed for '/oracle/SID/sapcheck/cepwngil.chk' at location main-5

BR0253E errno 13: Permission denied

BR0121E Processing of log file /oracle/SID/sapcheck/cepwngil.chk failed

BR0806I End of BRCONNECT processing: cepwngil.chk2015-02-17 02:46:19

BR0280I BRCONNECT time stamp: 2015-02-17 02:46:19

BR0804I BRCONNECT terminated with errors

External program terminated with exit code 3

BRCONNECT returned error status E

Job finished

Anyhow after Kernel upgrade I ran saproot.sh so all the brtools exe's permissions were set right.

-rwsrwxr--    1 orasid   sapsys     13542030 Nov  1 15:47 brbackup

-rwsrwxr--    1 orasid   sapsys     13385793 Nov  1 15:47 brarchive

-rwsrwxr--    1 orasid   sapsys     15952052 Nov  1 15:47 brconnect

-rwxr-xr-x    1 sidadm   sapsys     16806620 Nov  1 15:47 brspace

-rwxr-xr-x    1 sidadm   sapsys     14249766 Nov  1 15:46 brrecover

-rwxr-xr-x    1 sidadm   sapsys      5847113 Nov  1 15:32 brrestore

-rwxr-xr-x    1 sidadm   sapsys      6740867 Nov  1 15:32 brtools

Then after further troubleshooting found that SYSTEM password was locked which I found after triggering brconnect db check at Server level.

BR0801I BRCONNECT 7.40 (12)

BR0805I Start of BRCONNECT processing: cepwnoel.chk 2015-02-17 04:14:43

BR0484I BRCONNECT log file: /oracle/SID/sapcheck/cepwnoel.chk

BR0280I BRCONNECT time stamp: 2015-02-17 04:14:47

BR0301E SQL error -1017 at location db_connect-3, SQL statement:

'CONNECT system/*******'

ORA-01017: invalid username/password; logon denied

BR0310E Connect to database instance SID failed

BR0280I BRCONNECT time stamp: 2015-02-17 04:14:50

BR0301E SQL error -1017 at location db_connect-3, SQL statement:

'CONNECT system/*******'

ORA-01017: invalid username/password; logon denied

BR0310E Connect to database instance SID failed

BR0806I End of BRCONNECT processing: cepwnoel.chk 2015-02-17 04:14:47

BR0280I BRCONNECT time stamp: 2015-02-17 04:14:50

BR0804I BRCONNECT terminated with errors

So unlocked and updated the password thru brconnect below command.

brconnect -c -u / -p init%ORACLE_SID%.sap -f chpass -o SYSTEM -password new password

But still facing the issue as brconnect check DB command fails and locks the SYSTEM password.

Any suggestions would be very much valuable to me.

Please respond.

Thanks...

[former_member207186 · Accepted solution]

Hi,

Since you have changed the password of SYSTEM user, please refer to

   - Section "SYSTEM" in point 7 of  SAP Note 56286 - "FAQ: Logon mechanisms"

   - Section "HINT" in Scenario A of SAP KBA 1576837 - "ORA-01017: invalid username/password"

Regards,

Bíborka

[Former Member · Accepted solution]

Hello biborka

Thanks for your reply. Yes I checked your note and I am able to connect only if I specify the password explicitly with brconnect -u from the os level.

How to run the same from sap application level?

I mean using db13 or using external command. I tried using external command with -u option but permission denied issue arises for sapcheck directory.

So only if I change the permission of sapchange as a workaround I am able to run the job from application level.

And for your info sapschema password ops$sidadm are not changed after the installation and all those accounts are open only.

Also as I said earlier this issue popped up only after kernel upgrade but after upgrade I ran saproot.sh to sync the permission for brtools.

Still wondering the root cause why unable to run jobs from sap application level.

Please help.

Db13 by default uses system or ops$ to connect using brtools to oracle?

[Reagan · Accepted solution]

Have you tried setting the password of the DB user SYSTEM to manager and see if SAP is able to perform the check status?

[Former Member · Accepted solution]

Hi Reagen,

Even if the account is locked I can execute db check from db13 with permission set to sapcheck as 775.

So it means when executing from SAP brconnect user ops$sidadm to connnect to database.

Whereas before Kernel upgrade I cant understand how db check and update stats were executed with log files generated with ora<sid> sapsys.

Puzzled!!!.

Please share your thoughts

My NW Kernel is 741 and BRTOOLS is 7.40 (12).

SSFS is implemented in place.

[former_member489305]

Hi,

I had the same problem and got rid of by changing owner permission..

The "s" in the owner permissions means that the "setuid" is enabled, so these three commands brbackup, brarchive and brconnect are being executed with root permissions by SAP process.  

-rwsrwxr--    1 orasid   sapsys     13542030 Nov  1 15:47 brbackup

-rwsrwxr--    1 orasid   sapsys     13385793 Nov  1 15:47 brarchive

-rwsrwxr--    1 orasid   sapsys     15952052 Nov  1 15:47 brconnect

-rwxr-xr-x    1 sidadm   sapsys     16806620 Nov  1 15:47 brspace

-rwxr-xr-x    1 sidadm   sapsys     14249766 Nov  1 15:46 brrecover

-rwxr-xr-x    1 sidadm   sapsys      5847113 Nov  1 15:32 brrestore

-rwxr-xr-x    1 sidadm   sapsys      6740867 Nov  1 15:32 brtools

But the root user is not granted by oracle in our case for connection. That is why you had to change owner to sidadm for these three commands as others have and "setuid permission" is disabled itself.

[root@hostname]# chown sidadm:sapsys ./brbackup

[root@hostname]# chown sidadm:sapsys ./brarchive

[root@hostname]# chown sidadm:sapsys ./brconnect

Regards

Pavel

[Former Member]

This message was moderated.

[Former Member]

Hi Balaji,

I am facing the same issue as yours with check and update optimizer statistics in DB13. It is working in OS level using BRtools but not working in DB13 at application level.

The kernel was upgraded in the recent days. The current version is 741 patch level 31. This issue is faced after this kernel upgrade. I am pasting the log of check and update optimizer statistics below from DB13:

Job started

Step 001 started (program RSDBAJOB, variant &0000000000046, user ID IT_SALEEM)

Execute logical command BRCONNECT On host ERPPRD

Parameters: -jid STATS20150218040000 -u / -c -f stats -t ALL

BR0801I BRCONNECT 7.40 (1)

BR0805I Start of BRCONNECT processing: cepzqkml.sta 2015-03-06 04.00.43

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

BR0280I BRCONNECT time stamp: 2015-03-06 04.00.43

BR0301E SQL error -1017 at location db_connect-2,SQL statement:

'CONNECT /'

ORA-01017: invalid username/password; logon denied

BR0310E Connect to database instance PRD failed

BR0280I BRCONNECT time stamp: 2015-03-06 04.00.43

BR0301E SQL error -1017 at location db_connect-2,SQL statement:

'CONNECT /'

ORA-01017: invalid username/password; logon denied

BR0310E Connect to database instance PRD failed

BR0806I End of BRCONNECT processing: cepzqkml.sta2015-03-06 04.00.43

BR0280I BRCONNECT time stamp: 2015-03-06 04.00.43

BR0804I BRCONNECT terminated with errors

External program terminated with exit code 3

BRCONNECT returned error status E

Job finished A quick resolution is appreciated. Thanks & Regards, Aishwarya.

[Former Member]

Hello Aishwarya,

It seems you have updated your Oracle db user "system" account password when you try to execute from DB13 the default password(manager) is picked and after few unsuccessful attempts system user gets locked as per the parameters set on the db profile.

Unlock system account from db sql query and reset the password to standard password 'manager'.

Then execute DB check from SAP, share the results.

[Former Member]

Hi Balaji,

The SYSTEM user password is 'manager' only. Even then the update statistics are not working in DB13. Recent activities that were performed are Kernel upgrade to 741 patch level 31 and Database copy. This issue is occurring in both the production systems that underwent go-live recently. This issue is faced only after go-live and database copy.

Also, when executing select query for dba_users table to list the user names, the user OPS$ORABWP of production system BWP is not displaying. Instead, the user OPS$ORABWD of development system is displaying. I have attached the screen shot. Please advice.

Thanks & Regards,

Aishwarya.

[Former Member]

Hi Balaji,

When i checked SM37 for the jobs starting with DBA*, I found that the job DBA:UPDATESTATS is failing everyday. Here is the job log. Please help me.

Awaiting for your response.

Thanks & Regards,

Aishwarya.

[Former Member]

Hi Aishwarya,

Did you perform system copy? if so did you perform all the post system copy activities as per the System copy guide.

Your OS/DB? if you are on NW 7.4 SSFS must be in place?

Drop all the ops$ora<source> and create ops$<target>.

Also I see invalid system ID the job you are running is Production system right?

Please provide more info on what activities you performed so it will be easy to advice.

Your initially reported error says invalid user name/pwd and now the error says Invalid system ID?

Are they from the same system?

[Former Member]

Hi Balaji,

Please find the answers to your questions here:

1. A kernel upgrade and a database copy was performed recently.

2. OS is AIX and DB is Oracle 11.2.0.4.0

3. The issue is faced in the production systems only (BWP & PRD) which underwent go-live recently. FYI, the issue is being faced only after the go-live, kernel upgrade & Database copy.

BWP is on NW 7.40 and PRD is on ERP 6.0 EHP 7. SSFS is in place for BWP.

4. The errors in the screen shots attached are for the same system only (BWP).

In DB13 job log for check and update statistics, the error that is displayed is "Invalid username and password;logon denied" (as per the screen shot attached in my post on Mar 10, 2015).

And in SM37 DBA:UPDATESTATS job log, the error that is displayed is "Invalid system id" (as per the screen shot attached in my post on Mar 12, 2015).

Please advice.

Thanks & Regards,

Aishwarya.

[Former Member]

Hi Balaji,

FYI, the current version of BRTOOLS is BRTOOLS 7.40 (10).

Thanks & Regards,

Aishwarya.

[Former Member]

Hello Aishwarya,

Thanks for providing all the info.

I am wondering how BWD (Development) system ID's are being shown up.

Did you perform all the post DB copy activities as I can see OPS$orabwd account instead of ops$orabwp accounts. ?

All the environment variables set properly?

ORACLE_HOME ORACLE_SID DB_SID show?

My case I did only Kernel upgrade and after which I was facing the Permission denied issue, so I just updated the permission of /oracle/SID/sapcheck 775 and the issue was fixed although its a temporary fix only.

[Former Member]

Hi Balaji,

Could you please provide me a document or a link where I can find the post DB copy activities?

Thanks,

Aishwarya.

[Former Member]

Hello Aishwarya,

Please find these OSS notes they have references to system copy guides

OSS 1738258 - System Copy of Systems Based on SAP NetWeaver 7.3 and Higher

OSS 1049553 - SAP Forums - Installation, Upgrade and System Copy

Let me know if you need any further info..

[Former Member]

Hi Balaji,

I am getting "Document is not released" error for the SAP note 1738258.

Thanks,

Aishwarya.

[Former Member]

Aplogies. Please check the below link for the system copy guide..

If you had done system copy using sapinst source ops$users will be dropped and recreated by sapinst itself.

What procedure you followed for doing system copy?

https://websmp103.sap-ag.de/~sapidb/011000358700001323962012E

[Former Member]

Hello Aishwarya

What is the status now??

[Former Member]

Hi Balaji,

The issue is resolved now. I deleted all the scheduled jobs in DB13 and the jobs starting with DBA* in SM37 and rescheduled all. It is working fine now.

Thank you so much for helping me in this issue

Thanks & Regards,

Aishwarya.

[Former Member]

Hello aishwarya,

Good to hear that your issue is resolved.

So you mean to say you performed all the post copy activities?

Also you deleted and recreated OPS$BWD users?

Practically from your above response you did not do anything other than deleting some old logs and rescheduling DBA check and update stats job.

Make sure that BRtools permission are set properly as per 113747 - Owners and authorizations for BR*Tools.

Thank you

[Former Member]

Hi Balaji,

What I did was, I checked all the permissions for BRtools as you mentioned and deleted the jobs in DB13 and DBA jobs in SM37 and recreated them. Thats it

I did not do any deletion or recreation for the OPS$ users.

Thanks & Regards,

Aishwarya.

[Former Member]

Hello aishwarya,

Good that you fixed it.

Thanks for the response..

[janos_mucsi-besze]

Hi Balaji.

BR*Tools is using system users default password to perform the actions if -u <username>/password or -u / or even -u // is not specified. Either in SDBAC or in command line. As a result ora many ORA-01017 user gets locked because of FAILED_LOGIN_ATTEMPTS parameter set to 3 or 10 (i don't remember anymore). Set it to unlimited if note done already, as documented in note below:

951167 - ORA-28000: the account is locked

Make sure every action in SDBAC table contains at least -u /, -u // or -u system/<appropriate password> to avoid ORA-01017 in the future.

Regards,

János

[Former Member]

Hi Janos,

BRTOOLS is using system default password to connect using which account sidadm or orasid.

As I can see .chk and .stat files are generated only with sidadm, my reqmt is to run from application level.

This is the command being used in DB13 - command_line                   brconnect -jid CHECK20140916000000 -u / -c -f check

In SDBAC every action has PSTRING as below

-u / -c -f check

-u / -c -f cleanup

-u / -c -f next -t @,[]S

-u / -c -f stats -t @,[]S

-u / -c -f stats -v cascade -t @,[]S

My only question is even if al is set all files are generated using sidadm:sapsys for which permission was missing after Kernel upgrade whereas before Kernel upgrade all files under /oracle/SID/sapcheck were generated using orasid:sapsys.

Please someone if could clarify on the detailed connection mechanism would be helpful.

Like for the above actions whether system/ops$sidadm would be used.

[former_member196492]

Hi,

You also need to run sapdba_role.sql by using sidadm ID for database grants.

Thanks,

[Former Member]

Hello Lupin,

I dont get any error messages like below as per OSS Note 134592..

BR0301W SQL error -1031 at location BrLicCheck-7

ORA-01031: insufficient privileges..

I dont know what am i missing, when scheduling jobs from DB13 which user is used for connecting to db is it system or ops$sidadm??

If system is used it might fail as if the password is changed we must specify it with -u option.

Even if I specify it fails with Permission denied error for /oracle/SID/sapcheck directory.

I am clueless.

[former_member196492]

Could you please perform the following steps:

before doing this please check system user and unlock it in case locked.

1.log in with root change the permission as below

rwsrwxr--    1 orasid   sapsys     13542030 Nov  1 15:47 brbackup

-rwsrwxr--    1 orasid   sapsys     13385793 Nov  1 15:47 brarchive

-rwsrwxr--    1 orasid   sapsys     15952052 Nov  1 15:47 brconnect

-rwxr-xr-x    1 orasid   sapsys     16806620 Nov  1 15:47 brspace

-rwxr-xr-x    1 orasid   sapsys     14249766 Nov  1 15:46 brrecover

-rwxr-xr-x    1 orasid   sapsys      5847113 Nov  1 15:32 brrestore

-rwxr-xr-x    1 orasid   sapsys      6740867 Nov  1 15:32 brtools

2.  login with SIDADM and go to path sapmnt/SID/exe and run sapdba_role.sql by using following command.  sqlplus /nolog @sapdba_role <schema ID> ie. SAPSR3

     You need to check the log sapdba_role.log and can share output.

3. log in to SAP and execute the DB13 and do dbcheck or log backup or partial backup for test.

Please do the above steps first and reply with you questions.

Thanks,

[Former Member]

Hello,

As per the OSS note 113747 - Owners and authorizations for BR*Tools I have set the permissions.

I dont quite understand why should i change the ownership of all br exe's to orasid..

Yes these are the roles assigned to SYSTEM user

GRANTEE                        GRANTED_ROLE

------------------------------ ------------------------------

SYSTEM                         DBA

SYSTEM                         AQ_ADMINISTRATOR_ROLE

SYSTEM                         SAPDBA

SAPDBA role is already granted to it.

I am able to trigger DB13 jobs only if I change the permission of sapcheck to 775 only then sidadm:sapsys can write to that directory.

FYI: I have changed the SYSTEM password and can manually trigger from OS level using the updated system account.

Could anyone throw some light here how to fix this..

Also I am wondering how previously before the Kernel upgrade DB13 jobs were running successfully with ora<sid>:sapsys groups whereas sapsys group does not have ora<sid> assigned to it..

Old logs..

***********

-rw-r--r--    1 orasid   sapsys        32947 Dec  3 2013  cemrebcg.chk

-rw-r--r--    1 orasid   sapsys        21851 Dec  4 2013  cemridpn.sta

-rw-r--r--    1 orasid   sapsys        83771 Dec  6 2013  cemrtkts.chk

-rw-r--r--    1 orasid   sapsys      8959182 Dec  6 2013  cemrugbo.sta

-rw-r--r--    1 orasid   dba            4395 Dec 23 2013  cemuxnom.dst

-rw-r--r--    1 orasid   dba            2285 Dec 23 2013  cemuxnqi.sta

-rw-r--r--    1 orasid   dba            1757 Dec 23 2013  cemuxnrv.sta

-rw-r--r--    1 orasid   dba            2285 Dec 23 2013  cemvbbjt.sta

-rw-r--r--    1 orasid   dba            2285 Dec 23 2013  cemvbbok.sta

-rw-r--r--    1 orasid   sapsys        10707 Dec 23 2013  connsid.log

-rw-r--r--    1 orasid   dba            1825 Dec 23 2013  cemvbbum.sta

[former_member182657]

If still issue persists than follow SAP Note 113747 - Owners and authorizations for BR*Tools

and share the results.

Regards,

Gaurav

[Former Member]

Hi Gaurav,

Yes I did run saproot.sh after Kernel upgrade. These are the current permission set and as stated in the OSS Note

-rwsrwxr--    1 orasid   sapsys     13542030 Nov  1 15:47 brbackup

-rwsrwxr--    1 orasid   sapsys     13385793 Nov  1 15:47 brarchive

-rwsrwxr--    1 orasid   sapsys     15952052 Nov  1 15:47 brconnect

-rwxr-xr-x    1 sidadm   sapsys     16806620 Nov  1 15:47 brspace

-rwxr-xr-x    1 sidadm   sapsys     14249766 Nov  1 15:46 brrecover

-rwxr-xr-x    1 sidadm   sapsys      5847113 Nov  1 15:32 brrestore

-rwxr-xr-x    1 sidadm   sapsys      6740867 Nov  1 15:32 brtools

[former_member182657]

Hi Balaji,

Kernel patch upgrade from 741 PL 15 to 110.

After kernel upgrade have you executed ./saproot.sh <SID>,if not try to execute the command to provide correct permissions to files.

Additionally could you check for correct ownerships on br* files under kernel location.

Hope issue will get resolve with a nice retry after following .

Good luck !!

[manumohandas82]

Hi ,

Can you check the following note and see whether it helps

1576837 - ORA-01017: invalid username/password;

[Former Member]

Hi Mohan,

I checked the note and scenario A relates to my issue.

I already resetting the password after doing that it works when I do using command line.

Whereas it doesn't work when scheduled using DB13, as Permission denied comes and eventually system user also gets locked..

Please let me know if there would be any fix for this.

Also before Kernel upgrade there was no issue faced..