Information on how the OPS$ Users Work

Content Author: Sachin D. J.

Does anyone have information or a good understanding of how the OPS$ users work and operate under an Oracle SAP environment.

I would greatly appreciate some assistance as I have problems with my Brconnect and Brbackup within DB13 due to the OPS$ users.

I need info on how to permanently delete the OPS$ users and then recreate it, due to the fact that I have incorrect OPS$ users in some of the tables affected by the OPS$ users.

Below is the document I have prepared on recreating the OPS$ machanism. It helped me solve all my problem on DB13 and also on Schema owner connecting to database.

Hope this could help you.

Also refer to the following sapnotes:  
1. 400241 : Problem withe ops$ or sapr3 connect to oracle
2. 134592  : Importing the SAPDBA role (sapdba_role.sql)
3. 361641  : Creating OPS$ users on UNIX
4. 50088   : Creating OPS$ users on Windows NT/Oracle
5. 437648  : DB13: External program terminated with exit code 1/2

----------

select owner from dba_tables where table_name='SAPUSER';

## If owner is not the sid you require, then drop the table SAPUSER

Drop table "<owner>".SAPUSER;
#or#
Drop table "domain\OPS$SIDadm".SAPUSER;

## IF THE ANSWER IS 0 ROWS SELECTED THEN CREATE THE TABLE SAPUSER

# Check whether OPS$<SID>adm user exist, if no then create it

create user OPS$SIDadm default tablespace psapuser1d temporary tablespace psaptemp identified  

externally;

# if exist then drop it;

DROP USER OPS$SIDADM;
 

# Grant connect & resource roll to OPS$<SID>ADM;

grant connect, resource to OPS$SIDADM;

# Creat table SAPUSER

create table "OPS$SIDADM".SAPUSER ( USERID VARCHAR2(256), PASSWD VARCHAR2 (256));

# update "OPS$<SID>ADM.SAPUSER with the follwoing command

insert into "OPS$SIDADM".SAPUSER  values ('SAPR3', 'sap'); #sap = <password>

# Under NT it is required that user sapservice<sid> can also access the SAPUSER table. In order  

to avoid problems with the data consistency, it does not make sense to create an additional  

SAPUSER table having the same contents. You should rather define a synonym. Check if a suitable  

synonym exists by using the following call:

SELECT OWNER, TABLE_OWNER, TABLE_NAME FROM DBA_SYNONYMS WHERE SYNONYM_NAME = 'SAPUSER';

# IF NOT THEN CREAT IT  

create public synonym sapuser for OPS$SIDADM.SAPSUER;

# if synonym already exists drop the existing synonym by the following command

drop public synonym sapuser;

#If another name <name> is returned as first value:

DROP SYNONYM "<name>".SAPUSER;

# AND CREAT it again with above command

# To allow access to the synonym (or the associated table), a grant
           needs to be executed. The authorization for this has only the
           ops$ user who is the owner of the actual table - that is
           ops$<sid>adm. Therefore, you need to log on with the
           corresponding operating system user (<sid>adm) and execute the
           following commands:

            CONNECT /
           GRANT SELECT, UPDATE ON SAPUSER TO "OPS$SAPSERVICE<sid>";

# Now you can recreate the synonym (not PUBLIC, if possible):

 CREATE SYNONYM "OPS$SAPSERVICESID".SAPUSER FOR "OPS$SIDADM".SAPUSER;

 CREATE SYNONYM OPS$SAPSERVICESID.SAPUSER for OPS$SIDADM.SAPUSER;
 

# COMMIT AFTER COMPLETION of the activity & restart the DB