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.
2 thoughts on “Migrating AWR Warehouse Repository to a new OEM Target”
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.
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.