ORA-01031: insufficient privileges during backup/update statistics

[Former Member]

Hello,

I've problem with backup / update statistics via DB13.

Sys op: SuSE 11SP1

DB: Oracle 10.2.0.2

When I execute db stat from DB13 I have:

09.03.2011 22:46:11 BR0301W SQL error -1031 at location brc_dblog_open-1, SQL statement:

09.03.2011 22:46:11 BR0301W SQL error -1031 at location stats_dur_get-1, SQL statement:

09.03.2011 22:46:11 BR0301E SQL error -1031 at location stats_tab_collect-26, SQL statement:

09.03.2011 22:46:11 BR0301W SQL error -1031 at location brc_dblog_open-1, SQL statement:

09.03.2011 22:46:11 'INSERT INTO SAP_SDBAH (BEG, FUNCT, SYSID, OBJ, RC, ENDE, ACTID, LINE) VALUES ('20110309224512', 'sta', 'DEV', 'ALL', '9999', '

09.03.2011 22:46:11 BR0301W SQL error -1031 at location stats_dur_get-1, SQL statement:

09.03.2011 22:46:11 'OPEN curs_1 CURSOR FOR'

09.03.2011 22:46:11 'SELECT POS, LINE FROM SAP_SDBAD WHERE BEG = '00000000000002' AND FUNCT = 'DUR' ORDER BY SUBSTR(LINE, 9)'

09.03.2011 22:46:11 BR0301E SQL error -1031 at location stats_tab_collect-26, SQL statement:

09.03.2011 22:46:11 'UPDATE "SAPSR3"."DBSTATTORA" SET ANDAT = '201103092245', VWTYP = 'O', AMETH = 'E', OPTIO = 'P1', NROWS = 16471800, OCCBL = 1068

09.03.2011 22:46:11 ORA-01031: insufficient privileges

When backup is executed via DB13:

10.03.2011 09:36:53 BR0301W SQL error -1031 at location BrDbfInfoGet-32, SQL statement:

10.03.2011 09:36:53 'DELETE FROM SAP_SDBAH WHERE BEG > '10000000000000' AND BEG < '20100203000000''

10.03.2011 09:36:53 ORA-01031: insufficient privileges

10.03.2011 09:36:53 BR0280I BRBACKUP time stamp: 2011-03-10 09.23.40

10.03.2011 09:36:53 BR0301W SQL error -1031 at location BrDbfInfoGet-33, SQL statement:

10.03.2011 09:36:53 'DELETE FROM SAP_SDBAD WHERE BEG > '10000000000000' AND BEG < '20100203000000''

10.03.2011 09:36:53 BR0280I BRARCHIVE time stamp: 2011-03-10 09.23.40

10.03.2011 09:36:53 BR0301W SQL error -1031 at location BraDbLogOpen-5, SQL statement:

10.03.2011 09:36:53 'INSERT INTO SAP_SDBAH (BEG, FUNCT, SYSID, OBJ, RC, ENDE, ACTID, LINE) VALUES ('20110310092340', 'svd', 'DEV', 'disk', '9999', '

10.03.2011 09:36:53 ORA-01031: insufficient privileges

BUT when I start backup via command line as sidadm

brbackup

it works well!

R3trans -d works, SAP is up, no other problems.

Please advise

Michal

also form command line as sidadm:

brbackup -u / throws ORA-01031: insufficient privileges

but brbackup -u SYSTEM/MANAGER works fine

SQL> select granted_role from dba_role_privs where grantee = 'OPS$ORADEV';

GRANTED_ROLE

-

---

CONNECT

SAPDBA

SAPCONN

RESOURCE

SQL> select granted_role from dba_role_privs where grantee = 'OPS$DEVADM';

GRANTED_ROLE

-

---

CONNECT

SAPDBA

RESOURCE

SQL> SELECT * FROM DBA_ROLE_PRIVS;

GRANTEE GRANTED_ROLE ADM DEF

-

---

-

---

--- ---

SYS IMP_FULL_DATABASE YES YES

DBA SCHEDULER_ADMIN YES YES

SYSTEM AQ_ADMINISTRATOR_ROLE YES YES

TSMSYS RESOURCE NO YES

OPS$SAPSERVICEDEV CONNECT NO YES

SYS SAPCONN YES YES

SYS SAPDBA YES YES

SYS RECOVERY_CATALOG_OWNER YES YES

SYS DELETE_CATALOG_ROLE YES YES

DBA DELETE_CATALOG_ROLE YES YES

DBA EXECUTE_CATALOG_ROLE YES YES

GRANTEE GRANTED_ROLE ADM DEF

-

---

-

---

--- ---

SAPDBA SELECT_CATALOG_ROLE NO YES

SYS DBA YES YES

IMP_FULL_DATABASE EXECUTE_CATALOG_ROLE NO YES

LOGSTDBY_ADMINISTRATOR RESOURCE NO YES

OPS$DEVADM CONNECT NO YES

SAPSR3 SAPCONN NO YES

SAPCONN SELECT_CATALOG_ROLE NO YES

DBA EXP_FULL_DATABASE NO YES

DBA SELECT_CATALOG_ROLE YES YES

EXP_FULL_DATABASE EXECUTE_CATALOG_ROLE NO YES

IMP_FULL_DATABASE SELECT_CATALOG_ROLE NO YES

GRANTEE GRANTED_ROLE ADM DEF

-

---

-

---

--- ---

DBSNMP OEM_MONITOR NO YES

SAPSR3 RESOURCE NO YES

OPS$SAPSERVICEDEV SAPDBA NO YES

SYS HS_ADMIN_ROLE YES YES

SYS EXP_FULL_DATABASE YES YES

DBA GATHER_SYSTEM_STATISTICS NO YES

EXECUTE_CATALOG_ROLE HS_ADMIN_ROLE NO YES

EXP_FULL_DATABASE SELECT_CATALOG_ROLE NO YES

OPS$DEVADM SAPDBA NO YES

OPS$DEVADM RESOURCE NO YES

SYS OEM_MONITOR YES YES

GRANTEE GRANTED_ROLE ADM DEF

-

---

-

---

--- ---

SYS AQ_ADMINISTRATOR_ROLE YES YES

SYSTEM SAPDBA NO YES

OPS$ORADEV CONNECT YES YES

SAPSR3 CONNECT NO YES

SYS SCHEDULER_ADMIN YES YES

SYS AQ_USER_ROLE YES YES

SYS LOGSTDBY_ADMINISTRATOR YES YES

SYS EXECUTE_CATALOG_ROLE YES YES

SYS RESOURCE YES YES

DBA IMP_FULL_DATABASE NO YES

SYSTEM DBA YES YES

GRANTEE GRANTED_ROLE ADM DEF

-

---

-

---

--- ---

SELECT_CATALOG_ROLE HS_ADMIN_ROLE NO YES

OPS$ORADEV SAPDBA NO YES

SAPDBA GATHER_SYSTEM_STATISTICS NO YES

OPS$SAPSERVICEDEV RESOURCE NO YES

SYS OEM_ADVISOR YES YES

SYS GATHER_SYSTEM_STATISTICS YES YES

SYS SELECT_CATALOG_ROLE YES YES

SYS CONNECT YES YES

OUTLN RESOURCE NO YES

OPS$ORADEV SAPCONN YES YES

OPS$ORADEV RESOURCE YES YES

55 rows selected.

Edited by: Michal Sarna on Mar 10, 2011 9:50 AM

Edited by: Michal Sarna on Mar 10, 2011 10:01 AM

[former_member227600 · Accepted solution]

Hi,

ORA-01031: insufficient privileges When backup is executed via DB13

From the above error it seems that there are permission issue. Please correct permission & ownership of BRtools & try again.

switch as SIDADM & go to the exe directory & paste the result of below command.

ls -ltr br*

And also check permission as mention in below SAP Note

SAP Note 113747 - Permissions for DBA tools BR*Tools and SAPDBA

Best Regards

Karan

[Former Member · Accepted solution]

-rwsrwxr-x 1 oradev dba 2289337 2010-02-16 12:38 brtools

-rwsrwxr-x 1 oradev dba 5617510 2010-02-16 12:38 brspace

-rwsrwxr-x 1 oradev dba 1554379 2010-02-16 12:38 brrestore

-rwsrwxr-x 1 oradev dba 4537880 2010-02-16 12:38 brrecover

-rwsrwxr-x 1 oradev dba 5489731 2010-02-16 12:38 brconnect

-rwsr-srw- 1 oradev dba 4227280 2010-02-16 12:38 brbackup

-rwsr-srw- 1 oradev dba 4121272 2010-02-16 12:38 brarchive

In my opinion this is problem with privs in oracle, not in operating system

as I mentioned

when I execute

brbackup or brbackup -u SYSTEM/manager as sidadm from command line all wokrs fine

but when I execute

brbackup -u /

there are ORA-01031: insufficient privileges

[Former Member · Accepted solution]

Have a look at SAP Note 134592 - Importing the SAPDBA role (sapdba_role.sql)

Did you change anything, for example an upgrade of Oracle or SAP?

In this case you will have to apply the script sapdba_role.sql again.

And get the latest version of this script!

hope this helps

[Former Member · Accepted solution]

The system was migrated recently.

Homogenous migration, from SuSE10/Oracle to SuSE11/Oracle from ia64 to x86_64 server. No SID change.

I see that OPS$SIDADM and OPS$ORASID has SAPDBA role already.

Should I execute this script despite of existing SAPDBA role?

Edited by: Michal Sarna on Mar 10, 2011 11:33 AM

Edited by: Michal Sarna on Mar 10, 2011 11:46 AM

[Former Member · Accepted solution]

Hi,

I would run the script again as Jo said just for good measure.

I have had a similar issue in the past.

Try looking at the service user, try changing it and retest.

Also tho this is was a UNIX variable I removed TNS_ADMIN, and made sure the listener would work happily and

ran R3trans -x to be sure.

I imagine if you do an sqlplus "/ as sysdba" you get the ORA - 01031: insufficient privileges ?

What I played around with the above fixed it..

Mark

[Former Member · Accepted solution]

I will try with this script.

conn / as sydba works fine with sqlplus

also conn OPS$DEVADM/@SID works fine

listener is up nad works

R3trans -d and R3trans -x has return code 000 - so it is ok

SAP is UP, only brbackup from DB13 doesn't work, other things are ok,

[Former Member · Accepted solution]

Script from Note 134592 - Importing the SAPDBA role (sapdba_role.sql) has worked!

Thanks!

Michal