CNDBA社区
1 说明
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.
从12.2开始，ADG备库支持AWR。那么就可以分析备库的性能问题了。ADG备库的快照叫远端快照，一个目标库用于存储ADG备库的快照数据。目标库可以是ADG的主库也可以是非ADG的数据库，如果目标库是ADG的主库，即源库，那么主库的快照则叫本地快照。
更多信息，请查看官方文档：
目标库是通过unique name或source name来识别源库的。源库需要配置两个dblink，一个是目标库指向源库，一个是源库指向目标库。
可以根据计划的时间间隔或手动获取远程快照。远程快照通常由目标节点启动。在目标库创建快照之后，源库通过dblink将快照数据推送到目标库。
目标库管理以下任务：
1 Registering sources
2 Assigning unique identifier for each source
33 Creating database links between destination and sources
4 Scheduling and initiating automatic snapshots for sources
5 Managing destination workload by coordinating snapshots among sources
6 Managing snapshot settings for each source
7 Assigning identifiers to newly generated snapshots
8 Partitioning the AWR tables
9 Storing the performance data in the local AWR
10 Purging the AWR data of destination and sources
源库管理以下任务：
1 Storing its performance data in the local AWR
2 Sending its AWR data to the destination
3 Responding to service requests from the destination
4 Extracting the AWR data from the destination
2 管理ADG备库的AWR主要步骤
2.1 配置RMF(Remote Management Framework)
Remote Management Framework(RMF)是用于为Oracle捕获性能统计数据(AWR数据)。在12.2中RMF只能用于ADG备库和单实例环境的数据库。
The RMF topology is a centralized architecture that consists of all the participating database nodes along with their metadata and connection information. The RMF topology has one database node, called destination, which is responsible for storing and managing performance data (AWR data) that is collected from the database nodes, called sources. A candidate destination is a source that can be configured in such way that it can replace the original destination, when the original destination is unavailable or is downgraded. A topology can have only one destination, and one or more candidate destinations.
2.1.1 向RMF中添加数据库节点
在本示例中有三个数据库节点T，S0和S1。节点T是目标库(ADG备库)，节点S0和S1是源库(S1是备用源库，当S0不可用时，S1将升为新的存储AWR数据节点)
创建以下DBLINK
1 DBLINK_T_to_S0: T节点指向S0节点.
2 DBLINK_T_to_S1: T节点指向S1节点.
3 DBLINK_S0_to_T: S0节点指向T节点.
4 DBLINK_S0_to_S1: S0节点指向S1节点.
5 DBLINK_S1_to_T: S1节点指向T节点.
6 DBLINK_S1_to_S0: S1节点指向S0节点.
/* Configure the nodes T, S0, and S1 by executing these procedures on the respective nodes */
/* Execute this procedure on node T */
SQL> exec DBMS_UMF.configure_node ('T');
/* Execute this procedure on node S0 */
SQL> exec DBMS_UMF.configure_node ('S0', 'DBLINK_S0_to_T');
/* Execute this procedure on node S1 */
SQL> exec DBMS_UMF.configure_node ('S1', 'DBLINK_S1_to_T');
/* Execute all the following procedures on the destination node T */
/* Create the topology 'Topology_1' */
SQL> exec DBMS_UMF.create_topology ('Topology_1');
/* Register the node S0 with the topology 'Topology_1' */
SQL> exec DBMS_UMF.register_node ('Topology_1',
'S0',
'DBLINK_T_to_S0',
'DBLINK_S0_to_T',
'TRUE' /* Set it as a source */,
'FALSE' /* Set it as not a candidate destination */);
/* Register the node S1 with the topology 'Topology_1' */
SQL> exec DBMS_UMF.register_node ('Topology_1',
'S1',
'DBLINK_T_to_S1',
'DBLINK_S1_to_T',
'TRUE' /* Set it as a source */,
'TRUE' /* Set it as a candidate destination */);
/* Register the database links between the nodes S0 and S1 in the topology 'Topology_1'.
* When destination T is unavailable at the time of failover, the source S0 can connect to
* the candidate destination S1 using this database link.
*/
SQL> exec DBMS_UMF.create_link ('Topology_1',
'S0',
'S1',
'DBLINK_S0_to_S1',
'DBLINK_S1_to_S0');
/* Enable the AWR service on the node S0 in the topology 'Topology_1' */
SQL> exec DBMS_WORKLOAD_REPOSITORY.register_remote_database(node_name=>'S0');
/* Enable the AWR service on the node S1 in the topology 'Topology_1' */
SQL> exec DBMS_WORKLOAD_REPOSITORY.register_remote_database(node_name=>'S1');注：禁用AWR服务
SQL> exec DBMS_WORKLOAD_REPOSITORY.unregister_remote_database(node_name)2.1.2 管理ADG角色转换
在ADG主库故障或者发生switchover情况时，会触发ADG角色转换。Oracle推荐在修改角色前做以下配置：
2.1.2.1 在源库和备用源库之间创建dblink，在所有源库上执行
SQL> EXEC DBMS_UMF.CREATE_LINK (topology name,
source name, --源库
candidate destination name, --备用源库
source to candidate destination database link,
candidate destination to source database link);2.1.2.2 创建AWR快照
为了在角色转换后能够为候选源库生成AWR报告，在角色转换之前至少为候选源库创建一个快照点。
2.1.2.3 重启候选源库以及所有源库
2.1.2.4 执行角色转换
SQL> EXEC DBMS_UMF.SWITCH_DESTINATION(topology name, force_switch=>FALSE);2.2 管理ADG备库的快照
2.2.1 创建，修改，删除Remote Snapshots
#创建快照-本地
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;
/#创建快照-remote
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_REMOTE_SNAPSHOT ();
END;
/#删除快照
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 22,
high_snap_id => 32,
dbid => 3310949047);
END;
/#修改快照设置
BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 43200,
interval => 30,
topnsql => 100,
dbid => 3310949047);
END;
/2.2.2 查看快照配置
SQL> select snap_interval, retention from DBA_HIST_WR_CONTROL;
SNAP_INTERVAL RETENTION
---------------------------------------
+00000 01:00:00.0 +00008 00:00:00.0 2.2.3 导出导入Remote快照
导出脚本：awrextr.sql
导入脚本：awrload.sql
具体示例，参考官方文档：
2.3 查看ADG备库的AWR数据
2.3.1 通过AWR视图来查看AWR数据
以DBA_HIST开头相关的视图来查看AWR数据。如下：
| DBA_HIST View |
Description |
| DBA_HIST_ACTIVE_SESS_HISTORY |
Displays the history of the contents of the in-memory active session history for recent system activity. |
| DBA_HIST_BASELINE |
Displays information about the baselines captured on the system, such as the time range of each baseline and the baseline type. |
| DBA_HIST_BASELINE_DETAILS |
Displays details about a specific baseline. |
| DBA_HIST_BASELINE_TEMPLATE |
Displays information about the baseline templates used by the system to generate baselines. |
| DBA_HIST_CON_SYS_TIME_MODEL |
Displays historical system time model statistics, including OLAP timed statistics. |
| DBA_HIST_CON_SYSMETRIC_HIST |
Displays the historical information about the system metric values. |
| DBA_HIST_CON_SYSMETRIC_SUMM |
Displays history of the statistical summary of all the metric values in the system metrics for the long duration (60 seconds) group. |
| DBA_HIST_CON_SYSSTAT |
Displays historical system statistics, including OLAP kernel statistics. |
| DBA_HIST_CON_SYSTEM_EVENT |
Displays historical information about the total waits for an event. |
| DBA_HIST_DATABASE_INSTANCE |
Displays information about the database environment. |
| DBA_HIST_DB_CACHE_ADVICE |
Displays historical predictions of the number of physical reads for the cache size corresponding to each row. |
| DBA_HIST_DISPATCHER |
Displays historical information for each dispatcher process at the time of the snapshot. |
| DBA_HIST_DYN_REMASTER_STATS |
Displays statistical information about the dynamic remastering process. |
| DBA_HIST_IOSTAT_DETAIL |
Displays historical I/O statistics aggregated by file type and function. |
| DBA_HIST_RSRC_PDB_METRIC |
Displays historical information about the Resource Manager metrics for pluggable databases (PDBs) for the past one hour. |
| DBA_HIST_RSRC_METRIC |
Displays historical information about the Resource Manager metrics for consumer groups for the past one hour. |
| DBA_HIST_SHARED_SERVER_SUMMARY |
Displays historical information for shared servers, such as shared server activity, common queues and dispatcher queues. |
| DBA_HIST_SNAPSHOT |
Displays information on snapshots in the system. |
| DBA_HIST_SQL_PLAN |
Displays the SQL execution plans. |
| DBA_HIST_WR_CONTROL |
Displays the settings for controlling AWR. |
| DBA_HIST_WR_SETTINGS |
Displays the settings and metadata of the AWR. |
2.3.2 通过AWR报告来查看AWR数据
关于AWR相关脚本说明：
| Task |
SQL Script |
Description |
| awrrpt.sql |
Generates an AWR report in HTML or text format that displays statistics from a range of snapshot IDs in the local database instance. |
|
| awrrpti.sql |
Generates an AWR report in HTML or text format that displays statistics from a range of snapshot IDs in a specific database instance. |
|
| Generating an AWR Report for the Local Database in Oracle RAC |
awrgrpt.sql |
Generates an AWR report in HTML or text format that displays statistics from a range of snapshot IDs in the local database instance in an Oracle RAC environment. |
| Generating an AWR Report for a Specific Database in Oracle RAC |
awrgrpti.sql |
Generates an AWR report in HTML or text format that displays statistics from a range of snapshot IDs in a specific database instance in an Oracle RAC environment. |
| Generating an AWR Report for a SQL Statement on the Local Database |
awrsqrpt.sql |
Generates an AWR report in HTML or text format that displays statistics for a particular SQL statement from a range of snapshot IDs in the local database instance. |
| Generating an AWR Report for a SQL Statement on a Specific Database |
awrsqrpi.sql |
Generates an AWR report in HTML or text format that displays statistics for a particular SQL statement from a range of snapshot IDs in a specific database instance. |
具体例子：
参考官方文档：
版权声明：本文为博主原创文章，未经博主允许不得转载。
ADG AWR
