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:
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:
We have first to configure the AWR warehouse repository:
You select the configure button:
You enter the database and host credentials:
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:
The caw_load_setup job_%id is successful.
Now we have access to the AWR warehouse screen where we can add databases:
When you select add, you have to define preferred credentials to the database you add and add some grants:
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:
We have to grant the access to the databases added, we select a target database and we choose Privileges:
We select the sysman administrator account:
As we have configured the snapshot upload interval to 24 hours, we will have data every day:
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.