Oracle OEM Cloud Control 12.1.0.4 - AWR Warehousedbi services Blog | Oracle, SQL Server (etc

Oracle OEM Cloud Control 12.1.0.4 - AWR Warehousedbi services Blog | Oracle, SQL Server (etc.)

Oracle OEM Cloud Control 12.1.0.4 - AWR Warehouse Posted on Sep 12, Posted by Pierre Sicot Category Database Administration & Monitoring

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:

 

1 / 11

Oracle OEM Cloud Control 12.1.0.4 - AWR Warehousedbi services Blog | Oracle, SQL Server (etc.)

 

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

2 / 11

Oracle OEM Cloud Control 12.1.0.4 - AWR Warehousedbi services Blog | Oracle, SQL Server (etc.)

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_201408-25_16-45-20PM_analyze.log

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

                             -------------------------------

                             The following sub-patch(es) are applicable:

3 / 11

Oracle OEM Cloud Control 12.1.0.4 - AWR Warehousedbi services Blog | Oracle, SQL Server (etc.)

                             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

4 / 11

Oracle OEM Cloud Control 12.1.0.4 - AWR Warehousedbi services Blog | Oracle, SQL Server (etc.)

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

5 / 11

Oracle OEM Cloud Control 12.1.0.4 - AWR Warehousedbi services Blog | Oracle, SQL Server (etc.)

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:

 

6 / 11

Oracle OEM Cloud Control 12.1.0.4 - AWR Warehousedbi services Blog | Oracle, SQL Server (etc.)

We have first to configure the AWR warehouse repository:

 

 

 

You select the configure button:

 

7 / 11

Oracle OEM Cloud Control 12.1.0.4 - AWR Warehousedbi services Blog | Oracle, SQL Server (etc.)

 

You enter the database and host credentials:

 

8 / 11

Oracle OEM Cloud Control 12.1.0.4 - AWR Warehousedbi services Blog | Oracle, SQL Server (etc.)

Yousnapshot configureupload the snapshot intervalmanagement from 1 hour tofor24a hours: one year retention period, you can also define  the

 Now The caw_load_setup is successful. we have access job_%id to the AWR warehouse screen where we can add databases:

9 / 11

Oracle OEM Cloud Control 12.1.0.4 - AWR Warehousedbi services Blog | Oracle, SQL Server (etc.)

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

 [For We have to allocate execute privilege on dbms_swrf_internal to system: SYS> grant execute on sys.dbms_swrf_internal to system; Grant succeeded. the NMO error, I the forgot to/u00/app/oracle/agent12c/core/12.1.0.4.0 run the root.sh agent 12c script: root@vmtestoradg1 ~are ]# cd 12.1.0.4.0]# ./root.sh Finished product-specific root actions. /etc exist  Finally the databases added:

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

 We select the sysman administrator account:

10 / 11

Oracle OEM Cloud Control 12.1.0.4 - AWR Warehousedbi services Blog | Oracle, SQL Server (etc.)

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

 From or directly this dashboard go to the performance you canand make page ADDM of Oracle the comparaisons, database youlicense; view have ADDM selected. reports, ASH seems analytics The interesting deeper AWR inTagged the warehouse due next to the weeks. central feature requires consolidated the diagnostic AWR warehouse. pack I will This test new this feature exciting feature very Tags: in: Oracle 12c Enterprise Manager Cloud 12c

11 / 11