AWR in Multitenant Environment : Oracle 12cR2
Starting with Oracle Database 12c Release 1, Multitenancy is the core part of Database, where a container database (CDB) can include multiple pluggable database (PDBs).
Oracle Database 12c Release 1 (12.1.01) is having centralized Automatic Workload Repository (AWR) repository to store performance data related to CDB and PDBs. One can take an AWR snapshot only at a CDB-level, i.e CDB root. This AWR snapshot is for the whole database system, i.e it contains the statistical information about the CDB as well as all the PDBs in a multitenant environment.
Starting with Oracle Database 12c Release 2 (12.2), CDB root as well as individual PDBs store performance related data, view, and manage AWR data. One can take an AWR snapshot at a CDB-level, i.e on the CDB root, as well as at a PDB-level, i.e. on the individual PDBs.
The CDB-level snapshot data is stored in the SYSAUX tablespace of a CDB root.
The PDB-level snapshot data is stored in the SYSAUX tablespace of a PDB.
The AWR_PDB_AUTOFLUSH_ENABLED initialization parameter enables you to specify whether to enable or disable automatic snapshots for all the PDBs or for individual PDBs. The automatic snapshot operations are enabled by default for a CDB, but are disabled by default for a PDB.
To enable automatic snapshots for a PDB, the PDB administrator must connect to that PDB, set the value for the AWR_PDB_AUTOFLUSH_ENABLED parameter to true, and set the snapshot generation interval to a value greater than 0.
NOTE : PDB snapshot ID's can be different from CDB snapshot ID's
Let's see an example of how it looks like when running AWR report in 12c Release 2:
Step 1: Login sqlplus as command line.
Step 2: Run awrrpt.sql file as shown in below screen shot.
SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql
Step 3: Select AWR Type
Choose AWR_PDB to generate PDB level AWR report.
It will ask few things as below,
Enter value for report_type: html
Enter value for awr_location: AWR_PDB
No comments:
Post a Comment