Fixing OPS$ ORA-1017 after system copy \ refresh
Every now and then system copy must be done. Some call it System Refresh – meaning taking a Non Production system and refresh it’s data from a production system.
There are all kinds of ways to perform these system copies – from using the SAP standard way to cloning DB’s inside a storage machine.
After finishing the Sys copy \ Refresh and bringing up the newly refreshed system the OPS$ mechanism won’t work and in most cases will produce ORA-1017 Error.
Solutions may be found in all sorts of notes and forums, the following is my “step by step” way to resolve this issue (for Windows):
1. Connect to the db as sysdba and run:
SELECT OWNER FROM DBA_TABLES WHERE TABLE_NAME = ‘T000’;
Take note of the result – this would be the SCHEMAOWNER.
2. DROP TABLE “OPS$<DOMAIN>\<SID>ADM”.SAPUSER;
3. Use the SQL script in note 50088 https://service.sap.com/sap/support/notes/50088 according to the appropriate ORACLE version.
The syntax would be:
@oradbusr.sql <SCHEMAOWNER> NT <DOMAIN> <SID>
4. Restart the system.
5. ORA-1017 will not appear and OPS$ mechanism is back on track
If “ERROR => ORA-1403 when accessing table SAPUSER” appears just fix it using:
INSERT INTO “OPS$<DOMAIN\<SID>ADM”.SAPUSER VALUES (‘<SCHEMAOWNER>’ , ‘password‘);