brconnect gives ORA-01031: insufficient privileges after SAP Note 1139623 applied

[vince_laurent]

Now that I have run through the SAP note my brconnect gives me:

sapjbd:orajbd 27> brconnect -u / -c -f stats -t all -f nocoll -p 2

BR0801I BRCONNECT 7.20 (32)

BR0805I Start of BRCONNECT processing: celwopzy.opt 2013-08-16 10.56.30

BR0484I BRCONNECT log file: /oracle/JBD/sapcheck/celwopzy.opt

BR0280I BRCONNECT time stamp: 2013-08-16 10.56.31

BR0813I Schema owners found in database JBD: APPQOSSYS, DBSNMP, DIP, OPS$ORAJBD, OPS$SR3ADM, ORACLE_OCM, OUTLN, SAPSR3DB%

BR0280I BRCONNECT time stamp: 2013-08-16 10.56.32

BR0814I Number of tables in schema of owner APPQOSSYS: 4

BR0814I Number of tables in schema of owner DBSNMP: 24

BR0814I Number of tables in schema of owner OUTLN: 3

BR0814I Number of tables in schema of owner SAPSR3DB: 671

BR0280I BRCONNECT time stamp: 2013-08-16 10.56.33

BR0815I Number of indexes in schema of owner DBSNMP: 16

BR0815I Number of indexes in schema of owner OUTLN: 4

BR0815I Number of indexes in schema of owner SAPSR3DB: 1007

BR0280I BRCONNECT time stamp: 2013-08-16 10.56.33

BR0301E SQL error -1031 at location BrDbstatcRead-1, SQL statement:

'PREPARE stmt_15 FROM'

'SELECT DBOBJ, DOTYP, OBJOW, VWTYP, ACTIV, OBJEC, AEDAT, AMETH, OPTIO, SIGNI, TOBDO, HISTO, TDDAT, DURAT, PLAND FROM "SAPSR3DB".DBST

ATC WHERE (DOTYP IN ('01', ' ') AND OBJOW IN ('SAPSR3DB', 'SAPR3', ' ') OR DOTYP >= 'AA' AND DOTYP <= 'ZZ') AND DBTYP IN ('ORACLE',

' ') AND VWTYP IN ('A', 'O', ' ') ORDER BY DBOBJ, DOTYP, OBJOW, DBTYP'

ORA-01031: insufficient privileges

BR0806I End of BRCONNECT processing: celwopzy.opt 2013-08-16 10.56.33

BR0280I BRCONNECT time stamp: 2013-08-16 10.56.33

BR0804I BRCONNECT terminated with errors

I have reapplied the sapdba_role.sql as per SAP Note 134592.

I am at a loss.

HP-UX Oracle 11.2.0.2

[vince_laurent · Accepted solution]

SAP helped fix it.  had to run the correct schema owner:

sqlplus /nolog @sapdba_role SR3DB

Then change rights per note 1598594. 

They were:

They are now:

sapjbd:orajbd 23> ls -lrt br*

-rwsrwsr-- 1 orajbd sapsys 24224376 Aug 16 10:13 brarchive

-rwsrwsr-- 1 orajbd sapsys 24734040 Aug 16 10:13 brbackup

-rwsrwsr-- 1 orajbd sapsys 29717768 Aug 16 10:13 brconnect

-rwsrwsr-- 1 orajbd sapsys 25531824 Aug 16 10:14 brrecover

-rwsrwsr-- 1 orajbd sapsys 15801496 Aug 16 10:14 brrestore

-rwsrwsr-- 1 orajbd sapsys 31709600 Aug 16 10:14 brspace

-rwsrwxr-x 1 jbdadm sapsys 17096576 Aug 16 10:14 brtools

(root@sapjbd)# ls -lrt br*

-rwsrwxr-x 1 orajbd sapsys 18361536 May 16 2012 brarchive

-rwsrwxr-x 1 orajbd sapsys 18870544 May 16 2012 brbackup

-rwsrwxr-x 1 orajbd sapsys 23548776 May 16 2012 brconnect

-rwxr-xr-x 1 jbdadm sapsys 19595376 May 16 2012 brrecover

-rwxr-xr-x 1 jbdadm sapsys 7361416 May 16 2012 brrestore

-rwsrwxr-x 1 orajbd sapsys 25467592 May 16 2012 brspace

-rwsrwxr-x 1 orajbd sapsys 9578976 May 16 2012 brtools

[Reagan]

Hello

BR0813I Schema owners found in database JBD: APPQOSSYS, DBSNMP, DIP, OPS$ORAJBD, OPS$SR3ADM, ORACLE_OCM, OUTLN, SAPSR3DB%

First check whether the OPS$ users are properly created.

Note 1028220 - ORA-01031: Insufficient privileges despite SAPCONN role

Note 400241 - Problems with ops$ or sapr3 connect to Oracle

Regards

RB

[vince_laurent]

Thanks, read them before.  Don't seem to apply.  The users are matched (at least closely) to other sap systems we have and the one I applied the note to is the only system with the brconnect failing. 

SQL> connect sapsr3db/password

SQL> select * from session_roles;

ROLE

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

SELECT_CATALOG_ROLE

HS_ADMIN_ROLE

SAPCONN

HS_ADMIN_SELECT_ROLE

HS_ADMIN_EXECUTE_ROLE

SQL> select * from session_privs;

PRIVILEGE

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

ADVISOR

CREATE INDEXTYPE

CREATE OPERATOR

CREATE TYPE

ANALYZE ANY

CREATE TRIGGER

CREATE PROCEDURE

CREATE SEQUENCE

CREATE VIEW

CREATE SYNONYM

CREATE CLUSTER

PRIVILEGE

CREATE TABLE

UNLIMITED TABLESPACE

ALTER SESSION

CREATE SESSION

15 rows selected.

When I compare this user to one that works the roles and privs are the same

[former_member184473]

Hello Vince,

Check the following note:

480266 Problems with the SYSDBA/SYSOPER/INTERNAL connect

Specially point 8:

8.  UNIX: Oracle's definition of the dba and oper groups

Regards,

Eduardo Rezende

[vince_laurent]

Thanks but doesn't apply.  Worked fine until I applied the note.  Something in the scripts in the note or who I did them as changed something.

[former_member184473]

Hello Vince,

Attach to the thread the output of "cat config.c" (as <sid>adm).

Regards,

Eduardo

[vince_laurent]

/* SS_DBA_GRP defines the UNIX group ID for sqldba adminstrative access.*/

/* Refer to the Installation and User's Guide for further information. */

/* IMPORTANT: this file needs to be in sync with

rdbms/src/server/osds/config.c, specifically regarding the

number of elements in the ss_dba_grp array.

*/

#define SS_DBA_GRP "dba"

#define SS_OPER_GRP "oper"

#define SS_ASM_GRP ""

char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP, SS_ASM_GRP};

[nitinsalunkhe]

480266 Problems with the SYSDBA/SYSOPER/INTERNAL connect : point 8 solved my problem