DBA 实战运维小技巧~在 ADG 中配置 AWR

前 言

众所周知，Oracle 的 AWR (Automatic Workload Repository) 报告自 Oracle 10g 产生以来，只能查看分析主库上的性能问题，如果 ADG 备库出现性能问题，收集的 AWR 报告也是主库的性能数据，没法判断分析备库性能问题，那么自 Oracle 12cR2 (12.2) 以来，可以拍摄备库的自动工作负载存储库 (AWR) 快照。备库的 AWR 是确定 Active Data Guard 备库中恢复和报告工作负载性能问题的最佳工具。

有关配置和管理备库 AWR 的详细信息，请参阅管理 Active Data Guard 备库中的自动工作负载库。https://docs.oracle.com/en/database/oracle/oracle-database/23/tgdba/gathering-database-statistics1.html#GUID-309C107F-DC42-4119-9904-9504E9748B84

正 文

在 Oracle Database 23ai ADG Standby 环境中的 AWR 快照默认已启用，无需运行 dbms_workload_repository.enable_snapshot_service() 即可启用。它将启用 ADG 中 CDB 和所有 PDB 的自动快照。快照保留时间默认为 8 天，要更改保留时间，请使用 dbms_workload_repository.modify_snapshot_settings(retention)。

以下是在 ADG 备用数据库中管理 AWR 的主要步骤：

• 配置远程管理框架 (RMF)
• 管理 Active Data Guard 备用数据库的快照
• 查看 Active Data Guard 备用数据库中的 AWR 数据

此过程需要配置主备库间的 dblink，感兴趣的可自行查看官方文档进行详细配置。这里我们参考 How to Generate AWRs in Active Data Guard Standby Databases (Doc ID 2409808.1) 文档进行简单的设置。

从 Oracle Database 12c Release 2 (12.2) 开始，可为 Active Data Guard (ADG) 备用数据库捕获自动工作负载存储库 (AWR) 数据。

此功能可用于分析 ADG 备用数据库的任何性能相关问题。

在下面的示例中，主库和备库位于两节点 RAC 集群上，db_name 为 “jiekexu”。
主库（db_unique_name=jieke）和备库（db_unique_name=jiekestb）分别运行在节点 “jiekerac12c1, jiekerac12c2 ”和 “jiekemem1,jiekemem2 ”上。

配置步骤

• 1）确认打开模式和备库角色：

    SQL> select inst_id, open_mode, database_role from gv$database order by 1;
    INST_ID    OPEN_MODE            DATABASE_ROLE
    ---------- -------------------- ----------------
              ONLY WITH APPLY PHYSICAL STANDBY
              ONLY WITH APPLY PHYSICAL STANDBY
复制

• 2）在主库上解锁 SYS$UMF 用户

SYSUMF 用户是默认数据库用户，拥有访问系统级远程管理框架 (RMF) 视图和表的所有权限。RMF 中所有与 AWR 相关的操作都只能由 SYSUMF 用户执行。

SYS$UMF 用户默认是锁定的，在部署 RMF 拓扑之前必须将其解锁：

SQL> select USERNAME,ACCOUNT_STATUS,CREATED,LOCK_DATE,PROFILE,PASSWORD_VERSIONS,DEFAULT_TABLESPACE from dba_users where username like 'SYS$%' order by CREATED asc; 

USERNAME                  ACCOUNT_STATUS     CREATED             LOCK_DATE           PROFILE              PASSWORD_VERSIONS DEFAULT_TABLESPACE
------------------------- ------------------ ------------------- ------------------- -------------------- ----------------- ------------------------------
SYS$UMF                   LOCKED             2023-02-13 14:30:38 2023-02-13 14:30:38 DEFAULT                                USERS

SQL> alter user sys$umf identified by sysumf account unlock;  
复制

• 3） 在主库上创建 DBLink

在主库和备库之间创建数据库链接，反之亦然：

create database link dbl_jieke_to_jiekestb CONNECT TO sys$umf IDENTIFIED BY <password> using 'jiekestb';
create database link dbl_jiekestb_to_jieke CONNECT TO sys$umf IDENTIFIED BY <password> using 'jieke';

vim $ORACLE_HOME/network/admin/tnsnames.ora
-- 配置主库和备库之间的 TNS
grep -i jieke $ORACLE_HOME/network/admin/tnsnames.ora
grep -i jiekestb $ORACLE_HOME/network/admin/tnsnames.ora
-- 验证 dblink
select dbid,open_mode,database_role,db_unique_name  from v$database@dbl_jxrt4db_to_jxrt4dbstb union all select dbid,open_mode,database_role,db_unique_name  from v$database@dbl_jxrt4dbstb_to_jxrt4db;

      DBID OPEN_MODE            DATABASE_ROLE    DB_UNIQUE_NAME
---------- -------------------- ---------------- ------------------------------
3857716255 READ ONLY WITH APPLY PHYSICAL STANDBY jiekestb
3857716255 READ WRITE           PRIMARY          jieke
复制

注意：当 GLOBAL_NAMES=TRUE 时，dblink 名称必须包括目标数据库的 global_name.global_name 值（select global_name from global_name;），还可以在名称中添加一些附加值，例如：“<global_name.global_name>@racdb_to_racdbs ”就是一个有效的 dblink 名称。这将涵盖主备库具有相同 global_name 且 global_names=TRUE 的情况。

SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      jiekestb
SQL> show parameter global_names

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
global_names                         boolean     FALSE 
复制

• 4）配置数据库拓扑

我们需要配置要添加到拓扑中的数据库节点。拓扑中的每个数据库节点都必须分配一个唯一的名称（默认为 DB_UNIQUE_NAME）：

在本例中，我们选择 “jieke”代表主库，“jiekestb”代表备库。

-- 在执行 dbms_umf.configure_node 之前，在 primary 中设置以下参数：
alter system set "_umf_remote_enabled"=TRUE scope=BOTH;

exec dbms_umf.configure_node ('jieke');
复制

• 5）验证 dblink

备库位于目标系统（主库）的远程。我们可以通过相应的数据库链接注册它。

要验证 STANDBY 的 GLOBAL_NAMES 设置是否与主库的 GLOBAL_NAMES 值一致，如果不一致，则更改备库的值，使其与主库的值一致。

如果值不一致，DBMS_UMF 可能会出现以下错误：

ORA-15751: The RMF operation failed.
ORA-02085: database link ORA-02085: database link <from standby> connects to <primary>
复制

在备库上执行

exec dbms_umf.configure_node ('jiekestb','dbl_jiekestb_to_jieke');
复制

• 6）在主库创建 RMF 拓扑

    exec DBMS_UMF.create_topology ('Topology_1');
复制

• 7）验证目前已完成的步骤：

    set line 132
    col topology_name format a15
    col node_name format a15
    
    select * from dba_umf_topology;
    select * from dba_umf_registration;
    
    For example
    
    SQL> select * from dba_umf_topology;
    
    TOPOLOGY_NAME    TARGET_ID TOPOLOGY_VERSION TOPOLOGY
    --------------- ---------- ---------------- --------
    Topology_1      2585815591                1 ACTIVE
    
    SQL> select * from dba_umf_registration;
    
    TOPOLOGY_NAME   NODE_NAME          NODE_ID  NODE_TYPE AS_SO AS_CA STATE
    --------------- --------------- ---------- ---------- ----- ----- --------------------
    Topology_1      jieke           2585815591          0 FALSE FALSE OK
复制

• 8）在拓扑中注册备库

    -- 主库执行
    exec DBMS_UMF.register_node ('Topology_1', 'jiekestb', 'dbl_jieke_to_jiekestb', 'dbl_jiekestb_to_jieke', 'FALSE', 'FALSE');
    PL/SQL procedure successfully completed.
复制

在主节点上启用 AWR 服务：

exec DBMS_WORKLOAD_REPOSITORY.register_remote_database(node_name=>'jiekestb');
PL/SQL procedure successfully completed.

Elapsed: 00:00:21.60
复制

如果遇到 “ORA-15766: already registered in an RMF topology”（ORA-15766：已在 RMF 拓扑中注册），请按以下步骤取消注册节点，然后重新运行 “DBMS_UMF.register_node”：

exec DBMS_UMF.unregister_node ('Topology_1', 'stby');
PL/SQL procedure successfully completed.
复制

如果遇到 “ORA-13519: Database id (1730117407) exists in the workload repository”（ORA-13519: 数据库 ID (1730117407) exists in the workload repository），请按以下步骤取消注册远程数据库，然后重新运行 “DBMS_WORKLOAD_REPOSITORY.register_remote_database”：

exec DBMS_WORKLOAD_REPOSITORY.unregister_remote_database('stby','Topology_1',TRUE);
PL/SQL procedure successfully completed. 
复制

验证：

set line 132
col topology_name format a15
col node_name format a15
SQL> select * from dba_umf_topology;
TOPOLOGY_NAME TARGET_ID TOPOLOGY_VERSION TOPOLOGY
--------------- ---------- ---------------- --------
Topology_1      2585815591                4 ACTIVE

SQL> select * from dba_umf_registration;
TOPOLOGY_NAME NODE_NAME NODE_ID NODE_TYPE AS_SO AS_CA STATE
--------------- --------------- ---------- ---------- ----- ----- --------------------
Topology_1      jieke         2585815591          0 FALSE FALSE OK
Topology_1      jiekestb      3094496909          0 FALSE FALSE OK

SQL> select * from dba_umf_service;
TOPOLOGY_NAME NODE_ID SERVICE
--------------- ---------- -------
Topology_1      3094496909 AWR
复制

• 9）在主库使用 RMF 创建一个远程快照

    exec dbms_workload_repository.create_remote_snapshot('jiekestb');
复制

我们至少需要运行两次才能得到 begin_snap 和 end_snap。

如果遇到 “ORA-13516： AWR 操作失败： Remote source not registered for AWR”（远程数据源未注册 AWR），那么请在主系统上手动切换几个（2-3 个）日志文件：

alter system switch logfile;
复制

• 10）创建 AWR 报告

    @?/rdbms/admin/awrrpti.sql
复制

注意：如果切换数据库角色，请在切换后在新主库中运行以下操作。

在新主数据库中以 sysdba 登录并运行以下程序：

exec DBMS_UMF.SWITCH_DESTINATION(topology_name IN VARCHAR2,force_switch IN BOOLEAN DEFAULT TRUE); 
复制

根据未发布的 Bug 28930258，如果匆忙且尚未构建拓扑，则检查当前主库中 _remote_awr_enabled 的值。

SELECT b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
FROM sys.x$ksppi a,
sys.x$ksppcv b,
sys.x$ksppsv c
WHERE
a.indx = b.indx
AND
a.indx = c.indx
AND a.ksppinm = '_remote_awr_enabled';

-- 如果等于 TRUE，则只需将其设置为 FALSE。
ALTER SYSTEM SET "_remote_awr_enabled" = FALSE SCOPE = BOTH SID = '*';
-- 这将导致再次自动生成 AWR 快照。
复制

请注意，如果您看到 ORA-15755：当前节点已配置为远程操作。您需要取消配置并重新配置。

SQL> exec dbms_umf.unconfigure_node;
SQL> exec dbms_umf.configure_node ('test');
复制

19c ADG AWR 示例

我们直接在主库运行 sqlplus / as sysdba 来执行 awrrpti.sql 创建 AWR 报告。

SQL> @?/rdbms/admin/awrrpti.sql
Elapsed: 00:00:00.00
Elapsed: 00:00:00.00

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
AWR reports can be generated in the following formats.  Please enter the
name of the format at the prompt. Default value is 'html'.

   'html'          HTML format (default)
   'text'          Text format
   'active-html'   Includes Performance Hub active report

Elapsed: 00:00:00.01
Enter value for report_type: 


Elapsed: 00:00:00.00

Type Specified: html
Elapsed: 00:00:00.00


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  DB Id      Inst Num   DB Name      Instance     Host
------------ ---------- ---------    ----------   ------
  3094496909     1      jieke        jiekestb   jieke-rac19cadg
  3857716255     2      jieke        jieke2     jieke-rac19c-r2
* 3857716255     1      jieke        jieke1     jieke-rac19c-r1

Enter value for dbid: 3094496909
Using 3094496909 for database Id
Enter value for inst_num: 1
Using 1 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.


Enter value for num_days: 1

Listing the last day's Completed Snapshots
Instance     DB Name      Snap Id       Snap Started    Snap Level
------------ ------------ ---------- ------------------ ----------

jiekestb     jieke                1  29 Sep 2024 16:03    1
                                  2  29 Sep 2024 16:06    1


Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1

Enter value for end_snap: 2
End   Snapshot Id specified: 2


Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_1_2.html.  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name: 

Using the report name awrrpt_1_1_2.html
复制

省略输出，我们直接来看报告，如下所示，通过 Role 角色判断则是 Physical Standby 备库角色，这样我们便可以诊断 ADG 备库的性能问题了。好了就到这里了，这个小技巧你学废了吗？？？

参考链接

How to Generate AWRs in Active Data Guard Standby Databases (Doc ID 2409808.1)
https://docs.oracle.com/en/database/oracle/oracle-database/23/tgdba/gathering-database-statistics1.html#GUID-309C107F-DC42-4119-9904-9504E9748B84
复制

❤️ 欢迎关注我的公众号【JiekeXu DBA之路】，一起学习新知识！
——————————————————————————
公众号：JiekeXu DBA之路
墨天轮：https://www.modb.pro/u/4347
CSDN ：https://blog.csdn.net/JiekeXu
ITPUB：https://blog.itpub.net/69968215
腾讯云：https://cloud.tencent.com/developer/user/5645107
——————————————————————————