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 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
Enter value for report_type: html Type 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: 524737559 Using 524737559 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 ------------ ------------ ---------- ------------------ ----------
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: 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 |
查看AWR 报告：
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31480688/viewspace-2656126/ ，如需转载，请注明出处，否则将追究法律责任。
