如何在Active Data Guard环境下创建Standby的AWR
如何在Active Data Guard环境下创建Standby的AWR
SQLplusDB
发布于 2022-08-19 21:23:20
发布于 2022-08-19 21:23:20
编者按：
本文作者系杨昱明，现就职于甲骨文公司，从事数据库方面的技术支持。希望能通过发表文章，把一些零散的知识再整理整理。个人主页:https://blog.csdn.net/weixin_50513167，经其本人授权发布。
【免责声明】本号文章仅代表个人观点，与任何公司无关。
下面通过实际操作来演示一下在 ORACLE Active Data Guard 环境下，Standby 的 AWR 取得的具体步骤。
参照 Doc ID 2469650.1。
首先，确认一下主库和备库的状态。
■Primary
SQL> select inst_id, open_mode, database_role from gv$database order by 1;
INST_ID OPEN_MODE DATABASE_ROLE
---------- -------------------- ----------------
1 READ WRITE PRIMARY
■Standby
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在主库上，解锁 sys$umf 用户。
■Primary
SQL> alter user sys$umf identified by rmfuser account unlock;
User altered.
SQL> alter system switch logfile;
System altered.在主库上，创建主库和备库之间的 DBLINK，并做一下测试。
■Primary
SQL> create database link dbl_primary_to_standby CONNECT TO sys$umf IDENTIFIED BY rmfuser using 'orcl12201_stby';
Database link created.
SQL> create database link dbl_standby_to_primary CONNECT TO sys$umf IDENTIFIED BY rmfuser using 'orcl12201';
Database link created.
■Primary
SQL> select instance_name from v$instance@dbl_primary_to_standby;
INSTANCE_NAME
----------------
orcl12201_stby
SQL> select instance_name from v$instance@dbl_standby_to_primary;
INSTANCE_NAME
----------------
orcl12201
■Standby
SQL> select instance_name from v$instance@dbl_primary_to_standby;
INSTANCE_NAME
----------------
orcl12201_stby
SQL> select instance_name from v$instance@dbl_standby_to_primary;
select instance_name from v$instance@dbl_standby_to_primary
*
ERROR at line 1:
ORA-16000: database or pluggable database open for read-only access分别在主库和备库上，配置数据库节点以添加到拓扑中。
■Primary
SQL> show parameter DB_UNIQUE_NAME
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string orcl12201
SQL> exec dbms_umf.configure_node ('orcl12201');
PL/SQL procedure successfully completed.
■Standby
SQL> show parameter DB_UNIQUE_NAME
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string orcl12201_stby
SQL> exec dbms_umf.configure_node ('orcl12201_stby','dbl_standby_to_primary');
PL/SQL procedure successfully completed.在主库上，创建拓扑。
■Primary
SQL> exec DBMS_UMF.create_topology ('Topology_1');
PL/SQL procedure successfully completed.
SQL> select * from dba_umf_topology;
TOPOLOGY_NAME TARGET_ID TOPOLOGY_VERSION TOPOLOGY
--------------- ---------- ---------------- --------
Topology_1 4191410245 1 ACTIVE
SQL> select * from dba_umf_registration;
TOPOLOGY_NAME NODE_NAME NODE_ID NODE_TYPE AS_SO AS_CA STATE
--------------- --------------- ---------- ---------- ----- ----- --------------------
Topology_1 orcl12201 4191410245 0 FALSE FALSE OK在主库上，注册备库节点到拓扑上，假如 ORA-15766 发生的话，注销后，重新注册。
■Primary
SQL> exec DBMS_UMF.register_node ('Topology_1', 'orcl12201_stby', 'dbl_primary_to_standby', 'dbl_standby_to_primary', 'FALSE', 'FALSE');
BEGIN DBMS_UMF.register_node ('Topology_1', 'orcl12201_stby', 'dbl_primary_to_standby', 'dbl_standby_to_primary', '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
SQL> exec DBMS_UMF.unregister_node ('Topology_1', 'orcl12201_stby');
PL/SQL procedure successfully completed.
SQL> SQL> exec DBMS_UMF.register_node ('Topology_1', 'orcl12201_stby', 'dbl_primary_to_standby', 'dbl_standby_to_primary', 'FALSE', 'FALSE');
PL/SQL procedure successfully completed.在主库上，注册备库节点到 WORKLOAD REPOSITORY。
■Primary
SQL> exec DBMS_WORKLOAD_REPOSITORY.register_remote_database(node_name=>'orcl12201_stby');
PL/SQL procedure successfully completed.确认状态。
■Primary
SQL> select * from dba_umf_topology;
TOPOLOGY_NAME TARGET_ID TOPOLOGY_VERSION TOPOLOGY
--------------- ---------- ---------------- --------
Topology_1 4191410245 6 ACTIVE
SQL> select * from dba_umf_registration;
TOPOLOGY_NAME NODE_NAME NODE_ID NODE_TYPE AS_SO AS_CA STATE
--------------- --------------- ---------- ---------- ----- ----- --------------------
Topology_1 orcl12201 4191410245 0 FALSE FALSE OK
Topology_1 orcl12201_stby 1690288026 0 FALSE FALSE OK
SQL> select * from dba_umf_service;
TOPOLOGY_NAME NODE_ID SERVICE
--------------- ---------- -------
Topology_1 1690288026 AWR主库上，取得备库的 AWR Snap short。
假如发生 ORA-13516 的话，就手动写换日志文件。
■Primary
SQL> exec dbms_workload_repository.create_remote_snapshot('orcl12201_stby');
BEGIN dbms_workload_repository.create_remote_snapshot('orcl12201_stby'); END;
*
ERROR at line 1:
ORA-13509: error encountered during updates to a AWR table
ORA-13516: AWR Operation failed: ORA-13516: AWR Operation failed: Remote source not registered for AWR
ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 5132
ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 5175
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 101
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 167
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 2182
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 2213
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 2197
ORA-06512: at line 1
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> exec dbms_workload_repository.create_remote_snapshot('orcl12201_stby');
PL/SQL procedure successfully completed.创建过程基本上就完成，重起数据库，会发现多了很多的隐藏参数。
SQL> show parameter umf
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_dbmsumf$$1x string <UMF_SCHEMA>
<TOPOLOGY>
<TOPOLOGY_INST>
<TOPOLOGY_NAME>Topology_
1</TOPOLOGY_NAME>
<TARGET_ID>4191410245</T
ARGET_ID>
<TOPOLOGY_VERSION>6</TOP
OLOGY_VERSION>
<TOPOLOGY_STATE>0</TOPOL
OGY_STATE>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
</TOPOLOGY_INST>
</TOPOLOGY>
, <REGISTRATION>
<REGISTRATION_INST>
<TOPOLOGY_NAME>Topology_
1</TOPOLOGY_NAME>
<NODE_NAME>orcl12201</NO
DE_NAME>
<NODE_ID>4191410245</NOD
E_ID>
<NODE_TYPE>0</NODE_TYPE>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
<AS_SOURCE>0</AS_SOURCE>
<AS_CANDIDATE_TARGET>0<,
/AS_CANDIDATE_TARGET>
<STATE>0</STATE>
</REGISTRATION_INST>
<REGISTRATION_INST>
<TOPOLOGY_NAME>Topology_
1</TOPOLOGY_NAME>
<NODE_NAME>orcl12201_stb
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
y</NODE_NAME>
<NODE_ID>1690288026</NOD
E_ID>
<NODE_TYPE>0</NODE_TYPE>
, <AS_SOURCE>0</AS_SOURC
E>
<AS_CANDIDATE_TARGET>0</
AS_CANDIDATE_TARGET>
<STATE>0</STATE>
</REGISTRATION_INST>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
</REGISTRATION>
<LINK>
<LINK_INST>
<TOPOLOGY_NAME>Topology_
1</TOPOLOGY_NAME>
<FROM_NODE_ID>4191410245
</FROM, _NODE_ID>
<TO_NODE_ID>1690288026</
TO_NODE_ID>
<LINK_NAME>DBL_PRIMARY_T
O_STANDBY</LINK_NAME>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
</LINK_INST>
<LINK_INST>
<TOPOLOGY_NAME>Topology_
1</TOPOLOGY_NAME>
<FROM_NODE_ID>1690288026
</FROM_NODE_ID>
<TO_NODE_ID>419141, 0245
</TO_NODE_ID>
<LINK_NAME>DBL_STANDBY_T
O_PRIMARY</LINK_NAME>
</LINK_INST>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
</LINK>
<SERVICE>
<SERVICE_INST>
<TOPOLOGY_NAME>Topology_
1</TOPOLOGY_NAME>
<NODE_ID>1690288026</NOD
E_ID>
<SERVICE_ID>1</SERVICE_I
D>
</SERV, ICE_INST>
</SERVICE>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
</UMF_SCHEMA>
_dbmsumf$$nn string orcl12201
_dbmsumf$$p string 1
_umf_remote_enabled boolean TRUE
_umf_remote_target_dblink string本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2021-05-10，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号