How to clean EM12c repository after a failed installation or upgrade

Well, as nothing is perfect, it happened to me too…..I had a “never seen before” failed EM12c upgrade on a test environment (fortunately :)) and this helped me to do it from scratch:

Note: this is documented on Doc ID 1395423.1 (useful for one who has access, for those who don’t ….you can check it below)

If BI Publisher is not associated with Enterprise Manager 12c Cloud Control, use the following procedure.

Login to the repository database as SYS user and execute the below:

1. Drop the repository users:

drop user SYSMAN cascade;
drop user SYSMAN_MDS cascade;
drop user MGMT_VIEW cascade;
drop user SYSMAN_RO cascade;
drop user SYSMAN_OPSS cascade;
drop user SYSMAN_APM cascade; 
drop user SYSMAN_BIP;
drop user CLOUD_SWLIB_USER cascade;
drop user CLOUD_ENGINE_USER cascade;
drop role MGMT_USER;

2. Drop the synonyms:

SQL> spool synonyms.lst
SQL> select 'drop '|| decode(owner,'PUBLIC',owner||' synonym '||synonym_name, ' synonym '||owner||'.'||synonym_name) ||';' from dba_synonyms where table_owner in ('SYSMAN', 'SYSMAN_MDS', 'MGMT_VIEW', 'SYSMAN_BIPLATFORM', 'SYSMAN_APM', 'SYSMAN_OPSS', 'SYSMAN_RO') ;
SQL> Spool off
SQL> @synonyms.lst

3. Drop the tablespace objects:

SQL> spool tablespaces.lst
SQL> select 'drop tablespace '||tablespace_name||' including contents and datafiles;' from dba_tablespaces where tablespace_name like 'MGMT%';
SQL> Spool off
SQL> @tablespaces.lst

4. Drop the registry objects:

delete from SCHEMA_VERSION_REGISTRY where COMP_NAME='Metadata Services';
delete from SCHEMA_VERSION_REGISTRY where COMP_NAME='Authorization Policy Manager';
delete from SCHEMA_VERSION_REGISTRY where COMP_NAME='Oracle Platform Security Services';

If BI Publisher is associated with Enterprise Manager for running reports, then follow this procedure instead.
1. Run DBCA to create a template (structure only) of the existing repository database.
2. Run DBCA to drop the instance storing the EM repository.
3. Run DBCA to create a new instance, using the template created from the old repository instance, to support a new EM 12c installation.