Streams Replication| Monitoring Streams Capture Processes

Capture process is an optional Streams client that is an Oracle background process that scans a database redo log to capture DML and DDL changes made to database objects.

1)  Displaying the Queue, Rule Sets, and Status of Each Capture Process
2)  Displaying Change Capture Information About Each Capture Process
3)  Displaying State Change and Message Creation Time for Each Capture Process
4)  Displaying Elapsed Time Performing Capture Operations for Each Capture Process
5)  Displaying Information About Each Downstream Capture Process
6)  Displaying the Registered Redo Log Files for Each Capture Process
7)  Displaying the Redo Log Files that Are Required by Each Capture Process
8)  Displaying SCN Values for Each Redo Log File Used by Each Capture Process
9)  Displaying the Last Archived Redo Entry Available to Each Capture Process
10) Listing the Parameter Settings for Each Capture Process
11) Viewing the Extra Attributes Captured by Each Capture Process
12) Determining the Applied SCN for All Capture Processes in a Database
13) Determining Redo Log Scanning Latency for Each Capture Process
14) Determining Message Enqueuing Latency for Each Capture Process
15) Displaying Information About Rule Evaluations for Each Capture Process

1)    Displaying the Queue, Rule Sets, and Status of Each Capture Process
To display this general information about each capture process in a database, run the following query:

COLUMN CAPTURE_NAME HEADING 'Capture|Process|Name' FORMAT A15
COLUMN QUEUE_NAME HEADING 'Capture|Process|Queue' FORMAT A15
COLUMN RULE_SET_NAME HEADING 'Positive|Rule Set' FORMAT A15
COLUMN NEGATIVE_RULE_SET_NAME HEADING 'Negative|Rule Set' FORMAT A15
COLUMN STATUS HEADING 'Capture|Process|Status' FORMAT A15

SELECT CAPTURE_NAME, QUEUE_NAME, RULE_SET_NAME, NEGATIVE_RULE_SET_NAME, STATUS
FROM DBA_CAPTURE;

Capture         Capture                                         Capture
Process         Process         Positive        Negative        Process
Name            Queue           Rule Set        Rule Set        Status
--------------- --------------- --------------- --------------- ---------------
STREAMS_DATA_C STREAMS_DATA_C RULESET$_54                     ENABLED
 _Q

If the status of a capture process is ABORTED, then you can query the ERROR_NUMBER and ERROR_MESSAGE columns in the DBA_CAPTURE data dictionary view to determine the error.

2)    Displaying Change Capture Information About Each Capture Process
The query in this section displays the following information about each capture process in a database:
The current state of the capture process: INITIALIZING,WAITING FOR DICTONARY REDO, DICTIONARY INITIALIZATION, MINING, LOADING, CAPTURING CHANGES, WAITING FOR REDO, EVALUATING RULE, CREATING LCR, ENQUEUING MESSAGE, PAUSED FOR FLOW CONTROL, SHUTTING DOWN
The total number of redo entries passed by LogMiner to the capture process for detailed rule evaluation. A capture process converts a redo entry into a message and performs detailed rule evaluation on the message when capture process prefiltering cannot discard the change.

COLUMN CAPTURE_NAME HEADING 'Capture|Name' FORMAT A15
COLUMN PROCESS_NAME HEADING 'Capture|Process|Number' FORMAT A7
COLUMN SID HEADING 'Session|ID' FORMAT 9999
COLUMN SERIAL# HEADING 'Session|Serial|Number' FORMAT 9999
COLUMN STATE HEADING 'State' FORMAT A27
COLUMN TOTAL_MESSAGES_CAPTURED HEADING 'Redo|Entries|Evaluated|In Detail' FORMAT 9999999
COLUMN TOTAL_MESSAGES_ENQUEUED HEADING 'Total|LCRs|Enqueued' FORMAT 999999

SELECT c.CAPTURE_NAME,
 SUBSTR(s.PROGRAM,INSTR(s.PROGRAM,'(')+1,4) PROCESS_NAME,
 c.SID,
 c.SERIAL#,
 c.STATE,
 c.TOTAL_MESSAGES_CAPTURED,
 c.TOTAL_MESSAGES_ENQUEUED
 FROM V$STREAMS_CAPTURE c, V$SESSION s
 WHERE c.SID = s.SID AND
 c.SERIAL# = s.SERIAL#;
Redo
 Capture         Session                               Entries
Capture         Process Session  Serial                             Evaluated
Name            Number       ID  Number State                       In Detail
--------------- ------- ------- ------- --------------------------- ---------
 Total
 LCRs
Enqueued
--------
STREAMS_DATA_C CP01        217    2119 WAITING FOR TRANSACTION      ########
 #######

3)    Displaying State Change and Message Creation Time for Each Capture Process

COLUMN CAPTURE_NAME HEADING 'Capture|Name' FORMAT A15
COLUMN STATE HEADING 'State' FORMAT A27
COLUMN STATE_CHANGED HEADING 'State|Change Time'
COLUMN CREATE_MESSAGE HEADING 'Last Message|Create Time'

SELECT CAPTURE_NAME,
 STATE,
 TO_CHAR(STATE_CHANGED_TIME, 'HH24:MI:SS MM/DD/YY') STATE_CHANGED,
 TO_CHAR(CAPTURE_MESSAGE_CREATE_TIME, 'HH24:MI:SS MM/DD/YY') CREATE_MESSAGE
 FROM V$STREAMS_CAPTURE;

Capture                                     State             Last Message
Name            State                       Change Time       Create Time
--------------- --------------------------- ----------------- -----------------
STREAMS_DATA_C WAITING FOR TRANSACTION     13:03:11 01/26/10 13:03:13 01/26/10

4)    Displaying Elapsed Time Performing Capture Operations for Each Capture Process
To display this information for each capture process in a database, run the following query:

COLUMN CAPTURE_NAME HEADING 'Capture|Name' FORMAT A15
COLUMN ELAPSED_CAPTURE_TIME HEADING 'Elapsed|Capture|Time' FORMAT 99999999.99
COLUMN ELAPSED_RULE_TIME HEADING 'Elapsed|Rule|Evaluation|Time' FORMAT 99999999.99
COLUMN ELAPSED_ENQUEUE_TIME HEADING 'Elapsed|Enqueue|Time' FORMAT 99999999.99
COLUMN ELAPSED_LCR_TIME HEADING 'Elapsed|LCR|Creation|Time' FORMAT 99999999.99
COLUMN ELAPSED_PAUSE_TIME HEADING 'Elapsed|Pause|Time' FORMAT 99999999.99

SELECT CAPTURE_NAME,
 (ELAPSED_CAPTURE_TIME/100) ELAPSED_CAPTURE_TIME,
 (ELAPSED_RULE_TIME/100) ELAPSED_RULE_TIME,
 (ELAPSED_ENQUEUE_TIME/100) ELAPSED_ENQUEUE_TIME,
 (ELAPSED_LCR_TIME/100) ELAPSED_LCR_TIME,
 (ELAPSED_PAUSE_TIME/100) ELAPSED_PAUSE_TIME
 FROM V$STREAMS_CAPTURE;

Elapsed                   Elapsed
 Elapsed         Rule      Elapsed          LCR      Elapsed
Capture              Capture   Evaluation      Enqueue     Creation        Pause
Name                    Time         Time         Time         Time         Time
--------------- ------------ ------------ ------------ ------------ ------------
STREAMS_DATA_C       279.21          .26          .43          .26          .00

5)    Displaying Information About Each Downstream Capture Process
A downstream capture is a capture process runs on a database other than the source database.
You can display the following information about each downstream capture process in a database by running the query in this section:

COLUMN CAPTURE_NAME HEADING 'Capture|Process|Name' FORMAT A15
COLUMN SOURCE_DATABASE HEADING 'Source|Database' FORMAT A15
COLUMN QUEUE_NAME HEADING 'Capture|Process|Queue' FORMAT A15
COLUMN STATUS HEADING 'Capture|Process|Status' FORMAT A15
COLUMN USE_DATABASE_LINK HEADING 'Uses|Database|Link?' FORMAT A8

SELECT CAPTURE_NAME,
 SOURCE_DATABASE,
 QUEUE_NAME,
 STATUS,
 USE_DATABASE_LINK
 FROM DBA_CAPTURE
 WHERE CAPTURE_TYPE = 'DOWNSTREAM';

6)    Displaying the Registered Redo Log Files for Each Capture Process
This query displays information about these files for both local capture processes and downstream capture processes.

COLUMN CONSUMER_NAME HEADING 'Capture|Process|Name' FORMAT A15
COLUMN SOURCE_DATABASE HEADING 'Source|Database' FORMAT A10
COLUMN SEQUENCE# HEADING 'Sequence|Number' FORMAT 99999
COLUMN NAME HEADING 'Archived Redo Log|File Name' FORMAT A20
COLUMN DICTIONARY_BEGIN HEADING 'Dictionary|Build|Begin' FORMAT A10
COLUMN DICTIONARY_END HEADING 'Dictionary|Build|End' FORMAT A10

SELECT r.CONSUMER_NAME,
 r.SOURCE_DATABASE,
 r.SEQUENCE#,
 r.NAME,
 r.DICTIONARY_BEGIN,
 r.DICTIONARY_END
 FROM DBA_REGISTERED_ARCHIVED_LOG r, DBA_CAPTURE c
 WHERE r.CONSUMER_NAME = c.CAPTURE_NAME;

Capture                                                  Dictionary Dictionary
Process         Source     Sequence Archived Redo Log    Build      Build
Name            Database     Number File Name            Begin      End
--------------- ---------- -------- -------------------- ---------- ----------
STREAMS_DATA_C   PROD         7175 /mnt/logs/oradata/   NO         NO
 PROD/arch/1_7175_732278995.arc

STREAMS_DATA_C   PROD         7176 /mnt/logs/oradata/   NO         NO
 PROD/arch/1_7176_732278995.arc

STREAMS_DATA_C   PROD         7177 /mnt/logs/oradata/   NO         NO
 PROD/arch/1_7177_732278995.arc

7)    Displaying the Redo Log Files that Are Required by Each Capture Process
A capture process needs the redo log file that includes the required checkpoint SCN, and all subsequent redo log files.
You can query the REQUIRED_CHECKPOINT_SCN column in the DBA_CAPTURE data dictionary view to determine the required checkpoint SCN for a capture process. Redo log files prior to the redo log file that contains the required checkpoint SCN are no longer needed by the capture process. These redo log files can be stored offline if they are no longer needed for any other purpose. If you reset the start SCN for a capture process to a lower value in the future, then these redo log files might be needed.
To display this information about each required archive redo log file in a database, run the following query:

COLUMN CONSUMER_NAME HEADING 'Capture|Process|Name' FORMAT A15
COLUMN SOURCE_DATABASE HEADING 'Source|Database' FORMAT A10
COLUMN SEQUENCE# HEADING 'Sequence|Number' FORMAT 99999
COLUMN NAME HEADING 'Required|Archived Redo Log|File Name' FORMAT A40

SELECT r.CONSUMER_NAME,
 r.SOURCE_DATABASE,
 r.SEQUENCE#,
 r.NAME
 FROM DBA_REGISTERED_ARCHIVED_LOG r, DBA_CAPTURE c
 WHERE r.CONSUMER_NAME =  c.CAPTURE_NAME AND
 r.NEXT_SCN      >= c.REQUIRED_CHECKPOINT_SCN;

Capture                             Required
Process         Source     Sequence Archived Redo Log
Name            Database     Number File Name
--------------- ---------- -------- ----------------------------------------
STREAMS_DATA_C PROD        13418 /mnt/logs/oradata/PROD/arch/2_13418_732278995.arc
STREAMS_DATA_C PROD        10762 /mnt/logs/oradata/PROD/arch/1_10762_732278995.arc

8)    Displaying SCN Values for Each Redo Log File Used by Each Capture Process:

You can display information about the SCN values for archived redo log files that are registered for each capture process in a database by running the query in this section.
This query displays information the SCN values for these files for both local capture processes and downstream capture processes. This query also identifies redo log files that are no longer needed by any capture process at the local database.
To display this information about each registered archive redo log file in a database, run the following query:

COLUMN CONSUMER_NAME HEADING 'Capture|Process|Name' FORMAT A15
COLUMN NAME HEADING 'Archived Redo Log|File Name' FORMAT A25
COLUMN FIRST_SCN HEADING 'First SCN' FORMAT 99999999999
COLUMN NEXT_SCN HEADING 'Next SCN' FORMAT 99999999999
COLUMN PURGEABLE HEADING 'Purgeable?' FORMAT A10

SELECT r.CONSUMER_NAME,
 r.NAME,
 r.FIRST_SCN,
 r.NEXT_SCN,
 r.PURGEABLE
 FROM DBA_REGISTERED_ARCHIVED_LOG r, DBA_CAPTURE c
 WHERE r.CONSUMER_NAME = c.CAPTURE_NAME;

Capture
Process         Archived Redo Log
Name            File Name                    First SCN     Next SCN Purgeable?
--------------- ------------------------- ------------ ------------ ----------
STREAMS_DATA_C /mnt/logs/oradata/PROD/           7499664298   7499908128 NO
 arch/1_7175_732278995.arc

STREAMS_DATA_C /mnt/logs/oradata/PROD/           7499908128   7500009109 NO
 arch/1_7176_732278995.arc

9) Displaying the Last Archived Redo Entry Available to Each Capture Process
For a local capture process, the last archived redo entry available is the last entry from the online redo log flushed to an archived log file.
For a downstream capture process, the last archived redo entry available is the redo entry with the most recent SCN in the last archived log file added to the LogMiner session used by the capture process.

COLUMN CAPTURE_NAME HEADING 'Capture|Name' FORMAT A20
COLUMN LOGMINER_ID HEADING 'LogMiner ID' FORMAT 9999
COLUMN AVAILABLE_MESSAGE_NUMBER HEADING 'Last Redo SCN' FORMAT 9999999999
COLUMN AVAILABLE_MESSAGE_CREATE_TIME HEADING 'Time of|Last Redo SCN'

SELECT CAPTURE_NAME,
 LOGMINER_ID,
 AVAILABLE_MESSAGE_NUMBER,
 TO_CHAR(AVAILABLE_MESSAGE_CREATE_TIME, 'HH24:MI:SS MM/DD/YY')
 AVAILABLE_MESSAGE_CREATE_TIME
 FROM V$STREAMS_CAPTURE;

Capture                                        Time of
Name                 LogMiner ID Last Redo SCN Last Redo SCN
-------------------- ----------- ------------- -----------------
STREAMS_DATA_C                9    8087814747 23:13:18 10/26/10

10)    Listing the Parameter Settings for Each Capture Process
Following query displays the current setting for each capture process parameter for each capture process in a database:

COLUMN CAPTURE_NAME HEADING 'Capture|Process|Name' FORMAT A25
COLUMN PARAMETER HEADING 'Parameter' FORMAT A25
COLUMN VALUE HEADING 'Value' FORMAT A10
COLUMN SET_BY_USER HEADING 'Set by User?' FORMAT A15

SELECT CAPTURE_NAME,
 PARAMETER,
 VALUE,
 SET_BY_USER 
 FROM DBA_CAPTURE_PARAMETERS;

Capture
Process
Name                      Parameter                 Value      Set by User?
------------------------- ------------------------- ---------- ---------------
STREAMS_DATA_C           PARALLELISM               1          NO
STREAMS_DATA_C           STARTUP_SECONDS           0          NO
STREAMS_DATA_C           TRACE_LEVEL               0          NO
STREAMS_DATA_C           TIME_LIMIT                INFINITE   NO
STREAMS_DATA_C           MESSAGE_LIMIT             INFINITE   NO
STREAMS_DATA_C           MAXIMUM_SCN               INFINITE   NO
STREAMS_DATA_C           WRITE_ALERT_LOG           Y          NO
STREAMS_DATA_C           DISABLE_ON_LIMIT          N          NO
STREAMS_DATA_C           DOWNSTREAM_REAL_TIME_MINE Y          NO
STREAMS_DATA_C           IGNORE_TRANSACTION                   NO
STREAMS_DATA_C           MESSAGE_TRACKING_FREQUENC 2000000    NO
 Y

STREAMS_DATA_C           IGNORE_UNSUPPORTED_TABLE  *          NO
STREAMS_DATA_C           SKIP_AUTOFILTERED_TABLE_D Y          NO
 DL

STREAMS_DATA_C           SPLIT_THRESHOLD           1800       NO
STREAMS_DATA_C           MERGE_THRESHOLD           60         NO
STREAMS_DATA_C           CAPTURE_SEQUENCE_NEXTVAL  N          NO
STREAMS_DATA_C           XOUT_CLIENT_EXISTS        N          NO
STREAMS_DATA_C           EXCLUDEUSER                          NO
STREAMS_DATA_C           EXCLUDEUSERID                        NO
STREAMS_DATA_C           EXCLUDETRANS                         NO
STREAMS_DATA_C           GETREPLICATES             N          NO
STREAMS_DATA_C           GETAPPLOPS                Y          NO
STREAMS_DATA_C           CAPTURE_IDKEY_OBJECTS     N          NO
STREAMS_DATA_C           MAX_SGA_SIZE              INFINITE   NO

11)    Viewing the Extra Attributes Captured by Each Capture Process
You can use the INCLUDE_EXTRA_ATTRIBUTE procedure in the DBMS_CAPTURE_ADM package to instruct a capture process to capture one or more extra attributes from the redo log.
The following query displays the extra attributes included in the LCRs captured by each capture process in the local database:

COLUMN CAPTURE_NAME HEADING 'Capture Process' FORMAT A20
COLUMN ATTRIBUTE_NAME HEADING 'Attribute Name' FORMAT A15
COLUMN INCLUDE HEADING 'Include Attribute in LCRs?' FORMAT A30

SELECT CAPTURE_NAME, ATTRIBUTE_NAME, INCLUDE
 FROM DBA_CAPTURE_EXTRA_ATTRIBUTES
 ORDER BY CAPTURE_NAME;

Capture Process      Attribute Name  Include Attribute in LCRs?
-------------------- --------------- ------------------------------
STREAMS_DATA_C      THREAD#         NO
STREAMS_DATA_C      ROW_ID          NO
STREAMS_DATA_C      TX_NAME         NO
STREAMS_DATA_C      SERIAL#         NO
STREAMS_DATA_C      USERNAME        NO
STREAMS_DATA_C      SESSION#        NO

12) Determining the Applied SCN for All Capture Processes in a Database
The applied system change number (SCN) for a capture process is the SCN of the most recent message dequeued by the relevant apply processes. All changes below this applied SCN have been dequeued by all apply processes that apply changes captured by the capture process.

COLUMN CAPTURE_NAME HEADING 'Capture Process Name' FORMAT A30
COLUMN APPLIED_SCN HEADING 'Applied SCN' FORMAT 99999999999

SELECT   CAPTURE_NAME, APPLIED_SCN FROM DBA_CAPTURE;

Capture Process      APPLIED_SCN
-------------------- -----------
STREAMS_DATA_C       8087783293

13) Determining Redo Log Scanning Latency for Each Capture Process

COLUMN CAPTURE_NAME HEADING 'Capture|Process|Name' FORMAT A10
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,
 ((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 V$STREAMS_CAPTURE;

Capture    Latency               Current
Process         in Seconds Since Process           Message
Name       Seconds   Last Status Time              Creation Time
---------- ------- ------------- ----------------- -----------------
INFO_CONTR       2             1 23:21:36 10/26/10 23:21:35 10/26/10
ACT_C

The “Latency in Seconds” returned by this query is the difference between the current time (SYSDATE) and the “Message Creation Time.” The “Seconds Since Last Status” returned by this query is the difference between the current time (SYSDATE) and the “Current Process Time.”

14)    Determining Message Enqueuing Latency for Each Capture Process
Run the following query to determine the message capturing latency for each capture process:

COLUMN CAPTURE_NAME HEADING 'Capture|Process|Name' FORMAT A10
COLUMN LATENCY_SECONDS HEADING 'Latency|in|Seconds' FORMAT 999999
COLUMN CREATE_TIME HEADING 'Message Creation|Time' FORMAT A20
COLUMN ENQUEUE_TIME HEADING 'Enqueue Time' FORMAT A20
COLUMN ENQUEUE_MESSAGE_NUMBER HEADING 'Message|Number' FORMAT 999999

SELECT CAPTURE_NAME,
 (ENQUEUE_TIME-ENQUEUE_MESSAGE_CREATE_TIME)*86400 LATENCY_SECONDS,
 TO_CHAR(ENQUEUE_MESSAGE_CREATE_TIME, 'HH24:MI:SS MM/DD/YY') CREATE_TIME,
 TO_CHAR(ENQUEUE_TIME, 'HH24:MI:SS MM/DD/YY') ENQUEUE_TIME,
 ENQUEUE_MESSAGE_NUMBER
 FROM V$STREAMS_CAPTURE;

Capture    Latency
Process         in Message Creation                          Message
Name       Seconds Time                 Enqueue Time          Number
---------- ------- -------------------- -------------------- -------
STREAMS_DATA   -3 23:22:25 10/26/10    23:22:22 10/26/10    #######

15)    Displaying Information About Rule Evaluations for Each Capture Process

COLUMN CAPTURE_NAME HEADING 'Capture|Name' FORMAT A15
COLUMN TOTAL_PREFILTER_DISCARDED HEADING 'Prefilter|Changes|Discarded'
 FORMAT 9999999999
COLUMN TOTAL_PREFILTER_KEPT HEADING 'Prefilter|Changes|Kept' FORMAT 9999999999
COLUMN TOTAL_PREFILTER_EVALUATIONS HEADING 'Prefilter|Evaluations'
 FORMAT 9999999999
COLUMN UNDECIDED HEADING 'Undecided|After|Prefilter' FORMAT 9999999999
COLUMN TOTAL_FULL_EVALUATIONS HEADING 'Full|Evaluations' FORMAT 9999999999

SELECT CAPTURE_NAME,
 TOTAL_PREFILTER_DISCARDED,
 TOTAL_PREFILTER_KEPT,
 TOTAL_PREFILTER_EVALUATIONS,
 (TOTAL_PREFILTER_EVALUATIONS -
 (TOTAL_PREFILTER_KEPT + TOTAL_PREFILTER_DISCARDED)) UNDECIDED,
 TOTAL_FULL_EVALUATIONS
 FROM V$STREAMS_CAPTURE;

Prefilter   Prefilter               Undecided
Capture            Changes     Changes   Prefilter       After        Full
Name             Discarded        Kept Evaluations   Prefilter Evaluations
--------------- ---------- ----------- ----------- ----------- -----------
STREAMS_DATA_C   53128265     1662026    54790291           0         801

The total number of prefilter evaluations equals the sum of the prefilter changes discarded, the prefilter changes kept, and the undecided changes.