I realize that the majority of blog posts I have made are for problems I have encountered. I think is only natural as most of my time is spent investigating the few occasions where things don’t work as expected rather than the 99.99% of the time when things just work. Even when there are problems the code is normally very well instrumented, and there is such a wealth of great technical information available, troubleshooting issues can be almost enjoyable.
With a view to re-balance this state of affairs somewhat, I thought I would put down a few thoughts on AWR Warehouse. I am continually surprised that so few people seem to be using it, so I will try to do my part address this, and hopefully persuade you to give it a shot. If you are not using it, feel free to share via a comment why not; if you are using it also feel free to share any thoughts, good or bad about the product.
Licensing
One of the main reasons to consider using AWRW can be found in the Database Licensing Information User Manual. I quote the relevant section:
A separate single instance Oracle Database can be installed and used as an infrastructure repository for RMAN, Oracle Enterprise Manager Cloud Control, Automatic Workload Repository (AWR) Warehouse, Global Data Services Catalog, and Grid Infrastructure Management Repository without additional license requirements, provided that all the targets are correctly licensed. It may not be used or deployed for other uses.
The infrastructure repositories for RMAN, Oracle Enterprise Manager Cloud Control, AWR Warehouse, and Global Data Services Catalog can be shared in one database, or deployed in separate databases. Enterprise Edition must be used for the infrastructure repository database(s).
If Oracle RAC is used for the infrastructure database, then Oracle RAC licenses for all servers, and Enterprise Edition licenses for all but the first server, are required.
If Data Guard is used for the infrastructure database, then an Enterprise Edition license for the standby server is required.
So paraphrasing (note I am neither a lawyer nor a licensing expert), as long as the source databases are correctly licensed, you don’t use RAC or DataGuard, and you don’t use it for anything else, you don’t need to worry to worry licensing on AWR Warehouse Repository. This can (in fact must be) Enterprise Edition as it makes extensive use of the partitioning option.
There are not many occasions when Oracle corporation gives stuff out for free, when they do I suggest to take advantage.
The licensing advantages become even more attractive when you realize that AWRW actually saves you licensing on source databases by allowing you to offload AWR data storage and analysis from the source database.
Note, don’t even think about using an existing database for this purpose, especially not the OEM Repository database; trust me, on this, you’ll thank me later.
Tight Integration into Enterprise Manager
Database administrators that have not embraced Enterprise Manager (OEM) for managing their databases, may consider this disadvantage, however I believe that all companies that manage more than a handful of databases, should be fully utilizing the power of OEM; its benefits far outweigh any overhead in administration.
AWRW initial configuration and the addition of database sources is basically a case of making sure they are discovered within OEM, defining preferred credentials, and then clicking a few buttons.
Once databases are contributing to AWRW, making use of the historical data is again just a case of clicking a few buttons.
AWR reports can be easily run for data in AWRW rather than just local AWR data, comparisons between different databases from at the same, or different, periods of time can be made.
ASH analytics is another area where the power of AWRW and OEM combine to provide some powerful tools for in-depth analysis.
Database Versions
The AWRW documentation states that:
(The AWR Warehouse Repository) must be an equal or higher database version of the source databases it accommodates.
You might consider this a disadvantage, however I see it differently. It is an opportunity to try out upgrading and using new database versions with non-trivial (actually substantial) volumes of data before rolling out to databases where developers or users complain in case things go wrong.
We recently upgraded our AWRW Repository to 12.2. We had a few issues (as expected), but we could take our time resolving them, because we (the DBAs) were the only ones who noticed any outage. After this upgrade completed, we then felt brave enough to upgrade our OMR Repository database which went through much more smoothly.
Of course perform due diligence, practice on a test system, and make sure you have backups which will allow you to roll back in case things go wrong.
Summary
It’s license free, extends the power of Enterprise Manager, and gives you low-risk exposure to new database versions.
Why aren’t you using it? Seriously; let me know via comments.