Posted in EM12c, Oracle

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';
commit;

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.

Advertisements

Author:

I am an Oracle DBA with experience in Maximum Availability technology such as RAC, Data Guard, Grid Control and ASM. I worked with version 9i through 12c on a number of operating systems. Next to that I have an extensive experience with PL/SQL, Oracle eBusiness Suite and shell scripting. Specialties: Oracle RAC & ASM (Configuration/Monitoring/Tuning) Oracle Data Guard (Configuration/Monitoring/Tuning) EM12c (Installation / troubleshooting) SQL & PLSQL *nix Shell scripting

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.