[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报表收集器,不一定是备库,其它数据库应该也可以.