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
