10 Using Automatic Workload Repository Warehouse for Generating Performance Reports
The Enterprise Manager AWR Warehouse enables you to consolidate and store detailed performance data from the Automatic Workload Repository of your important Oracle databases. This consolidated AWR Warehouse allows DBAs and developers to view and analyze historical performance data beyond the AWR retention period of the source database. Enterprise Manager extracts Automatic Workload Repository (AWR) data from one or more source database targets and transfers it into the AWR Warehouse, which is maintained independent of the source databases. The AWR Warehouse lets you keep a long-term history of AWR data, forever, if so configured, from the selected Enterprise Manager database targets. This enables long-term analysis of AWR data across databases without performance or storage impact on the source database targets.
Therefore, by uploading AWR data to a centralized AWR Warehouse, you free up space and improve performance on your production systems.
Starting in version 19c, AWR supports pluggable databases (PDB) as source databases that upload their AWR data to the warehouse. Also PDBs can be the AWR warehouse repository. This feature requires Oracle Database 12.2 and higher as the source or repository.
To configure an AWR warehouse, an Enterprise Manager administrator needs to designate an existing Enterprise Manager database target as the AWR Warehouse.
The warehouse target database must be version 12.1.0.2 or higher or version 11.2.0.4 with the appropriate patch. It also must be an equal or higher database version of the source databases it accommodates.
The warehouse is built in the SYS schema, using the SYSAUX tablespace by default. Starting Database 19c, it is possible to specify another tablespace to store the AWR data collected from all the source databases. This tablespace must exist on the Warehouse database.
To use the feature, you first need to set up an Oracle database that Enterprise Manager can use as the AWR Warehouse. After you set up the warehouse database, you can identify source databases whose repositories you want to extract and upload to the warehouse.
In Oracle Enterprise Manager 13c Platform Release 4 Update 2 (13.4.0.2). Active Data Guard (ADG) supports the switch over when the warehouse database or source database is configured with ADG and the primary database goes down and the standby database becomes the primary. After the switchover, the databases are automatically switched in the AWR warehouse.