AWRW: AWR Datawarehouse cleanup

I have 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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

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