Resetting the OPS$ Mechanism

It always bothered me that not only is it possible to screw up the OPS$ mechanism in SAP, when running Unix/Oracle flavor. It's also quite easy. And then there's one thing wrong, then another, and the BRTOOLS just dont really cut it when it comes to fixing the errors.... It's always manual labor. And especially whenever someone is doing a system restore, homogenous system copy or similar, there's always problems afterwards.

So I've created a little script that resets all of it for me (and it also creates an ops$ mechanism for the Oracle user so that I can run backups with "-u /" istead of specifying the password, or storing the password in a plain text file).

As ora<SID> I go to sqlplus / as sysdba and issue the commands:

drop user "OPS$<old_sid>adm" cascade; #In case of homogenous system copies, remove incorrect SIDs
drop user "OPS$ora<old_sid>" cascade; #In case of homogenous system copies, remove incorrect SIDs
drop user "OPS$<new_sid>adm" cascade; #Regardless of error, remove the current SID too
drop user "OPS$ora<new_sid>" cascade; #Regardless of error, remove the current SID too
drop table SAPSR3.sapuser;   ## possibly other schemaowner, like SAP<SID>, <SID>R3, SAPR3
drop public synonym sapuser;
create user "OPS$<new_sid>adm" default tablespace <<usually psapsr3usr>> temporary tablespace <<usually psaptemp>> identified externally;
grant connect, resource to "OPS$<new_sid>adm";
create table "OPS$<new_sid>adm".sapuser ( USERID VARCHAR2(256), PASSWD VARCHAR2 (256));
insert into "OPS$<new_sid>adm".sapuser  values ('SAPSR3', 'sap'); #NOTE that the username and password ofcourse must reflect the schemaowner and current password
alter user sapsr3 identified by sap;  #and here the password must be set again
create public synonym sapuser for OPS$<new_sid>adm.sapuser;
create USER "OPS$ora<new_sid>" default tablespace system temporary tablespace <<usually PSAPTEMP>> identified externally;
grant DBA, CONNECT to "OPS$ora<new_sid>";
grant select, update on sapuser to "OPS$<new_sid>adm";

Test that this works by logging in as <sid>adm and issuing the command

R3trans -x 

Or as ora<sid>:

sqlplus / as sysdba

connect /

exit

This little snippet of code has saved me a lot of time once in a while. Sometimes I even think about making it remote executable, so I dont have to actually log on to a system whenever someone calls me with transport issues or other funny stuff following a bungled homogenous system copy.