[20230303]生成相關備庫的awr報表(補充說明).txt

[20230303]生成相關備庫的awr報表(補充說明).txt

--//在前一段時間我測試了在主庫建立備庫的awr報表,連結http://blog.itpub.net/267265/viewspace-2936073/.
--//裡面提到:
--//如果需要定時收集awr資訊,可以建立schedule,步驟略.
--//定期執行exec dbms_workload_repository.create_remote_snapshot('standby_site');命令.

--//我這裡犯了一個錯誤,有網友提示建立後也是定期收集的.我今天看了主庫確實這樣.

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.

SYS@192.168.100.235:1521/orcl> @ awr/get_settings.sql
==============================
DBID                          : 1585360079
SNAP_INTERVAL                 : +00000 01:00:00.0
RETENTION                     : +00060 00:00:00.0
TOPNSQL                       : DEFAULT
CON_ID                        : 0
SRC_DBID                      : 1585360079
SRC_DBNAME                    : orcl
==============================
DBID                          : 18526484
SNAP_INTERVAL                 : +00000 01:00:00.0
RETENTION                     : +00008 00:00:00.0
TOPNSQL                       : DEFAULT
CON_ID                        : 0
SRC_DBID                      : 1585360079
SRC_DBNAME                    : standby_site
PL/SQL procedure successfully completed.
--//只不過預設僅僅儲存8天.更正一下!!

SYS@192.168.100.235:1521/orcl> @ desc_proc sys dbms_workload_repository modify_snapshot_settings
INPUT OWNER PACKAGE_NAME OBJECT_NAME
sample : @desc_proc sys dbms_stats gather_%_stats

OWNER PACKAGE_NAME             OBJECT_NAME              SEQUENCE ARGUMENT_NAME   DATA_TYPE IN_OUT    DEFAULTED
----- ------------------------ ------------------------ -------- --------------- --------- --------- ----------
SYS   DBMS_WORKLOAD_REPOSITORY MODIFY_SNAPSHOT_SETTINGS        1 RETENTION       NUMBER    IN        Y
                                                               2 INTERVAL        NUMBER    IN        Y
                                                               3 TOPNSQL         NUMBER    IN        Y
                                                               4 DBID            NUMBER    IN        Y
                                                               5 TABLESPACE_NAME VARCHAR2  IN        Y
                                                               1 RETENTION       NUMBER    IN        Y
                                                               2 INTERVAL        NUMBER    IN        Y
                                                               3 TOPNSQL         VARCHAR2  IN        N
                                                               4 DBID            NUMBER    IN        Y
                                                               5 TABLESPACE_NAME VARCHAR2  IN        Y
10 rows selected.
--//指定dbid引數就可以設定.
--//有1個tablespace_name引數,是否說明awr報表資訊也可以儲存在別的表空間.
--//實際上使用這樣的方式可以建立一個awr報表收集器,不一定是備庫,其它資料庫應該也可以.