DBA Kevlar

Tips, tricks, (and maybe a few rants) so more DBAs become bulletproof!

AWR Warehouse in EM12c, Rel 4, Part II

So, there is a lot to cover on this topic and I hope Part I got you ready for more!  Part II, I’m going to cover the following areas of the AWR Warehouse:

  • Installation
  • Access
  • ETL Load Process

Installation

Let’s start with Prerequisites:

  • The AWR Warehouse Repository must be version 11.2.0.4 or higher.
  • The Targets and I think this will get a few folks, need to be 12.1.0.2 or higher, (now folks know why some docs aren’t available yet!:))
  • Just as with installing ASH Analytics, etc., make sure your preferred credentials are set up for the database you plan to use for the AWR Warehouse repository.
  • You should already have your database you plan on using for the AWR Warehouse repository discovered in your EM12c console.
  • Any database targets you wish to add at the this time to the AWR Warehouse setup should also be pre-discovered.
  • For a RAC target or AWR Warehouse, ensure you’ve set up a shared location for the ETL load files.

Now the repository is going to require enough space to receive anywhere from 4-10M per day in data, (on average…) and Oracle highly recommends that you don’t run anything else on the AWR Warehouse database, so don’t use your current EM12c repository database or your RMAN catalog, etc…  Create a new repository database to do this work and manage the data.

To Install:

Click on Targets, Databases, which will take you to your list of databases.  Click on the Performance drop down and choose AWR Warehouse.  As no AWR Warehouse has been set up, it will take you promptly to the wizard to proceed with an initial workflow page.

Click on the Configure option and choose the database from your list of available databases to make your AWR Warehouse repository database. Notice that you next need to select Preferred Credentials for both the database target and the host it resides on.  As the ETL process does perform host agent host commands, both these credentials are necessary.

Next page you’ll set up the retention period for your AWR Warehouse.  You can set the value to number of years or choose to retain the data indefinitely for you data hoaders… :)  You can then set the interval to upload data, (how often it processes the ETL load…) which defaults at 24hrs, but you can set it as often as once per hour.  Due to potential server load issues considering size of environment, # of targets, etc., I would recommend using the default.

Next set up the location for the ETL dump files.  For RAC, this is where you will need to specify the shared location, otherwise, for non-RAC environments, the agent state directory will be default.  I recommend setting up an exclusive directory for RAC and non-RAC targets/AWR Warehouse.

Click on Submit and monitor the progress of the deployment in the EM Job Activity.  The job will be found quickly if you search for CAW_LOAD_SETUP_*.

Accessing the AWR Warehouse

Once set up, the AWR home can be accessed from the Targets menu, click on Databases, then once you’ve entered the Databases home, (databases should be listed or shown in a load map for this screen, click on Performance and AWR Warehouse.

The following dashboard will then appear:

awr_ware_3

From this dashboard you can add or remove source targets and grant privileges to administrators to view the data in the AWR Warehouse.

You can also view the AWR data, run AWR reports, do ADDM Comparisons, ASH Analytics and even go to the Performance home for the source target highlighted in the list.

awr_ware_4

Note for each of the databases, you can easily see the Source target name, type, DB Name, (if not unique…) # of Incidents and Errors.  You can see if the ETL load is currently enabled, the dates of the newest and oldest snapshots in the AWR Warehouse and the # of AWR snapshots that have been uploaded to the repository.

Now how does this all load in?

The AWR Warehouse ETL

The process is actually very interesting.  Keep in mind.  These are targets, source and destination, but what will drive the ETL job?  How will the job be run on the targets and then to the destination AWR warehouse?  Now I was sure the repository used TARGET_GUID to keep everything in order, but since the ETL does have to push this data from a source target through to the destination repository via the host, there is definite use of the DBID, too.

To upload the AWR snapshots from the target to the AWR warehouse, an extract is added with a DBMS job as part of a new collection on a regular interval.  There is first a check to verify the snapshot hasn’t been added to the warehouse repeatedly and then the extract is identified by DBID to ensure unique info is extracted.

The second step in the ETL process is the EM12c step.  Now we are onto the EM Job Service and it submits an EM Job on the host to transfer the data from the target to the warehouse host for the third part of the ETL process.  This is an agent to agent host process to transfer the dump files directly from target host to AWR warehouse host, so at no time do these dump files end up on the EM12c host unless you were housing your warehouse on the same server as your EM12c environment.

The last step for the ETL process is to then complete the load to the AWR warehouse.  This is another DBMS job that takes the dump files and imports them into the AWR Warehouse schema.  DBID’s are mapped and any duplicates are handled, (not loaded to the final warehouse objects…)The ETL is able to handle multi-tenant data and at no time is there a concern then if more than one database has the same name.

Retention Period

Once the retention period has been reached for any AWR warehouse data, the data is purged via a separate job set up in EM12c at the time of creation, edited later on or set with the EM CLI command.  A unique setting can be made for one source target from another in the AWR Warehouse, too.  So let’s say, you only want to keep three months of one database, but 2 years of another, that is an option.

For Part III, I’ll start digging into more specifics and features of the AWR Warehouse, but that’s all for now!

Print Friendly

facebook comments:

  • Pingback: 12.1.0.4 is HERE!! – PeteWhoDidNotTweet()

  • Scott

    Thanks for the great article, do have a question around cloning. What happens when a database is cloned? For example a non production database is sending AWR data to the warehouse. Since it is non prod it is refreshed from production a weekly database. With a new DBID generate on each RMAN duplicate does that mean I can’t compare AWR data across RMAN clones? After cloning does the ETL process need to be setup again for the non prod database?

  • http://dbakevlar.com dbakevlar

    Yes, it is going to bread the source piece of the ETL unless it is already set on the original and you have done the proper DBID resets, which is expected. At the same time, if the DBID has changed, the information will change as well. A job could be set up to easily track this, resync and re-install.
    Personally- I would want it to break it and you would see the “ENABLED” status in the dashboard no longer displayed and be able to track, correct as needed.

    Hope this helps!
    Kellyn

  • http://ardentperf.com Jeremy Schneider

    This looks like a really interesting new feature! I’m a little disappointed that they don’t support 11.2.0.3, but it looks really cool and definitely something that’s been needed for a long time!

    -Jeremy

  • Pingback: AWR Warehouse in EM12c, Rel 4, Part II - Oracle - Oracle - Toad World()

  • Mikhail Velikikh

    Hello Kellyn,

    @ Click on Targets, Databases, which will take you to your list of databases.
    @ Click on the Performance drop down and choose AWR Warehouse.
    @ As no AWR Warehouse has been set up, it will take you promptly to the wizard to proceed with an initial workflow page.

    I did a fresh install of OEM 12.1.0.4 today.
    Username->About Enterprise Manager shows: Version 12.1.0.4.0

    Targets->Databases->Performance contains next entries:
    SQL Performance Analyzer
    Run SQL…
    Database Replay

    No AWR Warehouse :(
    Same for SYSMAN user.
    I have many discovered databases, versions: 11.2.0.3, 12.1.0.1.

    Searched MOS Community.
    Found topic with probably same issue: https://community.oracle.com/message/12507512#12507512

    I carefully read prerequisite section:

    @ The AWR Warehouse Repository must be version 11.2.0.4 or higher.
    @ The Targets and I think this will get a few folks, need to be 12.1.0.2 or higher, (now folks know why some docs aren’t available yet!:))

    I’m in doubt.
    AWR Warehouse entry will appear only when I install 12.1.0.2? :)
    Thanks.

    Best regards,
    Mikhail Velikikh.

  • http://dbakevlar.com dbakevlar

    I’ve been talking about it in presentations, but have been quiet and patiently waiting for the release of the feature. Although a couple of us had access to it here internally, there is a patch that is needed from 12.1.0.2. With this requirement, it was quickly decided to leave the drop down, etc. out of the release, choosing to create a patch to enable it with the 12.1.0.2 patch. Patience, it will soon arrive… :)

    I will be doing a webinar on the topic for ODTUG come July 17th if you are interested in more on the topic though! :)

  • Pingback: Configuring AWR Warehouse (AWRW) in EM12c | Pardy DBA()

  • Igor Veselinovic

    Hi Kellyn

    I configured AWR WH and added 2 DBs, one (Clod repository) is on same host and one is on different host in test environment.

    After successfully creating dmp files both transferAWR processes always fail with:

    Credential not found guid {0}

    13:42:08 [ERROR] checkDumpSpace: aborting, error in reading warehouse credentials

    I set predefined credentials for all three DBs (sys) and both hosts (oracle on cloud server and agoracle on test DB server).

    Best regards from Slovenia
    Igor

  • DBAkevlar

    Good Morning Igor,

    It sounds like you have the database credentials set up, but I’m a bit concerned that you are using the SYS account. The “checkDumpSpace error sounds like it could be a privilege issue to write to the OS level location and I don’t see host credential information. Please see the following support doc to assist in setting it up: http://docs.oracle.com/cd/E24628_01/server.121/e55047/tdppt_awr_warehouse.htm#CACDAGDG

    Thank you and let me know if you need anything else,
    Kellyn

  • Igor Veselinovic

    It’s Morning for you :)

    I was started with SYS to check if all is working as need and later I’ll use my DBA account. As our environment is very close to PCI DSS I hit many times to some undocumented “challenge”.
    I will read support doc tomorrow as here it is 5 p.m.
    Where I can find where to focus where and how transferAWR process is running? Any way to debug it?

    Thank you for your attention
    Igor

  • DBAkevlar

    Definitely look in the dump directory you chose for you AWR setup for the source database to ensure that 1. the file is created. Also look to see if there is anymore info in the dbms_scheduler_job_details
    Thanks,
    Kellyn

  • Igor Veselinovic

    Hi Kellyn

    Dmp files (with logs) are on …/agent_inst directories on both hosts (for one DB on the same host as AWR_WHDB and for one test DB on different host)

    In both gcagent.log I found:
    ..cut..
    Dispatching request: PerformStreamedOperationRequest
    ..cut..
    Reporting response: PerformStreamedOperationResponse (exitCode:0 output:null error:null) (request id 1)

    I checked script transfer_awr.pl and I could not found ZIP files which should be done from dmp files.

    Thanks,
    Igor

  • DBAkevlar

    Have you added the is level preferences credentials?

    Please confirm.
    Thanks,
    Kellyn

  • Igor Veselinovic

    List of my preferred credentials:
    2 Default Preferred Credentials
    SYSDBA Database Credentials (sys)
    Database Host Credentials (agoracle)
    and
    2 Target Preferred Credentials
    DB_awr – Database Host Credentials (oracle)
    DB_oragrid – Database Host Credentials (oracle)

    SYS pwd are same on both DBs and OS users are different on CLOUD/AWR server and test DB server.

    Thanks,
    Igor

  • http://dbakevlar.com dbakevlar

    Can you use this same login to perform a datapump to this directory?

  • Igor Veselinovic

    Hi Kellyn

    I removed both DBs from AWR WH, cleared all dump and log files, set all preferred credentials (agents, hosts, instances…) again, restart agents and error is still same.

    Best regards
    Igor

  • DBAkevlar

    Hi Igor,
    I’ve asked a number of our folks here who work with the product and no one has run into this issue. They recommend you open an SR, as they feel it’s a problem with the share that you are writing to.
    Thanks and good luck!
    Kellyn

  • Igor Veselinovic

    Hi Kellyn

    Thank you again for your time.
    I will open SR and hope :)

    Best regards
    Igor

  • Facebook
  • Google+
  • LinkedIn
  • Twitter