Configuring AWR Warehouse (AWRW) in EM12c

Oracle Enterprise Manager 12cR4 introduces the new “AWR Warehouse (AWRW)” feature, permitting administrators to consolidate AWR statistics from many individual databases managed by OEM into a single AWRW repository database.

As with all Oracle features, you must pay attention to licensing here.  I will not discuss licensing other than to point you to the relevant documents which you must read and understand yourself: Oracle Database Licensing Information 12c Release 1.

Documentation

At the moment official documentation appears limited to MOS note 1907335.1 and one section of the Oracle Database 2-Day Database + Performance Tuning Guide.  Get familiar with it.

Prerequisites

Repository Database

You must use Enterprise Edition for the AWRW repository database.  You must use version 12.1.0.2 or higher, or version 11.2.0.4 with patch 18547891 applied. Oracle recommends you use a database not used for any other purpose. I strongly agree with that recommendation.  Do not use your OEM repository. Note that I had to enable the diagnostic and tuning packs on the AWRW repository database by setting the control_management_pack_access initialization parameter to “DIAG+TUNING” before EM12c would allow me to select it for the repository.  I cannot reiterate enough how much I wish Oracle would explicitly state that users may enable management packs on their limited-use repository databases that support EM12c, RMAN catalogs and AWRW, but only a sucker expects license clarity out of Oracle.

I have selected 11.2.0.4 with patch 18547891 for my AWRW repository.

Oracle Enterprise Manager

You must use Oracle Enterprise Manager 12cR4 (12.1.0.4), and your OMS must have at least the August 31, 2014 bundle patch (19391521, or a later bundle patch) applied.  Your agents must run version 12.1.0.4.2 or later (requiring patch 19051570).

Licensing

Double check your licensing one more time.  Do not use features you have not licensed or you will pay a lot of money once you get audited, and you will get audited.

Configuration

For the purposes of this post I will skip the database installation and configuration steps.  If you have not yet gained proficiency with base installation and configuration tasks, you should probably gain some experience there before diving in to the AWR Warehouse.  Install a database of the appropriate version and register it with EM12c.

Planning

Think about your architecture.  With the recent release of AWRW functionality, some rough edges still exist.  These will probably get cleaned up over the next few releases but they took me by surprise and I have not seen them documented anywhere.

Oracle Enterprise Manager Agent Considerations

Do you use a separate dedicated user account on your servers to run the OEM Agent?  I do. Your AGENT_INSTANCE_DIR will get used by AWRW as a place to hold Data Pump output containing each source database’s AWR data.  I had to make this directory group writable by the dba group.  You also need to make sure the volume where this directory resides has enough free space to hold AWR extracts, which end up quite large on a busy system.  You may need to add more space if you keep your agent on a dedicated filesystem, as I do.

Do you run multiple instances under isolated accounts that don’t share a group membership?  You will probably need to create a group they all share that can write to the AGENT_INSTANCE_DIR.

Preferred Credential Considerations

AWRW strongly depends on the preferred credentials set for a database instance by the user that adds the database to AWRW.  If you already heavily use preferred credentials and want to use a different preferred database login for AWRW extraction compared to your usual DBA activities, you may elect to create a dedicated EM12c administrator to maintain AWRW to avoid conflicts.

The AWRW extraction user in the target database must have the DBA role and must also have an explicit execute grant on package SYS.DBMS_SWRF_INTERNAL.  I have chosen to use the SYSTEM account, to match my other preferred credential usage, but a more secure setup would use an account dedicated to this task.

Space Considerations

Take a look at how much space AWR consumes in your SYSAUX tablespaces already.  Your AWRW repository will need at least this much space, multiplied by however long you plan to keep these AWR snapshots around.  This will get very large, very quickly.

Added 20140912: I highly recommend that you disable data file autogrowth on your AWRW repository database.  I experienced repeated hangs until I determined that my jobs continually got stuck when SYSTEM or SYSAUX nearly filled and they sat there waiting on data file operations I/O as the system failed to resize the data files or identify a deadlock.  Do not rely on data file autogrowth, at least when using an 11.2.0.4 AWRW repository.

Initialize The AWR Warehouse

To begin configuring the AWR Warehouse, you must login using an EM12c super administrator account, like SYSMAN.  Once logged in, go to the Databases target list screen.  Unfortunately for this purpose you must use the “Database Load Map” form of the screen and not the infinitely more useful “Deprecated Search List (with Metrics)” that I have up on screen 99.9% of the time. Click the Targets menu, select Databases from the submenu that appears, and then if you do not see a “Performance” menu, enable the “Database Load Map” radio button.

Click the Performance menu and select the “AWR Warehouse” item.

This button makes things happen

At this point, if you used a super administrator account, you should see a summary screen that briefly describes the feature with a link to click to begin configuration.  If you don’t, log out and come back with the SYSMAN account.

Click Configure to continue

The next screen offers a search box to select the database to use as your AWRW repository and the usual credential selector.  Select the correct database, choose a database credential (I first selected SYSTEM, which failed, so use SYS as SYSDBA) and provide host credentials.

Rough edge: no warning that you must use SYSDBA

Once you click Next, the tool will pop up a dialog box warning you to make sure that your repository database has the necessary patch installed, and then asks you to select how long the system should keep AWRW data.  You can also select a staging location for AWR data extract storage prior to data loading.

Diamonds and AWR Warehouses are forever

Click Submit on this screen and OEM will submit a job to initialize the AWRW repository.  To find this job later, if needed, go to the advanced job activity page and search for jobs of type “dbSetupCAW”.  The job should complete successfully if you have done everything correct so far.  On my system it only took six seconds, so just wait a moment and then reload the page, which should now look like this.

That was easy

Click on the database icon at the upper left to switch away from the repository configuration tab to the database selection tab.

No data yet

As of this point you no longer need to use the SYSMAN account.  I switched back to my regular account, then returned to this screen.

Click the Add button to begin adding your first database(s). OEM will prompt you with the usual target selection screen.  Choose one or more databases and then click the Select button.  AWRW will NOT prompt you for credentials at this time.  Instead it will silently use the database host and normal database user preferred credentials you have established for the database target.  Another rough edge I expect to work better in future versions.  AWRW will perform some initial validations of those credentials to make sure that the database user has the DBA role and the previously mentioned execute grant on SYS.DBMS_SWRF_INTERNAL.  If you have missed any of these steps OEM will tell you about it and prevent you from adding the database.  Again, later I expect this to include an automated setup that will fix those issues.

I can’t show you the name

At this point you can just walk away and within about 24 hours you should have AWR data loaded into the warehouse.  If you feel impatient, click on one of the lines for a database to select it, then choose “Upload Snapshots Now” from the Actions menu.  This will submit a job to extract and load the AWR data, which you can find later under the job type “Run AWR Extract, Transfer and Load”.  In the background, this job extracts AWR data to the AGENT_INSTANCE_DIR on the target database’s server, compresses the data, transfers it to the staging area defined during AWRW repository setup, then loads the transferred data into the consolidated AWR Warehouse repository.

One database in there. So many to go.

Summary

The size of and load on your selected database, along with the amount of AWR history you keep online, will influence how long each load takes.  My first load appeared to hang, with the AWRW repository database full of sessions waiting on enq: HW contention and buffer busy waits.  I ended up doing a shutdown abort and following the workaround instructions in MOS note 1912230.1.  I do not know if I truly needed to do this or not, but the symptoms sounded similar.  I’ve also noticed that some limits appear to exist.  I keep 42 days worth of hourly snapshots in each AWR, and my initial load only picked up 20 days / 500 snapshots.  This may represent rate-limiting as a way to trickle-load the AWRW, or it may mean AWRW does not yet play nicely with large AWR data.  Time will tell, and I fully expect future versions to shake out any bugs and to hold the DBA’s hand a bit more through the process.

I hope to cover using AWRW for performance tuning in a later post and I look forward to comments.

More Information

See these other fine posts for more information.

• AWR Warehouse in EM12c Rel. 4
• AWR Warehouse in EM12c, Rel 4, Part II
• AWR Warehouse Webinar from ODTUG
• AWR Warehouse, Status