CNDBA社区
在之前的博客系列里我们搭建了多套DW环境，如果我们想查询DW的运行情况。 可以查询如下视图。
1 V$DMWATCHER
查询当前登录实例所对应的守护进程信息，注意一个守护进程可以同时守护多个组的实例，因此查询结果中部分字段（N_GROUP、SWITCH_COUNT）为守护进程的全局信息，并不是当前登录实例自身的守护信息。
另外 MPP 主备环境下，全局登录方式返回的是所有 MPP 站点上查询返回的守护进程信息，可以根据 INST_NAME 实例名字段来区分。
SQL> select * from v$dmwatcher;
LINEID N_GROUP GROUP_NAME INST_NAME DW_TYPE DW_MODE AUTO_RESTART DW_STATUS DW_SUB_STATUS LAST_MSG_TIME SWITCH_COUNT CTL_NUM INST_NUM
---------- ----------- ---------- ----------- ------- ------- ------------ --------- --------------- --------------------------- ------------ ----------- -----------
MAX_CONN_NUM
------------
1 1 GRP2 GRP2_RWW_01 GLOBAL AUTO 1 OPEN SUB_STATE_START 2029-03-13 16:14:22.000000 0 3 3
4
used time: 2.694(ms). Execute id is 37.
2 V$UTSK_INFO
查询守护进程向服务器发送请求的执行情况。
注意在 RUNNING 字段值为 TRUE 时，DSEQ、CODE 和 SEND_LSN 的值才有意义，另外SEND_LSN 和 RECOVER_BREAK 在主库上查询才有意义，并且需要主库的守护进程处于Recovery 状态。
SQL> select * from v$utsk_info;
LINEID RUNNING DSEQ CODE SEND_LSN RECOVER_BREAK AUTO_SWITCH
---------- ------- ----------- ----------- -------------------- ------------- -----------
1 FALSE 0 100 -1 FALSE TRUE
used time: 1.527(ms). Execute id is 38.
SQL>
3 V$RECOVER_STATUS
该视图需要在主库上查询，用于查询备库的恢复进度，如果已恢复完成，查询结果为空。
注意这里显示的是主库向备库发送日志的进度，由于备库重做日志也需要时间，在最后一批日志发送完成后，KBYTES_TO_RECOVER 为 0，RECOVER_PERCENT 为 100%，表示主库已经完成所有日志发送，需要等待备库将最后一批日志重做完成，此时主库的守护进程可能仍然处于 Recovery 状态，待备库重做完成后，主库的守护进程会自动切换 Open 状态。
RECOVER_PERCENT = (KBYTES_TOTAL - KBYTES_TO_RECOVER)/ KBYTES_TOTAL
4 V$KEEP_BUF
该视图需要在备库上查询，用于查询备库上的 KEEP_BUF 信息。读写分离集群下备库没有 KEEP_BUF 机制，该视图查询结果为空。
5 V$ARCH_FILE
查询本地归档日志信息。
SQL> select * from v$arch_file;
LINEID DB_MAGIC STATUS LEN FREE ARCH_LSN CLSN ARCH_SEQ NEXT_SEQ
---------- ----------- -------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
CREATE_TIME CLOSE_TIME PATH
--------------------------- --------------------------- -------------------------------------------------
1 -552901526 INACTIVE 551424 551424 257102 262071 0 1068
2019-09-13 18:10:47.811863 2019-09-13 18:11:25.068375 /dm/dmarch/ARCHIVE_LOCAL1_20190913181047811_0.log
2 -552901526 ACTIVE 134217728 160768 262072 262175 1069 1374
2019-09-13 18:11:25.068375 2019-09-13 18:13:06.558736 /dm/dmarch/ARCHIVE_LOCAL1_20190913181306558_0.log
used time: 8.854(ms). Execute id is 4.
SQL>
6 V$ARCH_STATUS
查询归档状态信息。
SQL> select * from v$arch_status;
LINEID ARCH_TYPE ARCH_DEST ARCH_STATUS
---------- --------- ----------- -----------
1 TIMELY GRP2_RWW_01 VALID
2 TIMELY GRP2_RWW_02 VALID
3 TIMELY GRP2_RWW_03 VALID
4 LOCAL /dm/dmarch VALID
used time: 2.229(ms). Execute id is 5.
SQL>
7 V$ARCH_SEND_INFO
此视图用于在主库上查询各备库的日志发送统计信息。
SQL> select * from v$arch_send_info;
LINEID ARCH_DEST ARCH_TYPE FOR_RECOVERY RECNT_SEND_CNT TOTAL_SEND_CNT TOTAL_SEND_LEN TOTAL_PTX_CNT TOTAL_SEND_TIME MAX_SEND_TIME
---------- ----------- ----------- ------------ -------------- -------------------- -------------------- -------------------- -------------------- --------------------
MAX_END_TIME MAX_PTX_CNT MAX_SEND_LEN MAX_SEND_LSN LAST_SEND_LEN LAST_SEND_PTX LAST_SEND_LSN LAST_SEND_TIME
--------------------------- ----------- ------------ -------------------- ------------- ------------- -------------------- --------------------
LAST_START_TIME LAST_END_TIME RECNT_SEND_LEN RECNT_SEND_PTX RECNT_SEND_TIME
--------------------------- --------------------------- -------------------- -------------------- --------------------
1 GRP2_RWW_04 5 N 6 19 680200 5079 95181 28947
2029-03-13 12:16:59.000000 415 186368 262175 27136 1 262175 5006
2029-03-13 12:24:38.000000 2029-03-13 12:24:38.000000 264704 109 50104
LINEID ARCH_DEST ARCH_TYPE FOR_RECOVERY RECNT_SEND_CNT TOTAL_SEND_CNT TOTAL_SEND_LEN TOTAL_PTX_CNT TOTAL_SEND_TIME MAX_SEND_TIME
---------- ----------- ----------- ------------ -------------- -------------------- -------------------- -------------------- -------------------- --------------------
MAX_END_TIME MAX_PTX_CNT MAX_SEND_LEN MAX_SEND_LSN LAST_SEND_LEN LAST_SEND_PTX LAST_SEND_LSN LAST_SEND_TIME
--------------------------- ----------- ------------ -------------------- ------------- ------------- -------------------- --------------------
LAST_START_TIME LAST_END_TIME RECNT_SEND_LEN RECNT_SEND_PTX RECNT_SEND_TIME
--------------------------- --------------------------- -------------------- -------------------- --------------------
2 GRP2_RWW_03 5 N 6 41 1188637 9960 159876 24233
2029-03-13 12:16:59.000000 415 186368 262175 27136 1 262175 5188
2029-03-13 12:24:38.000000 2029-03-13 12:24:38.000000 264704 109 47173
LINEID ARCH_DEST ARCH_TYPE FOR_RECOVERY RECNT_SEND_CNT TOTAL_SEND_CNT TOTAL_SEND_LEN TOTAL_PTX_CNT TOTAL_SEND_TIME MAX_SEND_TIME
---------- ----------- ----------- ------------ -------------- -------------------- -------------------- -------------------- -------------------- --------------------
MAX_END_TIME MAX_PTX_CNT MAX_SEND_LEN MAX_SEND_LSN LAST_SEND_LEN LAST_SEND_PTX LAST_SEND_LSN LAST_SEND_TIME
--------------------------- ----------- ------------ -------------------- ------------- ------------- -------------------- --------------------
LAST_START_TIME LAST_END_TIME RECNT_SEND_LEN RECNT_SEND_PTX RECNT_SEND_TIME
--------------------------- --------------------------- -------------------- -------------------- --------------------
3 GRP2_RWW_02 5 N 6 41 1188637 9960 169363 25679
2029-03-13 12:16:59.000000 415 186368 262175 27136 1 262175 6006
2029-03-13 12:24:38.000000 2029-03-13 12:24:38.000000 264704 109 48670
used time: 2.664(ms). Execute id is 40.
SQL>
8 V$DM_ARCH_INI
归档参数信息。
SQL> select * from v$dm_arch_ini;
LINEID ARCH_NAME ARCH_TYPE ARCH_DEST ARCH_FILE_SIZE ARCH_SPACE_LIMIT ARCH_HANG_FLAG ARCH_TIMER_NAME ARCH_IS_VALID ARCH_WAIT_APPLY ARCH_INCOMING_PATH
---------- --------------- --------- ----------- -------------- ---------------- -------------- --------------- ------------- --------------- ------------------
1 ARCHIVE_TIMELY1 TIMELY GRP2_RWW_02 NULL NULL NULL NULL Y 1 NULL
2 ARCHIVE_TIMELY2 TIMELY GRP2_RWW_03 NULL NULL NULL NULL Y 1 NULL
3 ARCHIVE_LOCAL1 LOCAL /dm/dmarch 128 0 1 NULL Y NULL NULL
4 ARCH_TIMELY1 TIMELY GRP2_RWW_04 NULL NULL NULL NULL Y 1 NULL
used time: 3.437(ms). Execute id is 41.
SQL>
9 V$DM_MAL_INI
MAL 参数信息。
SQL> select * from v$dm_mal_ini;
LINEID MAL_TYPE INST_NAME IP MAL_PORT INST_IP INST_PORT MAL_DW_PORT MAL_LINK_MAGIC
---------- --------- ----------- -------------- ----------- -------------- ----------- ----------- --------------
1 MAL_INST1 GRP2_RWW_01 192.168.56.193 5436 192.168.20.193 5236 5536 0
2 MAL_INST2 GRP2_RWW_02 192.168.56.194 5437 192.168.20.194 5237 5537 0
3 MAL_INST3 GRP2_RWW_03 192.168.56.195 5438 192.168.20.195 5238 5538 0
4 MAL_INST4 GRP2_RWW_04 192.168.56.192 5439 192.168.20.192 5239 5539 0
used time: 1.824(ms). Execute id is 42.
SQL>
10 V$DM_TIMER_INI
定时器参数信息。
11 V$MAL_SYS
MAL 系统信息视图。如果是数据守护环境，则只显示主库的 MAL 系统信息。
SQL> select * from v$mal_sys;
LINEID SYS_STATUS STMT_ID NEXT_MAL_ID MAL_PORT N_SITE MAL_NUM MAL_SEQ_NO EMPTY_LET_NUM MAL_CHECK_INTERVAL MAL_CONN_FAIL_INTERVAL
---------- ----------- ----------- -------------------- ----------- ----------- ----------- ----------- ------------- ------------------ ----------------------
MAL_COMPRESS_LEVEL MAL_BUF_SIZE MAL_SYS_BUF_SIZE MAL_VPOOL_SIZE MAL_TEMP_PATH
------------------ ------------ ---------------- -------------- -------------
1 0 32732 179215441083367 5436 4 4 0 11 5 5
0 100 0 128
used time: 2.206(ms). Execute id is 44.
SQL>
12 V$MAL_SITE_INFO
MAL站点信息视图，MPP模式下，自动收集MPP各个站点的信息。
SQL> select * from v$mal_site_info;
LINEID SRC_SITE_SEQ DEST_SITE_SEQ MAL_PORT_NUM BUILD_TIME LBTAPFDS CUR_LETTER_NUM MAX_LETTER_NUM TOTAL_LINK_NUM FREE_LINK_NUM SEND_LETTER_NUM
---------- ------------ ------------- ------------ -------------------- -------------------- -------------- -------------- -------------- ------------- ---------------
1 0 1 1 19262 55 0 0 0 0 19262
2 0 2 1 19238 55 0 0 0 0 19238
3 0 3 1 15153 5 0 0 0 0 15153
used time: 1.946(ms). Execute id is 45.
SQL>
13 V$MAL_LINK_STATUS
查询本地实例到远程实例的 MAL 链路连接状态。
SQL> select * from v$mal_link_status;
LINEID SRC_SITE DEST_SITE CTL_LINK_STATUS DATA_LINK_STATUS
---------- ----------- ----------- --------------- ----------------
1 GRP2_RWW_01 GRP2_RWW_02 CONNECTED CONNECTED
2 GRP2_RWW_01 GRP2_RWW_03 CONNECTED CONNECTED
3 GRP2_RWW_01 GRP2_RWW_04 CONNECTED CONNECTED
used time: 1.683(ms). Execute id is 46.
SQL>
14 V$RAPPLY_INFO
此视图用于在备库上查询备库重演日志的统计信息。
SQL> select * from v$rapply_info;
LINEID RECNT_APPLY_NUM TSK_MEM_USED TSK_START_TIME TOTAL_RECVED_NUM TOTAL_RECVED_LEN TOTAL_RECVED_TIME TOTAL_APPLY_NUM
---------- --------------- -------------------- --------------------------- -------------------- -------------------- -------------------- --------------------
TOTAL_APPLY_LEN TOTAL_APPLY_TIME TOTAL_WAIT_TIME MAX_RECVED_TIME MAX_WAIT_TIME MAX_APPLY_TIME MAX_APPLY_LEN
-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
LAST_RECVED_LEN LAST_RECVED_TIME RECNT_RECVED_LEN RECNT_RECVED_TIME LAST_WAIT_TIME RECNT_WAIT_TIME LAST_APPLY_LEN
-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
LAST_APPLY_TIME RECNT_APPLY_LEN RECNT_APPLY_TIME
-------------------- -------------------- --------------------
1 6 0 2019-09-13 18:20:45.189886 6 264704 22705 6
264704 21742 621 15937 130 15724 186368
27136 900 264704 22705 59 621 27136
770 264704 21742
used time: 2.395(ms). Execute id is 7.
SQL>
15 V$RAPPLY_LOG_TASK
该视图需要在备库上查询，用于查询备库当前重做任务的日志信息。
16 V$RAPPLY_SYS
该视图需要在备库上查询，用于查询备库重做日志时的一些系统信息。
SQL> select * from v$rapply_sys;
LINEID APPLYING SEC_MAX_LSN MAX_LSN TASK_NUM PRIMARY_NAME HAS_KEEP_BUF TASK_MEM_USED TASK_START_TIME LAST_REDO_TIME
---------- -------- -------------------- -------------------- ----------- ------------ ------------ -------------------- --------------------------- --------------------
TASK_NUM_APPLIED APPLIED_TOTAL_TIME
-------------------- --------------------
1 FALSE 262175 262175 0 GRP2_RWW_01 FALSE 0 2019-09-13 18:20:45.189886 0
6 21
used time: 1.581(ms). Execute id is 9.
SQL>
版权声明：本文为博主原创文章，未经博主允许不得转载。
