Finding the AWR Warehouse link

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.

Finding the AWR Warehouse link

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.

Begin AWRW Configuration

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.

Database Selection

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.

Repository Configuration (Continued)

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.

Repository Ready

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.

Database Selection

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.

First Target DB

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.

Loaded

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.

Advertisements

10 thoughts on “Configuring AWR Warehouse (AWRW) in EM12c

  1. Siva

    hi- can I host multiple awr warehouses one per data center as I see tremendous slowness with close to 1 million awrs from 60 databases!

    Reply
    1. Brian Pardy Post author

      Hello Siva,

      That is a great question. I believe that currently EM12c only allows a single AWR warehouse per OEM environment. If you have performance problems I suggest filing an SR with Oracle so that they can know about issues with a large AWR warehouse or advise you on tuning.

      Based on a previous blog post from Kellyn Pot’Vin (http://dbakevlar.com/2014/06/awr-warehouse-in-em12c-rel-4/) I think the AWR warehouse should be able to handle huge datasets. Her post shows an AWR warehouse holding 63TB of data from 713 databases. I do not know what kind of hardware she has behind that AWR warehouse, but you may simply need faster drives, more RAM, more CPUs or only some database tuning.

      Reply
  2. Catherine

    Hi Brian
    I am trying to enable AWR Warehouse on my system ,I recently upgraded to EM Cloud Control version 12.1.0.5.0, I don’t have a GI DB so patch 19051570 will not apply for me.
    When I try to access AWR Warehouse in EM I get the error “You are not yet licensed to use the Database Diagnostics Pack. If you like this functionality, please see your super administrator about obtaining a license” but control_management_pack_access parameter returns DIAGNOSTIC+TUNING
    Could you provide any info on where I might need to go from here to get this enabled please?

    Reply
    1. Brian Pardy Post author

      Hi Catherine,

      First I have to mention that I have upgraded to OEM 13c, so I no longer have an EM12c environment available for testing. But I think I have a good idea of the source of this problem.

      Before proceeding please make sure that you have already purchased a license for the Database Diagnostics Pack and Database Tuning Pack for at least one of the databases you are monitoring through EM12c, I don’t want to encourage anyone to violate their licensing agreements.

      You will need to have access to the SYSMAN super administrator account for your EM12c system (or another user with super administrator permissions). Login to EM12c as a super administrator, then go to the Setup menu and select “Management Packs” -> “Management Pack Access”. On this screen you should see a list of all databases known to your EM12c system. Please make sure that BOTH the database you intend to use as the AWR Warehouse and the source database(s) from which you intend to collect AWR data have the checkbox checked under “Database Diagnostics Pack” and “Database Tuning Pack” for their rows in the table listed. You may need to select the “Licensable targets” radio button and the “Target Based” radio button to see the correct list.

      After your AWR Warehouse database and source database(s) are confirmed to have the boxes checked for the diagnostics and tuning packs, you’ll also need to check the box in the “Pack Access Agreed” column for each of those databases to indicate your acceptance of the licensing terms. (EDIT: You also need to click ‘Apply’ on the Management Pack Access screen to commit changes.)

      So to make the long story a little shorter: in addition to setting the “control_management_pack_access” startup parameter to “DIAGNOSTIC+TUNING”, you also have to tell EM12c, through the Management Pack Access screen, that those specific targets (and your AWR Warehouse target) should be enabled for OEM functionality that relies on the diag/tuning packs.

      Once those steps are done I believe that you can continue with the AWR Warehouse setup as I described. I generally make sure the correct settings are in the Management Pack Access page immediately after adding a new database, so I forgot to mention in this blog post to make those changes before attempting to proceed. Thanks for catching that!

      I hope this will allow you to continue with AWRW, I think that it will.

      Reply
  3. Mel

    Hi,
    I am using EM13c, I temporarily disable the snapshot upload of a database. But, when I am trying to enable it again, I am getting this error:
    EMDB-00769: Enabling Snapshot Upload failed for following targets: PAPA. This can be because of following reasons: 1. Host or Database credentials are not set, or could not be authenticated; 2. The DB User specified in Database credentials does not have DBA Privileges or 3. The Database is down, or the agent is unreachable Please see the log files for more information.
    I already tested host and db credentials in the Preferred and Named Credentials and they are working fine. I think I am missing something. Please help. Thanks!
    Mel

    Reply
    1. Brian Pardy Post author

      Hi Mel,

      I have not seen this issue before. I just tested disabling snapshot upload, then enabling it for one of the AWR warehouse DBs on my fully patched EM13cR2 installation and it succeeded without any problems. Do you know that this source database was successfully uploading to the AWR warehouse before you disabled the snapshot upload? If it was broken and not fully added to the AWRW, that could cause failures trying to enable snapshots again.

      I can think of a few other things to check, but you may have to file a service request for this issue: when you view the AWR warehouse page and see the list of databases uploading snapshots, are you using the same EM13c administrator account that is listed as the DB’s “owner” to do the disable/enable snapshot steps? I noticed that even if I use SYSMAN, I cannot disable/enable snapshots for a database configured by my regular EM admin account.

      Is it possible that your AWR warehouse database itself is down or inaccessible? I have had strange errors when my AWR warehouse DB got stuck with an archiver error due to running out of space for archived logs.

      Is it possible that the DB user credentials either for the AWR warehouse DB or the database from which you collect AWR data might have a soft password expiration in the password grace period and needs a password reset?

      Does any error information appear in the OMS or agent logs?

      I would also check the job activity page to see if you might have any AWR warehouse related jobs that are stuck or suspended or sitting in problem status.

      Also I suggest checking the saved/preferred credentials set for the EM admin user you’re using for agent targets on the OMS, AWR repository DB, and AWR source DB, just in case those might be an issue.

      There is also always a chance that the latest plugin/agent/OMS PSUs and bundle patches resolve this issue but I haven’t noticed anything that looks relevant in the bugs fixed lists.

      Good luck!

      Reply
  4. Mel

    Hi Brian,

    Thank you for your prompt and very detailed reply. I will check on your recommendations and will let you know asap.

    Mel

    Reply

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 )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s