This post explains how to configure and use the new AWR warehouse functionality present in Enterprise Manager Cloud Control 12.1.0.4. This new feature offers the possibility to store date from the AWR of your main databases in the EM AWR warehouse.

As OEM AWR warehouse automatically extracts the AWR data of the database you have selected, there is no impact on your production databases. The main advantage is that it allows to keep AWR historical data beyond the retention period of the target databases. Another benefit is a performance improvement and a space gain of the target databases because AWR data are uploaded in a centralized AWR warehouse.

We have to apply a patch to get the AWR warehouse functionality available in the menu Database Performance. For Linux x86-64, you have to apply the patch 19176910 which contains the EM DB plugin bundle patch 12.1.0.6.2. Before applying of the patch the screen appears as follows:

 

aw1

We have to use the latest Opatchversion available for 11.1 release.

Watch out OPatch 11.2.0.x.0 version is not supported with Oracle Management Service (OMS) 12.1.0.3 and 12.1.0.4

 

oracle@vmtestoraem12c:/u01/app/oracle/MiddleWare_12cR4/oms/OPatch/ [oms12c] opatch version

OPatch Version: 11.1.0.11.0

OPatch succeeded.

 

Then we can run:

 

oracle@vmtestoraem12c:/home/oracle/19176910/ [oms12c] opatchauto apply -analyze

OPatch Automation Tool

Copyright (c) 2014, Oracle Corporation. All rights reserved.

OPatchauto version : 11.1.0.11.0

OUI version       : 11.1.0.12.0

Running from       : /u01/app/oracle/MiddleWare_12cR4/oms

Log file location : /u01/app/oracle/MiddleWare_12cR4/oms/cfgtoollogs/opatch/opatch2014-08-25_16-45-18PM_1.log

OPatchauto log file: /u01/app/oracle/MiddleWare_12cR4/oms/cfgtoollogs/opatchauto/19176910/opatch_oms_2014-08-25_16-45-20PM_analyze.log

[Aug 25, 2014 4:46:33 PM]   Prerequisites analysis summary:

——————————-

The following sub-patch(es) are applicable:

oracle_sysman_db91:19051532

oracle_sysman_vt91:19060193

oracle_sysman_mos91:18873245

oracle_sysman_emas91:19051543

oracle_sysman_ssa91:19051528

 

 

We can check if any patches are installed in the OMS:

 

oracle@vmtestoraem12c:/home/oracle/19176910/ [oms12c] opatchauto lspatches

OPatch Automation Tool

Copyright (c) 2014, Oracle Corporation. All rights reserved.

There are no patches installed in the OMS system.

Then we stop the OMS:

 

oracle@vmtestoraem12c:/home/oracle/19176910/ [oms12c] emctl stop oms

Oracle Enterprise Manager Cloud Control 12c Release 4

Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.

Stopping WebTier…

WebTier Successfully Stopped

Stopping Oracle Management Server…

Oracle Management Server Successfully Stopped

Oracle Management Server is Down

Then we run under the patch directory : opatchauto apply

Once the patch is applied you can start the oms with the classical command : emctl start oms

Finally we have the AWR warehouse menu:

 

aw2

 

We have first to configure the AWR warehouse repository:

 

aw3

 

 

You select the configure button:

 

aw4

 

You enter the database and host credentials:

 

aw4

 

You configure the snapshot management for a one year retention period, you can also define the snapshot upload interval from 1 hour to 24 hours:

 

aw6

 

The caw_load_setup job_%id is successful.

Now we have access to the AWR warehouse screen where we can add databases:

 

aw7

 

When you select add, you have to define preferred credentials to the database you add and add some grants:

 

aw8

We have to allocate the execute privilege on dbms_swrf_internal to system:

SYS> grant execute on sys.dbms_swrf_internal to system;

Grant succeeded.

 

For the NMO error, I forgot to run the root.sh agent 12c script:

 

[root@vmtestoradg1 ~]# cd /u00/app/oracle/agent12c/core/12.1.0.4.0

[root@vmtestoradg1 12.1.0.4.0]# ./root.sh

Finished product-specific root actions.

/etc exist

 

Finally the databases are added:

 

aw9

 

We have to grant the access to the databases added, we select a target database and we choose Privileges:

 

aw10

 

We select the sysman administrator account:

 

aw11

 

As we have configured the snapshot upload interval to 24 hours, we will have data every day:

 

aw12

 

From this dashboard you can make ADDM comparaisons, view ADDM reports, ASH analytics or directly go to the performance page of the database you have selected.

The AWR warehouse feature requires the diagnostic pack license; This new feature seems very interesting due to the central and consolidated AWR warehouse. I will test this exciting feature deeper in the next weeks.