AWRW: AWR Datawarehouse cleanup

I got one AWR Warehouse that has been running for a few years now. Time for a cleanup after EMCC 13.3 upgrade.

The EMCC UI is confusing as it show the view of the AWR Warehouse instance, instead of filtering whatever database information relevant for the current EMCC instance we are looking at, remember that the AWR Warehouse can be connected to many EMCC.

Since the UI does not offer all capabilities to troubleshoot, Here are a few tips:

#1 Use the emcli from all EMCC clients.

To view all source db:

$ORACLE_HOME/bin/emcli awrwh_list_src_dbs

To delete a source db:

$ORACLE_HOME/bin/emcli awrwh_remove_src_db -target_name=<dbname> -target_type=<rac_database or database_instance>'

Unfortunately the command above will hit the EMCC metadata, when, when a target is deleted in EMCC, the snapshots located in the AWR Warehouse are not deleted. So this command has many chance to fail with an error target not found, when the database is long gone.

Solution is to cleanup the AWR Warehouse database directly.

Connect with dbsmp.

To view all source databases, check the table CAW_SRC_DBS. Possibly, identify the em_id that corresponds to obsolete EMCC instances.

To delete a repository (remember that the AWR Warehouse can connect AWRs from many EMCC):

SQL> execute mgmt_caw_load.cleanup(<the emid>);

To mark a database as deleted:

SQL> execute mgmt_caw_load.mark_db_deleted<the em id>, '<db_name>','rac_database' ir 'oracle_database');

Do not forget to commit.