Oracle 12.2 physical standby备库收集AWR报告

来源：这里教程网时间：2026-03-03 14:12:41 作者：

Oracle 12.2 提供了收集备库AWR 的功能。确定备库角色和打开状态

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

On Primary (CDB) 执行解锁：

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

SYS$UMF 用户默认是locked 的；该用户具有Remote Management Framework (RMF) 有关的所有的视图和表的权限。

On Primary (CDB) 创建db_link

create database link dblk_EMNBBETA_TO_EMNBBETAPDG01 CONNECT TO sys$umf IDENTIFIED BY sysumf using 'LTACTESTPDG01';

create database link dblk_EMNBBETAPDG01_TO_EMNBBETA CONNECT TO sys$umf IDENTIFIED BY sysumf using 'LTACTEST';

RMF 拓扑结构中的所有节点必须有一个独一无二的名字，默认选择db_unique_name

On Primary （CDB 执行) ,LTACTEST 是主库db_unique_name

exec dbms_umf.configure_node ('LTACTEST');

On Standby ，LTACTESTPDG01 是备库db_unique_name

exec dbms_umf.configure_node ('LTACTESTPDG01','dblk_EMNBBETAPDG01_TO_EMNBBETA');

创建RMF 拓扑，On Primary ：

exec DBMS_UMF.create_topology ('EMNBBETA_Topology');

验证目前为止的操作

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_STATE

-------------------- ---------- ---------------- ------------------------

EMNBBETA_Topology 798157014 1 ACTIVE

SQL> select * from dba_umf_registration;

TOPOLOGY_NAME NODE_NAME NODE_ID NODE_TYPE AS_SOURCE AS_CANDIDATE_TA STATE

-------------------- --------------- ---------- ---------- --------------- --------------- --------------------

EMNBBETA_Topology LTACTEST 798157014 0 FALSE FALSE OK

注册备库到RMF 拓扑中

SQL> exec DBMS_UMF.register_node ('EMNBBETA_Topology', 'LTACTESTPDG01', 'dblk_EMNBBETA_TO_EMNBBETAPDG01', 'dblk_EMNBBETAPDG01_TO_EMNBBETA', 'FALSE', 'FALSE');

BEGIN DBMS_UMF.register_node ('EMNBBETA_Topology', 'LTACTESTPDG01', 'dblk_EMNBBETA_TO_EMNBBETAPDG01', 'dblk_EMNBBETAPDG01_TO_EMNBBETA', 'FALSE', 'FALSE'); END;

ERROR at line 1:

ORA-15766: already registered in an RMF topology

ORA-06512: at "SYS.DBMS_UMF_INTERNAL", line 132

ORA-06512: at "SYS.DBMS_UMF_INTERNAL", line 170

ORA-06512: at "SYS.DBMS_UMF", line 822

ORA-06512: at line 1

ORA-06512: at "SYS.DBMS_UMF", line 794

ORA-06512: at "SYS.DBMS_UMF", line 712

ORA-06512: at line 1

解决办法：

如果遇到了ORA-15766 ，那么就执行下面：

SQL> exec DBMS_UMF.unregister_node ('EMNBBETA_Topology', 'LTACTESTPDG01');

PL/SQL procedure successfully completed.

如果遇到了 ORA-13519: Database id (1730117407) exists in the workload repository ，然后重新运行 DBMS_WORKLOAD_REPOSITORY.register_remote_database

exec DBMS_WORKLOAD_REPOSITORY.unregister_remote_database('LTACTESTPDG01' ,' EMNBBETA_Topology ',TRUE);

注册到AWR

exec DBMS_WORKLOAD_REPOSITORY.register_remote_database(node_name=>'LTACTESTPDG01');

PL/SQL procedure successfully completed.

验证

set line 132

col topology_name format a20

col node_name format a15

SQL> select * from dba_umf_topology;

TOPOLOGY_NAME TARGET_ID TOPOLOGY_VERSION TOPOLOGY_STATE

-------------------- ---------- ---------------- ------------------------

EMNBBETA_Topology 798157014 6 ACTIVE

SQL> select * from dba_umf_registration;

TOPOLOGY_NAME NODE_NAME NODE_ID NODE_TYPE AS_SOURCE AS_CANDIDATE_TA STATE

-------------------- --------------- ---------- ---------- --------------- --------------- --------------------

EMNBBETA_Topology LTACTEST 798157014 0 FALSE FALSE OK

EMNBBETA_Topology LTACTESTPDG01 524737559 0 FALSE FALSE OK

SQL> select * from dba_umf_service;

TOPOLOGY_NAME NODE_ID SERVICE_ID

-------------------- ---------- ---------------------

EMNBBETA_Topology 524737559 AWR

用RMF 创建远程snapshot

SQL> exec dbms_workload_repository.create_remote_snapshot('LTACTESTPDG01');

PL/SQL procedure successfully completed.

如果遇到了 ORA-13516: AWR Operation failed: Remote source not registered for AWR ，手动切 2-3 个归档

alter system switch logfile;

收集备库AWR 报告

@?/rdbms/admin/awrrpti.sql

注意是 awrrpti.sql ，不是 awrrpt.sql 输入dbid 就可以了。

SQL> @?/rdbms/admin/awrrpti.sql Specify the Report Type~~~~~~~~~~~~~~~~~~~~~~~AWR reports can be generated in the following formats. Please enter thename of the format at the prompt. Default value is 'html'. 'html' HTML format (default) 'text' Text format 'active-html' Includes Performance Hub active report Enter value for report_type: htmlType Specified: html Instances in this Workload Repository schema~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id Inst Num DB Name Instance Host------------ ---------- --------- ---------- ------ 524737559 1 LTACTEST LTACTEST ORADB-53154. * 4166033225 1 LTACTEST LTACTEST ORADB-53163. Enter value for dbid: 524737559Using 524737559 for database IdEnter value for inst_num: 1Using 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> withoutspecifying a number lists all completed snapshots. Enter value for num_days: 1 Listing the last day's Completed SnapshotsInstance DB Name Snap Id Snap Started Snap Level------------ ------------ ---------- ------------------ ---------- LTACTEST LTACTEST 1 04 Sep 2019 15:41 1 2 04 Sep 2019 15:42 1 Specify the Begin and End Snapshot Ids~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Enter value for begin_snap: 1Begin Snapshot Id specified: 1 Enter value for end_snap: 2End 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