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:
Screen Shot 2017-04-17 at 11.55.06 AM

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.

2 thoughts on “Migrating AWR Warehouse Repository to a new OEM Target

  1. Excellent and very useful article. I’d love to hear or get any recommendations or required steps and configuration pre-requisites on adding an 18c database as a source DB to an AWR repository DB. There doesn’t seem to any docs that talk about. Or Most of the articles on the internet are very sparse in details about such a setup. Thanks.

    Regards
    Leon Poyau
    Oracle DBA

    Like

    • You shouldn’t need to do anything special, we’ve got 18c & 19c databases as AWR sources although mostly they were upgraded rather than adding as new sources. Significant pre-requisite really is that ARWW repository version has to be greater than any source DB.

      Like

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