[20230220][20230110]生成相關備庫的awr報表
[20230220][20230110]生成相關備庫的awr報表
--//前一陣子,我才知道我們一套生產系統一些sql語句在備庫執行,據說這些語句執行緩慢.我當時的想法就是定位有問題的sql語句,透過
--//sql profile解決該問題. 我執行tpt ashtop.sql定位語句沒有問題的,因為查詢的是gv$active_session_history.但是要在備庫生成
--//相關備庫的awr報表是不行的.我以前看過一些文件透過建立db link,job定時收集備庫的awr資訊.看了許多文件,嘗試自己實現看看.
--//主要參考連結:
1.環境:
SYS@192.168.100.235:1521/orcl> @ pr
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 19.0.0.0.0
BANNER : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
BANNER_LEGACY : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
--//配置如下:
--//192.168.100.235 主庫 oracle_sid=orcl
--//192.168.100.235 備庫 oracle_sid=orcldg
2.前期準備工作:
--//在主庫建立使用者SYS$UMF.在19c該使用者實際上存在的.
SYS@192.168.100.235:1521/orcl> select username,common,account_status from dba_users where username ='SYS$UMF';
USERNAME COM ACCOUNT_STATUS
-------- --- --------------
SYS$UMF YES LOCKED
SYS@192.168.100.235:1521/orcl> alter user SYS$UMF identified by o1r2c3l4 account unlock ;
User altered.
SYS@192.168.100.235:1521/orcl> select username,common,account_status from dba_users where username ='SYS$UMF';
USERNAME COM ACCOUNT_STATUS
-------- --- --------------
SYS$UMF YES OPEN
--//在主庫和備庫設定"_umf_remote_enabled"=TRUE.我看到預設是"_umf_remote_enabled"=TRUE.
alter system set "_umf_remote_enabled"=TRUE scope=BOTH;
--//在主庫建立db_link.連線主庫以及不備庫的dblink:
create database link link_to_primary connect to "SYS$UMF" identified by "o1r2c3l4" using '192.168.100.235:1521/orcl';
create database link link_to_standby connect to "SYS$UMF" identified by "o1r2c3l4" using '192.168.100.237:1521/orcldg';
SYS@192.168.100.235:1521/orcl> column HOST format a30
SYS@192.168.100.235:1521/orcl> select db_link, username, host from dba_db_links;
DB_LINK USERNAME HOST
--------------- -------- ------------------------------
SYS_HUB SEEDDATA
LINK_TO_PRIMARY SYS$UMF 192.168.100.235:1521/orcl
LINK_TO_STANDBY SYS$UMF 192.168.100.237:1521/orcldg
--//測試dblink連線情況,在主備庫上分別執行.貼出主庫的執行情況.
SYS@192.168.100.235:1521/orcl> select db_unique_name from v$database@link_to_primary;
DB_UNIQUE_NAME
------------------------------
orcl
SYS@192.168.100.235:1521/orcl> select db_unique_name from v$database@link_to_standby;
DB_UNIQUE_NAME
------------------------------
orcldg
--//Next we have to add the primary database node to the UMF repository, for that you have to run below command on both
--//the primary & standby databases.
--//指派primary site name ='primary_site' 和 standby database site name ='standby_site'.
--//主庫:
SYS@192.168.100.235:1521/orcl> exec dbms_umf.configure_node ('primary_site');
PL/SQL procedure successfully completed.
--//備庫:
SYS@192.168.100.237:1521/orcldg> exec dbms_umf.configure_node('standby_site','LINK_TO_PRIMARY');
PL/SQL procedure successfully completed.
--//如果取消配置節點
--//SQL> exec DBMS_UMF.UNCONFIGURE_NODE;
--//建立UMF topology
--//On PRIMARY Database create topology:
SYS@192.168.100.235:1521/orcl> exec DBMS_UMF.create_topology ('Topology_1');
PL/SQL procedure successfully completed.
--// Lets query if the toplogy is created with no errors and is ACTIVE.
SYS@192.168.100.235:1521/orcl> select * from dba_umf_topology;
TOPOLOGY_NAME TARGET_ID TOPOLOGY_VERSION TOPOLOGY
------------- ---------- ---------------- --------
Topology_1 3588577726 1 ACTIVE
SYS@192.168.100.235:1521/orcl> column TOPOLOGY_NAME format a20
SYS@192.168.100.235:1521/orcl> select * from dba_umf_registration;
TOPOLOGY_NAME NODE_NAME NODE_ID NODE_TYPE AS_SO AS_CA STATE
------------- ------------ ---------- ---------- ----- ----- -----
Topology_1 primary_site 3588577726 0 FALSE FALSE OK
--//Run in case want to DROP the topology
--//SQL> exec DBMS_UMF.drop_topology('Topology_1');
--//Next you have to add the standby node to the topology, for that you need to use register_node procedure and need to
--//mention both of the DBLinks that we have created earlier. This you have to run on PRIMARY node.
--//On PRIMARY database
SYS@192.168.100.235:1521/orcl> exec DBMS_UMF.register_node ('Topology_1', 'standby_site', 'LINK_TO_STANDBY', 'LINK_TO_PRIMARY', 'FALSE', 'FALSE');
PL/SQL procedure successfully completed.
SYS@192.168.100.235:1521/orcl> select * from dba_umf_registration;
TOPOLOGY_NAME NODE_NAME NODE_ID NODE_TYPE AS_SO AS_CA STATE
------------- ------------ ---------- ---------- ----- ----- -----
Topology_1 primary_site 3588577726 0 FALSE FALSE OK
Topology_1 standby_site 18526484 0 FALSE FALSE OK
--//Now when both of the nodes 'primary_site' and 'standby_site' are added to the topology, we have to register the
--//standby node for the AWR service.
SYS@192.168.100.235:1521/orcl> @ desc dba_umf_registration
Name Null? Type
------------------- -------- -------------
TOPOLOGY_NAME NOT NULL VARCHAR2(128)
NODE_NAME NOT NULL VARCHAR2(128)
NODE_ID NOT NULL NUMBER
NODE_TYPE NOT NULL NUMBER
AS_SOURCE VARCHAR2(5)
AS_CANDIDATE_TARGET VARCHAR2(5)
STATE VARCHAR2(20)
--//On PRIMARY database.
SYS@192.168.100.235:1521/orcl> exec DBMS_WORKLOAD_REPOSITORY.register_remote_database(node_name=>'standby_site');
PL/SQL procedure successfully completed.
SYS@192.168.100.235:1521/orcl> select * from dba_umf_service;
TOPOLOGY_NAME NODE_ID SERVICE
-------------------- ---------- -------
Topology_1 18526484 AWR
SYS@192.168.100.235:1521/orcl> select * from dba_umf_link;
TOPOLOGY_NAME FROM_NODE_ID TO_NODE_ID LINK_NAME
-------------------- ------------ ---------- ----------------
Topology_1 3588577726 18526484 LINK_TO_STANDBY
Topology_1 18526484 3588577726 LINK_TO_PRIMARY
--//生成備庫的awr snapshot.
--//On PRIMARY database.
SYS@192.168.100.235:1521/orcl> exec dbms_workload_repository.create_remote_snapshot('standby_site');
PL/SQL procedure successfully completed.
--//等一分鐘,繼續....
SYS@192.168.100.235:1521/orcl> host sleep 60
SYS@192.168.100.235:1521/orcl> exec dbms_workload_repository.create_remote_snapshot('standby_site');
PL/SQL procedure successfully completed.
3.看看是否產生備庫的awr報表:
SYS@orcl> @?/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: text
Type Specified: text
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ ---------- --------- ---------- ------
18526484 1 ORCL orcldg LISDG
* 1585360079 1 ORCL orcl LIS-DB
Enter value for dbid: 18526484
Using 18526484 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
------------ ------------ ---------- ------------------ ----------
orcldg ORCL 1 20 Feb 2023 10:37 1
2 20 Feb 2023 10:39 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
..
--//在toad下也可以.首先主要選擇db_id(即NODE_ID),操作步驟略.
--//如果需要定時收集awr資訊,可以建立schedule,步驟略.
--//定期執行exec dbms_workload_repository.create_remote_snapshot('standby_site');命令.
--//另外引數是db_link的最好選擇大寫,我遇到小寫出問題的情況,參考連線:
--//[20230110]sql profile run standby database.txt
4.總結大致步驟:
configure node -> create topology -> register node -> DBMS_WORKLOAD_REPOSITORY.register_remote_database.
--//主庫,configure_node:
exec dbms_umf.configure_node ('primary_site');
--//備庫,configure node:
exec dbms_umf.configure_node('standby_site','LINK_TO_PRIMARY');
--//如果取消配置節點
--//SQL> exec DBMS_UMF.UNCONFIGURE_NODE;
--//主庫,create topology:
exec DBMS_UMF.create_topology ('Topology_1');
--//DROP topology
--//SQL> exec DBMS_UMF.drop_topology('Topology_1');
--//主庫,register node:
exec DBMS_UMF.register_node ('Topology_1', 'standby_site', 'LINK_TO_STANDBY', 'LINK_TO_PRIMARY', 'FALSE', 'FALSE');
--//主庫,使用DBMS_WORKLOAD_REPOSITORY.register_remote_database register remote database:
exec DBMS_WORKLOAD_REPOSITORY.register_remote_database(node_name=>'standby_site');
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2936073/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [20230303]生成相關備庫的awr報表(補充說明).txt
- 如何在12.2版本ADG備庫生成AWR報告
- 報表從資料庫中採集相關資料生成報表,資料太少怎麼辦?資料庫
- Oracle生成awr報告Oracle
- Oracle 生成awr報告Oracle
- 生成awr報告的指令碼指令碼
- 【AWR】Oracle批量生成awr報告指令碼Oracle指令碼
- 本機生成遠端資料庫AWR報告資料庫
- [20140805]批量生成awr報表.txt
- 學用ORACLE AWR和ASH特性(8)-生成ASH報表Oracle
- 手工生成AWR分析報告
- oracle awr相關知識Oracle
- Oracle 12.2 physical standby備庫收集AWR報告Oracle
- 學用ORACLE AWR和ASH特性(3)-生成指定資料庫例項的統計報表Oracle資料庫
- 【AWR】自動生成AWR報告指令碼以及用法指令碼
- 自動生成AWR HTML報告HTML
- 【AWR】該怎樣清理SYSAUX表空間相關資料UX
- JPA透過表反向生成相關類
- 達夢資料庫如何來配置並生成AWR報告資料庫
- 學用ORACLE AWR和ASH特性(4)-生成指定SQL的統計報表OracleSQL
- 【AWR】Oracle awr相關檢視及體系介紹Oracle
- 定時自動生成awr報告
- 指令碼:定時生成awr報告指令碼
- ORACLE 11G生成AWR報告Oracle
- AWR報告自動生成指令碼指令碼
- 自動生成awr報告指令碼指令碼
- 關於發展報告的相關檔案生成的源程式
- 【轉】ORACLE 10G AWR 相關Oracle 10g
- awr報表中用到的幾個SQLSQL
- awr報告每天自動生成指令碼指令碼
- 轉貼:批量生成awr報告指令碼指令碼
- 批量生成AWR
- 一個自動生成awr報告的shell指令碼指令碼
- Oracle 11g RAC 如何生成AWR報告?Oracle
- Oracle 11g RAC生成 AWR 報告方法Oracle
- Oracle10g 自動生成AWR報告的指令碼Oracle指令碼
- shell指令碼實現自動生成awr報告指令碼
- 手工生成AWR執行期對比報告記錄