Focus On Oracle

Installing, Backup & Recovery, Performance Tuning,
Troubleshooting, Upgrading, Patching

Oracle Engineered System


当前位置: 首页 » 技术文章 » Oracle 12c+

How to generate AWR on PDB and ADG(12.2 afterwards)

AWR在性能分析时非常重要,通过AWR报告,大致上也能看出系统的繁忙程度,是否存在性能问题等。从12.2开始,AWR有了新的改进。本文只关注两个点,也是比较常用的。


12.2开始支持pdb级别的AWR(12.1只支持CDB级别)。下面摘抄自官方文档

The multitenant database architecture was introduced starting with Oracle Database 12c Release 1 (12.1.0.1). In the multitenant architecture, a container database (CDB) can include multiple pluggable databases (PDBs).

In Oracle Database 12c Release 1 (12.1.01), a centralized Automatic Workload Repository (AWR) stores the performance data related to CDB and PDBs in a multitenant environment. You can take an AWR snapshot only at a CDB-level, that is, on the CDB root. This AWR snapshot is for the whole database system, that is, it contains the statistical information about the CDB as well as all the PDBs in a multitenant environment.

In Oracle Database 12c Release 2 (12.2), CDB root as well as individual PDBs store, view, and manage AWR data. You can take an AWR snapshot at a CDB-level, that is, on the CDB root, as well as at a PDB-level, that is, on the individual PDBs. 

在12.2上生成pdb的awr报过,有两种方式
1.自动生成,通过设置AWR_PDB_AUTOFLUSH_ENABLED=TRUE自动生成(默认是FALSE)
alter session set container=PDB1;
alter system set awr_pdb_autoflush_enabled=true;
2.手动生成,这个也是推荐的做法(如果采用手动生成的方式,CDB和PDB的snapshot id是不一样的)
alter session set container=PDB1;
exec dbms_workload_repository.create_snapshot();

3.生成AWR报告

@?/rdbms/admin/awrrpt
Specify the location of AWR Data
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
AWR_ROOT - Use AWR data from root (default)
AWR_PDB - Use AWR data from PDB


特别提醒一定要修改AWR生成时间偏移,防止因为同时生成AWR报告造成的性能问题如果有很多的PDB,一般是0-3599,单位是秒。1000000表示是根据pdb的名字自动调节

alter system set AWR_SNAPSHOT_TIME_OFFSET=1000000 scope=both;


从12.2开始可以生成备库的AWR报过,用了分析备份的性能问题。下面摘抄自官方文档
Starting with Oracle Database 12c Release 2 (12.2), Automatic Workload Repository (AWR) data can be captured for Active Data Guard (ADG) standby databases. This feature enables analyzing any performance-related issues for ADG standby databases.

详细过程可参考https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgdba/gathering-database-statistics.html#GUID-E1369092-DA6B-4CF4-B286-69D4A3BDFA1E


Reference
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgdba/gathering-database-statistics.html#GUID-309C107F-DC42-4119-9904-9504E9748B84
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgdba/gathering-database-statistics.html#GUID-D64AEB01-18FF-47EF-BB5C-A0611117D180


关键词:db 12c oracle 

相关文章

在18c中通过ASM Flex DiskGroup克隆PDB
Oracle ASM Storage Limits
通过rpm包安装Oracle Database 18c
彻底搞懂Oracle RHP(Rapid Home Provisioning)
Oracle Database 18c (18.3.0) Upgrade and Migration hands-on Lab
Oracle数据库云服务
Oracle Graphpipe
How to config Oracle Member Cluster for database
Install Oracle Domain Service Cluster Step by Step
Oracle数据库备份云服务
Oracle RAC and Third Party Cloud
Open Source Languages and Oracle Database

联系我们

Top
Cell186-9589-1286
QQ 375349564
E-mail ohsdba@qq.com