changing OPS$ passwd during system refresh

[Former Member]

Hi All,

i have to do system Refresh/system copy ,i m using oracle 10g database.

while studying the docs and sap notes,i found that after database is backed from source system and restored in target system

.It is also required to change some passwords for OPS$ or SAP<Schema> user in table "sapuser" at OS level.

I am totally unaware of any such activity.can someone help me in this regard,as what are the post steps after DB is restored in target system and to how to perform them.

We are using brtools here.

Also what is the command line for restroring DB while system copy.

Regards,

Vishesh S

[Former Member · Accepted solution]

Hi all,

i am still confused that after doing a system copy/refresh, what changes i have to perform in OPS$ user.

what is the middle step between db recovery and starting the system.

Regards,

Vishesh S

[Former Member · Accepted solution]

To connect SAP to Database you need to perfom the OPS$ task which is very well documented in the note which already given.

Your R3trans -d will not work mean you can't start SAP.

[former_member524429 · Accepted solution]

*Hi,*

*1.*

*can someone help me in this regard,as what are the post steps after DB is restored in target system and to how to perform them.*

After doing DB restore, one has to

a. Apply the Control files of source system database

b. Perform DB recovery by applying available offline redo log files (based on Complete DB recovery or Point-in-time DB recovery).

c. Then after successful DB recovery, one has to OPEN the database with RESETLOGS options.

d. Upon successful Database instance startup, Start Target SAP system

*2.*

*what is the command line for restroring DB while system copy.*

brrestore -b logfilename -m full

e.g. brrestore -b bcnmhluz.aft -m full

*3.*

a) Logon using the OPS$ user ("connect /@<sid>") to determine the
sapr3 password that is stored in the database table SAPUSER.

Now i have no idea,that how to check the password,as i logged in using "connect /",it just got connected.
But how will i know that what password has been set.

In OPS$ connection mechanism, only the existence of ops$<sid>adm user requires. Under <sid>adm OS user if you execute "connect /" in SQLPLUS, then it will get connected with Database through ops$<sid>adm database user, without asking for password.

During OPS$ connection mechanism, the SAP work processes get connected to Database using this OPS$ user. Then as a next step, the password of Schema user (SAPSR3 or SAP<SID>) is read from the SAPUSER table from database, and then with that password od schema owner , the R/3 work processes actually get connected with Database Instance schema. So the SAPSR3 password should be same at Oracle DDIC and SAPUSER table levels.

If you do not know the password of SAPSR3 or SAP<SID> database users, you can change them with br*tools or by the execution of the following brconnect command.

brconnect -f chpass -o [sapr3 | sap<sid> | sap<xyz> | sapsr3] -p <new_password>

(Refer SAP Notes 562863, 361641 as reference)

*4.*

*after doing a system copy/refresh, what changes i have to perform in OPS$ user.*

If your getting with OPS$ user issue while SAP startup or R3trans -d execution, then you can refer SAP note 40024 or do the following .

a.

Check weather the correct OPS$<SID>ADM user exists at DB level or not ?

SELECT * FROM DBA_USERS;

b. If the required OPS$ user does not exist in Target DB....

CREATE USER "OPS$<SID>ADM" DEFAULT TABLESPACE <user_tsp> TEMPORARY TABLESPACE PSAPTEMP IDENTIFIED EXTERNALLY;

c. Grant roles/previleges to OPS$ user

grant sapdba to ops$<SID>ADM;

d. check the existence of SAPUSER table and the owner of SAPUSER table in target DB

SELECT OWNER FROM DBA_TABLES WHERE TABLE_NAME = 'SAPUSER';

e. If the owner of SAPUSER table is not OPS$<SID>ADM, then u need to drop it and recreate it in Database.

-- DROP TABLE "<owner>".SAPUSER;

-- CREATE TABLE "OPS$<SID>ADM".SAPUSER (USERID VARCHAR2(256), PASSWD VARCHAR2(256));

--INSERT INTO "OPS$<sid>ADM".SAPUSER VALUES ('<sapowner>', '<password>');

f.

Execute R3trans -d under <sid>adm OS level user and verify its return code. Also check trans.log file

5.

what is the middle step between db recovery and starting the system.

middle steps can be

a. to OPEN Database with RESETLOGS or NORESETLOGS option and restart the DB instance.

b. create OPS$ Database user according to new SID and new <SID>ADM OS user

I hope the above information will be helpful to provide you possible understanding.

Regards,

Bhavik G. Shroff

[Former Member]

Hi,

I studied the note 400241.but i have few doubts.As per the note:

a) Logon using the OPS$ user ("connect /@<sid>") to determine the

sapr3 password that is stored in the database table SAPUSER.

Now i have no idea,that how to check the password,as i logged in using "connect /",it just got connected.

But how will i know that what password has been set.

As i have to refresh quality from production,i will have to make changes.

If possible,kindly guide me with step by step procedure.

Thansk in Advance,

Vishesh S

[Former Member]

Hi Vishesh,

Go through 50088 Note, whic will help you in creating the OPS$ users. You have to execute the ORADBUSR.SQL script.

Execute the below command based on your OS:

UNIX: sqlplus /NOLOG @oradbusr.sql SCHEMAOWNER UNIX SAP_SID x

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

for eg: in NT: sqlplus /nolog @oradbusr.sql SAPSR3 NT saphost PRD

where saphost is the hostname of the machine, if the machine is connected to domain then it should be domain\saphost

Regards,

SBK

[Former Member]

You can find a restore document under --> backup and restore.

[Former Member]

Have a look at this note, it should help.

Note 400241

Thanks,

Naveed