--run as the xstrmadmin user, e.g. c##xstrmadmin is usually the default name of this user for a pdb system
--created 8/17/22 Steve Gere
--modified 9/13/22 Steve Gere

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
ALTER SESSION set container=CDB$ROOT;

prompt global_name
select global_name from global_name;

prompt time
select sysdate from dual;

prompt redo log scanning latency
COLUMN CAPTURE_NAME HEADING 'Capture|Process|Name' FORMAT A12
COLUMN LATENCY_SECONDS HEADING 'Latency|in|Seconds' FORMAT 999999
COLUMN LAST_STATUS HEADING 'Seconds Since|Last Status' FORMAT 999999
COLUMN CAPTURE_TIME HEADING 'Current|Process|Time'
COLUMN CREATE_TIME HEADING 'Message|Creation Time' FORMAT 999999

SELECT CAPTURE_NAME,
		STATE,
       ((SYSDATE - CAPTURE_MESSAGE_CREATE_TIME)*86400) LATENCY_SECONDS,
       ((SYSDATE - CAPTURE_TIME)*86400) LAST_STATUS,
       TO_CHAR(CAPTURE_TIME, 'HH24:MI:SS MM/DD/YY') CAPTURE_TIME,       
       TO_CHAR(CAPTURE_MESSAGE_CREATE_TIME, 'HH24:MI:SS MM/DD/YY') CREATE_TIME
  FROM gV$XSTREAM_CAPTURE;
  
  prompt processed low position for outbound server
COLUMN SERVER_NAME HEADING 'Outbound|Server|Name' FORMAT A10
COLUMN SOURCE_DATABASE HEADING 'Source|Database' FORMAT A20
COLUMN PROCESSED_LOW_POSITION HEADING 'Processed|Low LCR|Position' FORMAT A30
COLUMN PROCESSED_LOW_TIME HEADING 'Processed|Low|Time' FORMAT A9

SELECT SERVER_NAME,
       SOURCE_DATABASE,
       PROCESSED_LOW_POSITION,
       TO_CHAR(PROCESSED_LOW_TIME,'HH24:MI:SS MM/DD/YY') PROCESSED_LOW_TIME
FROM ALL_XSTREAM_OUTBOUND_PROGRESS;

prompt messages captured and enqueued
COLUMN CAPTURE_NAME HEADING 'Capture|Name' FORMAT A15
COLUMN STATE HEADING 'State' FORMAT A25
COLUMN TOTAL_MESSAGES_CAPTURED HEADING 'Redo|Entries|Evaluated|In Detail' FORMAT 99999999999999
COLUMN TOTAL_MESSAGES_ENQUEUED HEADING 'Total|LCRs|Enqueued' FORMAT 99999999999999

SELECT CAPTURE_NAME,
       STATE,
       TOTAL_MESSAGES_CAPTURED,
       TOTAL_MESSAGES_ENQUEUED 
  FROM gV$XSTREAM_CAPTURE;
  
prompt xstream transactions with 0 cumulative message count
select 'No Rows', count(*) from gv$xstream_transaction where CUMULATIVE_MESSAGE_COUNT =0
union all
select 'With Rows', count(*) from gv$xstream_transaction where CUMULATIVE_MESSAGE_COUNT >0;

prompt apply usage
SELECT APPLY_NAME AS APP,
SGA_USED/(1024*1024) AS USED,
SGA_ALLOCATED/(1024*1024) AS ALLOCATED,
TOTAL_MESSAGES_DEQUEUED AS DEQUEUED,
TOTAL_MESSAGES_SPILLED AS SPILLED
FROM gV$XSTREAM_APPLY_READER;

prompt capture usage
SELECT CAPTURE_NAME AS CAP,
SGA_USED/(1024*1024) AS USED,
SGA_ALLOCATED/(1024*1024) AS ALLOCATED,
TOTAL_MESSAGES_CAPTURED AS CAPTURED,
TOTAL_MESSAGES_ENQUEUED AS ENQUEUED
FROM gV$XSTREAM_CAPTURE;

prompt streams sgastat
select * from gv$sgastat where pool like '%streams%' and BYTES > 1000 order by BYTES desc;

prompt replication events
select * FROM ALL_REPLICATION_PROCESS_EVENTS where EVENT_TIME > (sysdate-7) order by EVENT_TIME desc;