We all know that we need to create RCU schemas before starting OBIEE 11G (In fact, for any Fusion Middleware application) Installation. Because it is the base store for FMW.
Recently I came across a scenario where my customer created RCU schemas with Oracle Repository Creation Utility (RCU) version 188.8.131.52.0 and Installed OBIEE 184.108.40.206.0. Now after new OBIEE 220.127.116.11.0 release he wants to upgrade his application from 18.104.22.168.0. But he is not interested at the In-place upgrade. So we uninstalled weblogic and OBIEE 22.214.171.124.0 and started RCU 126.96.36.199.0 to drop the _BIPLATFORM and _MDS schemas.
The RCU shown up with successful message and so we thought everything is cleaned up and it’s good to create schemas again. I started RCU 188.8.131.52.0 and selected Oracle Business Intelligence component and given prefix as DEV. But am not able to assign this prefix and got a message like “The specified prefix already exists”. Though I got successful dropped schemas message, just for cross check I looked at database for the DEV_BIPLATFORM and DEV_MDS schemas. There I am not seeing any of them.
Then I checked at dropped schemas log generated by RCU and realized that there is an entry in System.SCHEMA_VERSION_REGISTRY$ table which is stopping me to assign the same DEV prefix which we used for earlier version schemas.
Actually the RCU should drop schemas using mdsschemamanager.pl script. This script has capability to do cascade delete and will delete entries in System.SCHEMA_VERSION_REGISTRY$ table.
To make sure this log into RCU DATABASE as sys and run the following SQL Query:
Select * from System.SCHEMA_VERSION_REGISTRY$;
If this query returns any record then it means the RCU didn’t drop the schemas properly.
Actually what happened in this case is the RCU schemas were not dropped using mdsschemamanager.pl script, it’s just dropped with normal schema delete and the resulting repository record for the schema is not clean. Keep in mind, this is just in case if you want to use same prefix. Otherwise RCU will let you proceed to create new prefixed Schemas.
If the RCU schemas have been deleted incorrectly using the RCU drop user command, then We need to clean these entries before starting new repository schemas creation.
To do this, run the following SQL*Plus command as sys to complete the removal of the RCU schemas records in the repository database:
delete from System.SCHEMA_VERSION_REGISTRY$;
Once you confirmed there are no entries in System.SCHEMA_VERSION_REGISTRY$ table and start the RCU to create repository schemas with the prefix (DEV in my case). The RCU will go smooth and wont complain with any error.
Hope this helps you in your RCU issues troubleshooting.