Migrating AWR Warehouse Repository to a new OEM Target

AWR Warehouse is tightly integrated with Enterprise Manager, both the database which acts as the repository plus any databases that will contribute to it need to be discovered as targets within OEM before they can be used. If you are already using OEM to manage your databases (and you should be), this means that configuration is as simple as selecting the appropriate instance from a drop-down list of discovered targets; however it does have some disadvantages.

One of these disadvantages is that if the Enterprise Manger target of the repository database changes, then AWR Warehouse configuration is lost with it. The target could be changed due agent problems on the host forcing you to re-discover it. Even though it is the same database it is treated it as a new target.

If you attempt to re-configure by pointing it at the new target, a new AWRW instance is created (note the EM_ID field on many of the internal tables refers to this instance id). It is possible to re-add all source databases to this new instance, however it involves a lot of manual steps manipulating the internal tables to get everything working.  While I was researching this with a view to providing a step by step guide I found an easier way.

I will reproduce this situation, and walk through my solution. My AWRW repository is called OMR, and I have a database DGA contributing to it.

I remove instance OMR from OEM, and then re-add it.
When I go to the AWR Warehouse page, I can still see my source database is still listed, but note the error message:

To fix this, first identify the OEM GUID of the newly configured repository database:

 
SQL> SELECT
  2      target_guid
  3  FROM
  4      sysman.mgmt$target
  5  WHERE
  6          target_name = 'OMR'
  7      AND
  8          target_type = 'oracle_database';

TARGET_GUID
--------------------------------
4CAF735767113C73E0530201A8C0FFB8

Then update the AWRW repository table in OEM with this new GUID:

SQL> UPDATE SYSMAN.DB_CAW_REPOS_E SET TARGET_GUID = '4CAF735767113C73E0530201A8C0FFB8';
1 row updated.

SQL> COMMIT;

Commit complete.

Doing this seems to bypass the re-configuration process. I have tested that everything is working by performing “Upload Snapshots Now”, and all seems fine, however I haven’t performed extensive testing, and I would suggest exercising the usual caution before updating internal OEM tables.