Wednesday, March 25, 2015

AWR Warehouse

First create a database or you can use a with requires patches

Setup AWR Warehouse Repository
1.       Login to OEM
2.       Click on Targets>Databases
3.       Then click on Performance>AWR Warehouse
4.       Click configure button

5.       Select the database that will be used to store the AWR Warehouse DB. Then set the named credentials and click next.

6.       Leave all default and click submit we can change these settings later if we need to.

7.       Job will be submitted and the AWR Warehouse repository will be setup. You can monitor the job if you like by clicking the link

8.       Now we can see the AWR Warehouse repository is ready

Setup Source DBs to Upload AWRs
9.       Before adding databases to upload AWRs to warehouse we need to set preferred credentials for each target you will need the db host and db credentials set.
10.   Grant execute on SYS.DBMS_SWRF_INTERNAL to system in all databases that will upload AWR snapshot data to the AWR Warehouse repository. You can use a different account but make sure the account has DBA and the execution grant above is granted.
11.   In the AWR workload screen click the database icon then click add button.

12.   Select all the databases you want to upload AWR snapshots to the AWR Warehouse repository click select.

13.   Once the databases are added for upload you will see them in the upload AWR snapshot screen.

Manually Execute AWR Snapshot Upload
Note: In are example we setup the AWR snapshots to upload every 24 hours but we can also trigger a manual upload.

1.       Select the database then Actions>Upload Snapshots Now

2.       Job submits to start the upload you can monitor the job by clicking the link

Note: The job is just like an ETL it extracts the AWR snapshots from the source, transfers the data to the AWR warehouse repository server and then loads the data into the AWR Warehouse repository.

Depending on how much AWR snapshots there are the job can take up to 1 hour to complete

You can see there is snapshot data now

Performance Analysis with AWR Warehouse Data
1.       Select a database then select any of the different performance analysis tool

ASH Analytics

AWR Report