Db13 after system copy and oracle upgrade.

[Former Member]

Dear support,

I have 40B system with Oracle 8.1.7.4 with SID as PRD. Now i have copied the same to TST system and i forgot to perform ops$ mechanism setup in TST system as part of post database refresh.

Now i have upgraded database to oracle 9.2.0.5 and since DB13 jobs are not getting executed i have done following settings,

New SID is TST.....

create user "OPS$TSTADM" identified by TST;

grant connect, resource, dba to "OPS$TSTADM";

CONNECT "OPS$TSTADM"

select * from dba_users where username like 'OPS%' ;

alter user "OPS$TSTADM" temporary tablespace PSAPTEMP ;

create table sapuser as select * from "OPS$prdADM".sapuser ;

select * from dba_tab_privs where table_name = 'SAPUSER' ;

create user "OPS$SAPSERVICETST" identified externally ;

grant select, insert, update on sapuser to "OPS$SAPSERVICETST" ;

create synonym "OPS$SAPSERVICETST".sapuser for "OPS$TSTADM".sapuser ;

grant sapdba to "OPS$TSTADM" ;

grant connect, resource, sapdba to "OPS$SAPSERVICETST";

alter user "OPS$TSTADM" identified externally ;

revoke dba from "OPS$TSTADM" ;

select * from "OPS$TSTADM".sapuser ;

-

---

still i am getting below error in DB13 , should i use BRtools instead of SAPDBA

please let me know

error in db13 logs are as below

Job started

Step 001 started (program RSDBAJOB, variant &0000000000033, user name CYBERTEC

Execute log. command SAPDBA on host saptest

__Parameters: -u / -checkopt PSAP% -method E

SAPDBA: Wrong option.

Usage:

sapdba [ -u <userid>[/<password>]

-P <userid>/<coded_password> /* (only for batch start) */

]

[ -p <profile> ]

[ -h[elp] ]

[ -r <dir> /* restart in batch */

-export <tsp_list> <table>

[-dest <path>

<tape_device> [-tape_size <size_in_M>] ]

-alter_user <uid>/<pwd>

-init_sap_connect

-sapsid <SID1>[,<SID2>,...]

-version

-verbose

-V -VERSION [all]

]

sapdba without -u option would connect by

(1) <name>/<password> in <orapwd_file>, if spec. in init<SID>.dba or

(2) system/manager, if default password is set or

(3) prompting for name and password.

External program terminated with exit code 2

SAPDBA returned error status E

Job finished

Best regards,

AjitR

[Former Member]

Hi,

Please run sapdba_role

Regards

Umesh K

[Former Member]

Umesh,

do i need to execute <b>134592</b> attachment sapdbarole

but it is not getting executed in my case as below

D:\test>

D:\test>sqlplus

SQL*Plus: Release 9.2.0.5.0 - Production on Thu Jul 19 10:44:34

Copyright (c) 1982, 2002, Oracle Corporation. All rights reser

Enter user-name: sys as sysdba

Enter password:

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.5.0 - Production

SQL> @D:\test\sapdba_role.sql

No putpur coming just hanging , does it take more than 10 minutes

[andreas_herzog]

<i>> SQL> @D:\test\sapdba_role.sql</i>

aren't you supposed to use the following syntax:

> sqlplus /nolog @sapdba_role <b><SAPSCHEMA_ID></b>

so if your schema id is for example SAPTST this would result in:

> cd {directory where the sql script lies}

> sqlplus /nolog @sapdba_role <b>SAPTST</b>

if you're using schema id SAPR3 you'll have to use this id instead...

GreetZ, AH

[Former Member]

Andreas,

script executed successfully with SAPR3 user and now still i am same error in DB13

[Former Member]

I have one more doubt , for any system copy

for example , ECC 6.0 with Oracle 10.2 on windows from one system to another

after database refresh we need to execute this sapdba_role.sql ( for 10G script with note 134592 ) after we create ops$users as i described in my first post.Even if we are using Brtools in Ecc 6.0 still do we need to execute this script after database refresh

[Former Member]

After execution of script when i am trying to run backup through db13 i am getting this error now

Job started

Step 001 started (program RSDBAJOB, variant &0000000000037, user name CYBERTECH)

Execute log. command BRBACKUP on host saptest

__Parameters: -u / -c force -t online -m all -p initTST.sap

BR051I BRBACKUP 6.20 (18)

BR055I Start of database backup: bdvstnwj.and 2007-07-19 11.35.05

BR280I Time stamp 2007-07-19 11.35.05

BR301E SQL error -1017 at location BrDbConnect-2

<b>ORA-01017: invalid username/password; logon denied</b>BR310E Connect to database instance TST failed

BR280I Time stamp 2007-07-19 11.35.05

BR301E SQL error -1017 at location BrDbConnect-2

ORA-01017: invalid username/password; logon denied

BR310E Connect to database instance TST failed

BR056I End of database backup: bdvstnwj.and 2007-07-19 11.35.05

BR280I Time stamp 2007-07-19 11.35.05

BR054I BRBACKUP terminated with errors

External program terminated with exit code 3

BRBACKUP returned error status E

Job finished

[Former Member]

Hi,

Use BRTools to reset the SAP Owner's Password

Regards

Umesh K

[Former Member]

I have downloaded brtools.sar for 4.0B windows Oracle ,and uncar the same ,

but how to apply the same , remember i have 4.0B with Oracle 9.2.0.5

how to apply brtools and how to reset sap owner's password

[Former Member]

Umesh,

what about my previous question

[Former Member]

<b>for example , ECC 6.0 with Oracle 10.2 on windows from one system to another

after database refresh we need to execute this sapdba_role.sql ( for 10G script with note 134592 ) after we create ops$users as i described in my first post.Even if we are using Brtools in Ecc 6.0 still do we need to execute this script after database refresh</b> ?

[Former Member]

Database refresh is not supported.

Follow the official system copy guides and everything around roles and ops$ users is handeled automatically.

Peter

[Former Member]

Sorry peter to use term 'database refresh' i am talking about system copy only and i have folowed all steps as per system copy guide.

But this DB13 problem is specific.

[Former Member]

Hi,

Download the DBATOOLS.SAR and Uncar it put it in \usr\sap\sid\sys\exe\run

and login into the OS sidadm user and run brtools frm cmd or execute

BrConnect -u system/pwd -f chpass -o SAPR3 -p <new pwd>

Regards

Umesh K

[Former Member]

>>>>BrConnect -u system/pwd -f chpass -o SAPR3 -p <new pwd>

whis will not help:

br* was started with " -u / " which means he is going to do a

connect / as sysdba or

connect / as sysoper but not

connect sapr3/<new pwd>

when starting the programs with the users created during the installation the connect will succeed.

check wether the user running the SAPServiceTST user is member of the local ORA_DBA and ORA_OPER or (ORA_TST_DBA and ORA_TST_OPER) operating system groups.

on the other hand in your initial message the user

"OPS$TSTADM" and "OPS$SAPSERVICETST" users where referenced.

Those are not the right users for Oracle Windows.

Windows will use OPS$<computername|domainname>\TSTADM and OPS$<computername|domainname>\SAPSERVICETST depending on the users context (working with a local account or a domain account).

It looks very much like ORADBUSR.SQL was not executed right by SAPinst or somebody asked you to ignore errors resulting from it's execution

see SAP note 50088 for more details.

peter

[Former Member]

Peter and umesh,

Can you answer my question which i have posted before also

<b>for example , ECC 6.0 with Oracle 10.2 on windows from one system to another

after system copy do we need to execute this sapdba_role.sql ( for 10G script with note 134592 ) after we create ops$users as i described in my first post or note 50088.?

Even if we are using Brtools in Ecc 6.0 still do we need to execute this script after system copy. If you are talking about DBATOOLS.SAR then is it also in post system copy procedure ?.As i am facing exactly same problem after system copy in ECC 6.0 systems with Oracle 10.2</b>

Best regards,

AjitR

[Former Member]

if you use the standard system copy using Backup/Restore with SAPinst there is no need to execute sapdba_role.sql and oradbusr.sql manually.

This is automatically done in the right way by sapinst, otherwise you would not be able to finish the systemcopy successfully

peter

[Former Member]

Hello Peter,

Here the Db is connected with System/<password> and Brconnect will use fuction chpass to change the password of SAPR3 to the new value

So it will work.

Regards

Umesh K

[Former Member]

Peter,

I have used metho to backup , restore and then conrolfile modification generation way for the same.

[Former Member]

Hi,

Change the SAPR3's password to the default value 'sap' and check whether SAP is coming up or not

Regards

Umesh K

[Former Member]

Umesh,

As it is very ctrutial system for me , can you give procedure for the same.

Is it

alter user SAPR3 identified by SAP;

Can we revert and fix issue if SAP is not coming up. no negative effects right ?

[Former Member]

Hi,

Donnt use sql to change the password, as SAP use's encrpted method for storing the password.Do u remember the password of user system ? can u connect the db by connect /as sysdba.if u remenber the system password follow the procedure which i mentioned earlier ie.,

brconnect - u system/<password> -f chpass SAPR3 -p sap

If u donnt remember password of system

Alter user system identified '<new pwd>' ;

Regards

Umesh K

[Former Member]

Umesh,

Can you tell me what is reason to change password for SAPSR3 as below query telling me the password for the same.

SQL> select * from "OPS$CYSOFT52\EC6ADM".sapuser

2 ;

USERID

-

---

PASSWD

-

---

SAPSR3-CRYPT

V01/0050ZctvSB67Wv0KDpzUO98REh9oir/s+b5DPZe5LA==

[Former Member]

Umesh,

password for SAPSR3 is sap, i am able to connect through

connect

username: SAPSR3

password: sap

But as per note 400241 we should be able to connect as following

connect /@SID

this is failing in my system with error as below

SQL> connect /@EC6

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

so , as per note i should be creating OPS$<HOSTNAME>SIDADM and OPS$<HOSTNAME>SAPSERVICESID users along with proper rights as per note

50088 and then execute sapdba_role.sql as per note 134592

<b>Please let me know if i am wrong anywhere ?</b>

so all things i have done but still unable to execute DB13.

anything missing ?

[Former Member]

Hi,

>> have 40B system with Oracle 8.1.7.4 with SID as PRD. Now i have copied the same to TST system and i forgot to perform ops$ mechanism setup in TST system as part of post database refresh.

Now i have upgraded database to oracle 9.2.0.5 and since DB13 jobs are not getting executed i have done following settings <<

Is your System is 4.0B or ECC6.0

Regards

Umesh K

[Former Member]

Umesh,

as i mentioned earlier i am facing this problem for both 4.0 b and ECC 6.0 system after system copy.

problem seems to be same in both systems only version difference.

If it caused confusion i apologise for the same.

[Former Member]

you you please provide us with following information:

computer name

domain name

output of <b>set u</b> in a command box logged in as <SID>adm user.

contents of %oracle_home%\network\admin\sqlnet.ora

output of <b>select username from dba_users</b> (sqlplus "/ as sysdba")

peter

[Former Member]

Peter,

We have performed database refresh from Ecc 6.0 server to another Ecc

6.0 server.

Source system EC6 ( hostname = cysoft52 )

Target system EC6 ( hostname = cysoft2 )

now server is up and running fine but while executing DB13 jobs getting

errors.

-

---

C:\Documents and Settings\ec6adm>set u

USERDOMAIN=CYSOFT2

USERNAME=ec6adm

USERPROFILE=C:\Documents and Settings\ec6adm

-

---

sqlnet.ora:

AUTOMATIC_IPC = ON

TRACE_LEVEL_CLIENT = OFF

NAMES.DEFAULT_DOMAIN = WORLD

1. 05.01.06 unsorported parameter now

#NAME.DEFAULT_ZONE = WORLD

1. 05.01.06 set the default to 10

SQLNET.EXPIRE_TIME = 10

SQLNET.AUTHENTICATION_SERVICES = (NTS)

1. 05.01.06 set to default

#TCP.NODELAY=YES

1. 05.01.06 set to 32768

DEFAULT_SDU_SIZE=32768

-

---

SQL> select username from dba_users

2 ;

USERNAME

-

---

OPS$CYSOFT52\EC6ADM

OPS$CYSOFT52\SAPSERVICEEC6

OPS$CYSOFT52\SAPSERVICESR3

SYS

SYSTEM

SAPSR3

SAPSR3DB

OUTLN

TSMSYS

DIP

DBSNMP

11 rows selected.

Please provide me solution for this as SAP also hasnt provided solution for the same even after opening up connection.

If possible give me deatil step by step procedure

[Former Member]

first: the right usernames for the target systems are:

OPS$CYSOFT2\EC6ADM

OPS$CYSOFT2\SAPSERVICEEC6

both are missing in the database.

Implementing SAP Note 50088 would have been very helpfull at this point.

next: you did not use the official systemcopy guides and using SAPInst backup/restore for building up the system! SAPinst would have delete old accounts (or at least asked you if he should delete the obsolete accounts) and it would have created the new accounts running sqlscript oradbusr.sql followed by sapdba_role.sql and sapconn.sql. all those scripts should be found in one of the subdirectories of program files\sapinst.

But again: note 50088 will take you to the goal.

peter

peter

[Former Member]

Peter thank you very much for clear and consise reply ,

I have finished creating ops$ users suggested by you after going through note 50088.I would like to know these 3 scripts oradbusr.sql followed by sapdba_role.sql and sapconn.sql sould be executed by which user ?

Is it <b>SAPSR3</b>?

( i have all these scripts under \usr\sap\...\sys\exe\run directory , can i execute them after copying them to another folder in order to verify logs seperately ? )

[Former Member]

oradbusr.sql (will create the ops$users), sapdba_role.sql and sapconn.sql have to be executed by a user which is allowed to <b>connect / as sysdba</b>.

The parameters the scripts need are documented in their headers.

Peter

PS: once again: do not implement your own SAP System copy procedures. Follow the official ones.

[Former Member]

Thanks peter,

yes we will follow SAPINST method here after for system copy ,

I will try your solution and update you with results.

[Former Member]

Peter ,

Could you please tell me in which note i will find

ordbuser.sql , as it is not present in my kernel directory as other 2 scripts

sapdba_role.sql and sapconn.sql

[Former Member]

Peter,

I have found the same script under

C:\Program Files\sapinst_instdir\ERP\SYSTEM\ORA\CENTRAL\AS\oradbuser.sql

usage :

cmd : sqlplus /nolog @oradbusr.sql SCHEMAOWNER NT COMPUTER|DOMAIN SAP_SID

that will recreate the OPS$user

In my case , cysoft2 is hostname where

SQL> select username from dba_users;

USERNAME

-

---

OPS$CYSOFT52\EC6ADM

OPS$CYSOFT52\SAPSERVICEEC6

OPS$CYSOFT52\SAPSERVICESR3

SYS

SYSTEM

SAPSR3

SAPSR3DB

OUTLN

TSMSYS

DIP

DBSNMP

11 rows selected.

My machine hostname is " CYSOFT2 " and owner i think is SAPSR3.

so,

what would be my command

sqlplus /nolog @oradbusr.sql SAPSR3 NT CYSOFT2 EC6

Is it correct ?

[Former Member]

sqlplus /nolog @oradbusr.sql SAPSR3 NT CYSOFT2 EC6

looks fine so far.

peter

[Former Member]

Peter,

I have executed script with user sapsr3 user as below are tasks performed , please let me know whether i can go ahead and execute sapdba_role.sql and sapconn.sql

SQL> select username , account_status from dba_users;

USERNAME ACCOUNT_STATUS

-

---

-

---

OPS$CYSOFT52\EC6ADM OPEN

OPS$CYSOFT52\SAPSERVICEEC6 OPEN

OPS$CYSOFT52\SAPSERVICESR3 OPEN

SYS OPEN

SYSTEM OPEN

SAPSR3 OPEN

SAPSR3DB OPEN

OUTLN LOCKED

TSMSYS EXPIRED & LOCKED

DIP EXPIRED & LOCKED

DBSNMP EXPIRED & LOCKED

11 rows selected.

E:\oradbuser>sqlplus /nolog @oradbuser.sql SAPSR3

SQL*Plus: Release 10.2.0.2.0 - Production on Wed Aug 1 05:53:30 2007

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Connected.

old 6: if length('&&1') = 5 then

new 6: if length('SAPSR3') = 5 then

old 7: if substr(upper('&&1'),1,5) = 'SAPR3' then

new 7: if substr(upper('SAPSR3'),1,5) = 'SAPR3' then

Enter value for 2: SAPSR3

old 11: if upper('&&2') = 'NT' then

new 11: if upper('SAPSR3') = 'NT' then

Enter value for 3: NT

old 18: :sDomain := upper('&&3');

new 18: :sDomain := upper('NT');

Enter value for 4: CYSOFT2

old 19: :sSapSid := upper('&&4');

new 19: :sSapSid := upper('CYSOFT2');

old 21: :sSapSid := upper('&&3');

new 21: :sSapSid := upper('NT');

old 37: :sSchema := upper('&&1');

new 37: :sSchema := upper('SAPSR3');

old 39: if upper('&&2') = 'NT' then

new 39: if upper('SAPSR3') = 'NT' then

old 46: :sDomain := upper('&&3');

new 46: :sDomain := upper('NT');

old 47: :sSapSid := upper('&&4');

new 47: :sSapSid := upper('CYSOFT2');

old 49: :sSapSid := upper('&&3');

new 49: :sSapSid := upper('NT');

using following Parameters:

.

. Oracle Version: 10.2.0.2.0

. Parametervalue os_authent_prefix: OPS$

. Schema Id: SR3

. Database User (Schema): SAPSR3

. SAP R/3 Administrator: OPS$NTADM

. SAP R/3 Serviceuser: OPS$SAPSERVICENT

.

Connected.

Connected.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Pr

oduction

With the Partitioning, OLAP and Data Mining options

Now after running scripts below are 3 users created.

SQL> select username from dba_users;

USERNAME

-

---

OPS$CYSOFT52\EC6ADM

<b>OPS$SAPSERVICENT</b>

OPS$CYSOFT52\SAPSERVICEEC6

<b>OPS$ORAEC6

OPS$NTADM</b>OPS$CYSOFT52\SAPSERVICESR3

SYS

SYSTEM

SAPSR3

SAPSR3DB

OUTLN

USERNAME

-

---

DIP

TSMSYS

DBSNMP

14 rows selected.

[Former Member]

Peter,

Problem solved after running script again sqlplus /nolog @oradbusr.sql SAPSR3 now DB13 is working fine for me.

Thanks you very much for your patience and time.

Best regards,

AjitR

[Former Member]

Hi ALL

I have done system copy from Production to Quality sys after that i am able to schedule the backup through DB13 but backup is not happening and also i am not able to see the logs.

When i checked it in OS level of Quality sys through sqlplus/noplus it is showing the following OPS$ users:

OPS$Domainname\PRDADM

OPS$Domainname\SAPSERVICEPRD

OPS$QUAADM

OPS$SAPSERVICEQUA

My question is that whether i have to drop all these OPS$ users in Quality sys and create new one which belongs to QAI sys OPS$ user.

If this is the case then what is the procedure for dropping and creating the user and how shall i grant the permission for this user.

And is it necessary to shut down the database while creating OPS$ user.

I am using Oracle 8.1.7.4.1 version and SAP 4.6C.

Any help will be greatly appreciated.

Thanks

Sapna

[Former Member]

No need to drop any users or shutdown database ,

Just execute sqlplus /nolog @oradbusr.sql SAPSR3

and all required inputs will create necessary users for you .

backup will automatically start getting successfull , you dont need restart of system.

Best Regards,

AjitR